Solved

Access ADO offline RecordSets for editing data

Posted on 2014-02-06
2
575 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

19 Experts available now in Live!

Get 1:1 Help Now