VBA to change source of link table

DonLennon · March 18th, 2006, 10:39 AM

Hi,

I am trying to write a macro that changes the source of a link table. I have tried the code below, but am getting error 3420 "object invalid or no longer set". I can't seem to do anything with the object td at all. Can anyone tell me what I am doing wrong? the table test does already exist.

Thanks

Dim td As TableDef
Set td = CurrentDb.TableDefs("test")
td.Connect = "Text;DSN=Test Link Specification;FMT=Delimited;HDR=NO;IMEX=2;Characte rSet=850;DATABASE=C:\;TABLE=test#txt"
td.RefreshLink
Set td = Nothing

Bob Bedell · March 19th, 2006, 01:32 PM

Hi Don,

This line:

Code:

Set td = CurrentDb.TableDefs("test")

isn't instantiating a valid TableDef I believe because you havn't instatiated a valid DB object first. The following works for me:

Code:

Sub Test()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Set db = CurrentDb
    Set td = db.TableDefs("test")
    td.Connect = "Text;DSN=Test Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;DATABASE=C:\;TABLE=test#txt"
    td.RefreshLink
    Set td = Nothing
End Sub

The link to the text file is refreshed successfully.

HTH,

Bob

Bob Bedell · March 19th, 2006, 01:39 PM

If you run your code an take a peek at the locals window, you'll see that no database variable is created. If you run my code and do the same, a database variable gets created first, then the tabledef is added to the database variables tabledef collection.

Bob

DonLennon · March 19th, 2006, 05:25 PM

Thank you very much! That has solved the problem. Seems I'd missed out part of the code, after trying to bodge together various bits of info I had found around the web.

Also.. had never discovered the Locals window before... interesting

Thanks again,

Don

Bob Bedell · March 19th, 2006, 06:18 PM

Your welcome Don. The Locals Window simply displays all the local variables that are in scope as your code executes. I always have it open. Its a really useful aid for visualizing what your code is up to at any given moment. Glad things are working for you.

Bob