PDA

View Full Version : ODBC connection if not on users PC



blanchard306
05-26-2016, 09:59 AM
Okay, so I've been looking into how to set up an ODBC connection via VBA if a user doesn't have it. I have a parameter based query (in MS Access) tied to an Excel command button that needs an ODBC DSN connection however the users who will be running the Excel file do not have the ODBC connection nor do they have user permission to the SQL server for the ODBC. I have the below code but am still getting and ODBC connection error when it's not used on my machine.

The line in bold below is what I thought I needed to make it work but it's still not running correct on other machines.



Private Sub CommandButton1_Click()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim varConnection As String
Dim i As Integer
varConnection = "ODBC;DRIVER={SQL Server};Server=Myserver;DATABASE=mydatabase;Trusted_Connection=yes"
'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("myMSAccess.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("Query Zip Code & Market Seg")
'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter ZipCode]") = Range("D2").Value
.Parameters("[Enter Market Seg]") = Range("D3").Value
End With
'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset
'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A6:F10000").ClearContents
'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A6").CopyFromRecordset MyRecordset
' MsgBox "Your Query has been Run"
End Sub