VB6,Access97,ADO - Update query doesn't update all records 
Author Message
 VB6,Access97,ADO - Update query doesn't update all records

 I am using VB6 and Access 97 in an application and took the opportunity to
 start learning how to incorporate ADO into an VB application.  The
 database  contains several tables and one linked table (to another Access
97
 database).  I designed an update query in Access that uses data in the
 linked table to update a field in one of the other tables.   It works fine
 when I execute it from the Access environment.  However, when I try to
 execute the same query using ADO, not all of the records are updated
 unless  I run the query twice.  The records that are missed the first time
are not
 consistent (sometimes all records are updated).  I think I have tried all
 possible ways to execute the query using ADO to no avail.  Here is the
 basic  code I am using;

 {defined in a general module}
 strConnVOCdbs = "Provider=Microsoft.Jet.OLEDB.4.0; " _
         & "Data Source=" & pathVOCdbs & "; " _
         & "Persist Security Info=False"

 {defined in declaration of Form1}
 Public WithEvents connVOCdbs As ADODB.Connection

 {defined in a procedure on Form 1}
 Set connVOCdbs = New ADODB.Connection
 connVOCdbs.Open strConnVOCdbs

 Dim cmd As ADODB.Command
 Set cmd = New ADODB.Command
 Set cmd.ActiveConnection = connVOCdbs

 With cmd
     .Parameters.Append .CreateParameter("iAnRunSeq", adInteger,
adParamInput, 0)
     .Parameters("iAnRunSeq") = tAnRunSeq
     .CommandText = "update_Results_CalStdConc" 'the name of  the  query in
the Access database
     .CommandType = adCmdStoredProc
     .Execute , , adExecuteNoRecords
     .Parameters.Delete ("iAnRunSeq")
 End With

 Here is SQL statement executed by the Access query:

 sqlS = "PARAMETERS iAnRunSeq Long;" _
     & "UPDATE RunLog INNER JOIN (Results INNER JOIN (AnRuns INNER JOIN
 QCTrueValues ON (AnRuns.MethodID = QCTrueValues.MethodID) AND
 (AnRuns.RunType = QCTrueValues.RunType)) ON Results.Parameter =
 QCTrueValues.compound) ON (RunLog.RunLogSeq = Results.RunLogSeq) AND
 (AnRuns.AnRunSeq = RunLog.AnRunSeq) AND (RunLog.SamType =
 QCTrueValues.samtype) AND (RunLog.LabSampleID = QCTrueValues.Level) SET
 Results.[Value] = [QCTrueValues]![truevalue], Results.ValueUnit =
 [QCTrueValues]![units] " _
     & "WHERE ((([Results]![Value])<>[QCTrueValues]![truevalue]) AND
 ((AnRuns.AnRunSeq)=[iAnRunSeq]) AND ((RunLog.SamType)='cal') AND
 ((Results.ParaType)='co') AND ((Results.ValueType)='conc'));"

 I have tried substituting the sql statement for .CommandText but the
 results  are the same.

 I sure would appreciate any insight you can provide.  Thank you for your
 time and consideration.

Dan Hillman



Sat, 06 Apr 2002 03:00:00 GMT  
 VB6,Access97,ADO - Update query doesn't update all records
that looks like a bunch of {*filter*}to me, did u refresh both databases after
the update?
and if your using Jet, why not use the .Update method? all you have to do is
tell it what variables are equal to what in the database(s), then update,
the refresh

trippz


Quote:
> I am using VB6 and Access 97 in an application and took the opportunity to
>  start learning how to incorporate ADO into an VB application.  The
>  database  contains several tables and one linked table (to another Access
> 97
>  database).  I designed an update query in Access that uses data in the
>  linked table to update a field in one of the other tables.   It works
fine
>  when I execute it from the Access environment.  However, when I try to
>  execute the same query using ADO, not all of the records are updated
>  unless  I run the query twice.  The records that are missed the first
time
> are not
>  consistent (sometimes all records are updated).  I think I have tried all
>  possible ways to execute the query using ADO to no avail.  Here is the
>  basic  code I am using;

>  {defined in a general module}
>  strConnVOCdbs = "Provider=Microsoft.Jet.OLEDB.4.0; " _
>          & "Data Source=" & pathVOCdbs & "; " _
>          & "Persist Security Info=False"

>  {defined in declaration of Form1}
>  Public WithEvents connVOCdbs As ADODB.Connection

>  {defined in a procedure on Form 1}
>  Set connVOCdbs = New ADODB.Connection
>  connVOCdbs.Open strConnVOCdbs

>  Dim cmd As ADODB.Command
>  Set cmd = New ADODB.Command
>  Set cmd.ActiveConnection = connVOCdbs

>  With cmd
>      .Parameters.Append .CreateParameter("iAnRunSeq", adInteger,
> adParamInput, 0)
>      .Parameters("iAnRunSeq") = tAnRunSeq
>      .CommandText = "update_Results_CalStdConc" 'the name of  the  query
in
> the Access database
>      .CommandType = adCmdStoredProc
>      .Execute , , adExecuteNoRecords
>      .Parameters.Delete ("iAnRunSeq")
>  End With

>  Here is SQL statement executed by the Access query:

>  sqlS = "PARAMETERS iAnRunSeq Long;" _
>      & "UPDATE RunLog INNER JOIN (Results INNER JOIN (AnRuns INNER JOIN
>  QCTrueValues ON (AnRuns.MethodID = QCTrueValues.MethodID) AND
>  (AnRuns.RunType = QCTrueValues.RunType)) ON Results.Parameter =
>  QCTrueValues.compound) ON (RunLog.RunLogSeq = Results.RunLogSeq) AND
>  (AnRuns.AnRunSeq = RunLog.AnRunSeq) AND (RunLog.SamType =
>  QCTrueValues.samtype) AND (RunLog.LabSampleID = QCTrueValues.Level) SET
>  Results.[Value] = [QCTrueValues]![truevalue], Results.ValueUnit =
>  [QCTrueValues]![units] " _
>      & "WHERE ((([Results]![Value])<>[QCTrueValues]![truevalue]) AND
>  ((AnRuns.AnRunSeq)=[iAnRunSeq]) AND ((RunLog.SamType)='cal') AND
>  ((Results.ParaType)='co') AND ((Results.ValueType)='conc'));"

>  I have tried substituting the sql statement for .CommandText but the
>  results  are the same.

>  I sure would appreciate any insight you can provide.  Thank you for your
>  time and consideration.

> Dan Hillman



Sun, 07 Apr 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Can't update sorted access query from vb6 using ado

2. Updating fields: footer doesn't update right

3. List Box Doesn't Get Updated After Create/Update/Delete Event

4. Recordset.Update doesn't update on runtime only when debugging the code

5. UPDATE query not updating records

6. DATAREPEATER doesn't update records

7. Record doesn't update when only datacombo changes

8. Problem in VB6: ADO is caching record-updates

9. Problem in VB6: ADO is caching record-updates

10. Problem in VB6: ADO is caching record-updates

11. ADO Won't update records??

12. VB6/ADODB/Access97 Wildcard in SQL doesn't work

 

 
Powered by phpBB® Forum Software