Hi,
In order to get information about tables and views from a database using
ADO, you need to use ADOX, which is the ADO extension for Data Definition
Language and Security.
However I user only ADO 2.5 and 2.6, so I don't know if ADOX is available
for ADO 2.0 (although, it might be)
But there are few drawbacks with this approach (at least if you want to
modify a view)
From MSDN help:
"Important Although it is possible to create and modify a stored query in
an Access database by using Microsoft ActiveX® Data Objects Extensions for
Data Definition Language and Security (ADOX), if you do so your query won't
be visible in the Access Database window or in any other part of the Access
user interface-for example, you can't set the RecordSource property of a
form to a query created with ADOX, nor can you import a query created with
ADOX into another database. However, you can still run stored queries
created by using ADOX from ADO code. This is so because the Microsoft Jet
4.0 database engine can run in two modes: one mode that supports the same
Jet SQL commands used in previous versions of Access, a new mode that
supports new Jet SQL commands and syntax that are more compliant with the
ANSI SQL-92 standard"
A better approach will be to use querydefs collections of DAO.database
object to change the SQL statement for the query (which is DAO equivalent
for a ADO view)
In ADOX, you can use the following code to enumerate all tables and view:
Dim catDB As ADOX.Catalog
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = Access.CurrentProject.Connection ' or any
connection string, if you want to check a different database
Dim tb As ADOX.Table
Dim vw As ADOX.View
For Each tb In catDB.Tables
Debug.Print tb.Name
Next
For Each vw In catDB.Views
Debug.Print vw.Name
Next
However, ADOX Tables collection store also the name of the queries.
HTH,
Regards,
Bogdan Zamfir
_______________________________
Independent consultant
"Chad Reid" <creid.strategic@on.aibn.com> wrote in message
news:Pine.LNX.4.21.0311201000100.1456-100000@cyberdelia.homeunix.org...[color=blue]
> I was wondering what the VBA code would be to test to see if a view
> already exists, or how to overwrite a view if it already exists.
>
> Also, how would I do the same for a regular table?
>
> Database: Access 97
> Connection: ADO 2.0
>
> Please reply to my email aswell as the group if possible.
>
> -Thanks!
>
> --
> Chad Reid - Programmer
> Strategic Tech Solutions
>
creid.strategic@on.aibn.com
> Office: (905) 623-5693
>[/color]
