Method 1: Use the CreateTableDef method
The CreateTableDef method creates a link table. To use this method, create a new module, and then add the following AttachDSNLessTable function to the new module.
The code copy is as follows:
'//Name:AttachDSNLessTable
'//Purpose:CreatealinkedtabletoSQLServerwithoutusingaDSN
'//Parameters
'//stLocalTableName:Name of thetablethatyourecreating inthecurrentdatabase
'//stRemoteTableName:Name of thetable that you are linking toon the SQL Server database
'//stServer:Name of the SQL Server that youarelinkingto
'//stDatabase:Name of the SQL Server database that you are linking to
'//stUsername:Name of the SQL Server user whocan connect to SQL Server,leaveblank touseaTrustedConnection
'//stPassword:SQLServeruserpassword
FunctionAttachDSNLessTable(stLocalTableNameAsString, stRemoteTableNameAsString, stServerAsString, stDatabaseAsString, OptionalstUsernameAsString, OptionalstPasswordAsString)
OnErrorGoToAttachDSNLessTable_Err
DimtdAsTableDef
DimstConnectAsString
ForEachtdInCurrentDb.TableDefs
Iftd.Name=stLocalTableNameThen
CurrentDb.TableDefs.DeletestLocalTableName
EndIf
Next
IfLen(stUsername)=0Then
'//Usettrusted authenticationifstUsernameisnotsupplied.
stConnect="ODBC;DRIVER=SQLServer;SERVER="&stServer&";DATABASE="&stDatabase&";Trusted_Connection=Yes"
Else
'//WARNING:This willsave theusername and thepassword with thelinkedtable information.