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