Home |
Search |
Today's Posts |
#1
|
|||
|
|||
ODBC data insert to Oracle
the follow stops a - cnWERP.Execute sqlStmt - (run-time error'91 -
Object varible or with Block variable not set) anyone know why? Sub insertdata() 'soruceDBkey, DAILY_DATE, CODE, VALUE) 'test cases Dim ws As Worksheet Dim DBkey As String Dim DAILYDATE() As String Dim CODE() As String Dim VALUE() As String Dim sqlStmt As String 'Dim sqlstmtdelete As String Dim connectionStr As String Dim wrkODBC As Workspace 'DAO Dim cnWERP As Connection Dim rsLIMS As Recordset Set ws = Sheets("G211_C") PrefDATEr = Range("F65000").End(xlUp).Row soruceDATEr = Range("b65000").End(xlUp).Row count = 0 If soruceDATEr PrefDATEr Then DBkey = ws.Range("f6") For Each num In ws.Range("B" & PrefDATEr + 1 & ":B" & soruceDATEr) count = count + 1 ReDim Preserve DAILYDATE(1 To count) DAILYDATE(count) = num Next num count = 0 For Each num In ws.Range("c" & PrefDATEr + 1 & ":c" & soruceDATEr) count = count + 1 ReDim Preserve CODE(1 To count) CODE(count) = num Next num count = 0 For Each num In ws.Range("d" & PrefDATEr + 1 & ":d" & soruceDATEr) count = count + 1 ReDim Preserve VALUE(1 To count) VALUE(count) = num Next num End If 'Setup WERP Database Set wrkODBC = CreateWorkspace("WERPworkspace", _ "admin", "", dbUseODBC) ' 'open connection object supplied informatio in the connect string connectionStr = "ODBC;DATABASE=WRED;UID=rpfeilst;PWD=Nowwhat1;DSN= WRED" '; Set cnWRED = wrkODBC.OpenConnection("DBHYDRO", _ dbDriverNoPrompt, , _ connectionStr) wrkODBC.BeginTrans 'need testing added here sqlStmt = "insert into DM_DAILY_DATA(DBKEY, DAILY_DATE, CODE, VALUE) values ('" & DBkey & "','" _ & DAILYDATE(1) & "','" & CODE(1) & "','" & VALUE(1) & "')" cnWERP.Execute sqlStmt wrkODBC.commitTrans cnWRED.Close 'End 'ErrHandler: ' wrkODBC.rollback ' cnWRED.Close 'response = MsgBox(errMsg, vbCritical, "Error") End Sub 'Function Validate_Field(cnWRED As Connection, sqlStmt As String) As Boolean ' Dim rs As Recordset ' Dim status As Boolean ' Set rs = cnWRED.openRecordset(sqlStmt) ' If rs.Fields(0) 0 Then ' status True ' Else ' status = False 'End If 'Validate_Field = status 'End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort pages? | Excel Discussion (Misc queries) | |||
ODBC Driver/Import Data | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Formulas (If linked data is not null, then insert "Complete" in ce | Excel Worksheet Functions | |||
insert data from one excel file into another | Excel Discussion (Misc queries) |