How do I varify one table existing in the ACCESS database ?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: How do I varify one table existing in the ACCESS database ?

  1. #1
    Ken Guest

    How do I varify one table existing in the ACCESS database ?


    In my VB application I used ADO object to connect to ACCESS database. I want
    to verify whether one table is in ACCESS database. How do I implement code
    to verify it? Thanks!

  2. #2
    Douglas J. Steele Guest

    Re: How do I varify one table existing in the ACCESS database ?

    http://www.able-consulting.com/ADOX_Faq.htm#Q6 at Carl Prothman's site.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele


    "Ken" <kzhao@lighthouse.org> wrote in message news:3d83ded8$1@10.1.10.29...
    >
    > In my VB application I used ADO object to connect to ACCESS database. I

    want
    > to verify whether one table is in ACCESS database. How do I implement code
    > to verify it? Thanks!




  3. #3
    Roy Guest

    Re: How do I varify one table existing in the ACCESS database ?

    You can also use DAO's TableDefs collection to do this.

    Roy

    "Ken" <kzhao@lighthouse.org> wrote in message news:3d83ded8$1@10.1.10.29...
    >
    > In my VB application I used ADO object to connect to ACCESS database. I want
    > to verify whether one table is in ACCESS database. How do I implement code
    > to verify it? Thanks!




  4. #4
    michael Guest

    Re: How do I varify one table existing in the ACCESS database ?


    Using ADO you could you the ADOX object however this will have to be distruted
    in the setup routine. The other alternative is to catch the error.

    michael

    Private Function TableExists(TableName As String) As Boolean
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Const ERR_TABLE_NOT_EXISTS As Long = -2147217865
    On Error GoTo LocalError

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\TWI\TWIData.mdb;Persist
    Security Info=False"
    If cn.State > 0 Then
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "Select * from " & TableName & " where 0=1", cn, adOpenStatic,
    adLockReadOnly, adCmdText
    Set rs.ActiveConnection = Nothing
    rs.Close
    cn.Close
    End If
    TableExists = True

    CleanUp:
    Set rs = Nothing
    Set cn = Nothing

    LocalError:
    If Err.Number = ERR_TABLE_NOT_EXISTS Then
    'TableExists=False
    Else
    'Message other errors
    MsgBox Err.Number & " - " & Err.Description
    End If

    Resume CleanUp
    End Function

    "Ken" <kzhao@lighthouse.org> wrote:
    >
    >In my VB application I used ADO object to connect to ACCESS database. I

    want
    >to verify whether one table is in ACCESS database. How do I implement code
    >to verify it? Thanks!



  5. #5
    Roy Guest

    Re: How do I varify one table existing in the ACCESS database ?

    I truly believe that when working against MS Access DAO is better choice simply because it's "natural" to Access's Jet engine. So
    consider doing this:

    Private Sub Form_Load()
    '===================
    Dim daoDB As Database
    Dim i%, TblExist As Boolean

    Set daoDB = OpenDatabase("c:\my documents\biblio.mdb")
    TblExist = False
    For i = 0 To daoDB.TableDefs.Count - 1
    If daoDB.TableDefs(i).Name = "Authors" Then
    TblExist = True
    End If
    Next i
    If TblExist = True Then
    'some code goes here
    End If

    End Sub

    Note: some error handler must be in order

    Roy

    "michael" <mmayfield_NOSPAM@houston.rr.com> wrote in message news:3d85d627$1@10.1.10.29...
    >
    > Using ADO you could you the ADOX object however this will have to be distruted
    > in the setup routine. The other alternative is to catch the error.
    >
    > michael
    >
    > Private Function TableExists(TableName As String) As Boolean
    > Dim cn As ADODB.Connection
    > Dim rs As ADODB.Recordset
    > Const ERR_TABLE_NOT_EXISTS As Long = -2147217865
    > On Error GoTo LocalError
    >
    > Set cn = New ADODB.Connection
    > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=L:\TWI\TWIData.mdb;Persist
    > Security Info=False"
    > If cn.State > 0 Then
    > Set rs = New ADODB.Recordset
    > rs.CursorLocation = adUseClient
    > rs.Open "Select * from " & TableName & " where 0=1", cn, adOpenStatic,
    > adLockReadOnly, adCmdText
    > Set rs.ActiveConnection = Nothing
    > rs.Close
    > cn.Close
    > End If
    > TableExists = True
    >
    > CleanUp:
    > Set rs = Nothing
    > Set cn = Nothing
    >
    > LocalError:
    > If Err.Number = ERR_TABLE_NOT_EXISTS Then
    > 'TableExists=False
    > Else
    > 'Message other errors
    > MsgBox Err.Number & " - " & Err.Description
    > End If
    >
    > Resume CleanUp
    > End Function
    >
    > "Ken" <kzhao@lighthouse.org> wrote:
    > >
    > >In my VB application I used ADO object to connect to ACCESS database. I

    > want
    > >to verify whether one table is in ACCESS database. How do I implement code
    > >to verify it? Thanks!

    >




  6. #6
    George Shubin Guest

    Re: How do I varify one table existing in the ACCESS database ?

    Ken:

    Here's a quick little routine I use:

    Function IsTable(sTableName As String) As Boolean
    'Determines if a data table exists in a database
    Dim oDb As ADODB.Connection
    Dim oRs As ADODB.Recordset
    Set oDb = New ADODB.Connection
    oDb.CursorLocation = adUseServer
    oDb.Open GetProvider(gsDriver, gsDataPath, gsDatabase, False) '<--- my
    function to provide a connection string
    Set oRs = oDb.OpenSchema(adSchemaTables, Array(Empty, Empty, sTableName,
    "TABLE"))
    If Not oRs.EOF Then
    IsTable = True
    Else
    IsTable = False
    End If
    oRs.Close
    Set oRs = Nothing
    oDb.Close
    Set oDb = Nothing
    End Function


    --
    ------------------------------------------------------------------------
    George Shubin Custom Software Development
    dX Software Systems Database Applications
    Ph: 503-981-6806 Fax: 503-982-0120
    www.dxonline.com george@dxonline.com
    ------------------------------------------------------------------------


    "Ken" <kzhao@lighthouse.org> wrote in message news:3d83ded8$1@10.1.10.29...
    >
    > In my VB application I used ADO object to connect to ACCESS database. I

    want
    > to verify whether one table is in ACCESS database. How do I implement code
    > to verify it? Thanks!




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center