Solved

VB 4.0, Access 7, DAO: Recordset.Edit / Update problem

Posted on 1997-04-01
26
750 Views
Last Modified: 2012-08-13
I have a Sheridan DBGrid in VB 4.0, bound to a Recordset resulting
from a Query to an Access 7.0 Database.

The problem: The Database cannot be updated. I use Edit and Update, but before
Update, the Recordset has the new Value, after Update it has the old one again:

    Do
        ' Data1.Recordset.Updatable yields True here!
        Data1.Recordset.Edit
        ' Data1.Recordset.Updatable yields True here!
       
        Data1.Recordset!SortValue = Data1.Recordset!SortValue + 1
        'This works, SortValue is incremented from 1 to 2 here.
        Data1.Recordset.Update
        ' ... but here, it is 1, the old value, again! (if Update isn't executed
        ' here, it is performed by "MoveNext")

        Data1.Recordset.MoveNext
    Loop While (Not Data1.Recordset.EOF)
   
The Loop shall increment a special sorting field of each Record, starting
with the current one. The Recordset was the result of a Query
(Data1.Recordset = Query.OpenRecordset()), and is Dynaset-Type. It is requeried
after this action, but has the old values.

The Query, saved in the Database, looks like:
PARAMETERS [Key] Long;
SELECT Bauteilschicht.*
FROM Bauteiltyp INNER JOIN Bauteilschicht ON Bauteiltyp.BT_ID = Bauteilschicht.BT_ID
WHERE (((Bauteiltyp.BT_ID)=[Key]))
ORDER BY Bauteilschicht.SortValue;

I also tried wrapping a transaction around the complete loop, with no effect.

Can anyone tell me what might be wrong ?

If I wanted to implement the source within the Access-Database: would it be
faster ? How is it done, as a macro or as a module ?

One field of the data table is connected to the key
(auto value) of the "parent" table, 1 : n, with referential
integrity, and all updating provided.
0
Comment
Question by:PC-Alex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 4
  • 2
  • +4
26 Comments
 
LVL 2

Expert Comment

by:dirkmartin
ID: 1424906
I don't read this much code for 50 points.  I didn't wade through all your code, but rather just to let you know, there is also an UpdateRecord method also.
0
 
LVL 1

Expert Comment

by:aktis
ID: 1424907
This code will work if you create a relationship between the two tables with Update Cascade.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424908
Edited text of question
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:PC-Alex
ID: 1424909
Edited text of question
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424910
aktis:

One field of the data table is connected to the key
(auto value) of the "parent" table, 1 : n, with referential
integrity, and all updating provided.

Sorry, it doesn't work instead.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424911
Adjusted points to 100
0
 
LVL 1

Expert Comment

by:mjsmith032997
ID: 1424912
This is the way I do one:

Dim Qd As QueryDef
Dim Rs As Recordset
Set Qd = dAccounts.QueryDefs("qFindAccountDetails")
Qd.Parameters("MailListID") = mUserMailID&
Set Rs = Qd.OpenRecordset()
Set Data1.Recordset = Rs

The DBGrid has to have the properties set to allow edit and updates (AllowAddNew, AllowAupdate), otherwise I can add new records right on the grid and edit records on the grid or by program control with:

Data1.Recordset.Edit
Data1.Recordset("Credit/Debit") = "C"
Data1.Recordset.Update

I hope this helps
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424913
mjsmith,

I have the two Allows on True, and use nearly the same code;
it doesn't work instead.

Thank you & sorry.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424914
Adjusted points to 150
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424915
Adjusted points to 200
0
 

Expert Comment

by:avellap
ID: 1424916
Are you using the32bit or 16bit VB?
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424917
32 Bit.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424918
Adjusted points to 275
0
 
LVL 1

Expert Comment

by:ksleung
ID: 1424919
Where do you put the above code?
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424920
I have a special "insert" event, that is triggered after clicking a button.
0
 
LVL 1

Expert Comment

by:ksleung
ID: 1424921
I want to clarify some more before I can figure out the cause of the problem.  Can you show me the special "insert" event (function?) first?  Also, is the Sheridan Data Grid has to do with your update process?  Tell me your flow of program, too.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424922
ksleung,

I'm on holiday in the moment, I try to tell you as good as I know from here.
The source of this one function is mainly listed above. The problem is, I want to give users the ability to enter a Row "in between" a SSDBGrid, and the order shall be maintained. So I have a field (SortValue) to contain this sorting information. After a user inserts a Row, this SortValue of the Rows that follow must be incremented by 1. That leads to the problem mentioned.
I guess there are some "AddNew", "Update" and "Edit" commands fired automatically from the SSDBGrid ... but even if so, theoretically the code above should work, but it doesn't.
So much for today, I'm on duty on April 29th next time.

0
 
LVL 1

Expert Comment

by:BBC
ID: 1424923
I must admit, that I really dont understand what the data-sturcture behind is. But here is my proposal to avoid the problem (its the best method for working when using access :)) ):
Give your rows a primary key, e.g. a counter. Save the sorting-position in a seperate table, where you keep  the primary key and the sorting position. Perhaps the update will work properly on this table. For sorting you could use something like the dlookup() function.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424924
Hey BBC,
you did not mean that honestly, did ya? The sorting field is an attribute of the record ... where would you come if you'd always create a new table with all that cross-referencing when you encounter such a problem ? Think of the "theoretical", object - oriented approach ... there must be a "proper" way, I still believe in that.
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424925
worked on the problem today. Got a new trace: I'm sure now that the cause has to do with the Query, from that the recordset results. As one can see in the definition of the query, the SortValue is used as a sorting criteria, and exactly this field isn't updatable later on. There must be some properties of the recordset or data control or database to be set afterwards, to make the changes in this SortField last even behind the update.
I experimented with the database definition of the SortValue, tried "indexed, double values allowed" and "not indexed", also tried "value must be supplied" and "doesn't have to be supplied", all with no effect.
0
 
LVL 1

Expert Comment

by:BBC
ID: 1424926
Just to verify this: Are there double values allowed ? It is not a primary key or something like that ?
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424927
BBC,
the data type of this OrderValue is Long Integer, and it isn't a primary key.
0
 
LVL 1

Expert Comment

by:BBC
ID: 1424928
Are you sure there is no other procedure for lets say "beforeUpdateRecord" that cancels your update somewhere else ?
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424929
BBC,
there it is:

Private Sub BaustoffGrid_BeforeUpdate(Cancel As Integer)
    Data1.Recordset!BT_ID = DataBauteilbez.Recordset!BT_ID
    Data1.Recordset!OrderValue = BaustoffGrid.Row
End Sub

0
 
LVL 1

Accepted Solution

by:
BBC earned 270 total points
ID: 1424930
*** in BaustoffGrid_BeforeUpdate
*** Data1.Recordset!OrderValue = BaustoffGrid.Row

is OrderValue an other field than SortValue (used in loop-code), if not, the little BeforeUpdate-Procedure will overwrite the new value !
0
 
LVL 1

Author Comment

by:PC-Alex
ID: 1424931
Hey BBC,
perhaps you're right, I solved the problem two weeks ago in another, a little more complicated way, and have no time to roll up the whole thing once again now.
But as you were the one to spend the most time trying to help me I give the points to you with an Average grading, I think thats fair.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Advice in Xamarin 21 109
pop out of webbrowser1 control vba6 5 44
Send outlook email from VBS Script 2 59
VBA Replace Function - What am I missing? 3 22
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question