By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Detecting names of Tables and Queries in Catalog

P: n/a
Hello,

Could someone point me to the syntax required to detected whether a
particular table (say tbl1) and a particular Query (Qry1) exists in
the catalog?

Thank you in advance.

Cheers!

Uttam
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Presumably you want ADOX, as you use the term "catalog". The Type
distinguishes whether is a Table, View, etc.

Function ShowAllTables()
Dim cat As New ADOX.Catalog 'Root object of ADOX.
Dim tbl As ADOX.Table 'Each Table in Tables.

'Point the catalog to the current project's connection.
Set cat.ActiveConnection = CurrentProject.Connection

'Loop through the tables.
For Each tbl In cat.Tables
Debug.Print tbl.Name, tbl.Type
Next

'Clean up
Set tbl = Nothing
Set cat = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Uttam" <u0***@hotmail.com> wrote in message
news:77**************************@posting.google.c om...
Could someone point me to the syntax required to detected whether a
particular table (say tbl1) and a particular Query (Qry1) exists in
the catalog?

Nov 12 '05 #2

P: n/a
u0***@hotmail.com (Uttam) wrote in message news:<77**************************@posting.google. com>...
Hello,

Could someone point me to the syntax required to detected whether a
particular table (say tbl1) and a particular Query (Qry1) exists in
the catalog?

Thank you in advance.

Cheers!

Uttam


Using DAO...
Option Compare Database
Option Explicit

Public Function ObjectExists(strObjectName As String, strObjectType As
String) As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef

'Assume failure
ObjectExists = False

Set db = CurrentDb
If UCase$(strObjectType) = "TABLE" Then
For Each tdf In db.TableDefs
If tdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tdf
ElseIf UCase$(strObjectType) = "QUERY" Then
For Each qdf In db.QueryDefs
If qdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next qdf
End If

Set qdf = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
Nov 12 '05 #3

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
u0***@hotmail.com (Uttam) wrote in message news:<77**************************@posting.google. com>...
Hello,

Could someone point me to the syntax required to detected whether a
particular table (say tbl1) and a particular Query (Qry1) exists in
the catalog?

Thank you in advance.

Cheers!

Uttam


Using DAO...
Option Compare Database
Option Explicit

Public Function ObjectExists(strObjectName As String, strObjectType As
String) As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef

'Assume failure
ObjectExists = False

Set db = CurrentDb
If UCase$(strObjectType) = "TABLE" Then
For Each tdf In db.TableDefs
If tdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tdf
ElseIf UCase$(strObjectType) = "QUERY" Then
For Each qdf In db.QueryDefs
If qdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next qdf
End If

Set qdf = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

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

CurrentProject.Connection.OpenSchema(adSchemaViews , Array(Empty,
Empty, "Name of Particular Table")).BOF
Nov 12 '05 #4

P: n/a
ly******@yahoo.com (Lyle Fairfield) wrote in
news:42**************************@posting.google.c om:
pi********@hotmail.com (Pieter Linden) wrote in message news:

<bf**************************@posting.google.com>. ..
u0***@hotmail.com (Uttam) wrote in message news: <77**************************@posting.google.com>. ..
> Hello,
>
> Could someone point me to the syntax required to detected whether a
> particular table (say tbl1) and a particular Query (Qry1) exists in
> the catalog?
>
> Thank you in advance.
>
> Cheers!
>
> Uttam


Using DAO...
Option Compare Database
Option Explicit

Public Function ObjectExists(strObjectName As String, strObjectType As
String) As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tdf As DAO.TableDef

'Assume failure
ObjectExists = False

Set db = CurrentDb
If UCase$(strObjectType) = "TABLE" Then
For Each tdf In db.TableDefs
If tdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tdf
ElseIf UCase$(strObjectType) = "QUERY" Then
For Each qdf In db.QueryDefs
If qdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next qdf
End If

Set qdf = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

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

CurrentProject.Connection.OpenSchema(adSchemaViews , Array(Empty,
Empty, "Name of Particular Table")).BOF


should be:

CurrentProject.Connection.OpenSchema(adSchemaViews , Array(Empty,
Empty, "Name of Particular Query")).BOF
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.