Hey thanks on the library thing – that worked

But now I can not get the Execute to work – run-time error’91 object
variable or with block variable not set.

Any suggestions?
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


*** Sent via Developersdex http://www.developersdex.com ***