ACCESS TABLE EXISTS-ADO

[AL]

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.

 

[Harald Staff]

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

 

[Jan Karel Pieterse]

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

 

[Harald Staff]

Sure. Sorry. Will do.

Best wishes Harald

 

[Jan Karel Pieterse]

Hi Harald,

Will do.

Thanks.

Regards,

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

 

[onedaywhen]

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

 

[onedaywhen]

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

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

--

 

[Harald Staff]

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