469,272 Members | 1,473 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

Checking for existance of a view

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
cr*************@on.aibn.com
Office: (905) 623-5693

Nov 12 '05 #1
1 2684
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" <cr*************@on.aibn.com> wrote in message
news:Pi*************************************@cyber delia.homeunix.org...
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
cr*************@on.aibn.com
Office: (905) 623-5693

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by William Kossack | last post: by
1 post views Thread by Xeno Campanoli | last post: by
4 posts views Thread by lyndon hughey | last post: by
5 posts views Thread by A.M | last post: by
2 posts views Thread by tshad | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.