Chapter 16: Working with External Data
603
& CurDir() & “ to continue”
Exit Function
End If
Set db = CurrentDb
‘ Create a new tabledef in the current database:
Set td = db.CreateTableDef(sTableName)
‘ Build Connect string:
sConnect = “Excel 8.0;HDR=YES;DATABASE=“ & sFileName
td.Connect = sConnect
‘ Specify Range Name sRangeName:
td.SourceTableName = sRangeName
‘ Append new linked table to TableDefs collection:
db.TableDefs.Append td
‘Return True:
AttachExcel = True
ExitHere:
Exit Function
HandleError:
Select Case Err
Case conCannotOpen
sMsg = “Cannot open “ & sFileName
Case conTableExists
sMsg = “The table “ & sTableName & _
“ already exists.”
Case conNotRange
sMsg = “Can’t find the “ & sRangeName & “ range.”
Case Else
sMsg = “Error#” & Err & “: “ & Error$
End Select
MsgBox sMsg, vbExclamation + vbOKOnly, _
“Error in Procedure “ & sFunction
AttachExcel = False
Resume ExitHere
End FunctionConnect strings and source table names are more involved when you’re using ODBC data
sources. For example, when you connect to a SQL Server ODBC data source, you have the option
of specifying the type of source you’ll be using (ODBC), the data source name (DSN), the applica-
tion you’re using, the table within the data source that contains the data you want, the workstation
using the application, and a user ID and password. Not all these parameters are available to every
ODBC data source, so you need to consult your ODBC driver manual to find out what you can and
can’t use.
By the way, you might not want to hard-code a user ID and password in your connect string but
instead use some combination of Access and a customized security setup that allows you to capture
a user’s ID and password when the user logs in to your application and then pass those values
dynamically.