Opening a database in Excel

[Rburn99]

I am trying to open a database in excel vba. I started a macro and chose Get External Data from Microsoft Query. That populated a column with data but what I want is a database object I can use to create recordsets such as
db.OpenRecordset(sMySqlString, dbOpenDynaset). The macro created this code:

Sub Macro8()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array("ODBC;DSN=Global_PLA;ServerName=someserver.1583;ServerDSN=GLOBALPLA;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;ClientVersion=10.00.1" _
), Array("51.000;CodePageConvert=1252;AutoDoubleQuote=0;")), Destination:= _
Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT ITEM_MASTER.PART FROM ITEM_MASTER ITEM_MASTER WHERE (ITEM_MASTER.PART Like 'GN%') ORDER BY ITEM_MASTER.PART")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I cleaned that up to this:

Dim db As ADODB.Connection
Dim rs As Recordsets

sConn = "ODBC;DSN=Global_PLA;"
sConn = sConn & "ServerName=someserver.1583;"
sConn = sConn & "ServerDSN=GLOBALPLA;"
sConn = sConn & "ArrayFetchOn=1;"
sConn = sConn & "ArrayBufferSize=8;"
sConn = sConn & "TransportHint=TCP"
sConn = sConn & "ClientVersion=10.00.1"

sSql = "SELECT ITEM_MASTER.PART FROM ITEM_MASTER "
sSql = sSql & "WHERE (ITEM_MASTER.PART LIKE 'GN%' "
sSql = sSql & "ORDER BY ITEM_MASTER.PART"

set db = New ADODB.Connection
db.Open sConn '<-----THIS ERRORS as does db.ConnectionString = sConn : db.Open
set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
MsgBox rs.RecordCount

This obviously is not correct as I get "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Can someone show me what needs to be changed?

Thanks!
Robb

 

[gdesreu]

I think I may be able to help. Ive dealt with this a lot. In order to solve the problem you need to setup a machine data source (exactly as it states in the error) however if youve already set up a machine data source connection and identified a driver you may find it still doesnt work. You need to see if you are running 32bit or 64bit windows. If you havent setup a mdsc then you simply need to do that, if you have and it didnt work, run either of the two cmds from your run window depending on your setup
see below: Im guessing you need to run the syswow cmd to setup your connection and driver. From my understanding the Microsoft uses the system32 folder even on 64bit machines because of the number of applications that are hard coded to run to the sys32 folder. Therefore you need to set the machine data source for the second scenario from a hidden location. (if im wrong about that someone please correct me)
%SystemRoot%\system32\odbcad32.exe is used to create and maintain data source names for 64-bit applications.

%SystemRoot%\SysWOW64\odbcad32.exe is used to create and maintain data source names for 32-bit applications, including 32-bit applications that run on 64-bit operating systems.

 

[Fazza]

hi, Robb. I suspect it is purely the connection string. Please check at http://www.connectionstrings.com/ regards, Fazza

 

[Norie]

I think you are missing a semi-colon between TCP and ClientVersion.

 

[Fazza]

I think you are missing a semi-colon between TCP and ClientVersion.

Well spotted, Norie

 

[gdesreu]

Well spotted, Norie

Man, I am such a noob! Well I guess I am in the right company.

 

[Fazza]

'FWIW, I use this style of construct

sConn = Join$(Array( _
  "ODBC;DSN=Global_PLA", _
  "ServerName=someserver.1583", _
  "ServerDSN=GLOBALPLA", _
  "ArrayFetchOn=1", _
  "ArrayBufferSize=8", _
  "TransportHint=TCP", _
  "ClientVersion=10.00.1"), ";")


'though normally for SQL. such as

sSQL = Join$(Array( _
  "SELECT whatever", _
  "FROM whereever", _
  "WHERE whatever", _
  "GROUP BY whatever", _
  "HAVING whatever", _
  "ORDER BY whatever"), vbcr)

 

[Rburn99]

Thank you everyone for your suggestions and pointers. Norie, thanks for spotting the missing semicolon. I also needed to add "driver=Pervasive ODBC Client Interface;" to the connect string. My finished working connectionstring ended up being this.

"driver=Pervasive ODBC Client Interface;" & _
"ServerName=someserver.1583;" & _
"ServerDSN=GLOBALPLA;" & _
"ArrayFetchOn=1;" & _
"ArrayBufferSize=8;" & _
"TransportHint=TCP;" & _
"ClientVersion=10.00.1"