I tried to figure this out myself, but all I know about VBA I learned from this list. I’ve got the VBA figured out to write the insert strings I need, but I can’t figure out how to connect to oracle to add these rows to the table.
I selected the Oracle Data Control and Oracle InProc Server 2.3 Type Library references from Tools>References. I tried using some examples I found online, but I keep getting unhandled exceptions. Maybe I’m not understanding exactly what it’s looking for here. I put my database name where it says “ExampleDB” and my username and password where it says “scott/tiger” and my select statement.
'Declare variables as OLE Objects.
Dim OraSession As Object
Dim OraDatabase As Object
Dim OraDynaset As Object
'Create the OraSession Object.
Set OraSession = CreateObject(“OracleInProcServer.XOraSession”)
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.DbOpenDatabase(“ExampleDb”, “scott/tiger”, 0&)
'Create the OraDynaset Object.
Set OraDynaset = OraDatabase.DbCreateDynaset(“select empno, ename from emp”, 0&)
For connect to database you can create a dsn and use it
I am pasting an example , it select record from database table
Dim cnn1 As ADODB.Connection 'Create an ADO Connection Object
Dim rs As ADODB.Recordset 'Create an ADO recordset Object
Dim strCnn As String 'Create a string to hold the connection information
'Initialize connection string
strCnn = “DSN=dsnname;Uid=user1;Pwd=password1;”
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn 'Open the Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenForwardOnly
rs.Open “select object_name,desc1 from object_desc”, cnn1
rs.MoveFirst 'moves currentRecord pointer to first record
NOTE: to use it you need to add Microsoft ACtive X Dataobjects 2.0 library
Another method for obtaining a connection string that I find I use a lot is to create a .udl file. Once you set it up you can right click and edit it, enabling you to copy the connection string directly into your code.
In Windows:
Create a new text file and save it with a .udl extension, eg test.udl
Open the file by double clicking on it
Complete the first 2 tabs
Test your connection using the test button
Save the file (you’ll have to decide if you want to save your password as part fo the file
Hold the shift key down and right click the file, the menu should have an option called “Open with…”
Select the “Open with” option and then use a text editor like notepad.
8 ) The connection string can then be highlighted and cut and pasted into your code.
This method works for Oracle, MSSQL, ACCESS, Word, etc.
Just be careful you don’t leave the file lying around, since password info can be read from it.
Joanne
(BOB member since 2002-07-10)