Solved

Access ADO offline RecordSets for editing data

Posted on 2014-02-06
2
582 Views
Last Modified: 2014-02-09
Hi

I asked a previous question on how to edit a SQL table from Access and was given the following answer. What code would I actually use to do this?

You could use an Access table for that and convert that and so on - but there is a much easier way to do that especially because you have only few records (40,000 is really not much): Use ADO offline recordsets. The trick is simple: Create an ADO recordset and close the connection of the recordset after downloading it. ADO offers a bunch of properties and methods for batch processing so you can easily let the user edit that recordset with a normal Access form by assigning the recordset directly to the form recordset (which can be either DAO or ADO). There are some differences in handling, i.e. the ID column cannot get a new value at the time the user edit it, ADO automatically uses a replacement ID (negative number as far as I remember) and replaces all new record IDs later when the changes are submitted to the server. When the user clicks a "Submit" button, your code can then reenable the connection to the server and use the methods of ADO which offers for example a conflict list that tells you which record was successfully updated and where are which kind of conflicts (like "other user changed that" or "record was already deleted" and so on) and offers methods to handle these conflicts. You only need to program that in VBA because you must say what to do in which conflict case.
0
Comment
Question by:murbro
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39838662
An "offline" (or "disconnected") recordset is created like this:

Dim con As New ADODB.Connection
Dim rst AS New ADODB.Recordset

con.ConnectionSTring = "Your connection string"
con.Open

rst.CursorLocation = adUseClient
rs.Open "SELECT * FROM SomeTable", con, adLockBatchOptimistic
'/ now disconnect it
rs.Activeconnection = Nothing

Open in new window

From there, you could use bind a form with that recordset. When the user has finished making edits, you can then reconnect and save the changes:

Dim con As New ADODB.Connection
con.ConnectionSTring = "Your connection string"
con.Open

rst.ActiveConnection = con
rst.UpdateBatch

Open in new window

See this KB article for more information: http://support.microsoft.com/kb/184397/en-us
0
 

Author Closing Comment

by:murbro
ID: 39845471
Thanks very much
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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

12 Experts available now in Live!

Get 1:1 Help Now