Opening as read-only even with full rights?

[mattetc]

I am with a website hosting company. One of our clients uses an ODBC connection in Access to update data on our MSSQL server.



Earlier this week we moved the database from an old server to a new server.

I have logged in remotely to the client's computer and I'm finding that all their tables...both local tables, and the external tables on our server that we have reestablished connection to...are being accessed as read-only. I checked permissions on the user and full Admin rights are granted, but nothing can be edited or deleted. In the "Records" area of the ribbon, New, Save and Delete are all greyed out.

The client's computer is running Access 2007 on Windows 7 professional SP1.

I am not sure how to assist the client, but since problems didn't occur until we made our database change earlier this week it appears as though we caused it. Is there any guidance? I'll be glad to provide further information.

[ItsMe]

It sounds like an ODBC driver issue. Unfortunately, this can get complex. It may be a simple fix too. What version did you upgrade from and what version did you upfrade to? I would recommend these drivers for newer versions od SQL Server, up to 2014. I cannot find a driver for 2016. I guess 11.0 will work?
https://www.microsoft.com/en-us/down....aspx?id=36434

Microsoft documentation for SQL Server Native Client
https://msdn.microsoft.com/library/ms130892.aspx

Before going crazy with driver updates, I would determine what the Access file is using now. Sometimes upgrades will need to be done in tandem. Depending on the features used by the Access file, design changes may need to be implemented.

The fun part will be updating the Access file with the correct connection string. The most fun part will come if they are using features to connect that are not supported by the new driver/server. I would recommend searching the VBA code for ADO to determine if ADO is being used within the Access application.

[mattetc]

Thank you so much for the reply!

I have figured out what was causing the problem...but I don't know why it caused a problem. For my purposes I have solved my issue, but in case this would come up for someone else in the future I'll provide the solution that worked for me.

In my specific case, I had linked to the tables in the relocated database in one operation. Meaning I selected all the tables and linked them at once.

While troubleshooting this morning, I decided to try linking to just one table - and it worked! I then repeated the connection process for each table individually and now they all are working as expected.

Luckily for me there were only 7 tables. Had there been a large number I would probably still be looking for a better solution - of which what was suggested above very well might have done it. But for a small number of tables, doing them one at a time was the solution.

[ItsMe]

Thanks for posting your solution here. So it sounds like you were able to use the Linked Table Manager or one of the ODBC Database Wizard?

Glad to hear it was an easy fix.

[mattetc]

I was using the "ODBC Database" option under External Data. At the "Link Tables" step, rather than selecting multiple tables (which didn't work) I select just one (which did work) and then repeated the whole process six additional times to take care of all seven tables.

Since I was doing this directly on the client's PC, I'm not sure what caused the issue. I went through the exact same steps on my PC and didn't have any issues whatsoever selecting multiple tables on the "Link Tables" step.

Thanks again for the replies.

[ItsMe]

Great. Have a good day and weekend!