Results 1 to 9 of 9
  1. #1
    dweller569 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Walnut Creek, CA
    Posts
    3

    ODBC Import table drops primary keys

    I have an Access 2010 application that uses several linked MySQL tables. These work just fine.



    BUT, we sometimes want to bring down the DB data to local tables. This is done by using the ODBC "Import the source table into a new table ...". This does import the data, but the primary keys are not in the imported tables. They ARE IN the linked tables. The missing PKs really mess up some of our queries.

    I did notice that you can right-click a LINKED table and select "convert to local table" and the resulting table does have the primary keys. This is fine, but some of our developers are using Access 2007 which doesn't have the "convert to local" command. Plus, this is a pain to do when you have 50+ tables in the DB.

    I have tested the same scenario with a SQLServer DB and got the same results. I have also tried with Access 2016 and still don't get the primary keys.

    So, is this a "feature" of Access, or is there a way to get the PKs in the imported tables?

    Thanks for any suggestions.
    Attached Thumbnails Attached Thumbnails PK Prioblem.jpg  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make the correct keyed access table,
    run an append query.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A 5-field compound key - gag!

    Import records to existing table that has the keys/indexes defined. Delete/Insert records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I have a similar situation to yours with linked tables that originate in SQL server

    Occasionally I create DEMO versions of some of my Access databases for distribution to potential customers via my website.
    Normally the BE files are SQL Server databases, but for the purposes of the DEMO, I create Access BE files.

    As you said, it is essential that the data tables retain their primary key fields.
    With no primary key fields, data sources used in queries & any forms based on several tables become read only
    For this reason, the Export Data wizard from SQL Server CANNOT be used as this copies data but not PK fields

    In order to retain PK fields, I do the following:

    • Import all linked tables from Access database front end including any hidden tables - these become linked tables in the new database
    • Select all tables & run ‘Convert to local table’ – this keeps all primary key fields


    I have a similar issue with SQL views which do not retain PK fields (using connection strings) but that is also solvable

    I also wouldn't use a 5 field compound PK - why not use an autonumber field?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    another more flexible option: create a SSIS package on SQL server to export the data from SQL server to Access.

  6. #6
    dweller569 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Walnut Creek, CA
    Posts
    3
    Thanks, everyone for the good feedback. I am surprised and disappointed that this is the way that Access Import Tables is supposed to work. This is confusing since the Linked tables are OK but the imported tables are incomplete. I wonder why Microsoft designed Access this way.

    So, what I did to address this problem was to create a VBA utility that loops through all of the tables and does a "convert to local table" command on each linked table. This works fine, but I was hoping that there was a setting somewhere that would activate the creation of PKs in the imported tables.

    I appreciate your suggestion that I change my five-column primary key to an auto-number column. I would love to discuss this further, but that topic would need to be in another forum.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by dweller569 View Post
    So, what I did to address this problem was to create a VBA utility that loops through all of the tables and does a "convert to local table" command on each linked table. This works fine, but I was hoping that there was a setting somewhere that would activate the creation of PKs in the imported tables.
    Just so you know, I just use Shift & select all the tables, then click Convert to Local Table & it does them all in one go.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Apparently this is not exclusively Access type of issue. Review http://williamsportwebdeveloper.com/cgi/wp/?p=153
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    Apparently this is not exclusively Access type of issue. Review http://williamsportwebdeveloper.com/cgi/wp/?p=153
    See my comment about exactly that point in post #4
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 1
    Last Post: 01-27-2016, 04:30 PM
  2. ODBC - inport-how many primary keys can i use?
    By HU957 in forum SQL Server
    Replies: 2
    Last Post: 01-08-2015, 08:31 AM
  3. Table allowing duplicate primary keys
    By Count Duckula in forum Access
    Replies: 4
    Last Post: 06-13-2013, 09:12 AM
  4. Replies: 3
    Last Post: 09-25-2012, 05:24 PM
  5. Replies: 11
    Last Post: 06-11-2012, 12:23 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