Solved

Access ADO offline RecordSets for editing data

Posted on 2014-02-06
2
590 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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

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