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

--
Ai_Jun_Zhan
-----------------------------------------------------------------------
Ai_Jun_Zhang's Profile: http://www.excelforum.com/member.php...fo&userid=2547
View this thread: http://www.excelforum.com/showthread.php?threadid=39799

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

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

--
michelxl
-----------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...fo&userid=1736
View this thread: http://www.excelforum.com/showthread.php?threadid=39799

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"
wrote in message
news:Ai_Jun_Zhang.1u72ub_1124762717.6606@excelforu m-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

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


--
michelxld
------------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
View this thread: http://www.excelforum.com/showthread...hreadid=397999

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

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