Part III: More-Advanced Access Techniques
604
One final example I’ve included in this section is one that shows you how to connect to a text file.
As I mention in the “Linking to text files” section, earlier in this chapter, you can link to delimited
or fixed-width text files. Linking to a text file follows the same process as the previous examples;
the biggest difference is the DSN parameter. Before you can link to a text file, you must create an
import specification that tells Access what the file looks like.In previous versions of Access, you created import/export specs only when you imported or
exported fixed files. But, beginning with Access 2007, you can create a spec for delimited files as
well. If you use the Import Wizard, Access creates an import specification for you. The connect
string for a text file is the name of the import spec you’ve created. The Database parameter is the
path to the file, and the SourceTableName property is the filename you want to link to, without
the file extension.Import specifications are very convenient when data must be periodically linked or imported. For
example, your users might require a weekly update from an external data source, or ad hoc linking
from text files attached to e-mail messages. Once the import specification is prepared, it can be
used over and over again without modification.On the CD-ROM
The following function is located in the basLinkText module in Chapter16.accdb on this book’s com-
panion CD-ROM.
Function LinkText( _
ByVal sFileName As String, _
ByVal sDSN As String, _
ByVal sFMT As String, _
ByVal sHDR As String, _
ByVal sIMEX As String, _
ByVal sTableName As String _
) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim x As Integer
Dim sType As String
Dim sPath As String
Dim sPathAndFileName As String
Dim sDatabase As String
Dim sConnect As String
Dim sMsg As String
Dim sFunction As String
Const conTableExists = 3012
On Error GoTo HandleError
LinkText = False
sFunction = “LinkTxt”
‘ Check for existence of file:
sPath = CurDir() & “\”
sDatabase = sPath & sFileName
If Len(Dir(sDatabase)) = 0 Then