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
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
For 2003:
For 2010:
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
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
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
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=
Set rs = New Recordset
sql = "SELET * from VENDOR"
rs.Open sql, cnProgress
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
http://www.progresstalk.com/showthread.php?118661-Excel-ActiveX-ADO-2.8-and-Open-Edge-10.2B-driver-problem
ASKER
Modify the string with "cnProgress.Open "DSN=EpicorTest905;HOST=HK G-SQL03;PO RT=9460;DB =mfgsys;UI D=sysprogr ess;PWD='t est'" could get rid of the problem. However, the Excel hang up and never retrieve any data.
Any idea ?
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.
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.
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
Tks
Please post Excel Version and Code. I'm just guessing without more information.
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=HK G-SQL03;PO RT=9460;DB =mfgsys;UI D=SYSPROGR ESS;PWD='x xxxx' "
Set rs = New Recordset
sql = "SELET * from VENDOR"
rs.Open sql, cnProgress
End Sub
Sub RtnData()
Dim sql As String
Dim rs As Recordset
Dim cnProgress As New Connection
cnProgress.Open "DSN=EpicorTest905;HOST=HK
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
Alternative using long names
Include only the parameters you want to override from the system DSN settings
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;
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;
What version of ADO do you have now?
ASKER
Microsoft ActiveX Data Objects 2.8 Library. I run Excel 2010 on Window 2003.
Tks
Tks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tks
This site might help