insert data ODBC

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

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 ..........

"renee" wrote:

> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
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

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 ***