Check for existance of table by name

  • Thread starter Thread starter paul.schrum
  • Start date Start date
P

paul.schrum

Access 2003

I am linking tables at startup, and I need to block the linking of
tables which have already been linked.

I accomplish the link with lines like this:
DoCmd.TransferDatabase acLink, "Microsoft Access", _
dbName, acTable, "tbl_tasks", "tbl_tasks"

In which dbName is something like "C:\db\tasks.mdb"

In order to prevent the linking of a table which has already been
linked, I want to wrap the above command in an if-block like so:

if doesTableExist("tbl_tasks") = False then
DoCmd.TransferDatabase acLink, "Microsoft Access", _
dbName, acTable, "tbl_tasks", "tbl_tasks"

end if

I am attempting to implement the Function doesTableExist now, but my
approach, it turns out, is unacceptably slow. I need advice on the
best, fastest way to block Access from linking a given table if the
tablename already exists.

Here is the code for doesTableExist

Function doesTableExist(tableName As String) As Boolean
Dim i, count As Integer
i = 0
count = CurrentDb.TableDefs.count

Dim checkName As String

doesTableExist = False
For i = 0 To count
checkName = CurrentDb.TableDefs(i).Name ' 7 seconds per
execution
If 0 = StrComp(tableName, checkName, vbTextCompare) Then
doesTableExist = True
Exit For
End If
Next

End Function

TIA.

- Paul Schrum
 
Jeanette,

Thanks for your response. However the articles you refered me to are
about relinking tables. My code already relinks tables okay. My
problem is that I get two links to the same table, one named
"tbl_tasks" and the other one named "tbl_tasks1". I am asking for a
way either to prevent relinking to a table that is already linked, or
a way to determine whether a given table is already linked by knowing
its name. In fact, I can already do that: step through the names one
at a time a do a string comparisson. But it is too slow. I am hoping
there is a fast way to do this.

Now, there was a good idea in that code which I can use: start at the
end of the collection and step backwards. That will speed up my
process considerably. But having a way to make this check in 2
seconds (not 42 seconds) is what I am seeking.

- Paul
 
Paul,
Yes, starting at the end of the collection and stepping backwards should
eliminate the problem of linking a table twice.
One way to speed up the process is to have a persistent connection to the
backend open at all times.
Another way is to do a check to see if the backend is in the same place as
it was the last time the frontend was opened.
If it is in the same place, you just assume that all the tables will be
there and only bother with relinking if the backend is in a different
place - such as when the database has just been installed or moved.

There is also code here which I find very quick
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
Click on the link called
J Street Access Relinker


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeanette,

Thanks for your response. However the articles you refered me to are
about relinking tables. My code already relinks tables okay. My
problem is that I get two links to the same table, one named
"tbl_tasks" and the other one named "tbl_tasks1". I am asking for a
way either to prevent relinking to a table that is already linked, or
a way to determine whether a given table is already linked by knowing
its name. In fact, I can already do that: step through the names one
at a time a do a string comparisson. But it is too slow. I am hoping
there is a fast way to do this.

Now, there was a good idea in that code which I can use: start at the
end of the collection and step backwards. That will speed up my
process considerably. But having a way to make this check in 2
seconds (not 42 seconds) is what I am seeking.

- Paul
 
Here is some code that shows how to keep a connection to the back end open
while you check the table links.
The code below is a snippet of code from relinking code in the book called
Access 2003 by John L Viescas.

--------------------
Public Function ReConnect()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim rstV As DAO.Recordset

' Called by frmSplash - the normal startup form for this application

On Error Resume Next
Set db = CurrentDb

' First, check linked table version
'always opens the table called ztblVersion first
Set rstV = db.OpenRecordset("ztblVersion")
' Make sure we're on the first row
rstV.MoveFirst

' NOTE: We're leaving rstV open at this point for better efficiency
' in a shared database environment. JET will share the already
established thread.
' Loop through all TableDefs
For Each tdf In db.TableDefs
' Looking for attached tables
If (tdf.Attributes And dbAttachedTable) Then
' Try to open the table
Set rst = tdf.OpenRecordset()
' This one OK - close it
rst.Close
' And clear the object
Set rst = Nothing
End If
Next tdf
ReConnect = True
rstV.Close
Set rstV = Nothing
Set db = Nothing
' DONE!
Exit Function

End Function
 
In fact, I can already do that: step through the names one
at a time a do a string comparisson. But it is too slow. I am hoping
there is a fast way to do this.
Click to expand...

Check for the existance of CurrentDb.Tabledefs("tablename").
 
The answers you have from Jeanette and John should head the problem off
before it occurs.

If you want to run through the tables of the current database to see if one
of them is connected using an alias, this function should do it:
http://allenbrowne.com/unlinked/ShowTablesFrom.txt

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Jeanette,

Thanks for your response. However the articles you refered me to are
about relinking tables. My code already relinks tables okay. My
problem is that I get two links to the same table, one named
"tbl_tasks" and the other one named "tbl_tasks1". I am asking for a
way either to prevent relinking to a table that is already linked, or
a way to determine whether a given table is already linked by knowing
its name. In fact, I can already do that: step through the names one
at a time a do a string comparisson. But it is too slow. I am hoping
there is a fast way to do this.

