Locating a Table object

[Guest]

I am having a problem with the following code:

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If obj.IsLoaded = True Then
If TblObj.Name = strHoldTableName Then
MsgBox "Another user is viewing this record"
intBeingViewed = 1
Exit For
End If
End If
Next obj

This code is intended to check if a table with the (string) name
strHoldTableName exists, but it's not working. Am I missing a reference, or
is there another set of code that I should be using for this purpose?

Thanks in advance for your assistance!

 

[Marshall Barton]

I am having a problem with the following code:

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If obj.IsLoaded = True Then
If TblObj.Name = strHoldTableName Then
MsgBox "Another user is viewing this record"
intBeingViewed = 1
Exit For
End If
End If
Next obj

This code is intended to check if a table with the (string) name
strHoldTableName exists, but it's not working. Am I missing a reference, or
is there another set of code that I should be using for this purpose?

Your terminolgy is too vague to express your goal. If the
table name in strHoldTableName matches, then the table
exists in the Current database. OTOH, the IsLoaded property
only tells you if the table is open in design or sheet view.

OTOOH, your message box talks about another user, which
would be in another database. If you really need to make
sure a table is used exclusively, then you need to open a
recordset with the dbDenyWrite option and check for an in
use error.

 

[Guest]

Is this what you want?

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef

TableExists = False
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdfs = Nothing

End Function

It will look for a table where the name of the table matches what you pass
the function. If the table exists, it returns True; otherwise, it returns
false.
If this is not what you want, post back with more detail, please.

 

[Guest]

OK, I did not explain myself well. I do not want the table to be exclusive
to one user; this will help determine how a form opens later in the code (not
seen here, long story). Run time versions of the db will be used by more
than one user, but will link back to the same table. The table that I want
to identify would have been created by a different piece of code.

To make a long story short, my objective is to find out if a table with a
name that matches the string expression exists or not. I don't care if I
need to throw out the code below entirely; I copied it from the help files
anyway. Just trying to find a solution - any assistance would be
appreciated.

Thank you!!

 

[Guest]

Rebecca,
See my previous post. It does what you want.
Now, you make a statement that raises the famous red flag.
"Run time versions of the db will be used by more than one user"

If you are saying that the mdb will be in a shared folder and multiple
users will use it, that is the correct way to set up an Access application.
Each user should have his own version of the front end on his computer, and
be linked to the bac end in a shared folder.

If you are not familiar with Split databases, I suggest you do some reading
on it.

 

[Guest]

Yes Dave, I am setting it up the "correct way" as you describe below. I
don't know where the "run time" verbiage originates, but it's what the folks
back home call this. Thanks!

 

[Guest]

This is what I need. Thanks!

Is this what you want?

Public Function TableExists(strTableName As String) As Boolean
Dim tdfs As TableDefs
Dim tdf As TableDef

TableExists = False
Set tdfs = CurrentDb.TableDefs
For Each tdf In tdfs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Set tdfs = Nothing

End Function

It will look for a table where the name of the table matches what you pass
the function. If the table exists, it returns True; otherwise, it returns
false.
If this is not what you want, post back with more detail, please.

 

[Vantastic]

Another quality piece of code there Klatuu... Helped me too!