Using INSERT INTO with more than one table (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

All straight forward really.

Thanks Charlotte.
Pat

Reply | Quote

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

Reply | Quote

I understand that, but what info is in the PAR set of tables?
Pat

Reply | Quote

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 !!
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

Reply | Quote

What field relates PAR and PARDetails?
Pat

Reply | Quote

>>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

Reply | Quote

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.

Reply | Quote

The table structure for Donations and DonationDetails is as listed in the first post in this thread. Pat was asking about the PAR table structure (which is for storage of fixed information only) which I am not sure about. Should the PAR table structures mirror exactly the Donations and DonationDetails including DonationID as the linking field and primary key?

As to the whole issue of Accounts, the church in question stores its records of donations in a somewhat unusual way in that when a donation is divided into two parts, they physically store two separate envelopes as primary evidence of the donation. So if you put one envelope on the plate with $50 in it and half goes to the current account and half goes to the building fund, they will create a second envelope and change the amount on each to $25. (Don’t ask, that’s just the way they do it!)

Consequently, when posting of the collection plate offering is done into the Donations form, the main form has the Donation ID, Date, Deposit Description (which is used only as a sort of comment field as needed) and the Account. The details then record all of the envelope numbers and the amounts that were posted to that account on that day. The join field is DonationID (autonumber on main, number on sub).

The PAR information, therefore needs to be set up in a similar manner in order to fit the existing table structure. I am quite happy to set up the PAR tables in whatever manner is necessary. In my original design I was leaving out the Date and Deposit Description as that information needs to change with each posting and it seems to make more sense to grab that info from the form that will run the procedure.

Peter

Reply | Quote

After reading this thread again it seems like you have the data captured in PAR and PARDetails, but what is linking these 2 tables together? These tables are populated by the form, right?

If you link your PAR tables via an Autonumber in PAR and a copy of it in PARDetails, then all you have to do is to copy data from PAR/PARDetails to the Donations tables.

If I’m off base about my assumptions, set me straight please.
Pat

Reply | Quote

Here is a copy so you can look at it. PAR and PARDetails are for storage of information which gets posted monthly to tblDonations and tblDonationDetails. The form – Form1(clever name, huh?) is for the convenience of the poster who inserts the date she wants and a description and hits OK. The data is then copied from PAR tables into Donation tables to be recorded. My next step is to get the detail information. Right now the only part that works is copying the data from the main table and setting up the new AutoID (DonationID)

Hopefully this explains it.

Peter

Reply | Quote

Hey that’s good.
What you need to do is to introduce a new field in the tblDonations table called PARDonationID and this is to receive the DonationID field from the PAR table.
Then all you need is an Append query that joins tblDonations and PARDetails together to get all the fields required for the INSERT into tblDonationDetails.

The INSERT query becomes:
INSERT INTO tblDonationDetails ( DonationID, EnvelopeNumber, Amount, Comment )
SELECT tblDonations.DonationID, PARDetails.EnvelopeNumber, PARDetails.Amount, PARDetails.Comment
FROM tblDonations INNER JOIN PARDetails ON tblDonations.PARDonationID = PARDetails.DonationID;

Pat

Reply | Quote

That seems to make sense. I will try it tomorrow. Do I need to add a Where clause to my SQL to limit the Select to the current batch? Otherwise wouldn’t I be picking up every set of batches I ever posted? So, the WHERE clause would be something along the lines of “WHERE tblDonations.DepositDate = strDepositDate” or something with correct syntax (I haven’t actually thought that far.

Peter

Reply | Quote

You will need a WHERE clause, and you could do it your way, as long as the strDepositDate holds the correct date, or, you could introduce a field (yes/no) in the PAR table denoting that the record has been transferred and then use this in the WHERE clause.
Pat

Reply | Quote

OK. Now I’m stumped. The code I posted above works fine and I can get the following append query (from a plain ordinary query not code) to work fine after I run the code to grab the Donation details and dump them in:

INSERT INTO tblDonationDetails ( DonationID, EnvelopeNumber, Amount, Comment )
SELECT tblDonations.DonationID, PARDetails.EnvelopeNumber, PARDetails.Amount, PARDetails.Comment
FROM tblDonations INNER JOIN PARDetails ON tblDonations.PARID = PARDetails.DonationID
WHERE (((tblDonations.ContributionDate)=#5/15/2003#));

Here are the two things I can’t figure out.

1) I have no idea how to insert the SQL into my code. In addition, I have no idea where to insert it in my code either. Do I need to instantiate yet a third recordset? I played around with it for a couple of hours this evening but got nowhere. I have never worked with a querydef object and Access being Access I can’t find the exact syntax I need to set that bit up. So some help about exact syntax and where to insert it in the procedure a few posts back in this thread would be greatly appreciated.

2) I need to replace the actual date in the WHERE clause with the DepositDate as it is entered in my unbound text box on my entry form. In other words, I need to grab the info as a parameter the way you do with any other query by form type data. I have no idea how to revise that syntax either.

BTW, I took Pat’s advice about the PARID being added as a field in tblDonations and that works beautifully. It’s this (&^%^&
SQL/DAO/VBA syntax that has me tied in knots!

Peter

Reply | Quote

Between lines Loop and End Sub in post 226108 above put the following code:
Dim sSql as String
sSql = “INSERT INTO tblDonationDetails ( DonationID, EnvelopeNumber, Amount, Comment )”
sSql = sSql & ” SELECT tblDonations.DonationID, PARDetails.EnvelopeNumber, PARDetails.Amount, PARDetails.Comment”
sSql = sSql & ” FROM tblDonations INNER JOIN PARDetails ON tblDonations.PARID = PARDetails.DonationID”
sSql = sSql & ” WHERE (((tblDonations.ContributionDate)=#5/15/2003#));”
DoCmd.RunSql sSql

If the unbound text box on the form Form1 is named DepositDate then change the last line of the INSERT from:
sSql = sSql & ” WHERE (((tblDonations.ContributionDate)=#5/15/2003#));”
to:
sSql = sSql & ” WHERE (((tblDonations.ContributionDate)=Forms!Form1!DepositDate));”

Pat

Reply | Quote

Worked like a charm! Thank you more than you can imagine.

I was trying to insert my SQL inside the loop, but now that I’ve seen it work I understand why it can go where it goes.

Peter

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

I’m halfway there. This is the code I came up with today. It seems to work quite nicely on the first part of the task which is creating the Donations table entries. I’m not sure where to go from here in terms of where to put the the SQL Insert Into statement to add in the DonationDetail information from the PARDetails table and what the syntax would be. I have never worked with QueryDefs before. If there I things I should fix up or change in the code here, I would appreciate your comments.
*******************************
Private Sub OK_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strAcct As String
Dim strDepDate As Variant
Dim strDepDesc As Variant

Set db = CurrentDb()
Set strDepDate = Forms!Form1!DepositDate
Set strDepDesc = Forms!Form1!DepositDescription

Set rst2 = db.OpenRecordset(“PAR”, dbOpenDynaset)
Do While Not rst2.EOF
With rst2
strAcct = rst2.Fields(“Account”).Value

Set rst = db.OpenRecordset(“tblDonations”, dbOpenDynaset)
With rst
.AddNew
![Account] = strAcct
![ContributionDate] = strDepDate
![DepositDescription] = strDepDesc
.Update
End With
strAcct = “”
.MoveNext
End With
Loop
End Sub
************************

Everything was nicely indented before. I don’t know how to preserve formatting in a post like this. There is a bit of naming housekeeping to do as well. Form1 will get a more useful name. And I started off with strDepDesc and strDepDate as a string and a date respectively and access only seemed to like them declared as variants. I suppose something to do with them coming from unbound fields on an unbound form?

Peter

Reply | Quote