Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56

    Unanswered: Create Primary Keys on Linked SQL Tables

    Hello,

    I have a reporting database that deletes and relinks SQL tables during the opening of the database. The problem I am having is that 2 of the 5 tables I am linking do not have primary keys established. Therefore, I get a pop-up box asking me to choose what field should be the unique identifier.

    Is there a way in my connection string to set the identifier? Here is my connection string:

    Code:
    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL Server;UID=MyID;DATABASE=MyDatabase;WSID=MyMachine Name;APP=Microsoft Open Database Connectivity;SERVER=MyServer;PWD=MyPW", acTable, "MyTbl", "MyTbl"
    For a bit more information, if I had the privileges, I would change the table in SQL Server myself, but I don't and getting a change through would probably take 6 months by our vendor.

    My reason for doing this programatically is that it is intended to be part of an AutoImport database that I will run through task scheduler. Since I want the imports to run in the middle of the night, I obviously don't want to be here to select the key and hit OK.

    Any thoughts are greatly appreciated!

    Ben

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Linked tables

    This doesn't answer your question but I thought I'd send this to you anyway. You probably already have code similar to this but here's some code which you can also look at which automatically creates ODBC DSN's/refreshes linked tables. You'll need to change the server name/user in the function but maybe you can compare it with your code and utilize it somehow. I think it also has the ability to link in SQL Server tables.
    Attached Files Attached Files
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Thanks for that code. I have never thought of doing the refresh that way. I will definitely give it a shot. Unfortunately, it doesn't help in my current predicament. I am definitely interested in anyone's thoughts on how to designate the unique identifier through the connection string.

    Thanks!

    Ben

  4. #4
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Still trying to puzzle this out. One thing that I have come across in the help files that might be an option is instead of deleting the link and relinking, to refresh the links. I kind of understand what it is talking about, but I need to use Trusted_Connection=Yes and it says in the help files that you have to use Windows Authentication.

    Does anyone have any ideas on that?

    Thanks,
    Ben

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •