Using ADO in EXCEL VBA, I'd like to check if an Access
table exists before I run my query. Is this possible
using ADO?

Thanks in advance.

Hi

If you have reference to the ADO Ext library then this function (from MSDN)
can be modified:

Sub ADOListTables()

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"

' Loop through the tables in the database and print their name
For Each tbl In cat.Tables
If tbl.Type < "VIEW" Then Debug.Print tbl.Name
Next

End Sub

Otherwise, you could just search the table for anything. If empty or error
then it doesn't exist:

Function TableXists(strTableName As String) As Boolean
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
On Error GoTo Bad
rst.Open _
"SELECT * FROM " & strTableName, _
cnn, adOpenForwardOnly, adLockReadOnly
TableXists = Not (rst.EOF)

rst.Close
Exit Function
Bad:
TableXists = False
End Function

Sub test()
MsgBox TableXists("Customers")
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please

"AL" skrev i melding
...
Using ADO in EXCEL VBA, I'd like to check if an Access
table exists before I run my query. Is this possible
using ADO?

Thanks in advance.

Hi Harald,

I've been having trouble reaching you by email. Could you send me one
please?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Sure. Sorry. Will do.

Best wishes Harald


"Jan Karel Pieterse" skrev i melding
...
Hi Harald,

I've been having trouble reaching you by email. Could you send me one
please?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Hi Harald,

Will do.

Thanks.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

"Harald Staff" wrote in message ...

you could just search the table for anything. If empty or error
then it doesn't exist

Harold, Surely an empty table *does* exist?!

--

Harold, Surely an empty table *does* exist?!

Definitely :-), but our friend wants do run this before doing a query. I
assume this table has something to do with the query, and if so it would
return nothing more from an empty table than from a non-existing one.

But I may be wrong, perhaps he's just an unusually exploring and curious
person <bg

Best wishes Harald

How about trying a query which uses the table and see whether you get
an error, either a VBA run-time error and/or look in the ADO
Connection object's Errors collection. Use a query with low
processing: no need to return any rows or columns e.g.

SELECT 1 FROM MyTable WHERE 0=1

--

"AL" wrote in message ...
Using ADO in EXCEL VBA, I'd like to check if an Access
table exists before I run my query. Is this possible
using ADO?

Thanks in advance.