Question Linked Table manager (1 Viewer)

FoFa · Mar 1, 2010

What would cause Linked Table manager to relink table fine (except read only for some reason) but if I check the always prompt for new location, then it gives me an "ODBC -- call failed" error.
I wanted to specify a different DSN, but won't let me get that far.

Access 2007
Sql Server 2008

permissions are correct on the SQL server.

HiTechCoach · Mar 1, 2010

AFAIK, you can not use teh Link Table manage for this.

The only way I know to do it is to delete all the linked ODBC tables and create new linked tables.

SOS · Mar 1, 2010

You can modify the ODBC links to a different DSN when using Linked Table Manager. I do it almost daily here at work. We have a test DSN and a Live DSN and we have to switch back and forth while doing programming changes and then releasing to live.

I don't know why you would get a "Read-only" issue but I would select each table ONE-AT-A-TIME and then see which is causing you a problem.

HiTechCoach · Mar 1, 2010

SOS said:
You can modify the ODBC links to a different DSN when using Linked Table Manager. I do it almost daily here at work. We have a test DSN and a Live DSN and we have to switch back and forth while doing programming changes and then releasing to live.

I don't know why you would get a "Read-only" issue but I would select each table ONE-AT-A-TIME and then see which is causing you a problem.

SOS,

Are you doing it with Access 2007?

SOS · Mar 1, 2010

HiTechCoach said:
SOS,

Are you doing it with Access 2007?

2003 and 2007.

MSAccessRookie · Mar 1, 2010

HiTechCoach said:
SOS,

Are you doing it with Access 2007?

I do it regularly with Access 2003 and although I have not tried it with Access 2007, I expect no major issues when we convert to using Access 2007 (or perhaps even Access 2010) later this year.

FoFa · Mar 1, 2010

So far every table gives me that error.
But the first table is not read only, so that does not appear to be the issue.
I have done this before, just this is a new test system I used migration assistant to break the data to a SQL server.
NOTES - Design table (in access 2007), compare ODBC connection strings appear to be the same, except for the table name. If i connect using SQL Server's MS, as the same userID/Password, I can run an update statement on any of the tables. Currently the ODBC Connection string has a coded connection string (DSNless) and i was trying to change to point to an actual DSN to see if made the tables that won't let me update them, update-able. i don't see anything significantly wrong with the current connection strings (other than they don't show the password).

Code:

ODBC;Description=Calibration Control;DRIVER=SQL Server;SERVER=SIIHARDYDEV31;UID=CalibCntrl;DATABASE=CalibrationControl;LANGUAGE=us_english;TABLE=dbo.Assign_Too

MSAccessRookie · Mar 1, 2010

FoFa said:
So far every table gives me that error.
But the first table is not read only, so that does not appear to be the issue.
I have done this before, just this is a new test system I used migration assistant to break the data to a SQL server.
NOTES - Design table (in access 2007), compare ODBC connection strings appear to be the same, except for the table name. If i connect using SQL Server's MS, as the same userID/Password, I can run an update statement on any of the tables. Currently the ODBC Connection string has a coded connection string (DSNless) and i was trying to change to point to an actual DSN to see if made the tables that won't let me update them, update-able. i don't see anything significantly wrong with the current connection strings (other than they don't show the password).

Code:

ODBC;Description=Calibration Control;DRIVER=SQL Server;SERVER=SIIHARDYDEV31;UID=CalibCntrl;DATABASE=CalibrationControl;LANGUAGE=us_english;TABLE=dbo.Assign_Too

One more thing to consider is system related. You might need to check whether the SA for your Network/Database (there might be more than one) has enabled your Network/Database user ID with all of the rights that are required to use the database.

HiTechCoach · Mar 1, 2010

MSAccessRookie said:
I do it regularly with Access 2003 and although I have not tried it with Access 2007, I expect no major issues when we convert to using Access 2007 (or perhaps even Access 2010) later this year.

2007 has issues with comveeting databases.

SP2 helped a lot.

SOS maybe having better luck because of how they have set up the DSN .

With 2007 others are having issues.

I am not converting any of my companies apps to 2007. We might go to 2010.

MSAccessRookie · Mar 1, 2010

HiTechCoach said:
2007 has issues with comveeting databases.

SP2 helped a lot.

SOS maybe having better luck because of how they have set up the DSN .

With 2007 others are having issues.

I am not converting any of my companies apps to 2007. We might go to 2010.

Thanks for the heads up Coach. I will advise the steering committee and maybe we will wait to go to 2010 after all.

FoFa · Mar 1, 2010

I figured it out, was reading some other posts and it struck me, have to have a primary key defined on the SQL table (and represented in the linked table). (Smacks forehead).
Dang user designed Access systems I.T. gets stuck with

SOS · Mar 1, 2010

Yep, that would do it all right.

Glad you figured it out.

Question Linked Table manager (1 Viewer)

FoFa

Registered User.

HiTechCoach

Well-known member

SOS

Registered Lunatic

HiTechCoach

Well-known member

SOS

Registered Lunatic

MSAccessRookie

AWF VIP

FoFa

Registered User.

MSAccessRookie

AWF VIP

HiTechCoach

Well-known member

MSAccessRookie

AWF VIP

FoFa

Registered User.

SOS

Registered Lunatic

Similar threads

Users who are viewing this thread