how to check if table exists in Access with ADO?

I need to check if a table exists in Access using ADO externally. I
tried

"Select Name from MSysObjects Where Name = 'myTable'"

with the ADO command object, but I got an error than I don't have
permission to read MySysObjects. Is there another way to check without
having to use On Error Resume Next?

Thanks,
GG



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: how to check if table exists in Access with ADO?

ADO is only half the story. For information on the database schema, you need
the ADOX library so you can examine the Tables collection of the Catalog.

Unfortunately, ADOX is really half-baked and unreliable.

It should be possible to read MSysObjects though (unless you are blocked by
MDW security).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"G G" <anonymous@discussions.com> wrote in message
news:4097c919$0$202$75868355@news.frii.net...[color=blue]
> I need to check if a table exists in Access using ADO externally. I
> tried
>
> "Select Name from MSysObjects Where Name = 'myTable'"
>
> with the ADO command object, but I got an error than I don't have
> permission to read MySysObjects. Is there another way to check without
> having to use On Error Resume Next?[/color]

re: how to check if table exists in Access with ADO?

Thanks. Basically, I want to drop a table if it exists and then
recreate it. I guess On Error Resume Next it is.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: how to check if table exists in Access with ADO?

G G <anonymous@discussions.com> wrote in news:4097c919$0$202$75868355
@news.frii.net:
[color=blue]
> I need to check if a table exists in Access using ADO externally. I
> tried
>
> "Select Name from MSysObjects Where Name = 'myTable'"
>
> with the ADO command object, but I got an error than I don't have
> permission to read MySysObjects. Is there another way to check without
> having to use On Error Resume Next?
>
> Thanks,
> GG[/color]

You could try

TableExists = Not CurrentProject.Connection.OpenSchema(adSchemaTable s, _
Array(Empty, Empty, "Name of Particular Table")).BOF

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

re: how to check if table exists in Access with ADO?

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:4097cbbc$0$4321$5a62ac22@freenews.iinet.net.a u:
[color=blue]
> ADO is only half the story. For information on the database schema, you
> need the ADOX library so you can examine the Tables collection of the
> Catalog.[/color]

Why do you say that?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

re: how to check if table exists in Access with ADO?

There was actually one other thing I tried, along the same vein as ADOX.
I created a DropTable Function and a CreateTable Function. If the
DropTable function errors out I just resume next within the function and
exit out to the next call. Now, at least, the Resume Next's are
isolated within a function instead of all over the whole routine. I
can't figure out why I can't read MSysObjects with this:

strSql = "Select Name From MSysObjects Where Name = 'tbl1'"
cmd.CommandType = adCmdText
cmd.CommandText = strSql
cmd.Execute i

using acc97. will try later on a2k, a2002. In the meantime, the
functions seem to do the trick.

Thanks all for your replies.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: how to check if table exists in Access with ADO?

Will the new table have the same structure as the one you're deleting? If
so, it may be easier to just delete all of the data in the table.

--
Wayne Morgan
Microsoft Access MVP


"G G" <anonymous@discussions.com> wrote in message
news:4097d95f$0$206$75868355@news.frii.net...[color=blue]
> Thanks. Basically, I want to drop a table if it exists and then
> recreate it. I guess On Error Resume Next it is.[/color]

re: how to check if table exists in Access with ADO?

Well, in this particular project, an end user will be selecting any
arbitrary (already existing) mdb file from an Open/Save Dialog box
(using api code) to pass specifically selected records from a
Non-Microsoft datasource. XYZ.mdb probably won't contain the temp table
tblxxxyyyzzz. But say the end user has already retrieved records from
the external datasource to the selected mdb file XYZ.mdb. If the end
user decides to select other records from the datasource to the same mdb
file, tblxxxyyyzzz will be dropped and a new tblxxxyyyzzz will be
re-created on the fly and the new selection of records will be
transferred to the table. If the end user wants to accumulate the
records, the records will have to be transferred to another table with
the same schema as tblxxxyyyzzz.

GG



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!