Results 1 to 2 of 2
  1. #1
    Petr Danes is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    18

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

    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,
    [http://support.microsoft.com/default...oduct=acc2000][1]



    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.


    [1]: http://support.microsoft.com/default...roduct=acc2000

  2. #2
    Petr Danes is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    18
    Never mind, I got a response form someone in a US forum. Here's all it took:

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

    You can't do it in the graphic environment, but VBA does it just fine and with the primary key in place, the table is updateable again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-17-2010, 08:29 PM
  2. link sql server table to access
    By broken_ice in forum Access
    Replies: 0
    Last Post: 06-28-2010, 12:50 PM
  3. ODBC table read to blank Access table write
    By dibblejon in forum Access
    Replies: 2
    Last Post: 03-10-2010, 08:39 AM
  4. Replies: 5
    Last Post: 03-29-2009, 07:20 AM
  5. Unable to import or link tables through odbc in Access SP2
    By Dave Jenkins in forum Import/Export Data
    Replies: 3
    Last Post: 11-09-2005, 11:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums