Check if table exists 1

[Rauken]

I want to check if a table exist in my database. I have something like:

dim db as dao.database
dim td as dao.tabledef

set db = currentdb
td = db.tabledefs("tablename")

It works if the table exist but creates an error if it does not.

 

[Rauken]

Ah I found it myself on

http://www.mvps.org/acccess:

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function

 

[PHV]

Or simply play with the On Error Resume Next instruction and the Err object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hwkranger]

Here's it in ADO - if you care:

Public Function fFindTable(strTableName As String) As Integer
Dim i As Integer 'counter
fFindTable = False
For i = 0 To CurrentData.AllTables.Count - 1
If strTableName = CurrentData.AllTables(i).Name Then
fFindTable = True
End If
Next i
End Function

Randall Vollen
National City Bank Corp.

 

[RoyVidar]

Another version, since we've got started:

[tt]public function FindTableAdo(byval vstrTable as string) as boolean
dim rs as adodb.recordset
set rs=currentproject.connection.openschema( _
adschematables,array(empty,empty,vstrTable))
FindTableAdo=not rs.eof
rs.close
set rs=nothing
end function[/tt]

But as stated by PHV, one of the easiest would be to just throw the exeption, and act upon it:

[tt]dim db as dao.database
dim td as dao.tabledef
set db = currentdb
on error resume next
td = db.tabledefs("tablename")
if err.number<>0 then
' not found - do something?
err.clear
end if[/tt]

Roy-Vidar

 

[oharab]

For a generic approach:

http://www.mvps.org/access/modules/mdl0014.htm

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a

 

[hwkranger]

RoyVidar,

I thought your post was hot - I never thought of checking to see if a table was available by checking the schema. Although I can't say that I would do that, I liked your approach. A Star to you!

Randall Vollen
National City Bank Corp.

 

[MrsMope]

Ok so all of that is great, but I don't have database as a data type in my access application, how do I get the tableRef and database type to declare?

 

[RoyVidar]

[tt]If ADP Then
use one of the ADO/ADOX alternatives
Else
in VBE - Tools | References
reference Microsoft DAO 3.# Object Library
End If[/tt]

Roy-Vidar

 

[MrsMope]

Thank you,
I have the Microsoft DAO 3.6 Object library checked, but I do not have the database type. Any other suggestions?

 

[RoyVidar]

If you don't have DAO.Database as a datatype, then there's probably somathing faulty with your Access/Office installation. Check the references, reinstall Office, detect/repair...?

Roy-Vidar

 

[MrsMope]

FYI~~~ I was incorrect in my last post, I did not have the Microsoft DAO 3.6 object library checked!
thanks for the help!

 

[MrsMope]

Ok so here is my little function, when I attempt to run the form, I recieve the following message:
[red] RUN TIME ERROR 91: Object variable or With block variable not set[/red]

Public Function TableExists(sTable As String) As Boolean
    
    Dim db As database
    Dim tbl As TableDef
    Set db = CurrentDb()
    
    TableExists = False
    
    For Each tbl In db.TableDefs
        If tbl.Name = sTable Then TableExists = True
    Next tbl
    
End Function

when I debug the app, I notice my variable db is set to nothing.

 

[RoyVidar]

When someone adds questions to the end of an old thread, one assumes the poster have questions relating to some of the suggestions within the thread, else, at least I'm expecting the question as a new question (new thread).

This thread contains several different ways of checking whether a table exists or not, and links to others AND hints about how to declare DAO variables, but I fail to see resemblence in your code (which, with explicit declaration worked on my setup)...

faq181-2886 has some information on how to get the best answers.

Roy-Vidar

 

[MrsMope]

I have created a new thread with the exact same information as I posted here, however I also posted onto this thread becuase it is an example of how to check for an existing table. I posted the run time error here as well becuase anyone else who may be trying to use the code might recieve the error. If I am incorrect for posting this here, then I apologize.
Also, I do have explicit declaration in my database.
Thanks,
MrsMope