ODBC connection if not on users PC

[seattlerose]

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.

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"[/B][/U]

'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

 

[ChrisM]

One way around this is to use a File DSN as opposed to a System or User DSN.

Will only work in your case if you have a network share drive that all your users are mapped to.

If so, create a new ODBC, exact same settings, just create it as a physical file, then post it to an out-of-the-way folder on your network. Then in Excel use it as your source link.