<P ID="edit"><FONT SIZE=-1>Edited by D Storms on 01/05/03 18:28.</FONT></P> <img src=/S/joy.gif border=0 alt=joy width=23 height=23> <img src=/S/joy.gif border=0 alt=joy width=23 height=23> PROBLEM RESOLVED - see solution at bottom.

I'm at a loss on how to write to tables in Oracle through Microsoft Excel VBA. My ultimate goal is to get Excel VBA to update the records in an Excel spreadsheet to an Oracle database.

I CAN get it to read tables from Oracle, however, I CAN'T get it to insert new or update existing records in Oracle. I get a 'Run-time error '3146' ODBC--call failed. I must be missing something - ODBC connections from Excel are not my strong point. Any help would be greatly appreciated!

Here is the code I'm using:

'EXCEL VBA to read from Oracle Database - WORKS
Dim ws As Workspace
Dim cn As Connection
Dim rs As Recordset
Dim sql As String
Dim Count As Long

Dim office As String
Dim officenm As String

Set ws = CreateWorkspace("{ODBC connection name}", "{user ID}", "{user pswd}", dbuseODBC)
Set cn = ws.OpenConnection("{ODBC connection name}", dbDriverCompleteRequired, False, "ODBC;DSN={ODBC connection name}")
sql = "select OFFICE_ID, OFFICE_NAME from CONV._SALES_HISTORY "
Set rs = cn.OpenRecordset(sql)
Count = 1
While Not rs.EOF
office = rs("OFFICE_ID")
officenm = rs("OFFICE_NAME")
MsgBox office + ":" + officenm
rs.MoveNext
Count = Count + 1
Wend