Solved

Access ADO offline RecordSets for editing data

Posted on 2014-02-06
2
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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