Solved

Access ADO offline RecordSets for editing data

Posted on 2014-02-06
2
585 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

831 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