Tabledefs object (DAO) in a .adp database application

[Tim]

I'm converting an mdb database application to use a SQL backend and moving it
to an ADP. One of the first things I do is check for the existance of a
table by looping through the tabledefs object. It works in the mdb, but not
in the ADP. I have the reference to DAO 3.6 and it recognizes the object
properties that I'm using, but it won't allow me to set a variable
obstantiated as a tabledefs object to the currentdb.tabledefs property.

Here's the code...

Dim tbls As TableDefs, tbl As TableDef
Dim fTableExists As Boolean

fTableExists = False
For Each tbl In CurrentDb.TableDefs
If tbl.Name = strTblName Then
fTableExists = True
Exit For
End If
Next tbl

Is there a different way that I should be trying to verifiy the existance of
a SQL table in an ADP?

I'm sure that I'm going to run across many different anomolies converting
over to an ADP and working with SQL, but I'm not sure how compatible the DAO
library is with the SQL back end. Any tips or good books that I can
reference for the conversion?

 

[Tim]

By the way, the code shows an attempt to bypass the tabledefs object by just
trying to loop through the currentdb.tabledefs object which obviously didn't
work...

 

[Alex Dybenko]

Hi,
DAO works in MDB only
in ADP you can use CurrentData.AllTables or ADO and query sysobjects table

--
Best regards,
___________
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

 

[Brendan Reynolds]

You can use DAO in an ADP to refer to an external Jet database, e.g. using
OpenDatabase, but in an ADP, CurrentDb returns Nothing. This is because
CurrentDb returns a reference to the current Jet database, and in an ADP
there isn't one.

You can use code very similar to the code you posted in an ADP, just loop
through the CurrentData.AllTables collection instead of the
CurrentDb.TableDefs collection. Check out the topics 'AllTables Collection'
and 'AccessObject Object' in the help file for more information.

 

[Tim]

I see that I'm going to need to move it over to ADO.


Thank you both for the replies. Hopefully the conversion from DAO to ADO
isn't too painfull...

 

[John Kounis]

I hope this helps. The following is a routine I copied off the web somewhere
(so I can't take credit for it). It's much more efficient than looping
through all the tables:

Set oRs = CurrentProject.Connection.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Tablename, "TABLE"))
fTableExists = Not oRs.EOF
oRs.Close

John Kounis