Link to home
Start Free TrialLog in
Avatar of AXISHK
AXISHK

asked on

Excel VB code for ODBC Connection

Currently, I used 3rd party ODBC created on the computer to a Progress Database and write query to retrieve some data through the Microsoft Excel Query.

Is it possible to put this connection into VB code such that I could pass the query string as parameter to the macro ? Is there any sample coding to illustrate that ?

Thanks
Avatar of borgunit
borgunit
Flag of United States of America image

http://www.connectionstrings.com/


This site might help
Avatar of ScriptAddict
For 2003:

Sub CreateParam()
     Dim oQuery As QueryTable
     Dim oParam As Parameter
     Set oQuery = Sheet3.QueryTables(1)
     oQuery.CommandText = Replace(oQuery.CommandText, "='Berlin'", "=?")
     Set oParam = oQuery.Parameters.Add("CityParam")
     oParam.SetParam xlRange, Sheet3.Range("J1")
     oParam.RefreshOnChange = True
     oQuery.Refresh
End Sub

Open in new window


For 2010:

Sub RunParameterQuery()

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("C:\Integration\IntegrationDatabase.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("MyParameterQuery")

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Segment]") = Range("D3").Value
.Parameters("[Enter Region]") = Range("D4").Value
End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Main").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i

MsgBox "Your Query has been Run"

End Sub

Open in new window

Avatar of AXISHK
AXISHK

ASKER

Coding the following with error :
Automation error
Unspecified error
The code stops at the cnProgress.Open statement, guessing something with the string. For the 2nd suggestion, how to setup the IntegrationDatabase.accdb file. Is it a database or just a connection file ? The database locates on the other server. Tks

Dim sql As String
Dim rs As Recordset
Dim cnProgress As New Connection

cnProgress.Open "localhost=HKG-SQL03;PORT=9460;User ID=sysprogress;PWD=test;"
Set rs = New Recordset
sql = "SELET * from VENDOR"
rs.Open sql, cnProgress

Avatar of AXISHK

ASKER

I use"Progress OpenEdge 10.2A Driver" but I receive different message.

http://www.progresstalk.com/showthread.php?118661-Excel-ActiveX-ADO-2.8-and-Open-Edge-10.2B-driver-problem
Avatar of AXISHK

ASKER

Modify the string with "cnProgress.Open "DSN=EpicorTest905;HOST=HKG-SQL03;PORT=9460;DB=mfgsys;UID=sysprogress;PWD='test'" could get rid of the problem. However, the Excel hang up and never retrieve any data.

Any idea ?


The code stops at the cnProgress.Open statement, guessing something with the string.

For the 2nd suggestion, how to setup the IntegrationDatabase.accdb file. Is it a database or just a connection file ?
This is the new file format for an access database.  In this example it was pulling data from the MS Access database.

Avatar of AXISHK

ASKER

seem like it is related DAO driver in Window 2003 that is not compatible with the Progress. Any idea how to fix it ?

Tks
Please post Excel Version and Code.  I'm just guessing without more information.
Avatar of AXISHK

ASKER

Here is the coding, it stop at cnProgress.Open , tks

Sub RtnData()


Dim sql As String
Dim rs As Recordset
Dim cnProgress As New Connection


cnProgress.Open "DSN=EpicorTest905;HOST=HKG-SQL03;PORT=9460;DB=mfgsys;UID=SYSPROGRESS;PWD='xxxxx' "

Set rs = New Recordset
sql = "SELET * from VENDOR"
rs.Open sql, cnProgress


End Sub
It looks like the connection your using is for this Software specific connection.  I've included a link as well as a copy of the documentation related to this connection type.  Please note that to use a DSN it must be setup as a DSN on your workstation.  If it isn't this won't work.  
I can't help more then this on this line.  If your database is not made by Progress Software please let me know.

Link to your Connection Type

Progress ODBC Driver
TypeODBC Driver
Usage
ManufacturerProgress Software

Standard
Include only the parameters you want to override from the system DSN settings
DSN=myDSN;HOST=myServerAddress;DB=myDataBase;UID=myUsername;PWD=myPassword;PORT=2055;

Open in new window


Alternative using long names
Include only the parameters you want to override from the system DSN settings
DataSourceName=myDSN;HostName=myServerAddress;Database=myDataBase;LogonID=myUsername; Password=myPassword;PortNumber=2055;

Open in new window

What version of ADO do you have now?
Avatar of AXISHK

ASKER

Microsoft ActiveX Data Objects 2.8 Library. I run Excel 2010 on Window 2003.

Tks
ASKER CERTIFIED SOLUTION
Avatar of ScriptAddict
ScriptAddict
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AXISHK

ASKER

Tks