Linking tables from SQL 7.0 using Access 97

[newtond]

I have a database in SQL 7.0. I want a user to update entries in an SQL database using linked tables from Access 97. I can create the link tables and view the data ok. However, when trying to save updated data I get a dialogue box stating that: -

'This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.'

The only options I have are to Copy to Clipboard or to Drop Changes. If I choose either option I get the message: -

'You can't save this record at this time.

Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyway? I choose the No option.'

Can anyone help me with this? Or can you suggest another method?

Thanks

Dan

 

[jitter]

Do you have any bit fields in the SQL Server database? If so are they Null? I had this problem one time and it was related to a bit field I had and if there was a null value Access did not like that so it would change it to a 0. The thing is it did not seem to know that it was the user making this change. Once I got rid of the nulls everything seemed to work correctly.

The other thing to check is that you don't have any code changing any of the values when the form is started up. I don't know if this will help but it is all I know to check right now......

 

[newtond]

Hi Jitter

I have checked the Db and there are no bit fields. I have one null value but this is located in an nvarchar field. I have tried putting an entry in this field and tried the table linking again but it still fails with the same error! I can't see any other code that could be causing an issue.

I am alittle new to SQL so maybe I need to set something in the database? I guess I will have to keep plugging at it!

Thanks for the help anyway!

Dan

 

[tlbroadbent]

The SQL Tables to be updated from Access must be uniquely identifiable to be updateable. One of the following must be true.

1) The table has a Primary Key (unique by definition).
2) The table has a unique index.
3) The table has a timestamp column. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[newtond]

Terry

I assigned an additional timestamp field to the table and used both this and the primary key (id) when using Access to Link Tables. I could then update information in the SQL database using Access ok.

Thanks for the help!

Regards

Dan

 

[GlenJohnson]

I have an old 16 bit application called R-Base. I've upgraded it to the 32 bit version, and imported it into SQL2000. My problem is since it's such an old database, it can't be modified to insert a primary key. This is causing me fits when trying to link to an access front end because it won't let me insert new data into the sql database. Ideas? Thanks. Glen A. Johnson
Johnson Computer Consulting
MCP W2K
glen@johnsoncomputers.us



"There is only one good: knowledge; and only one evil: ignorance."
Socrates (470- 399 BC); Greek philosopher.

 

[tlbroadbent]

Can you use existing columns to create a natural primary key or unique index? Compound keys and indexes can be created on multiple columns. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[GlenJohnson]

No, a lot of the old info is repeated over and over. Someone just told me to make a new field as an int, set the indentity to yes, and then select as primary key. Knowing the person that told me this, I'm betting it will work. Thanks. Glen A. Johnson
Johnson Computer Consulting
MCP W2K
glen@johnsoncomputers.us



"There is only one good: knowledge; and only one evil: ignorance."
Socrates (470- 399 BC); Greek philosopher.

 

[tlbroadbent]

Sure it will work but I thought you said you couldn't add a primary key. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[GlenJohnson]

I don't know enough about sql to realize this was all that was needed. I'm fairly familiar with odbc, and moving tables and such, but when it comes to designing a table and it's attributes, I'm pretty weak. Just moving things without changing them are simple. Once started with one of the old 16bit rbase apps, not to big, converted to 32 bit. Then for the fun of it, I imported into Access, then put the whole thing on my palm pilot. That was easy, changing designs while making sure you don't break rules is a different story. Thanks. Glen A. Johnson
Johnson Computer Consulting
MCP W2K
glen@johnsoncomputers.us



"There is only one good: knowledge; and only one evil: ignorance."
Socrates (470- 399 BC); Greek philosopher.