Link table problem (1 Viewer)

[accessman2]

Hi,

I created the table in SQL Server, and the fieldnames are as

field0 => char (and setup this field as primary key)
field1 => char
Select => bit

After that, I used ODBC link the table to MS Access database,
I input the data in the linked table in MS Access, then I jump to next record, and then go back the previous record, and then edit the information, but, it give out the error message as follow

Write Conflict
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.

I tried to remove the fieldname "Select" in the SQL Server table, then it works fine, but I want to set up one field as the bit datatype in the SQL Server, and then input/edit data in the MS Access.

Does anybody have experience of it?

 

[pdx_man]

Add a field of datatype Timestamp. Don't worry about having to populate it. Do a Google search to find out why. Also, change the name of the field Select. That is a reserved word.

 

[synersoft]

All these years later, this post saved me. In my case, an AFTER UPDATE Trigger on the linked SQL table was causing the dreaded write conflict. Resolved the issue by adding a timestamp column to the SQL table (and relinking) as suggested.

 

[ellenr]

I have an Access program distributed across the state to board members of an organization. It has been used without a problem for around 10 years. The data are in a mysql database accessed by the program via odbc connector. Now, a new user cannot connect. She is running under the access runtime, and kept getting error msg odbc connection failed. So, we downloaded the program and ran the odbc connector on hubby's Win11 MS365 computer with full Access installed. Still won't open because odbc connection failed there also. Just to be sure, I uninstalled the odbc connector on my computer, reinstalled it (the exact same version as theirs), opened the exact same version of the program on my computer and it works perfectly. I am out of troubleshooting ideas--don't even know where to start looking.

 

[ellenr]

To add to my question, I had the user send screen shots of the results when she went to relink tables. The Data Source Information: DRIVER={MySql ODBC 5.1 Driver}: SERVER=www.nc###a.com;DATABASE = nc###a_tables;
result:
ODBC--call failed.
[Microsoft][ODBC Driver Manager] Data source not found and no default driver specified (#0)

 

[cheekybuddha]

Are you really all using such an old MyODBC driver?

What version of MySQL is the server running?

 

[ellenr]

Yes. It is what was available when I wrote this thing, and about ten users are still using the original. I have used it this this month to install on another person's computer without a hitch. I tried on my own computer deleting 5.1 and installing the 8.3 version of the odbc connector and updating the constr table. When I restart the program, it fails. I single step Autoexec macro and it seems to connect but fails on the following step that captures the user's name and login time. The error says ODBC--connection to Mysql odbc 5.1 driver failed. I cannot find anyplace in my code that has 5.1 hardcoded. Any clues where to look? (I really don't want to have to help all of these old people uninstall 5.1 and install 8.3! If I can get 5.1 to work on the one new computer I would prefer.)

 

[cheekybuddha]

You need to ensure that the bit'ness of the driver matches the bit'ness of each machine's Access.

So, if your user has 32bit Access, then use MyODBC 5.1 32bit; if they have 64bit Access then use MyODBC 5.1 64bit.

If that's not the issue, then check firewalls and open ports.

Really you should consider upgrading for security's sake if nothing else.

 

[ellenr]

The bit'ness was the first thing I checked. The first try was on a Win11 64 bit computer without Access, so I had her load the runtime. Got the failure there, so I had her use her husband's computer running 365. He installed the 5.1 connector and got the same result--could not connect. Apparently I can't upgrade to 8.3 connector without going into the registry to clear the 5.1 reference.