finding if a table exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

finding if a table exists

Author

Topic

dpwhitedp
Starting Member

5 Posts

Posted - 2004-06-15 : 09:56:20

I need to know if a table exists.
I got this code from someone but it does not seem to work
collection1 is an input field in the form.

Private Sub findit1()
If IsNull(collection1) Then GoTo findit1x
Dim i As Integer
Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0)
findresults = "N"
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If collection1 = db.TableDefs(i).Name Then
findresults = "Y"
Exit For
Else
End If
Next i
findit1x:
Set db = Nothing
End Sub

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-15 : 11:38:34

A handy function for your VB/VBA toolbox:


function Exists(coll as object, Value as variant) as boolean
   dim o as object
   on error goto e
   o = coll(Value)
   Exists = True
   exit function
  e:
   Exists = False
end Function

that accepts any collection and checks to see if the value exists in the collection, and returns a true/false. A having to use "on error" to check this is a big oversight of the collection object in VB in my opinion.

Anyway, to see if a table exists in Access:


if Exists(currentdb.tabledefs, "TableNameHere") then ....

enjoy!

- Jeff

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-15 : 19:23:24

Another way is to query the Access system tables:

SELECT Count(*) FROM MSysObjects WHERE Name = 'tablename' AND Type = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources