Part III: More-Advanced Access Techniques
606
To import a comma-delimited text file named ImpDelim.txt (also found on the companion
CD-ROM) for which you have created a corresponding import link specification, you can use the
following statement:LinkText(“ImpDelim.txt”, _
“EmployeeImport Link Specification Delimited”, _
“Delimited”, “NO”, “2”, “EmployeeLink2”)As you can see, there are dozens of combinations you can use when linking to external data
sources. The connect strings for each can get a little confusing, but there is a way to make connect-
ing easy. If you pretend you’re an end user and use the wizards, the process can be a lot easier.
Once you step through the process of linking the table you want using the Link Wizard, open the
Debug window and query the Connect and SourceTableName properties of the table you’ve
linked. Then you’ll have all you need to build the VBA code for doing the same thing programmat-
ically. Just copy the Connect string from the Debug window and paste it into your procedure.Checking links
You (or, more accurately, your users) will at some point encounter a situation where a linked table
in one of your applications becomes unavailable. For example, suppose your application links to a
SQL Server database and the network goes down. One of your users, who doesn’t know the net-
work is down, sits at his workstation and tries to pull open the application. As soon as he tries to
access data from the attached table, an error occurs and your uninformed user panics. Here’s
another common scenario: Suppose your application is linking to an Excel spreadsheet, but some-
one decides to clean up a directory and moves, renames, or deletes the spreadsheet. Again, an error
occurs when someone tries to access data from the linked table. You might not be able to prevent
these situations, but you can plan for them ahead of time.The following function, CheckLinks, should probably be run as a startup routine for your appli-
cation, or in addition to any procedures you run when your application is accessed. Pass
CheckLinks the name of an attached table, and test to see if the link is still valid. All the proce-
dure does is try to open the table as a recordset. If the OpenRecordset method fails, either the
table doesn’t exist in the database or the link has been lost. All this function has to do is flash a
descriptive message to the user and a return value to announce that the application should proceed
no further.On the CD-ROM
The following function is located in the basTestLinks module in Chapter16.accdb on the book’s com-
panion CD-ROM.
Cross-Reference
The last portion of the error handler dealing with ODBC data sources is explained in Chapter 37.