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.


"renee" wrote in message
oups.com...
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 & ":D" & 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