Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using INSERT INTO with more than one table (2000)

    I need to run a batch append on a monthly basis and want to use an SQL statement in a sub so that the operator can check that the data is current, then click a button to append the records into another table. INSERT INTO seems to be the way to go, but the problem is there is a table and a sub table and I can

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using INSERT INTO with more than one table (2000)

    You will need to describe the input table/file/whatever. In other words, where do Account, Envelope, Amount and Comment come from.
    You will need 2 insert statements.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Using INSERT INTO with more than one table (2000)

    It's tricky to insert into a multi-table query in Access 2000 and later, even though it was fairly easy in Access 97. The query engine had some significant changes between versions and it's easier to wind up with a query that is not updateable in Access 2000. It might be simpler to just use a recordset on the parent table to add a record, which will allow you to read the unique key for the new record from the recordset . Then you can run an append query to add the child record and use that value as the foreign key.
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using INSERT INTO with more than one table (2000)

    You say it was fairly easy to do in A97.
    If there is an Autonumber field to tie the tables together, how was that accomplished in the query?
    Pat

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

    Re: Using INSERT INTO with more than one table (2000)

    You enforced referential integrity between the tables with cascading updates and included the fields from both tables in the query. In 97, if both tables' primary key fields were in the query, a new record would be added to each and the RI could insert the foreign key into the second table as soon as you added another value to the second table.
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using INSERT INTO with more than one table (2000)

    All straight forward really. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks Charlotte.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using INSERT INTO with more than one table (2000)

    My VBA skills are limited (mainly tweaking others' code to do what I need). Could you talk me through the basics? Is this something that can be done in one procedure with something like a loop or does this need to be done in two steps?

    (As to Pat's question: The static information will be stored in the PAR pair of tables and copied from there into the Donations pair of tables)

    Peter

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using INSERT INTO with more than one table (2000)

    I understand that, but what info is in the PAR set of tables?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using INSERT INTO with more than one table (2000)

    In PAR the one constant is Account and in PARDetails are EnvelopeNumber, Amount, Comment.

    I have been waffling with PAR whether to use update to take the info from the unbound form and insert it into DepositDate and DepositDescription fields and then append the records or whether it is possible to append Account only and grab the other stuff from the unbound form directly. The PARDetails table will remain as listed above.

    The only other question is the whole issue of depositID as that is the Parent/Child field between table and subtable.

    Quite frankly, I don't really care how I accomplish it. I'm just interested in doing it simply and efficiently <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> !!
    The more research I do, the more I realize there are about 6 different ways to do it (if not 60!) and the problem with VBA is that you need to be consistent whichever way you start.

    Peter

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

    Re: Using INSERT INTO with more than one table (2000)

    It can be done in a single procedure and doesn't need a loop. Here are the basic steps that would be fleshed out in code:

    1. Declare a recordset object and then instantiate it using the SET keyword. Using DAO, that would be Dim rst As DAO.Recordset. If you use ADO, it's Dim rst As ADODB.Recordset. The methods and properties and the way you instantiate the object differs for the two models, so you have to pick one. If you specify which object model you want to use, someone can give you the correct syntax for that approach. This will be a recordset on your parent table.

    2. Use the recordset object's AddNew method to add a record to the parent table. Once you have used the Update method to save the record (not required in ADO but allowed), you will be able to refer to the DonationID for that new record in code as rst!DonationID and retrieve the value assigned to it.

    3. In DAO, you would build an "INSERT INTO" SQL statement and create and execute a temporary QueryDef using the DonationID to populate the linking field in the new child record. In ADO, you could use a Command object to execute the same kind of SQL statement.

    Does that help? If you indicate whether you want to use ADO or DAO, the instructions can become more detailed.
    Charlotte

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using INSERT INTO with more than one table (2000)

    What field relates PAR and PARDetails?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using INSERT INTO with more than one table (2000)

    >>What field relates PAR and PARDetails?

    That needs to be determined by how I can make the code work. I started off with a dummy DonationID field that would only function to relate the two fields. I also played around with adding Account into the PARDetails as for a reference table, that also wold work.

    Peter

  13. #13
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using INSERT INTO with more than one table (2000)

    Charlotte, this is exactly what I need. I will use DAO as I find it slightly less inscrutable than ADO. I think I have most of the pieces of the puzzle to give this a try. Three further questions for now.
    1) Do I also need to Dim db as DAO.Database and Set db = CurrentDb()
    2) What would the syntax look like for referring to rst!DonationID
    3) Given that there will be more than two (I can't tell you off hand as I have to get the real data) records in the parent table (one for each account to be posted to) your basic recipe doesn't tell me how to move on to the second record. Wouldn't I need to do the whole thing over again for each record in PAR? In which case, if not a Loop, then what?

    Many thanks for your help so far.

    Peter

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

    Re: Using INSERT INTO with more than one table (2000)

    If your first question is should you separate the two statements instead of Dim dbs As New DAO.Database, the answer is Yes. Resource usage is more controlled when you use separate statements.

    The syntax for referring to it is just to refer to it, i.e., lngDonationID = rst!DonationID. With an Access back end, as soon as you start to create the record, the autonumber is created--assuming you're using an autonumber.

    I don't understand your table structure, and I was answering the question about posting the donation to two tables, not anything to do with accounts. If you have multiple accounts, how are you dividing up the donation?
    Charlotte

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

    Re: Using INSERT INTO with more than one table (2000)

    Sorry, but that doesn't make sense. You don't create linking keys based on what's in your code, you do it the other way around.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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