Solved

ADP/SQL (2000) only - Problem adding records to bound forms

Posted on 2004-08-22
16
301 Views
Last Modified: 2008-01-09
See http://www.experts-exchange.com/Databases/MS_Access/Q_21072493.html for a bit more back ground.
I'm nearly ther but not quite:

Form is bound to a View(joining three tables) with unique table set.
User hits custom "New" button.
User fills prompts for fields that are part of FK relationships.
.recordset.addnew method called.
Above data is applied to bound controls.
.recordset.update method is called.
Form is left waiting for user to complete other, non-required, info.

If I check in the SQL Server at this stage I see the new record with above info PLUS defaults that are set in the server.
However the record on the form doesn't reflect these defaults and it doesn't contain information that is calculated on the form OR fields that are calculated in the View.

e.g. check boxes on the form that default to no/off are shown as null.
... controls on the form with expressions such as "=Make + ' ' + Product are empty"

I have a "Done" button on the form including the code ...

    If .Recordset.EOF <> True Then
        If .Recordset.EditMode > 0 Then
            .Recordset.Update
        End If
    End If

The User adds extra info to bound controls then hits "Done".
However when the form moves to another record you get the old "Write Conflict" error box.
Then you get a bunch of "trouble access the OLE blah" and "cannot locate the record blah"

Things are a little better if no extra data is added to the form but it never updates this record with the data from the Server unless you do a menu>refresh. Then this causes more errors which I won't detail here.  ho hum.

Any ideas

Simon


0
Comment
Question by:simon_knee
  • 10
  • 6
16 Comments
 

Author Comment

by:simon_knee
ID: 11863720
Bit more info.

When the "Done" code tries to run it is the .update line that causes a "Row cannot be located for updating. Some values may have been changed since it was last read. Well yes, the defaults that the server applies!
0
 
LVL 9

Expert Comment

by:solution46
ID: 11865220
Simon,

if you have linked tables to a SQL Server BE, it is well worth-while adding a timestamp field to the tables in SQL Server. This is used (so far as I can tell - anyone else please feel free to correct me!) purely to allow Access to get a grip on when rows were updated and handle locking a bit better, and may help with your problem.

I ran into similar trouble with bound forms and linked tables in an Access FE / SQL Server BE a while back. Eventually I just gave up trying to catch all of Access's little foibles and started writing objects to sit behind each form, with an 'all in one' connection object to handle communication with SQL server. This can be a pain (you have to write everything form record navigation to search and sort functions by hand) but is a much more powerful way of doing things and gets round the issues of read/write timings in SQL Server being not what Access was expecting.

Good arguments against this are...
1. the resulting application is effectively a VB app using Access forms, so why bother using Access at all?
2. one of the joys of Access is it allows very quick application development if you use bound forms. By unbinding them and using OO code, this benefit is pretty much killed off and Access offers few benefits over VB.
3. Converting to ADO and direct access to SQL Server can be a very time consuming exercise for anything but the simplest databases and there is a very strong argument for re-writing the FE from scratch.

My best (and probably only) response to this is that unless the Access database has been very well designed (as opposed to the more common 'evolved' databases!), if you are considering moving the BE to SQL Server, it's probably time to re-think the FE anyway.

Hope this is of some help,

s46.
0
 

Author Comment

by:simon_knee
ID: 11870317
Thanks for the thoughts s46.
After more testing I think all I actually need is to find out how to get Access to refresh this new record from the SQL server. All the errors I am getting are because Access appears to be displaying a cached local copy in it's recordset and so doesn't know about the extra info.

A .refresh or .requery doesn't help because.
1. My forms are bound to views with 2000 - 15000 records so are slow to requery.
2. Then forms recordset gets "out of sync" with a listbox I populate from another view.

In fact this listbox is interesting...
0
 

Author Comment

by:simon_knee
ID: 11870343
...continued

3. I still don't see all values from the linked tables in the view.

Back to the listbox, it has a rowsource as a SELECT query based on a View.
This is requeried when the new record is added and it shows information fine. Information that the bound form doesn't show??
0
 

Author Comment

by:simon_knee
ID: 11872060
HURRAH HURRAH HURRAH!!!

I have found my problem and the fix and it is in

http://support.microsoft.com/?kbid=239886

Basically you have to create a procedure in the SQL server the same as the view for the form (with changes to the WHERE statement if there is one) and then put this in the resynccommand property for the form. Strangly I didn't have a WHERE clause so my Procedure is identical to my View but you must use a Procdure in the ResyncCommand property.

