append simultaneously to multiple tables from form

[R. Choate]

I need to determine the most appropriate way to accomplish the subject task. I am still developing the form but it will have lots of
controls, mostly txtboxes, to take the data from. The form contains several controls corresponding to fields in about 6 or so
related tables. I may end up not setting joins for all of the tables if it becomes too cumbersome and overly complicates my queries.
Also, on a related topic, would I be best advised to create subforms relative to each table? Please consider that I prefer not to
write directly to any table from the form or any subform, so that I can use some error-checking measures such as having some
comboboxes that are populated from various fields from tables which will not be included in the "append". Basically, I'm wanting to
force users to select various names, etc from restricted lists, without allowing them to add a new name. Hopefully my questions make
sense. I'm hoping to avoid a huge append query from the form, naming every control and sending all the data in 6 different
directions.

Thanks.

 

[Allen Browne]

It is actually possible to connect to your (unbound) data source, open a
transaction, and either AddNew to recordsets or Execute action query
statments, before commmitting or rolling back the transaction, so you
ultimately get data simultaneously appended to multiple tables. But they may
well be better ways to do it; in fact you don't need Access at all to do
what we just described.

Access is a relational database (tables with relationships, and queries),
with a rapid-development interface (forms with events and code), and
reporting. Therefore the *first* step in learning how to use Access, is to
create the right relational data structure. We can't tell from your post if
you know how to do this or not, but if you are not sure what 'normalization'
means, you are probably wasting your effort focusing on the wrong things.
The absolutely core concept is to get the one-to-many relationships right
first, before you worry about the interface.

Once you have the tables and relationships right, you can start to think
about the interface. The second thing to learn is about how to work with the
events in the bound form to achive what you need, such as using
Form_BeforeUpdate to run the validation checks you are talking about. If you
choose to ignore these and go with completely unbound forms, you are losing
most of the rapid-development and data-handling power of Access. You may
even be better off not using Access at all once you throw away bound forms.

While I'm sure that this is not the reply you are wanting to hear, I do hope
it is helpful in helping you 'to determine the most appropriate way to
accomplish' your task.

 

[R. Choate]

It appears you are helping me with two seperate posts on different problems. On this topic, I'm afraid I have no choice but to use
Access. I do think Access is a valid choice because there is a lot of related data which is the basis for numerous reports which
specify different configurations of the subsets. Also, my task is to re-build an existing abortion of a database which only has 950
rows but has only one table with 196 fields. The guy who built it knew nothing about Access, and therefore tried to "Excel it", and
force the data into reports. Unfortunately, as you would expect, many of the fields should be data instead. I've attempted
normalization to the best of my ability, but relationships aren't my strong suit. Also, because of the small number of invoices
(primary key - autonumber) in the old single table, I'm having a hard time forcing myself to make this more complex than necessary
(which was the problem I'm supposed to be fixing anyway). I'm sure you can imagine my frustration when I see only 950 rows and
consider investing tons of time doing this. The 196 fields is hard to normalize reasonably and masks the complexity of the
problem(s). Re-working the forms to give them what they saw before (there was a 4-page tab form that pulled from a query for editing
existing records and an identical-looking form for entering data, only it was directly tied to the table) is proving to be more
time-consuming than I ever expected or wanted. I've worked with Access alot but this one is confounding. Your first solution sounds
somewhat interesting but possibly a little over my head. The interface, as you can imagine, is a nightmare because of expectations
based on the old one. As you will also conclude, it is having to be rebuilt because it started freezing up, as I had warned long
ago. I was given the task after about 5 or 10 freeze-ups.

I've got to go post a follow-up question now in the other group where you were trying to help me with ranking records

There is an Excel MVP who is also from Perth who is in the forums a lot. His name is Dave Hawley and operates under the name
"Ozgrid".

--
RMC,CPA


It is actually possible to connect to your (unbound) data source, open a
transaction, and either AddNew to recordsets or Execute action query
statments, before commmitting or rolling back the transaction, so you
ultimately get data simultaneously appended to multiple tables. But they may
well be better ways to do it; in fact you don't need Access at all to do
what we just described.

Access is a relational database (tables with relationships, and queries),
with a rapid-development interface (forms with events and code), and
reporting. Therefore the *first* step in learning how to use Access, is to
create the right relational data structure. We can't tell from your post if
you know how to do this or not, but if you are not sure what 'normalization'
means, you are probably wasting your effort focusing on the wrong things.
The absolutely core concept is to get the one-to-many relationships right
first, before you worry about the interface.

Once you have the tables and relationships right, you can start to think
about the interface. The second thing to learn is about how to work with the
events in the bound form to achive what you need, such as using
Form_BeforeUpdate to run the validation checks you are talking about. If you
choose to ignore these and go with completely unbound forms, you are losing
most of the rapid-development and data-handling power of Access. You may
even be better off not using Access at all once you throw away bound forms.

While I'm sure that this is not the reply you are wanting to hear, I do hope
it is helpful in helping you 'to determine the most appropriate way to
accomplish' your task.

 

[Allen Browne]

If you want to follow through with the idea of wrapping a transaction around
multiple action queries (for an all-or-nothing result), here's an example:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
The example contains only 2 actions (copy to one table, then delete from the
original), but the task is the same for multiple actions, so it just serves
to illustrate what's involved. As you guessed you do need some understanding
of VBA and SQL to use this.

All the best with trying to discern the best way to sort out the issues you
were handed.