Power Query / Microsoft Query / DSN Issue

Hibbster

Board Regular
Joined
Feb 1, 2006
Messages
95
I have a spreadsheet which connects to a database and pulls down data for a particular cost centre using a parameter.

To do this I use Data /Get Data / From Other Sources / From Microsoft Query

Everything works like a dream for me but if another user uses the spreadsheet on their PC they get an error message:

[ODBC Driver Manager] Data source name not found and no default driver specified

I could go to ODBC data sources for each user and set up relevant DSN's but I don't want to do that for +100 users.

So my question is......can I query the database in a way that doesn't require the end users to have the DSN's configured on their PC.

Or am I going about getting my data in the wrong way?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I use ADO. No DSN to setup.
BE SURE ADO is put in VBE References: alt-F11, tools, references, Microsoft ActiveX Data Objects x.x Library


connect to the db when the workbook opens
usage:
Code:
Private Sub Workbook_Open()
  CONNECTDB()
end sub


save data to table:
RunActionQry "Insert into table (clientID,Phone) values (123, '534-555-1234')"


get data from db to spreadsheet:
GetLateRecords()


In VBE , insert MODULE and paste the code below into it.
Code:
public goConn as ADODB.Connection
public gDb


'-----------------
sub GetLateRecords()
'-----------------
dim rst
dim sSql as string
 
sSql = "select * from table where [late]=true"
Set rst = getRst(sSql)


range("A1").select
ActiveCell.CopyFromRecordset rst
set rst = nothing
end subs


'-----------------
Public Function getRst(ByVal pvQry) As ADODB.recordset
'-----------------
Dim rst As ADODB.recordset


On Error GoTo errGetRst


If goConn Is Nothing Then ConnectDB


Set rst = CreateObject("ADODB.Recordset")
With rst
    Set .ActiveConnection = goConn
    .CursorLocation = adUseClient
    .Open pvQry
End With
Set getRst = rst


Exit Function
errGetRst:
MsgBox Err.Description, , "getRst():" & Err
End Function




'-----------------
sub ConnectDB()
'-----------------
'BE SURE ADO is put in VBE References:  alt-F11, tools, references.


gDB = "\\server\folder\myDb.accdb"


sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & gDB
OLEDB:EngineType=4"
Set goConn = New ADODB.Connection
goConn.Open sConnect
end sub




'-----------------
Public Function RunActionQry(pvQry, Optional ByVal pbIsSql As Boolean)
'-----------------
On Error GoTo errRun


  ' Assign to ADO Command object
Set goCmd = New ADODB.Command
With goCmd
  .ActiveConnection = goConn
  .CommandText = pvQry
  
  If pbIsSql Then
    .CommandType = adCmdText
  Else
    .CommandType = adCmdStoredProc
  End If
  
  .Execute
End With
Exit Function


errRun:
RunActionQry = Err
End Function
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top