Connecting to sql server using access vba
|May 6, 2014||Posted by forumadmin under TechQns||
Hello everyone I have spent a few days looking at ways to connect to SQL server using vba and found an interesting post by microsoft on how to setup a DSN-less connection they have provided the code this is what it looks like.
'//Name :AttachDSNLessTable '//Purpose:Create a linked table to SQL Server without using a DSN '//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 Private Sub Form_Open(Cancel As Integer) If AttachDSNLessTable("authors", "authors", "(local)", "pubs", "", "") Then '// All is okay. Else '// Not okay. End If End Sub
My problem here is that this code works but it does not work properly for me because if I were to hand this form to someone else they will not be able to open it because the event is set in Form_open and you cant open the form unless you already have the data table available. Is there a better event feature I can use to get this to create the DSN-less connection before I open the form?
This is the error I receive if I try to open the form without a set DSN “The record source specified on this form or report does not exist.”
Here is the link to the source http://support.microsoft.com/kb/892490 I used method 1.
|Asked By – CaptainKid||Read Answers|