Microsoft KB Archive/892490

= How to create a DSN-less connection to SQL Server for linked tables in Access =

Article ID: 892490

Article Last Modified on 3/26/2007

-

APPLIES TO


 * Microsoft Office Access 2007
 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.accdb and .mdb).



INTRODUCTION
This article describes how to create a connection to Microsoft SQL Server for linked tables in Microsoft Access that does not use a data source name (DSN). This is also known as a DSN-less connection. The examples that this article contains apply to Microsoft Office Access 2007, to Microsoft Office Access 2003, and to Microsoft Access 2002.



MORE INFORMATION
You can use a DSN to create linked SQL Server tables in Microsoft Access. But when you move the database to another computer, you must re-create the DSN on that computer. This procedure may be problematic when you have to perform it on more than one computer. When this procedure is not performed correctly, the linked tables may not be able to locate the DSN. Therefore, the linked tables may not be able to connect to SQL Server.

When you want to create a link to a SQL Server table but do not want to hard-code a DSN in the Data Sources dialog box, use one of the following methods to create a DSN-less connection to SQL Server.

Method 1: Use the CreateTableDef method
The CreateTableDef method lets you create a linked table. To use this method, create a new module, and then add the following AttachDSNLessTable function to the new module. '//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 = &quot;ODBC;DRIVER=SQL Server;SERVER=&quot; & stServer & &quot;;DATABASE=&quot; & stDatabase & &quot;;Trusted_Connection=Yes&quot; Else '//WARNING: This will save the username and the password with the linked table information. stConnect = &quot;ODBC;DRIVER=SQL Server;SERVER=&quot; & stServer & &quot;;DATABASE=&quot; & stDatabase & &quot;;UID=&quot; & stUsername & &quot;;PWD=&quot; & stPassword End If   Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect) CurrentDb.TableDefs.Append td   AttachDSNLessTable = True Exit Function

AttachDSNLessTable_Err: AttachDSNLessTable = False MsgBox &quot;AttachDSNLessTable encountered an unexpected error: &quot; & Err.Description

End Function To call the AttachDSNLessTable function, add code that is similar to one of the following code examples in the AutoExec macro or in the startup form Form_Open event:   When you use the AutoExec macro, call the AttachDSNLessTable function, and then pass parameters that are similar to the following from the RunCode action. AttachDSNLessTable (&quot;authors&quot;, &quot;authors&quot;, &quot;(local)&quot;, &quot;pubs&quot;, &quot;&quot;, &quot;&quot;)   When you use the startup form, add code that is similar to the following to the Form_Open event. Private Sub Form_Open(Cancel As Integer) If AttachDSNLessTable(&quot;authors&quot;, &quot;authors&quot;, &quot;(local)&quot;, &quot;pubs&quot;, &quot;&quot;, &quot;&quot;) Then '// All is okay. Else '// Not okay. End If End Sub Note You must adjust your programming logic when you add more than one linked table to the Access database. 

Method 2: Use the DAO.RegisterDatabase method
The DAO.RegisterDatabase method lets you create a DSN connection in the AutoExec macro or in the startup form. Although this method does not remove the requirement for a DSN connection, it does help you resolve the issue by creating the DSN connection in code. To use this method, create a new module, and then add the following CreateDSNConnection function to the new module. '//Name    :   CreateDSNConnection '//Purpose :   Create a DSN to link tables to SQL Server '//Parameters '//    stServer: Name of 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 CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean On Error GoTo CreateDSNConnection_Err

Dim stConnect As String If Len(stUsername) = 0 Then '//Use trusted authentication if stUsername is not supplied. stConnect = &quot;Description=myDSN&quot; & vbCr & &quot;SERVER=&quot; & stServer & vbCr & &quot;DATABASE=&quot; & stDatabase & vbCr & &quot;Trusted_Connection=Yes&quot; Else stConnect = &quot;Description=myDSN&quot; & vbCr & &quot;SERVER=&quot; & stServer & vbCr & &quot;DATABASE=&quot; & stDatabase & vbCr End If   DBEngine.RegisterDatabase &quot;myDSN&quot;, &quot;SQL Server&quot;, True, stConnect '// Add error checking. CreateDSNConnection = True Exit Function CreateDSNConnection_Err: CreateDSNConnection = False MsgBox &quot;CreateDSNConnection encountered an unexpected error: &quot; & Err.Description End Function Note If the RegisterDatabase method is called again, the DSN is updated.

To call the CreateDSNConnection function, add code that is similar to one of the following code examples in the AutoExec macro or in the startup form Form_Open event:   When you use the AutoExec macro, call the CreateDSNConnection function, and then pass parameters that are similar to the following from the RunCode action. CreateDSNConnection (&quot;(local)&quot;, &quot;pubs&quot;, &quot;&quot;, &quot;&quot;)   When you use the startup form, add code that is similar to the following to the Form_Open event. Private Sub Form_Open(Cancel As Integer) If CreateDSNConnection(&quot;(local)&quot;, &quot;pubs&quot;, &quot;&quot;, &quot;&quot;) Then '// All is okay. Else '// Not okay. End If End Sub 

Note This method assumes that you have already created the SQL Server linked tables in the Access database by using &quot;myDSN&quot; as the DSN name.

Additional query words: ACC2002 ACC2003 Jet ODBC SQL Server VB VBA

Keywords: kbprogramming kbconfig kbdatabase kbhowto kbinfo KB892490

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.