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.ActiveConne ction = 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.goo gle.com...

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.c om (Uttam) wrote in message news:<77******* *************** ****@posting.go ogle.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(st rObjectName 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$(strObjec tType) = "TABLE" Then
For Each tdf In db.TableDefs
If tdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tdf
ElseIf UCase$(strObjec tType) = "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********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...

u0***@hotmail.c om (Uttam) wrote in message news:<77******* *************** ****@posting.go ogle.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(st rObjectName 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$(strObjec tType) = "TABLE" Then
For Each tdf In db.TableDefs
If tdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tdf
ElseIf UCase$(strObjec tType) = "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.Open Schema(adSchema Tables, Array(Empty,
Empty, "Name of Particular Table")).BOF

CurrentProject. Connection.Open Schema(adSchema Views, Array(Empty,
Empty, "Name of Particular Table")).BOF

[Lyle Fairfield]

ly******@yahoo. com (Lyle Fairfield) wrote in
news:42******** *************** ***@posting.goo gle.com:

pi********@hotm ail.com (Pieter Linden) wrote in message news:

<bf************ **************@ posting.google. com>...

u0***@hotmail.c om (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(st rObjectName 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$(strObjec tType) = "TABLE" Then
For Each tdf In db.TableDefs
If tdf.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tdf
ElseIf UCase$(strObjec tType) = "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.Open Schema(adSchema Tables, Array(Empty,
Empty, "Name of Particular Table")).BOF

CurrentProject. Connection.Open Schema(adSchema Views, Array(Empty,
Empty, "Name of Particular Table")).BOF

should be:

CurrentProject. Connection.Open Schema(adSchema Views, Array(Empty,
Empty, "Name of Particular Query")).BOF
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)