Determining if a table link exists

[CharlieT302]

Hi Folks,

I want to be able to check for the existence of a table link. If it does not exist, recreate it. If it does, delete it and then recreate it.

I had a similar problem determining if a linked file exists. Thanks to the help of some of you, I corrected it. However, this seems to be slightly different.

Background:
I have four table links that connect to different worksheets on an Excel workbook. The workbook is located off the My Documents folder. Because the dbase will transfer between workstations, I have created a form that uses a command button to delete the existing links and recreate them on the current workstation using the path specified in a text box.

What I am doing currently is this: if the links do not exist, an error code results if an attempt is made to delete them (makes sense). As of now, my code says if the error code value occurs move to the section that recreates the link.

Isn't there a better way of determing if a link exists?

 

[lameid]

Ultimately you have to find out by trapping some sort of error condition. Whether that is opening it in a recordset or otherwise using it it does not matter.

In your specific case, I would make a delete link procedure.

Then trap the error when it doesn't exist. If it is some other error, don't clear it so it passes up to the calling function.

 

[Golom]

You can scan the tables collection and look for it or you can write a query against the system tables to see if it exists or you can try to reference it and trap the error.

They all work and I don't see much to distinguish one over the other for your purposes. Pick what you're most comfortable doing.

 

[lameid]

I hadn't thought of the other options but I would avoid them.

I would avoid using the system tables as there is no guarantee they will stay the same between versions of Access.

Scanning tables should work but you do have to scan the entire collection and check for matches. This could be slow if there are a lot of tables.

 

[PHV]

have to scan the entire collection and check for matches
Really ?

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = (CurrentDb.TableDefs(strTableName).Name = strTableName)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I read Golom's post as providing alternatives to error trapping but I see that I may have read that into it.

I really meant, to avoid error trapping and use a scan of tables, you have to scan the collection completely. Which is why I said that error trapping is the way to go. Of the methods we can expect to stick around, it is definitely the fastest. Whether that is PHV's code or my concept does not really matter. I like my idea better but I'm biased.

 

[CharlieT302]

Thanks for the input. Currently, I am using the error code to trigger the link creation; as some of you have suggested. I wanted to check if there was a better way that I wasn't aware of, or if trapping the error code was the simplest method.

I would like to try PHV's method. Although, lameid's comment about it slowing the system slightly concerns me.

Thanks to all for great input.

 

[Golom]

Although, lameid's comment about it slowing the system slightly concerns me

"Slightly" is the operative word here. PHV's solution will use a binary search to find a key in a collection. The time it takes will be on the order of the ln(# of tables). That is, if you went from 10 tables to 100 tables, the time required would increase by a factor of 2 and not a factor of 10.

Just for comparitive purposes, this code

On Error Resume Next
For n = 1 To 10000
    If m > UBound(tables) Then m = 0
    tname = tables(m)
    TableExists = (db.TableDefs(tname).Name = tname)
    m = m + 1
Next

Where "tables" is just an array containing all the tables in the database ... 240 of them in this case with an invalid table name every 10th entry.

This runs in about 0.3 seconds or 0.00003 seconds per table.

I don't think you need be too concerned about how long this will take.

 

[lameid]

I do not have any problems at all with PHV's code (Whether that is PHV's code or my concept does not really matter. ). I was just pointing out that, to stay on topic, there is no avoiding error trapping. To me PHV's code use error trapping with resume next (error trapping at least because it assumes certain errors are acceptable; this is a loose interpretation). I was just clarifying what I meant as I expect longer run times from something that ducks error trapping with something like the following.

Function TableExixts (strTableName as string) as boolean
     Dim tbl as DAO.tabledef
     dim tbls as DAO.tabledefs
     Set tbls = Currentdb.tabledefs
     TableExixts = False
     For each tbl In Tbls
           If tbl.name = strTablename Then
                 TableExixts = True
           End if
     Next
     Set tbl = Nothing
     Set tbls = Nothing
End Function

I would expect that to be relatively slow as it scans all the tables. Unless there is something else going on, it should be relatively fast but if you wrote the above, I would ask you why didn't you write it like <PHV's code>? It is shorter and faster.

I still lean to deleting the table and trapping errors in one procedure. That way eliminates more lines of code from your main procedure making it easier to read. My prefernece is 100% style preference. Both ways look good to me. My gut tells me that PHV's code may even be faster than my method. It is also written and appealing to you so you might as well go ahead with it.