insert data ODBC

[renee]

I trying to use an ODBC connection and insert data into a database. I
got a script for excel help with I'm trying to modify to my uses.

The scprit call out :

Dim wrkODBC As Workspace
Dim cnWERP As Connection
Dim rsLIMS As Recordset

But VBA dose not recognize, Workspace, Connection, or Recordset -
Compile error: User-defined type not defined.

How do I define these??


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
Dim cnWERP As ODBC.Connection
Dim rsLIMS As ODBC.Recordset


Set ws = Sheets("G211_C")
PrefDATEr = Range("F65000").End(xlUp).Row
soruceDATEr = Range("b65000").End(xlUp).Row

If soruceDATEr > PrefDATEr Then
DBkey = ws.Range("f6")
DAILYDATE = ws.Range("B" & PrefDATEr & ":B" & soruceDATEr)
CODE = ws.Range("C" & PrefDATEr & ":C" & soruceDATEr)
VALUE = ws.Range("D" & PrefDATEr & "" & soruceDATEr)

End If

'Setup WERP Database
Set wrkODBC = CreateWorkspace("WERPworkspace", _
"admin", "", dbUseODBC)
'Set wrkODBC = CreateWorkspace("WERPworkspace", _
' "admin", "", dbUseODBC)
'open connectionobject supplied informatio in the connect string
connectionStr = "ODBC;DATABASE=WRED;UID=pub;PWD=pub;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 & "','" & CODE & "','" & VALUE &
"')"

cnWERP.Execute (sqlStmt)
wrkODBC.commitTrans
cnWRED.Close
'End

ErrHandler
wrok.rollback
cnWRED.Close
response = MsgBox(errMsg, vbCritical, "Error")
End Sub

Function Validate_Field(cnWRED As ODBC.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_Fiels = status

End Function

 

[Guest]

TRY fully qualifying your Data Objects

Excel can use either ADO or DAO

IF you choose ADO ...

Dim rs as ADODB.Recordset

hit compile and if error...
check to see that you have established a reference to the appropriate library
- From VBE >Tools > References ..........

 

[David Lloyd]

Renee:

Your error message is most likely generated because of a missing reference
to the Microsoft DAO 3.6 Object library (assuming you want the latest
version). You will also need to prefix your object declarations with "DAO"
as opposed to "ODBC." For example:

Dim cnWERP As DAO.Connection
Dim rsLIMS As DAO.Recordset

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I trying to use an ODBC connection and insert data into a database. I
got a script for excel help with I'm trying to modify to my uses.

The scprit call out :

Dim wrkODBC As Workspace
Dim cnWERP As Connection
Dim rsLIMS As Recordset

But VBA dose not recognize, Workspace, Connection, or Recordset -
Compile error: User-defined type not defined.

How do I define these??


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
Dim cnWERP As ODBC.Connection
Dim rsLIMS As ODBC.Recordset


Set ws = Sheets("G211_C")
PrefDATEr = Range("F65000").End(xlUp).Row
soruceDATEr = Range("b65000").End(xlUp).Row

If soruceDATEr > PrefDATEr Then
DBkey = ws.Range("f6")
DAILYDATE = ws.Range("B" & PrefDATEr & ":B" & soruceDATEr)
CODE = ws.Range("C" & PrefDATEr & ":C" & soruceDATEr)
VALUE = ws.Range("D" & PrefDATEr & "" & soruceDATEr)

End If

'Setup WERP Database
Set wrkODBC = CreateWorkspace("WERPworkspace", _
"admin", "", dbUseODBC)
'Set wrkODBC = CreateWorkspace("WERPworkspace", _
' "admin", "", dbUseODBC)
'open connectionobject supplied informatio in the connect string
connectionStr = "ODBC;DATABASE=WRED;UID=pub;PWD=pub;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 & "','" & CODE & "','" & VALUE &
"')"

cnWERP.Execute (sqlStmt)
wrkODBC.commitTrans
cnWRED.Close
'End

ErrHandler
wrok.rollback
cnWRED.Close
response = MsgBox(errMsg, vbCritical, "Error")
End Sub

Function Validate_Field(cnWRED As ODBC.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_Fiels = status

End Function

 

[Renee Pfeilsticker]

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