To call the AttachDSNLessTable function, add code that is similar to one of the following code examples in the Auto-Exec macro or in the startup form Form_Open event: MsgBox 'AttachDSNLessTable encountered an unexpected error: ' & Err.Descriptionįor more information about the CreateTableDef method parameters, see Database.CreateTableDef method (DAO). Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect) StConnect = 'ODBC DRIVER=SQL Server SERVER=' & stServer & ' DATABASE=' & stDatabase & ' UID=' & stUsername & ' PWD=' & stPassword '//WARNING: This will save the username and the password with the linked table information. StConnect = 'ODBC DRIVER=SQL Server SERVER=' & stServer & ' DATABASE=' & stDatabase & ' Trusted_Connection=Yes' '//Use trusted authentication if stUsername is not supplied. '// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connectionįunction AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)Ĭ stLocalTableName '// stDatabase: Name of the SQL Server database that you are linking to '// stServer: Name of the SQL Server that you are linking to '// stRemoteTableName: Name of the table that you are linking to on the SQL Server database '// stLocalTableName: Name of the table that you are creating in the current database '//Purpose : Create a linked table to SQL Server without using a DSN To use this method, create a new module, and then add the following AttachDSNLessTable function to the new module. The CreateTableDef method lets you create a linked table. 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.

Therefore, the linked tables may not be able to connect to SQL Server. When this procedure is not performed correctly, the linked tables may not be able to locate the DSN.

This procedure may be problematic when you have to perform it on more than one computer. But when you move the database to another computer, you must re-create the DSN on that computer. You can use a DSN to create linked SQL Server tables in Microsoft Access. It's also known as a DSN-less connection. This article describes how to create a connection to Microsoft SQL Server for linked tables in Microsoft Access that doesn't use a data source name (DSN). This article applies only to a Microsoft Access database (.accdb and. For more information about this change, read this blog post.Īdvanced: Requires expert coding, interoperability, and multiuser skills. Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise.

Ms access linked tables