How to define wheter a table is part of a database via code

[Leen]

Hi,

Is there some way to determine wheter or not a table is present in a database or not? For example, I have the table Roads in my database. Sometimes the user imports this table, sometimes not.

When it it imported some actions should be run on it. But how can I check via code wheter or not the table is present?

I searched in properties of DAO.tabledef and of DAO.database but did not find something that can do that? (it is possible to count the number of tables, but to know wheter a specific table is present?)

Thanks for any help!!
Leen

 

[chergh]

Try this:

dim tblexists as boolean

tblexists = cbool(not currentdb.tabledefs(tbl_name_as_string) is nothing)

So if the table exists then tblexists = true if it doesn't exists tblexists = false

 

[Leen]

Thanks for your help but somehow it doesn't works:

If have several table in my database. If the table is present, the tblexists indeed returns the value true, but if the table is not part of the database, I get an error: "Item not found in this collection" on the folowing line:

tblexists = CBool(Not CurrentDb.TableDefs(layername) Is Nothing)

Do you have any idea how to solve this?
Thanks again!!

And just a little question, what exactly is happening in that line?

 

[gemma-the-husky]

similar thing, maybe clearer

just

tblexists = len(currentdb.tabledefs("tablename").name)>0

this looks for the table called "tablename" if it finds it the name returned IS the tablename, so the length is greater than 0

however if it doesnt find it, this causes an error, so in fact you need an error handler - which is as follows

sub testit
dim tblexists as boolean

onerror goto fail:
tblexists = len(currentdb.tabledefs("tablename").name)>0

msgbox("Exists")

exithere:
exit sub

fail:
msgbox("Table doesnt Exist")
resume exithere

 

[chergh]

tblexists = CBool(Not CurrentDb.TableDefs(layername) Is Nothing)

In this statement it is testing to see if the table with the name specified is not nothing. So if the table exists then that it is "not nothing" is true if the table does not exists then it is nothing so "not nothing" is false

 

[chergh]

Ok I've looked up how I've used this in the past

Function tblexists(SName As String, Optional db As database) As Boolean

If db Is Nothing Then
    Set db = currentdb
End If

On Error Resume Next

    tblexists = CBool(Not db.tabledefs(SName) Is Nothing)

On Error GoTo 0

End Function

Then in your sub add the line

tblexists(layername)

 

[Leen]

Hi,

Im trying two use the errorhandling method but in some way it still gives the same error: "Item not found in this collection" on the line

tblexists = CBool(Not CurrentDb.TableDefs(layername) Is Nothing)

.

My complete code:

Function present(layer As String)

On Error GoTo fail
tblexists = CBool(Not CurrentDb.TableDefs(layer) Is Nothing)

Exit Function

fail:
tblexists = False
Resume

End Function

Actually, I made a separate function in which I want to decide wheter or not the table is part of the database. I use the variable "tblexists" (which is True when it does in the above code) in my code where the further actions, run on that present table, are described.

Any idea how to fix this?
Thanks again!

 

[chergh]

just copy and paste the code I have posted.

 

[Leen]

Hi,

Thanks for the code, I copied it litterarly, but it also still gives the same mistake: on the folowing line

tblexists = CBool(Not db.TableDefs(SName) Is Nothing)

The error is:"Item is not found in the collection"

I really don't understand, as it is preceded with "On Error resume next". I haven't worked with errorlogging yet, but by looking at the help this should work?

And just another question if you have some time.. what do you mean by

If db Is Nothing Then
    Set db = currentdb
End If

Why can't you just put:

Function tblexists(SName As String) As Boolean

If db Is Nothing Then
    Set db = currentdb
End If

On Error Resume Next

    tblexists = CBool(Not currentdb.tabledefs(SName) Is Nothing)

On Error GoTo 0

End Function

And really thanks a lot for all help!
Leen

 

[chergh]

First of all the error. In the VBE (visual basic editor) goto to Tools > Options then click on the general tab. Under error trapping ensure that "break on unhandled errors" is selected.

The currentdb stuff is more to make sure the code is looking at the correct tabledefs collection as you may be working with more than one access database.

The reason I have an optional parameter is that I may want it to look at the tabledef collection of a different database and if I do want to do that then passing it as a parameter to the function is an effective method to do this.

 

[Leen]

HI, that works! The code is not bugging on that anymore, but:

My aim was the folowing:
A user imports some tables using a form using check boxes. Then in my code, I'like to see which ones are imported (my question on this forum). Because, once I know which tables are imported, I can perform another code on them (called actions).

The problem is that with the function tblexists, I need somehow an output that says: True (and False in case the table doesn't exist)

So in a seperate function I've put what you told me:

Function tblexists(SName As String) As Boolean

On Error Resume Next
    tblexists = CBool(Not CurrentDb.TableDefs(SName) Is Nothing)
On Error GoTo 0

End Function

But in my actual code, where I want to run the actions on my table if it is imported I've put:

.....
tblexists (layername) 

If tblexists Then
    
    Set tbl = dbs.TableDefs(layername)
    sSql = "SELECT * FROM " & layername
    Set rst = dbs.OpenRecordset(sSql, dbOpenDynaset)

.....
[/CODE]

However, on the line

If tblexists Then

I get the error: "Compile error: argument not optional"

However, the value of tblexist is a boolean no? So I think:
-if the table exists in the database, the tblexists(layername) is run, then the output of tblexists =True and the actions beneath are run
-in case the table is not part of the database, then the output of tblexists= False?

I'm not at all a big expert, but isn't that what it should do? What is going wrong then?

Thanks a lot for all your time you've already put on this!
Leen

 

[chergh]

You just need to specify which boolean value you want as the program doesn't know.

if tblexists = true then

Is what you want

 

[Leen]

I tried that already but it gives the same error:
"Compile error:argument not optional"

Actually, the function tblexist, it does returns a true or false? no? And it does returns false when an error is "jumped over"?

I tried the following but that doesn't work (I tried to enter a variable "tablepresent" to keep track of the table exists or not, and use this variable in my principle code:

In my code with function:

Function tblexists(SName As String) As Boolean

tablepresent = False

On Error Resume Next
    tblexists = CBool(Not CurrentDb.TableDefs(SName) Is Nothing)
    tablepresent = True

On Error GoTo 0

End Function

In my principal code:

tblexists (layername)

If tablepresent = True Then
     
    Set tbl = dbs.TableDefs(layername)
    sSql = "SELECT * FROM " & layername
    Set rst = dbs.OpenRecordset(sSql, dbOpenDynaset)

But this doesn't work either...

 

[chergh]

try:

If tblexists(layername) = true then

    Set tbl = dbs.TableDefs(layername)
    sSql = "SELECT * FROM " & layername
    Set rst = dbs.OpenRecordset(sSql, dbOpenDynaset)

end if

 

[Leen]

IT WORKS!!!!!!!!!!!!!!

Actually I tried the folowing:
In my code of the function:

Function tblexisting(SName As String) As Boolean
tblexists = False

On Error Resume Next
    tblexists = CBool(Not CurrentDb.TableDefs(SName) Is Nothing)

On Error GoTo 0

End Function

And in the general actions code:

tblexisting (layername)

If tblexists = True Then
    
    Set tbl = dbs.TableDefs(layername)
    sSql = "SELECT * FROM " & layername
    Set rst = dbs.OpenRecordset(sSql, dbOpenDynaset)

By using the addwatch method to find mistakes, I saw that tblexists did not became false. So by entering the extra variable "tblexists as boolean" It does work!!
In case the table is not part of the database, "tblexists" stays on false, els e, if no error is run, it changes to true.

Thank you very very much for all help!!!!!!