Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    660
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Transferring records (Access 2000)

    What is the basic process for transferring all the records from an existing Access table into a new SQL one? Not all the fields in the old table are needed in the new one.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Transferring records (Access 2000)

    You can use either a query in Access or DTS from SQL Server to transfer the records, depending on whether the SQL Server table already exists or not.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    660
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Re: Transferring records (Access 2000)

    Thanks for your reply. The SQL table already exists and has test data in it, so I need to delete that and then import all the records from the Access table (but not all fields, which are also in a different order).

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Transferring records (Access 2000)

    If your table is a linked (ODBC) table in SQL Server, you can use an append query directly, and it lets you change the field sequence, etc. You can also export a table to SQL Server but that creates a new table in the chosen SQL Server database.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    660
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Re: Transferring records (Access 2000)

    Thanks for your reply. The table is linked via ODBC, but I am a novice with Access and a complete newbie to SQL and am hoping that there may be a user-friendly method ('wizard'?) of transferring records from the Access table into the SQL one.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Transferring records (Access 2000)

    Since you are working with a linked table, it is fairly simple to write a query to delete all the records in the table using Access. As well, an append query written in Access using the query designer can take the desired fields and populate the SQL table. Linked tables behave just like Access tables in almost all respects - specifically just like Access in the two operations you need to perform. If you are unfamiliar with delete and append queries in Access, you will need to study the Help files, and perhaps an introductory Access book.
    Wendell

Posting Permissions

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