Check table exist in ADO

[Tim]

Hi folks,

Could anyone show me the code to check the table exist or not in ADO?

Thanks in advance.

Tim

 

[Alex Dybenko]

You can just try to open recordset on this table, if error - then table does
not exist

 

[David Lloyd]

Tim:

Alex's method will work. A second alternative is to use the OpenSchema
method of the ADO connection object. For example:

Function CheckTable() As Boolean
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty,
"MyTableName"))

If Not rs.EOF Then CheckTable = True

Set cn = Nothing
Set rs = Nothing

End Function


The field name in the recordset that contains the table name(s) is
"Table_Name".

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi folks,

Could anyone show me the code to check the table exist or not in ADO?

Thanks in advance.

Tim

 

[Dirk Goldgar]

Hi folks,

Could anyone show me the code to check the table exist or not in ADO?

Alex and David have answered your specific question, but it occurs to me
to wonder whether you really need to use ADO? If you're working in
Access 2000 or later and you're trying to see whether the table exists
in the current database, you can use the CurrentData.AllTables
collection, or -- unless it's an ADP -- the CurrentDb.TableDefs
collection. The former is a native Access approach, while the latter is
a DAO approach. With either of those collections you could either try
to access the table by name in the collection and trap the error that
occurs if it isn't there, or else loop through the collection checking
each item for a match on the name.

 

[Tim]

Hi folks,

Thanks a lot for the help.

Tim.

Alex and David have answered your specific question, but it occurs to me
to wonder whether you really need to use ADO? If you're working in
Access 2000 or later and you're trying to see whether the table exists
in the current database, you can use the CurrentData.AllTables
collection, or -- unless it's an ADP -- the CurrentDb.TableDefs
collection. The former is a native Access approach, while the latter is
a DAO approach. With either of those collections you could either try
to access the table by name in the collection and trap the error that
occurs if it isn't there, or else loop through the collection checking
each item for a match on the name.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Kalehm]

I Think I have almost the same problem. I need to create a condition in a
macro that if the link table exist do something and if the link is broken
then end the macro. Can someone help me on this one???