Inserting data into an oracle database

[xxing]

Hi

I have some data on sheet1 that I want to insert into an oracle database.

I use excel 7 and the oracle version is 10.

Does someone have some example code that does this? Also, what reference libraries do I have to Tick

Than you for your help
Mark

 

[sxschech]

I have updated excel data into Oracle, but used vba in MS-Access as the code portion to do it. Probably not what you are looking for, but if so, I'll provide the steps.

 

[xxing]

Sure I will look at your Access VBA code. It must have similarities to the Excel VBA code

Thanks for your assistance

Mark

 

[xxing]

Hi

I worked out how to do this. Here is my code for future reference


Sub InsertDataTooOracle(Sql As String)

' The following code inserts data into the oracle database
' Can only run 1 sql statement. Don't use commit. Don't use semi-colon at the end of the sql statement

On Error GoTo ErrorEnd:

Dim oConn As ADODB.Connection 'Reads from Microsoft ActiveX Data Objects 2.8 Library. Has to be checked in <<Tools <<References
Dim Cmd As Command

'Initialise
Set oConn = New ADODB.Connection
Set Cmd = New ADODB.Command

'Database login details
oConn.Open "Driver={Microsoft ODBC for Oracle};" & "Server=NHR3;" & "Uid=nzfsdata;" & "Pwd=NZFSDATA"

'ActiveX commands for setting up
Cmd.ActiveConnection = oConn

'Open the conection
' oConn.Open ' Uncomment if you need to open your database
oConn.BeginTrans
oConn.Execute Sql
oConn.CommitTrans

'Close the conection
oConn.Close

Exit Sub

ErrorEnd:

' Display Error Message for debugging
MsgBox Err.Description

End Sub

Sub Test()

Dim Sql As String

Sql = "INSERT INTO RESP_HDR values('ZZ67','10 PUMP')"
Call InsertDataTooOracle(Sql)

End Sub

In the test procedure add your VBA statetments that read the values from cells in your sheet. Then add a loop to do bulk inserts into the oracle database.

Regards
Mark