Function Behaves Differently In Module

[bob bisquick]

I have created a public function:

Public Function LinkTables(tblName, path As String)
On Error GoTo LinkTheTable

DoCmd.SelectObject acTable, tblName, True

DoCmd.DeleteObject acTable, tblName

GoTo LinkTheTable

LinkTheTable:
DoCmd.TransferDatabase acLink, "Microsoft Access", path, acTable, _
tblName, tblName

End Function

This function links to tables in a database I specify when it is called. If I place this function in the code of my form, it works fine. If I plce this function in a module, it links the tables fine, but does not delete the older instances of those tables (the DeleteObject statement). Why is this happening?

 

[namliam]

Why delete it when you can "simply" relink the table..

?currentdb.tabledefs("YourTable").connection

That will show you your current connection in the debug window.

By assigning it a new value and doing a refresh of same table you have acheived the same, but cleaner result....

Regards

 

[bob bisquick]

Yes, but I am trying to automate this process. Also, I am really more interested in why it works when the code is associated with the form and why it does not work when the code is in a module. I was under the impression a public function should work anywhere in the database.

 

[namliam]

it should work i would think yes....

Anyway that i dont know. But the relinking can be done automated as well...

Regards

 

[ChrisO]

G’day Bob.

Don’t know if you are still having a problem with this.

If so can you strip down the FE just containing the Form (with the code) and the BE with the table (you can remove all the data from the table). Convert both to A97, zip and post them.

Shouldn’t be too hard for someone to figure out what’s going on.

Regards,
Chris.