Results 1 to 5 of 5
  1. #1
    MrFormula is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    4

    Change a field to Primary key in linked table


    I need to change the properties of a field to be a primary auto number field. I know that we can't just change it so I am needing some code to copy the existing Contacts table structure. Change the ID field to Primary autonumber. Then we need to copy the contents of the original contacts table to the existing table, rename the old contacts to contactsOld and change the name of the temp table to contacts.

    The reason for these gymnastics is that I am selling some software that will integrate with ShipRite and I need this to happen programitically so I don't have to physically go to each client to do the install.

    I appreciate all the help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You are developing software for market and you have no idea where to start for coding this process? You need to do research and make attempt and when encounter specific issue post question.

    Why aren't tables already set up for integration?
    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.

  3. #3
    MrFormula is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    4
    I appreciate your response and I do have a specific issue.
    I purchased a piece of software called ShipRite They use access for their back end. For me to be able to integrate with their software I only need to change the properties of one field in their contacts table. It is the ID field I need to change it to be autonumber.

    We all know that you can't just change a field to autonumber when there is data in the table. The process that I used on my copy is to create another table with no data and then change the properties of that field. when that is finished I do an append query and move the data over. Rename the table name and it works.

    Now I just need to make this happen programitacally.

    That is the specific question.

    Thanks in advance for the help.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sounds like you need to run a sequence of SQL statements. Look at CREATE TABLE, ALTER TABLE, INSERT SELECT or SELECT INTO FROM, DROP TABLE
    http://www.w3schools.com/sql/sql_alter.asp
    http://msdn.microsoft.com/en-us/library/ms188263.aspx
    And this to rename table
    http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
    Last edited by June7; 11-26-2011 at 01:48 PM.
    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.

  5. #5
    MrFormula is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    4
    Thanks for getting me started.

    Now off to learn how to apply these tools in the right order.

    thanks again.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-19-2011, 06:47 AM
  2. Replies: 1
    Last Post: 09-29-2010, 08:01 AM
  3. Change linked table reference automatically
    By kjuelfs in forum Access
    Replies: 1
    Last Post: 07-20-2010, 09:14 AM
  4. Replies: 1
    Last Post: 06-01-2009, 01:09 PM
  5. Field Types for a Linked Table
    By Jeff_J in forum Access
    Replies: 9
    Last Post: 05-05-2009, 07:12 AM

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