Now all I need to find out is why a form refresh puts the listbox out of sync with the form, I think that this is some to to do with Indexes but we shall see.

Now what do I do with all the points across these questions?

Good comments about ADP v VB. I'd like to _think_ my access database was well designed. I've been honing the FE for five years with a number of ground up re-writes. I suspect a future version will move to VB but I am more interested in getting the ADP to work (than learning VB) so that I can get on with new features, a web FE and a mac FE too (well maybe). So I'm not going to give up on ADP just yet as I'm nearly there!
0
 
LVL 9

Expert Comment

by:solution46
ID: 11873024
Have you tried requerying the list box after you resync the form?

About the points - in all honesty you have pretty much figured this out for yourself so I would close the question without allocating points.  I wont be offended, promise! If there's anything in my comments above that is of any use to you, allocate as many points as you see fit :).

s46.

0
 

Author Comment

by:simon_knee
ID: 11873080
There are various (custom) buttons on the form that do this but still the listbox is "out of sync".

I have custom code to search for a record in the recordset based on the unique ID in the, hidden, first column of the listbox. This works fine until the... ah what a tit, of course the recordsetclone is what is out of sync.

That was a realtime eurika moment there!

Bet I can fix it now (or I have another of access's nice little features).

SImon
0
 
LVL 9

Expert Comment

by:solution46
ID: 11873109
Yes, the recordsetclone would be out of date.. :)
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:simon_knee
ID: 11873276
Any quick ideas on how I refresh it too?
I would have hoped that it stayed in sync with the recordset all the time?

Simon
0
 

Author Comment

by:simon_knee
ID: 11873313
FYI it's in sync after the .update it's after a programmatic or menu-item refresh?
(go on work for them points!! (actually you'll get 'em anyhow ;-) ))
0
 
LVL 9

Expert Comment

by:solution46
ID: 11873370
If I remember rightly (been a little while since I've worked with bound forms & stuff) the recordsetclone is a snapshot of the recordset and does not get refreshed with it.

This ties in with the comment that's it's in sync after the update (the main recordset hasn't changed yet) but not after you resync the recordset (when it updates itsefl from SQL Server).

Your best bet is probably to recreate the recordsetclone after the resync.

s46.
0
 

Author Comment

by:simon_knee
ID: 11873831
How do I recreate the recordsetclone?
I set rstFind = .recordsetclone every time I make a search but it is the recordsetclone itself which is out of sync.
I need a method that gets the recordsetclone back in sync with the recordset.
It gets out of sync such that the using bookmark takes you to the wrong record.

e.g.
1-100 records in recordset.
.addnew creates record 101.
.update adds this to sql (and the ResyncCommand property ensures the form is OK)
listbox search feature works fine.
User issues a refresh (or requery)
Now when you click on 1 in listbox the form goes to 2, 2 to 3, ... 101 to 1.
The bookmark in the recordsetclone is out of whack with that of the recordset.

Simon
0
 

Author Comment

by:simon_knee
ID: 11873956
Actually I don't use the recordesetclone, I use the clone method...

With frmForm
   
    Set rstFind = .Recordset.Clone
    If IsNull(strValue) Then
        rstFind.MoveFirst
        FindRecord = "FIRST"
    Else
        rstFind.Find strID & " = " & strValue
        If rstFind.EOF = True Then
            FindRecord = "EOF"
        Else
            .Bookmark = rstFind.Bookmark
            FindRecord = "FOUND"
        End If
    End If
0
 
LVL 9

Accepted Solution

by:
solution46 earned 250 total points
ID: 11873968
Sorry bud, gonna have to bail out on you. My wife's calling me for dinner (and I'm supposed to be working on my tax returns anyway!)

One way or another, you will have to get the recordsetclone every time the user requeries - or else use the original query / view / whatever as the recordsource for the listbox.

s46.
0
 

Author Comment

by:simon_knee
ID: 11874282
FIXED

Ha ha, it was all my own fault.

I had this code in the "Done" button.

    If .Recordset.EOF <> True Then
        If .Recordset.EditMode > 0 Then
            .Recordset.Update
            Call HitListReQuery(frmForm)
        End If
    End If

But in an attempt to track the earlier problem I had commented out the test for EditMode.
Hence I was calling update when I had no data to be updated.
This was screwing things up right royally.
Doh.

Here's some points s46 (may your dinner be tasty and you get a rebate)
0
 
LVL 9

Expert Comment

by:solution46
ID: 11874509
many thanks! sorry I couldn't be more help

the food was good
and the wine was fine (still is, too!)

s46.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now