Power Query / Microsoft Query / DSN Issue

[Hibbster]

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?

 

[ranman256]

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:

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.

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