Problem with .Update

[Mike H.]

On the below code, I get an error on the line .Update. The message says

"[SmartWare ODBC Driver] Parameter can not be used in select list." Anyone
have any idea what to do to write a new record to this odbc-compliant table?


Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "DRIVER=SmartWare Driver (*.db);SERVER=Smart4;DBTYPE=Smart4;DBQ=
" & DataSource
Set rs = New ADODB.Recordset
rs.Open "pr_ckreg", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew ' create a new record
.Fields("gross_pay1") = DataArray(X, 18)
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

 

[Ronald R. Dodge, Jr.]

Look through the documentations dealing with the SmartWare Driver and see
what cursor key types are allowed against the engine using ADO. For
Example:

When using a Jet Engine (What Access uses), the only way to get a dynamic
connection with it is to use the dbOpenDynamic for the type and the
dbSeeChanges for the options using DAO 3.60. However, for when using ADO
against the Jet Engine, can't use adOpenDynamic or adOpenKeyset, thus forces
one to think about using the disconnected FE/BE model similar to what is
trained with ADO.NET coding.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Mike H]

Mike,

Some weeks ago Bob Philips was trying to contact you, he thought I was you
because of the similar sig.

It concerns a post you made some time ago regarding named ranges in Excel. I
don't see him posting tonight but if you send me an email to the address
below I'll contact him and send your email if that's OK.

I think it was this post

http://www.microsoft.com/office/com...c737&cid=131da317-0d41-4136-9eba-261c6be94592

mhughes10 at sky.com


Change the obvious

Mike

 

[Mike H.]

I actually solved this problem but now have another one.

the .Update line yields this error message:

[Smartware ODBC driver] Parameter cannot be used in select list.

Ideas on this?

This is the code:

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "DRIVER=SmartWare Driver (*.db);SERVER=Smart4;DBTYPE=Smart4;DBQ=
" & DataSource
Set rs = New ADODB.Recordset
rs.Open "pr_ckreg", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew ' create a new record
.Fields("full_name") = DataArray(X, 1)
.Fields("checkdate") = CheckDate
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

 

[Ronald R. Dodge, Jr.]

The keyword Parameter makes me think of criterias like the Where Clause
requesting for prompted values from the user prior to the running of the
query. There again, I'm not sure what the limitations are of that
particular DB engine, but look through the documentations.

One thing I do know, the Select list (Clause) only list fields. The Select
Clause will not involve clauses. There has been times when I have had very
complex SQL statements to get around such issue, but it should be more of an
exception to the rule and those cases are generally to a special type case
when one have to think of how things work in real life and convert to
computer language (what ever language one is using such as SQL in this
case). The one case I am referring to, it's using expressions based on data
within the records already, but still don't rely on values from the user.
Here's an example below that I have had to use within one of my more complex
queries, though this is just a minor part of that rather complex SQL
statement.

MAX ( CASE
WHEN
CASE
WHEN ifnull ( f41002_1.UMRUM, '0' ) = '0' THEN f41002.UMRUM
ELSE f41002_1.UMRUM
END = 'LB' THEN
CASE
WHEN IFNULL ( F41002_1.UMCONV, '0' ) = '0' THEN F41002.UMCONV
ELSE F41002_1.UMCONV
END
ELSE 0
END ) AS UMLBBD,

FROM Clauses dealing with joins can get to be rather complex too. The
following is a bit on the simple side, but if not careful, can also run into
time issues wtih running such type join clauses.

LEFT OUTER JOIN CSKDTA.F4801 F4801_1
ON
F4801.WAMCU = F4801_1.WAMCU AND
F4101_2.IMITM = F4801_1.WAITM AND
F4801.WADOCO > F4801_1.WADOCO AND
F4801.WASTRX >= F4801_1.WASTRX AND
DATE( F4801.WASTRX, CYYDDD ) <= DATE( DAYS( DATE( F4801_1.WASTRX,
CYYDDD ) ) + 15 )


Anyhow, parameters only go into the WHERE Clause as far as I know.


--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000