ACCESS TABLE EXISTS-ADO

  • Thread starter Thread starter AL
  • Start date Start date
A

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.
 
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
 
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
 
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
 
Back
Top