Now, there was a good idea in that code which I can use: start at the
end of the collection and step backwards. That will speed up my
process considerably. But having a way to make this check in 2
seconds (not 42 seconds) is what I am seeking.

- Paul
 
m:
Function doesTableExist(tableName As String) As Boolean
Dim i, count As Integer
i = 0
count = CurrentDb.TableDefs.count

Dim checkName As String

doesTableExist = False
For i = 0 To count
checkName = CurrentDb.TableDefs(i).Name ' 7 seconds
per
execution
If 0 = StrComp(tableName, checkName, vbTextCompare) Then
doesTableExist = True
Exit For
End If
Next

End Function
Click to expand...

Your code is *very* inefficient in that you're using a call to
CurrentDB to look up the table name for every loop. There are two
approaches to this:

1. try to use the table name and if it throws an error, it doesn't
exist.

2. loop through the tables, OR

The former is implemented in this code:

Public Function TableExists(strTableName As String, _
Optional ysnRefresh As Boolean, _
Optional db As DAO.Database) As Boolean
' Originally Based on Tony Toews function in
' TempTables.MDB, http://www.granite.ab.ca/access/temptables.htm
On Error GoTo errHandler
Dim tdf As DAO.TableDef

If db Is Nothing Then Set db = CurrentDb()
If ysnRefresh Then db.TableDefs.Refresh
Set tdf = db(strTableName)
TableExists = True

exitRoutine:
Set tdf = Nothing
Exit Function

errHandler:
Select Case err.Number
Case 3265
TableExists = False
Case Else
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Error in TableExists()"
End Select
Resume exitRoutine
End Function

Implemented in the second way, you get this:

Public Function TableExists(strTableName As String, _
Optional ysnRefresh As Boolean, _
Optional db As DAO.Database) As Boolean
On Error GoTo errHandler
Dim tdf As DAO.TableDef

If db Is Nothing Then Set db = CurrentDb()
If ysnRefresh Then db.TableDefs.Refresh
For Each tdf In db.TableDefs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf

exitRoutine:
Set tdf = Nothing
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Error in TableExists1()"
Resume exitRoutine
End Function

When I run this, it's very fast. but note why:

It uses a For/Each loop on a single call to the TableDefs
collection. This is going to be orders of magnitude faster than
looking it up each time from a fresh instance of CurrentDB, and
CurrentDB's TableDefs collection. My code makes one call to
CurrentDB (or uses the db passed in as an argument to the function)
and uses it's TableDefs collection directly.

Philosophically, I prefer *not* producing an error. Given the
limitations on the maximum number of possible tables (with a total
of 32,768 objects) in an Access database, I would not anticipate
that the time it takes to walk the TableDefs collection would ever
be excessive. Indeed, I doubt there are many database out there that
have even 1000 tables in them.

The other method is to use the MSysObjects table and query it
directly:

Public Function TableExists(strTableName As String, _
Optional db As DAO.Database) As Boolean
On Error GoTo errHandler
Dim strSQL as String
Dim rs As DAO.Recordset

If db Is Nothing Then Set db = CurrentDb()
strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
strSQL = strSQL & "WHERE MSysObjects.Name="
strSQL = strSQL & Chr(34) & strTableName & Chr(34)
strSQL = strSQL & " AND MSysObjects.Type=6;"
Set rs = db.OpenRecordset(strSQL)
TableExists = (rs.RecordCount <> 0)

exitRoutine:
If Not (rs Is Nothing) Then
rs.Close
Set rs = Nothing
End If
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Error in TableExists1()"
Resume exitRoutine
End Function

I don't like to do this kind of thing because it depends on private,
undocumented features of Jet databases. Now, MS has promised that
once something in the system tables is in use, it will be retained
from then on, but that doesn't make me satisfied that some future
change in the behavior and structure of MSysObjects might lead to it
not producing reliable results. So I would never code this. But it's
likely to be the fastest method.
 
Here is some code that shows how to keep a connection to the back
end open while you check the table links.
The code below is a snippet of code from relinking code in the
book called Access 2003 by John L Viescas.
Click to expand...

But the biggest reason the code you quote is faster than Paul's is
because that code uses the TableDefs collection of a single call to
CurrentDB. For actual relinking, yes, maintaining an open connection
(i.e., creating and retaining the LDB file) can certainly speed
things up (and you don't need to open a recordset -- all you need to
do is establish a database variable pointing to the back end data
file), but the key problem with Paul's code was elsewhere.
 
Thanks to all. I am working on my problem, and will shortly post a
related question. I did not want anyone to think I just forgot about
this thread though.

- Paul
 
To all who responded to this thread, thanks. The final solution I
have used is to check the count attribute of CurrentDB
().TableDefs.count. If the number of TableDefs is > 11, my linked
tables are already linked. Otherwise, I need to link them. Maybe not
the best solution, but it is fast and it is good for now.

- Paul Schrum
 
m:
To all who responded to this thread, thanks. The final solution I
have used is to check the count attribute of CurrentDB
().TableDefs.count. If the number of TableDefs is > 11, my linked
tables are already linked. Otherwise, I need to link them. Maybe
not the best solution, but it is fast and it is good for now.
Click to expand...

I think that's inadvisable:

1. any number of things could end up changing the table count.

2. system tables and the like are included in that, and the number
of system tables can change, and that could mask a missed linked
table.

3. it still doesn't verify that the tables are linked to a valid
path.
 
Back
Top