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.

Here is a simple function. Call with

?IfTableExists("C:\temp\MtDB.mdb","DB")

Function IfTableExists(FileName As String, Table As String) As Boolean
Dim oConn As Object

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
FileName

On Error Resume Next
oConn.Execute "SELECT 1 FROM [" & Table & "] WHERE 0=1"
IfTableExists = (Err.Number = 0)

oConn.Close
Set oConn = Nothing

End Function



--

HTH

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


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

Thanks, Bob. It works and I really appreciate your help.

Bob, I have a side question. Is there any way this forum can notify me by email if somebody has responded to my question? It will be very convenient for me. Otherwise, as I am doing now, I have to check the forum periodically to see if anybody has responded to my email.

Thanks,

Aijun.

Hi Aijun,

I am afraid that I cannot help you with that question. I do not subscribe to
the forum, I connect to the Microsoft public newsgroups, where questions on
your forum are forwarded to. You would need to raise it there, but my
experience is that it's all or noting with these forums, you get an email
about all posts, or none.

Sorry I could not help more.

Bob


"Ai_Jun_Zhang" <[email protected]>
wrote in message
news:[email protected]...
>
> Thanks, Bob. It works and I really appreciate your help.
>
> Bob, I have a side question. Is there any way this forum can notify me
> by email if somebody has responded to my question? It will be very
> convenient for me. Otherwise, as I am doing now, I have to check the
> forum periodically to see if anybody has responded to my email.
>
> 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=397998
>