Temporary recordset in MS Access VBA

765 pts.
Tags:
Access 2003
ADO
DAO
Microsoft Access
MS Access 2003
MS Access VBA
Hi, I am using MS Access 2003 as a front end to 2 Oracle databases. I am trying to retrieve a recordset from a database through ADO/DAO, display that recordset in a Form for users to make changes, and then write the records that have been changed to a different database (i.e. I don't need to make changes to the original recordset). I am happy with retrieving the initial recordset, and inserting/updating records in the different database, but the middle part is causing me lots of pain. Basically, I have an unbound form, which has a search button on it. When the user clicks it, they enter a value in an InputBox, and then I have used this value to fetch data into a recordset from a read only Oracle database connection. I then bind the form to this recordset, and the user sees the records in the form. No problems yet. When I try to change 1 or 2 of the values in form view, it brings back error messages, or won't allow me to update the field/s (dependant on which field I change first), and I can't get past this. I have tried to use a Disconnected recordset, as well as various combinations of cursor location, cursor types and locktypes (although doesn't really make much difference to a read only connection), but nothing seems to work. I am trying not to have to use a temporary database and/or temp table, but is this the only option I have? Can I use an array to pass the recordset into, and then use the array as the source for the form (without having to loop through each record in the array)? Is there another way to do this? Thanks, Darryn
ASKED: February 16, 2009  6:40 PM
UPDATED: February 19, 2009  12:05 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

What error messages exactly are you receiving and when exactly do you receive them?

we really need to the type of error that you receive…Remember that the error handling label should be place as a comment during code writing and testing to show you the line in the error has occured. but after testing and compiling the the comments should be removed to tell the user what error in description form..

I hope you are familiar with this one.

on error goto errorhandler

‘executed statements here

errorhandler:

msgbox Err.description,0, Error

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    You say that you bind the form to the read only database. This is why you can't make changes to the fields. If you don't want a temp table, you will probably need to leave the form unbound and manully set the value of each field from the recordset. Then when you make a change to the field and update a new updatable recordset.
    1,740 pointsBadges:
    report
  • Darryn
    Ok, I have passed the read-only recordset into an array, then created a new recordset, and added the same number of fields (and datatype etc) to the recordset, with the same field names. I have then opened the recordset, and passed the values from the array into the new recordset (I have double checked, and all the values are consistent between the 2). I then bound the form to the new recordset, which has caused even stranger behaviour of the form. The number of rows displayed in the form is correct, but every field comes back as "#Error" (when I bind it to the original recordset it displays correctly). Now, what is even stranger, when I click in one of the list boxes, an error occurs, stating that the db engine could not find the object '~sq_fFORMNAME' (not sure why it is looking for that form, but it is correct when it says it doesn't exist). When I click on 'Ok' from the error message, it then lets me select a value from the list (Yes/No). Strangely, when I select one of them, the rest of the record then populates correctly, but the 2nd record doesn't. I can then change the values of the first record as expected. However, when I click in the list box of the 2nd record, it immediately sets all the values in the form to '#Name?', and says that the control can't be edited as it is bound to an unknown field. All very strange, and very frustrating.........
    765 pointsBadges:
    report
  • Randym
    How are you binding the form to the recordset? What syntax are you using. Is it a linked table?
    1,740 pointsBadges:
    report
  • Darryn
    No, no linked tables. My form has 5 fields, with the field's control source set to a, b, c, d and e respectively. My code goes like something like this: sub populate() dim...... set rs = new adodb.recordset rssql = "select a, b, c, 'No' as d, 'No' as e from table1" with rs .ActiveConnection = OracleConn .Source = rssql .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockBatchOptimistic .Open End with countRs = rs.recordcount if countRs > 0 then rsArray = rs.GetRows(rs.recordcount) set rs2 = new adodb.recordset With rs2 .fields.append "a", adBigInt,,AdFldUpdatable .fields.append "b", adBigInt,,AdFldUpdatable .fields.append "c", adChar,10,AdFldUpdatable .fields.append "d", adChar,3,AdFldUpdatable .fields.append "e", adChar,3,AdFldUpdatable .index = "a" .cursorlocation = adUseClient .cursortype = adOpenStatic .LockType = adLockOptimistic .Open For n = LBound(transA,2) to UBound(transA,2) With rs2 .AddNew For p = 0 to .Fields.Count - 1 .Fields(p).Value = transA(p,n) Next p .Update End With Next n Set Me.Recordset = rs2 'If I use 'rs.ActiveConnection = Nothing 'Set Me.Recordset = rs 'it populates the form, but doesn't allow any updates End if End sub
    765 pointsBadges:
    report
  • Darryn
    Ok, got it working, but I don't know why it wouldn't work. Basically, I removed the form fields controlsource property, and then set the controlsources in the code, just before I set the me.recordset = rs2. Thanks to all.
    765 pointsBadges:
    report
  • JohnKennedy
    I it is not always late to take the right decision.I use a simple codes. If still av the same problem you can contact thru' my email.U need to write a procedure for calling table data on the formfor instance;private sub fillForm_load() with rstTable        txtname.text=!Firstname  end withend subfor saving; also write the procedure to and binds the controls on the form with the fields in your new database.private sub fillTable() with rstTable        !MyNewField=txtname.text  end withend subAnd attached this Save procedure in the Save Command.
    415 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following