Updating a Table via DAO.Recordset

Question

I've hit a roadblock and need your help.  I have a XML saved permissions table which I import and then update the existing permissions table to match the old data saved in the XML file.  My code is below.  Within the debugger my code seems to work except the rstDestination.Update doesn't do the job.  The table is not updated.  Where did I go wrong?

TIA, -Pat

Answer 1

Hi Pat,

Just wondering if maybe you're hitting a Type Mismatch problem.

Just thinking out loud...

Answer 2

Unfortunately this can't be the case.  My testing uses the exact same data, except for one field change which doesn't get updated. 

Answer 3

I see... The next thought in mind is would the recordset be "read only?"

Still thinking out loud...

Answer 4

Where did I go wrong?

Hi Pat,

Your rstDestination and rstSource use the same table TblPermissions. In that case, an update updates the same values, thus without any changes.

Imb.

Answer 5

No, there are two tables.. TblPermissions  & TblPermissions1 ....

Answer 6

How could I check for this or make sure it's updatable.

Another item I forgot to mention is that one field is typed 'autonumber'.  Maybe this could be my headache.  Grasping at straws, etc...

Answer 7

Are you trying to assign a value to an Autonumber field? If so, do you know if the value you're trying to assign already exists in the table? If so, that would be a problem.

Answer 8

Solution Found!  Turns out the 'FindFirst' command was in error.  I added the strFindString variable to FindFirst.

Answer 9

No, there are two tables.. TblPermissions  & TblPermissions1 ....

Hi Pat,

I see it now. I have misread it time after time.

When opening a recordset you do not need to test for both EOF and BOF: EOF only is sufficient.

When the set contains records, it is Always positioned on the first record, so rstSource.MoveFirst is obsolete.

Imb.

Answer 10

Hi,

Glad to hear you got it sorted out. Good luck with your project.