Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    18

    Unanswered: "Select Into" query un-links linked table

    Hi all.

    I have an Access 2003 database that now has a backend in SQL Server 2005 due to my converting it.

    In the Access database there is a query that runs to pull data down out of a DB2 table and inserts the data into a table. Since I have converted all tables that used to be local to ACCESS into SQL Server, I am now trying to do a "Select Into" on a linked table. Herein lies my dilemma -- everytime the query is run, the linked table becomes local.

    Can someone tell me why the linked table dissappears and how can I prevent this from happening?

    Thanks!
    g-coding

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No it's not normal and something is wrong in there. Can you post the SQL of your query?

  3. #3
    Join Date
    Feb 2009
    Posts
    18
    A macro calls the following SQL Query:

    SELECT ItemData.PLANT,
    ItemData.ITEMID,
    ItemData.ITEMDESC,
    ItemData.UOM
    INTO [Item Desc Table]
    FROM ItemData;

    ItemData is linked to the DB2 table and [Item Desc Table] is linked to a SQL Sever table.

    Both ItemData and the [Item Desc Table] have four fields for Plant, ItemID, ItemDesc, UOM. If I view the design of the tables within Access they have the same data type. The differences are that [Item Desc Table] has a Primary Key defined on Plant and ItemId and ItemData has no primary keys defined at all. Also ItemDesc and UOM are listed as being required on the ItemData table but not on [Item Desc Table]. I hope this helps.

    g-coding

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does [Item Desc Table] (btw, you shouldn't use spaces in your object names) already exist by any chance?

    If it does then you have to use INSERT INTO... SELECT
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No wonder why the link is broken! Select ... INTO creates a new (local) table:

    SQL Server 2005 Books Online (November 2008)
    INTO Clause (Transact-SQL)

    Updated: 15 September 2007
    Creates a new table and inserts the resulting rows from the query into it.
    see: INTO Clause (Transact-SQL)

    Have a nice day!

  6. #6
    Join Date
    Feb 2009
    Posts
    18
    Thanks for the suggestions.

    Yes, the table already exists. Since I want to get rid of all the data and do a total refresher of information, would you recommend a delete then an insert into...select?

    g-coding

  7. #7
    Join Date
    Feb 2009
    Posts
    18
    Sinndho,

    That makes sense now. I was using the same query the access database originally had. Prior to the SQL Server converstion, the creation of the local table would not have mattered.

    Thanks!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Glad I could help you.

    Have a nice day!

Posting Permissions

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