Detecting names of Tables and Queries in Catalog

[Uttam]

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

[Allen Browne]

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?

[Pieter Linden]

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

[Lyle Fairfield]

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

[Lyle Fairfield]

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)