ODBC link from Access to SQL Server changes to read-only

pdanes

Registered User.
Local time
Today, 15:50
Joined
Apr 12, 2011
Messages
228
I have an Access 2007 db front-end that interacts with a SQL Server engine. All works OK, but there is a linked table that has me scratching me head.

There are two copies of the entire database on the SS engine - one real and one test/development. I have the ODBC link set to the test version on my machine, so I don't mangle any real data, and all user machines have their ODBC link pointing to the real data.

When I create a link to a table or view, Access creates the connection string with the name of the database as part of the string. This would create a problem when I deploy a new version of the front end, since it would point to the test data instead of the real data. So I have code that removes the database name from the connection string after creating the link, thereby allowing Access to use whichever database is pointed to by the ODBC link, instead of the hard-coded one in the connection string – test/dev on my machine and real on users' machines. This all works fine.

My problem is with a particular view. The view uses an Outer Join, one main record to zero or one auxiliary record. It is joined to one count field and one calculated field, which may or may not exist, but no duplicates of the main table are created, so the primary key field from the main table remains unique in the view. However, I cannot make it an indexed view – SQL Server does not allow that for views with Outer Joins. It is updateable in SSMSE, and when I create the ODBC link, I specify the unique primary key from the main table as the unique identifier of the linked table. At that point the linked table is updateable from Access, but when I run my VBA code to drop the database name from the connect string, the primary key designation on the ID field disappears and the entire linked table becomes read-only.

Is there some way to re-establish the primary key on the linked table, or to keep it from disappearing? Again, the table is updateable when the link is first created, so it is possible, somehow, but even when I set the database name back into the connection string, the table stays non-updateable. The tbl.RefreshLink command is what blows away the primary key designation and makes the table non-updateable. Actually, if I execute the tbl.RefreshLink all by itself, with NO changes to the connect string, even that trashes the updateable property. I found this article about it,

support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc2000

but no helpful advice. The Access table-linking wizard creates some form of primary key when it creates the linked table, so it can be done, but I haven't been able to figure out how to do it myself.
 
Found this in searching, it may help a bit:

Good grief, yes. That's all it took. Here's the line that did the trick:

Currentdb.Execute "ALTER TABLE [vwAkcesCore] ADD CONSTRAINT PrimaryKey PRIMARY KEY ( [AkcesAutoID] )"

Punched that into the immediate window and the table is updateable again. Funny, though, you can't change that property in the graphic environment - I get the message that it is a linked table and may not be changed, do I want to open it anyway. When I do and make this field the primary key, it seems to work, but then can't be saved. VBA did it just fine, though.

Many thanks, I'm back in business.
 
Great, I am glad that it worked out. If you can, please add to my rep by clicking on the scales above.

;)
 

Users who are viewing this thread

Back
Top Bottom