Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    table exists? (Access2k)

    How do I find out whether a table exists in another database by using vb codes??

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table exists? (Access2k)

    I have not used this, but saw it and thought it may be applicable. It is for Access 97, but should lead you in the right direction. HTH
    <A target="_blank" HREF=http://www.mvps.org/access/modules/mdl0014.htm>Check if Object Exists</A>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: table exists? (Access2k)

    What are you trying to do? Is the other database linked to the current database? Are you trying to link to the table? Are you looking for a particular table or just any non-system table?
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table exists? (Access2k)

    a particular table called tblData
    what I want to do is create a linked table if the table is not already there in that program.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table exists? (Access2k)

    for some reason I can't open the database. Would u be able to open it and copy and paste the codes into a word document for me? PLEASE???

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: table exists? (Access2k)

    It would not help you. That code only looks in the CurrentDb and you want to know if a table exists in a remote database.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: table exists? (Access2k)

    Wait a minute. Create a linked table where? I thought you said you wanted to test whether a table existed in *another* database.

    Which database are you trying to create a link in? If you want to link a table to the current database, just try to create the link. If you succeed, then the table was there in the remote database. If you want to know whether a table exists in the current database, just try to set a tabledef object variable and trap any errors. That will tell you whether the tabledef already exists in the current database.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table exists? (Access2k)

    Thanx Charlotte.
    What I wanted to do was to create a linked table in another database. I don't have to do this if the linked table exists. I wanted to do this coz I will finish my placement soon and won't be here to do that manually. The company I work for upgrades the other database every now and then and when they do do that then the linked table will be deleted and this is when I have to create that link.

    All these coding is TOOOO much for me so I thought I'd just put that in my documentation and they could help themselves.
    Thanx guys.

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  9. #9
    New Lounger
    Join Date
    Jan 2001
    Location
    Vermont
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table exists? (Access2k)

    Here's some code that might help. Given a table name, it will check to see if the table exists in the current database or in another database. To call if for the current database, say

    if adoTableExists("TableName",adoConnection:=currentp roject.connection) then ...

    To call it for another database, say
    if adoTableExists("TableName",MDBPathname:="mydirmyfi le.mdb") then ...

    Hope it helps.
    Jordan

    Code:

    Function adoTableExists(TableName As String, Optional adoConnection As ADODB.Connection, Optional MDBPathName As String) As Boolean
    Dim strCurrentConnectionString As String
    Dim bolCloseConnection As Boolean
    Dim objTable As Object
    Dim adoxCatalog As ADOX.Catalog
    Set adoxCatalog = New ADOX.Catalog
    On Error GoTo adoTableExistsError

    If adoConnection Is Nothing Then
    If Dir(MDBPathName) = "" Then
    adoTableExists = False
    Exit Function
    End If

    Set adoConnection = New ADODB.Connection
    strCurrentConnectionString = Left(CurrentProject.Connection.ConnectionString, _
    InStr(";" & CurrentProject.Connection.ConnectionString, ";Data Source=") - 1)
    strCurrentConnectionString = strCurrentConnectionString & "Data Source=" & MDBPathName & "" & _
    Mid(CurrentProject.Connection.ConnectionString, _
    InStr(Len(strCurrentConnectionString) + 1, CurrentProject.Connection.ConnectionString, ";"))
    adoConnection.Open strCurrentConnectionString
    bolCloseConnection = True
    End If

    adoxCatalog.ActiveConnection = adoConnection

    adoTableExists = False
    For Each objTable In adoxCatalog.Tables
    If LCase(objTable.Name) = LCase(TableName) Then
    adoTableExists = True
    Exit For
    End If
    Next objTable

    adoTableExistsError:
    If Not adoxCatalog Is Nothing Then
    Set adoxCatalog.ActiveConnection = CurrentProject.Connection
    Set adoxCatalog = Nothing
    End If
    If bolCloseConnection And Not adoConnection Is Nothing And adoConnection.Mode <> 0 Then
    adoConnection.Close
    End If
    If bolCloseConnection And Not adoConnection Is Nothing Then
    Set adoConnection = Nothing
    End If
    On Error GoTo 0

    End Function

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table exists? (Access2k)

    Thanx Jordan,
    I decided not to do that since I've only got 2 days left till I leave and wouldn't have time to do anything but testing the program.
    I might use it in future.
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •