Created
August 28, 2015 18:47
-
-
Save juan-fdz-hawa/6b15d4736dc451cbd291 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'//Name : AttachDSNLessTable | |
'//Purpose : Create a linked table to SQL Server without using a DSN | |
'//Parameters | |
'// stLocalTableName: Name of the table that you are creating in the current database | |
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database | |
'// stServer: Name of the SQL Server that you are linking to | |
'// stDatabase: Name of the SQL Server database that you are linking to | |
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection | |
'// stPassword: SQL Server user password | |
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) | |
On Error GoTo AttachDSNLessTable_Err | |
Dim td As TableDef | |
Dim stConnect As String | |
For Each td In CurrentDb.TableDefs | |
If td.Name = stLocalTableName Then | |
CurrentDb.TableDefs.Delete stLocalTableName | |
End If | |
Next | |
If Len(stUsername) = 0 Then | |
'//Use trusted authentication if stUsername is not supplied. | |
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes" | |
Else | |
'//WARNING: This will save the username and the password with the linked table information. | |
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword | |
End If | |
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect) | |
CurrentDb.TableDefs.Append td | |
AttachDSNLessTable = True | |
Exit Function | |
AttachDSNLessTable_Err: | |
AttachDSNLessTable = False | |
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description | |
End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment