0

I have a project where I have to send data from an Excel spreadsheet to an Oracle database.

Here is the code I am using and I need some help. It stops at the second string statement. I am trying to use an Excel range name to Insert or Update into an Oracle database. Any help would be appreciated.

Const hostName = "host"
Const portNo = "port"
Const srvSID = "SID"
Const usrID = "username"
Const usrPwd = "password"

Sub ADOInsert()
Dim cnAdo As ADODB.Connection
Dim strConnString As String
Dim strSQL As String
Dim ws1 As Excel.Worksheet
'set the worksheet:
Set ws1 = ThisWorkbook.Worksheets("IMPDATA")
Dim Imprange As Range
Set Imprange = ws1.Range("a2:d6")

    strDriver = "Driver={Microsoft ODBC for Oracle};"
    strParams = "CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostName + ")(PORT=" + portNo + "))(CONNECT_DATA=(SID=" + srvSID + ")));"
    strUser = "UID=" + usrID + ";PWD=" + usrPwd + ";"

    strConnString = strDriver + strParams + strUser

    Set cnAdo = New ADODB.Connection
    cnAdo.Open strConnString

    '/ this is hardcoded for number of rows and columns.
    For lngRow = 1 To 10
        strSQL = "Insert Into TESTDATA1 Select "
        strSQL = strSQL & "'" & Range(Imprange, 1).Value & "', "   '/ REQUEST_NUMBER
        strSQL = strSQL & "'" & Range(Imprange, 2).Value & "', "   '/ CID
        strSQL = strSQL & "'" & Range(Imprange, 3).Value & "', "   '/ SERVER_TYPE
        strSQL = strSQL & "'" & Range(Imprange, 4).Value & "' "    '/ COST
        cnAdo.Execute strSQL
    Next
    cnAdo.Close
    Set cnAdo = Nothing
End Sub

Edited by Reverend Jim: Moved to programming forum

2
Contributors
1
Reply
6
Views
6 Months
Discussion Span
Last Post by rproffitt
0

It's been a week so help others help you and call out what line number you see it fail at. My reading of your problem statement has me looking at line 18. Is that it?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.