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

[Ai_Jun_Zhang]

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 wa
that I can use ADO to check if a table exists in this database?

Thanks,

Aijun

 

[Rowan]

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 OLEDBatabase 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

 

[michelxld]

Hello Aijun

this macro list the Access tables . you may use it to check if a tabl
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
miche

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[michelxld]

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

 

[Ai_Jun_Zhang]

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

Aijun.