Access 2007 Unable to update linked SQL 2005 table 1

[dpelizzari]

I have two SQL 2005 tables, ISD SACD and ISD Lease, both tables have a primary key assigned (all bit values have been set to 0, and do not allow nulls). I have created a form using a join between these two tables in Access. I am prepopulating two fields in ISD Lease to ensure the join will work properly (serial number and model number, which are used in join). I have full access to both tables, but I am beating my head against a wall when I try to update any of the fields. I get the ever elusive "recordset is not updateable" error. It would be delightful to be able to update this information, HELP?!

 

[dpelizzari]

you lost me, PHV. Backing up, I created this second table (ISD_Lease2) to capture user input while leaving the asset data (ISD SACD1) alone. Since the table ISD SACD1 is purged and recreated, we wanted to keep the user input data in a second table, and link them based on serial number and part number (from the join in previous posts). If there is an easier way, I am all ears... Thanks for your help so far.

 

[Hap007]

Hi,

Try this.

Edit the query in a design window.
From the Queries Properties, change the RecordSet Type
Fromynaset
To: Dynaset (Inconsistent Updates)

Save the query and then give it a try.

Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[dpelizzari]

Well, I actually took dhookum's advise, and beat my head against the wall until I figured out how to do a form/subform. This is doing exactly what I wanted (so far). Thanks again all for your help!

 

[Prulite]

I recently upgraded to Access 2007. We have a number of users using one database, some with 2003 and some with 2007. The 2003 users are fine but the 2007 users get a corrupt error when trying to open the database. I have no idea where to start, I am guessing it has something to do with security?

 

[Hap007]

Hi Prulite,

For the best answer to your question, I would suggest starting a new post for your problem.

1) My Suggestion would be to split mdb into a frontend and backend. (This may already be done on your system)
2) Create an Access 2003 frontend for your 2003 users
3) Create an Access 2007 frontend for your 2007 users.

Now, it all should be good,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[dpelizzari]

Prulite, not sure how this applies to the original posting, but... can you elaborate on the error at all? Can you get exclusive access to the database in Access 2007 and see what process is failing? I would suggest making a copy of the database and tinkering with it to try to figure out what the issue is offline. I have seen Access 2007 blow up on macros written in older versions of Access.