LarsBone
asked on
Excle 2000/XP/03: How to realise SQL REQUEST to Oracle database via ODBC as cell function or VBA?
Hi everybody!
From inside Excel 2000/XP/2003 (cell function or VBA) I want to make an sql request via ODBC to an Oracle 9i database. The request itself would be very simple and would fill approx. 1 column and a couple of rows. TNSPING to this DB works fine.
The question now is: how can I realize this request in an easy way? I had a look at the sql.request function inside Excel but couldn't get it working because I am not sure how to define the connection_string properly. In this case the database is defined by an Oracle service name ("dbserv_tmp") that is the same than the ODBC service name ("dbserv_tmp"). My result was always the same: #value in the cell with the sql.request function.
From inside Excel 2000/XP/2003 (cell function or VBA) I want to make an sql request via ODBC to an Oracle 9i database. The request itself would be very simple and would fill approx. 1 column and a couple of rows. TNSPING to this DB works fine.
The question now is: how can I realize this request in an easy way? I had a look at the sql.request function inside Excel but couldn't get it working because I am not sure how to define the connection_string properly. In this case the database is defined by an Oracle service name ("dbserv_tmp") that is the same than the ODBC service name ("dbserv_tmp"). My result was always the same: #value in the cell with the sql.request function.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What references do you have added in? (TOOLS/REFERENCES in the VBE)
NG,
NG,
ASKER
- Visual Basic For Applications
- Microsoft Excel 9.0 Object Library
- OLE Automation
- Microsoft Office 9.0 Object Library
- Microsoft Excel 9.0 Object Library
- OLE Automation
- Microsoft Office 9.0 Object Library
ASKER
Now I selected the right references and changed the code to this one and it works fine!
Thanks a lot!!
Sub Makro2()
Dim SQL As String
On Error GoTo errhandler:
'set up a remote data connection using the MySQL ODBC driver.
'change the connect string with your username,
'password, server name and the database you wish to connect to.
Set cnMySql = New ADODB.Connection
cnMySql.ConnectionString = "UID=vetsone;PWD=vetsone;d
& "CONNECTSTRING=DBSERV_TMP;
cnMySql.Open
Set adoRS = New ADODB.Recordset
adoRS.CursorLocation = adUseServer
SQL = "SELECT TESTTYP FROM TESTTYPDEFINITION"
Debug.Print SQL
' Query Sales from data
adoRS.Open SQL, cnMySql
' Push to Excel
Application.DisplayAlerts = False
Sheets("Tabelle1").Range("
Application.DisplayAlerts = True
' close record set
adoRS.Close
Exit Sub
errhandler:
MsgBox "There was a problem conecting to ORACLE: " & vbCr & Err.HelpContext & " - " & Err.Description
End Sub
Thanks a lot!!
Sub Makro2()
Dim SQL As String
On Error GoTo errhandler:
'set up a remote data connection using the MySQL ODBC driver.
'change the connect string with your username,
'password, server name and the database you wish to connect to.
Set cnMySql = New ADODB.Connection
cnMySql.ConnectionString = "UID=vetsone;PWD=vetsone;d
& "CONNECTSTRING=DBSERV_TMP;
cnMySql.Open
Set adoRS = New ADODB.Recordset
adoRS.CursorLocation = adUseServer
SQL = "SELECT TESTTYP FROM TESTTYPDEFINITION"
Debug.Print SQL
' Query Sales from data
adoRS.Open SQL, cnMySql
' Push to Excel
Application.DisplayAlerts = False
Sheets("Tabelle1").Range("
Application.DisplayAlerts = True
' close record set
adoRS.Close
Exit Sub
errhandler:
MsgBox "There was a problem conecting to ORACLE: " & vbCr & Err.HelpContext & " - " & Err.Description
End Sub
Great glad it worked for you I should have mentioned you needed ADO as reference....
NG,
NG,
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Are you sure this code should work on Excel 2000?