Determining if a table exists in ADO

[Neil Crawford]

Dear all,

I tried amending this DAO code to work as ADO but failed
miserably. Does anyone know the equivalent? Thanks,

Neil


Public Function tableexists(tblname As String)

Dim db As Database, t As String
Set db = CurrentDb()
On Error Resume Next

t = db.TableDefs(tblname).Name
If Err = 3265 Then 'name not in table
collection
tableexists = False
Else
tableexists = True
End If

db.Close

End Function

 

[Randy Harris]

Dear all,

I tried amending this DAO code to work as ADO but failed
miserably. Does anyone know the equivalent? Thanks,

Neil


Public Function tableexists(tblname As String)

Dim db As Database, t As String
Set db = CurrentDb()
On Error Resume Next

t = db.TableDefs(tblname).Name
If Err = 3265 Then 'name not in table
collection
tableexists = False
Else
tableexists = True
End If

db.Close

End Function

The closest that ADO comes to TableDefs is AllTables. There are lots of
ways of doing this, but if you would like a means similar to what you have,
but using ADO:

Public Function tableexists(tblname as String) As Boolean
Dim t as String
On Error Resume Next
t = Application.CurrentData.AllTables(tblname).Name
If Err = 2467 Then
tableexists = False
Else
tableexists = True
End If
End Function

Code tested with A2K
HTH,
Randy

 

[Neil Crawford]

Randy,

thanks, I'll give it a go,

Neil

-----Original Message-----



The closest that ADO comes to TableDefs is AllTables. There are lots of
ways of doing this, but if you would like a means similar to what you have,
but using ADO:

Public Function tableexists(tblname as String) As Boolean
Dim t as String
On Error Resume Next
t = Application.CurrentData.AllTables(tblname).Name
If Err = 2467 Then
tableexists = False
Else
tableexists = True
End If
End Function

Code tested with A2K
HTH,
Randy


.

 

[Roger Carlson]

Why re-write it in ADO at all? The following work in an Access 2000
database WITHOUT a reference to DAO:

Public Function tableexists(tblname As String)
Dim t As String
On Error Resume Next
t = CurrentDb().TableDefs(tblname).Name
If Err = 3265 Then
tableexists = False
Else
tableexists = True
End If
End Function

 

[Mike Sherrill]

[snip]

The closest that ADO comes to TableDefs is AllTables. There are lots of
ways of doing this, but if you would like a means similar to what you have,
but using ADO:

Public Function tableexists(tblname as String) As Boolean
Dim t as String
On Error Resume Next
t = Application.CurrentData.AllTables(tblname).Name
If Err = 2467 Then
tableexists = False
Else
tableexists = True
End If
End Function

What are the odds that *every* error means the table exists? Every
error except 2467, that is.

 

[Scott McDaniel]

ADO is a data manipulation library and doesn't really support object stuff.
Instead, you'll have to set a reference to the ADOX library and use the
Catalog to return this information. I can't recall the exact syntax required
to do this, but a quick Google on "ADOX Catalog Table" should give you
everything you need.