Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Data Transfer (10.4)

    Continuing the Data Transfer thread, the code works for transferring from one Source table to another Target table. If I want to combine 2 or more Source tables into one Target table only the the data into table Members is transferred. I need to have the corresponding Attending table also added. I have added a where statement to the second ssql but that did not help. Here is the code so far:

    sSql = "INSERT INTO Members(Firstname, Lastname, Address," & _
    " City, State, Zip, Country, Phone, Fax, ClassYear," & _
    " Birthdate, Email,Spouse, MarriedName)" & _
    " IN '" & App.Path & "school.rlc'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & strSelect

    cData.Execute sSql, Options:=adCmdText + adExecuteNoRecords
    cData.Close
    cData = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security" & _
    " Info=False;Data Source=" & App.Path & "School.rlc"
    cData.Open
    sSelect = "SELECT ID FROM Members"
    sSql = "INSERT INTO Attending(ID)" & _
    " WHERE Attending.ID <> Members.ID" & _
    " IN '" & App.Path & "school.rlc'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & sSelect
    cData.Execute sSql, Options:=adCmdText + adExecuteNoRecords

    Any ideas are greatly appreciated.
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Transfer (10.4)

    You state that you "want to combine 2 or more Source tables into one Target table", but in your code you have "INSERT INTO Members" and "INSERT INTO Attending", so it seems that you are inserting into two Target tables.
    Can you state precisely what you want to do?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Data Transfer (10.4)

    Thanks for the response. What I meant to say is that I have a db with 2 tables and are joined. I was able to transfer, with the generous help of this forum, data from one Source table to the two Target tables. Your post re: 242257 listed three options. the out come is this code:
    sSql = "INSERT INTO Members(Firstname, Lastname, Address," & _
    " City, State, Zip, Country, Phone, Fax, ClassYear," & _
    " Birthdate, Email,Spouse, MarriedName)" & _
    " IN '" & App.Path & "school.mdb'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & strSelect

    In order to have the matching ID' s in the second table:

    sSelect = "SELECT ID FROM Members"
    sSql = "INSERT INTO Attending(ID)" & _
    " IN '" & App.Path & "school.mdb'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & sSelect

    This code works when there is one Source table and my two Target tables. However, when I select the second Source table, the data is inserted only into the first Target (Members) table. This combined code gives me a msg can not do this because it would create duplicates... So I tried adding the WHERE statement

    sSql = "INSERT INTO Attending(ID)" & _
    " WHERE Attending.ID <> Members.ID" & _
    " IN '" & App.Path & "school.mdb'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & sSelect

    which gives me a syntax error. So I changed the Attending.ID to Attending(ID) <> Members(ID). Syntax error. Both Target tables are needed since they both will eventually contain data for the program. And without the relationship between the two tables, I can not view the records.

    At this point, I do not know what else to try.
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Transfer (10.4)

    You can't use INSERT INTO ... WHERE ... SELECT ... FROM ...
    Instead, you must use INSERT INTO ... SELECT ... FROM ... WHERE ...

    but this is going to be difficult because you have to select records from Members whose MemberID doesn't occur in the target table. Since the target table lives in another database I don't know if the following will work:

    sSql = "INSERT INTO Attending (ID)" & _
    " IN '" & App.Path & "school.mdb'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & sSelect & _
    " WHERE Members.ID Not In " & _
    " (SELECT ID FROM Attending IN " & App.Path & "school.mdb'" & _
    " [Provider=Microsoft.Jet.OLEDB.4.0] " & ")"

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

    Re: Data Transfer (10.4)

    I'm not going to ask why you are inserting the same data into two tables, but if you need matching IDs in the tables, then why not insert the data originally into *one* table and then do an append from that table into the second instead of the way you're going about it? Doing it that way, you could append the ID along with any other information you needed into the second table in one pass. As to your last attempt, I haven't any idea what you're trying to do. You didn't specify the sSelect value going into that SQL, so I can only suspect that you're still using the previous value of that variable, which would indeed give you a duplicate key or index error because you've already appended the IDs from the Members table.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Data Transfer (10.4)

    Thanks a bunch, Hans. All I had to add was an apostrophe after the Where clause WHERE Members.ID Not In ' " & _ .

    Appreciate your help.
    Thanks

    Kim

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Data Transfer (10.4)

    Charlotte,

    I am not adding the same data to two Target tables only to the Members table. The Attending table is used for other purposes but needs to maintain the relationship. Therefore the need to add the Members.ID to the Attending table.
    Thanks

    Kim

Posting Permissions

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