Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: Linking to SQL tables

    Hey all--

    I'm using VBA to link Access dynamically to some SQL tables. However, one of the tables pops up the annoying (shouldn't this have been done when the table was created in SQL Server) window asking for the Unique Identifier. Is there a way to either cancel it or pass the field that's the PK while linking it? Thanks.

    C

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    When you link the SQL Server tables into MSAccess, if you have not established a Unique Identifier, you will always be prompted for one (I think you can click OK/Next without selecting anything to continue without establishing a unique identifier.) Keep in mind that SQL Server and MSAccess are 2 different products so something that is part of MSAccess (ie. asking for a primary key) may not necessarily be part of SQL Server.

    Once the tables are linked, you won't be prompted again for a unique identifier.
    Last edited by pkstormy; 10-02-07 at 10:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Your best option is to add a PK to the table in SQL Server. It may not be possible in your case (database admin won't do it) but in my case I am the admin and it is much easier to deal with.

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    DCKunkle--

    You're right...it would be easier to have the admin add the PK. Unfortunately, I work for a very large company and I'm dynamically linking to many different SQL servers (I'm talking possibly over 1,000 different servers) to pull data from different sites. In other words, I would have better odds winning the lottery than having the PK key added to all the tables.

    Thanks for the replies.

    C

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you get a chance, you may want to gander at this: http://www.dbforums.com/showpost.php...3&postcount=22

    which has vba code to automatically create an ODBC DSN for the user and refresh linked SQL Server tables.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I ran into this problem myself and had to figure out how to avoid it. Here is what I found:

    Code:
        Dim tdfPresort As DAO.TableDef
        Dim strConnectionString As String
    
        strConnectionString = "ODBC;DSN=Visual FoxPro Tables;SourceDB=" & strPath & ";SourceType=DBF;"
        strConnectionString = strConnectionString & "Exclusive=No;BackgroundFetch=No;Collate=Machine;Null=No;Deleted=No;;"
        strConnectionString = strConnectionString & "TABLE=_sortprt"
    
        Set tdfPresort = CurrentDb().CreateTableDef("tblData", dbAttachSavePWD, "_sortprt", strConnectionString)
        CurrentDb().TableDefs.Append tdfPresort
    I did it for a FoxPro table but it should work for SQL Server. One thing that may cause a problem is that because the table has no Primary Key the table is Read Only. For me that is not a problem, but your situation might be different.

Posting Permissions

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