+ Reply to Thread
Results 1 to 6 of 6

How can I use VBA ADO to check if a table exists in an Access DB?

  1. #1
    Registered User
    Join Date
    07-21-2005
    Posts
    28

    How can I use VBA ADO to check if a table exists in an Access DB?

    How can I use VBA ADO to check if a table exists in an Access DB?

    --------------------------------------------------------------------------------

    Hi!

    If I aready have an access DB called c:\temp\MyDb.mdb, is there any way that I can use ADO to check if a table exists in this database?

    Thanks,

    Aijun.

  2. #2
    Forum Contributor
    Join Date
    12-11-2004
    MS-Off Ver
    2007
    Posts
    137
    Hello Aijun

    this macro list the Access tables . you may use it to check if a table exist
    i hope this help you


    Sub ListTables()
    Dim Conn As ADODB.Connection
    Dim rsT As ADODB.Recordset

    Set Conn = New ADODB.Connection
    With Conn
    .Provider = "Microsoft.JET.OLEDB.4.0"
    .Open ThisWorkbook.Path & "C:\MaBase_V01.mdb"
    End With

    Set rsT = Conn.OpenSchema(adSchemaTables)

    While Not rsT.EOF
    If rsT.Fields("TABLE_TYPE") = "TABLE" Then _
    Debug.Print rsT.Fields("TABLE_NAME")

    rsT.MoveNext
    Wend

    Set rsT = Nothing
    Conn.Close
    End Sub



    regards
    michel

  3. #3
    Rowan
    Guest

    RE: How can I use VBA ADO to check if a table exists in an Access DB?

    From a post by michelxld:

    Dim cnn As New ADODB.Connection
    Dim rsT As ADODB.Recordset
    Dim Verif As Boolean
    Dim dbName As String

    Set cnn = New Connection
    dbName = ("C:\Data\MYDataBase1.mdb")
    With cnn
    ..Provider = "Microsoft.Jet.OLEDB.4.0"
    ..Mode = adModeWrite
    ..Properties("Jet OLEDB:Database Password") = "abc"
    ..Open dbName
    End With

    Set rsT = cnn.OpenSchema(adSchemaTables)

    Verif = False
    While Not rsT.EOF
    If rsT.Fields("TABLE_NAME") = "MYTABLE" Then Verif = True
    rsT.MoveNext
    Wend

    If Verif = False Then
    MsgBox "The Table does not Exist ."
    Else
    MsgBox "the table exists"
    End If

    cnn.Close
    Set cnn = Nothing
    Set rsT = Nothing

    Hope this helps
    Rowan

    "Ai_Jun_Zhang" wrote:

    >
    > How can I use VBA ADO to check if a table exists in an Access DB?
    >
    > --------------------------------------------------------------------------------
    >
    > Hi!
    >
    > If I aready have an access DB called c:\temp\MyDb.mdb, is there any way
    > that I can use ADO to check if a table exists in this database?
    >
    > Thanks,
    >
    > Aijun.
    >
    >
    > --
    > Ai_Jun_Zhang
    > ------------------------------------------------------------------------
    > Ai_Jun_Zhang's Profile: http://www.excelforum.com/member.php...o&userid=25474
    > View this thread: http://www.excelforum.com/showthread...hreadid=397999
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: How can I use VBA ADO to check if a table exists in an Access DB?

    See my response in .excel for a version that doesn't have to iterate through
    all tables

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Ai_Jun_Zhang" <Ai_Jun_Zhang.1u72ub_1124762717.6606@excelforum-nospam.com>
    wrote in message
    news:Ai_Jun_Zhang.1u72ub_1124762717.6606@excelforum-nospam.com...
    >
    > How can I use VBA ADO to check if a table exists in an Access DB?
    >
    > --------------------------------------------------------------------------

    ------
    >
    > Hi!
    >
    > If I aready have an access DB called c:\temp\MyDb.mdb, is there any way
    > that I can use ADO to check if a table exists in this database?
    >
    > Thanks,
    >
    > Aijun.
    >
    >
    > --
    > Ai_Jun_Zhang
    > ------------------------------------------------------------------------
    > Ai_Jun_Zhang's Profile:

    http://www.excelforum.com/member.php...o&userid=25474
    > View this thread: http://www.excelforum.com/showthread...hreadid=397999
    >




  5. #5
    Forum Contributor
    Join Date
    12-11-2004
    MS-Off Ver
    2007
    Posts
    137
    Hello Rowan , Hello Bob

    dear Bob , i'm interested by your version but i didn't find it
    could you place here your solution or the link

    thanks
    michel

  6. #6
    Registered User
    Join Date
    07-21-2005
    Posts
    28

    Thanks,

    Thank you all for the help. I really appreciated them. The code snip works great!

    Aijun.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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