Editing more than 1 table at a time with SQL.

  • Thread starter Thread starter The Flash
  • Start date Start date
T

The Flash

Say I have two tables in a DB called TABLE1, and TABLE2.

TABLE1
[IDNUMBER] [FIRSTNAME]
1 Ryan
2 Jennifer
3 Monica

TABLE2
[IDNUMBER] [LASTNAME]
1 Johnson
2 Snipes
3 Smith


My question is would I be able to use SQL in code to Edit, Delete or Insert
data to both tables in one string rather than one table at a time?

For instance what if I want to change both tables with a [IDNUMBER] = 2 to a
[IDNUMBER] = 4, OR change the [LASTNAME] and [FIRSTNAME] records which have a
[IDNUMBER] = 2 to "Graduated" OR Delete those instances where they equal
that criteria ([IDNUMBER] = 2) all together.

Any help or references are much appreciated!
 
If you set up table relationships, you can have certain 'cascading'
operations happen automatically (not always a good idea).
You cannot operate on multiple rows in separate tables simultaneously
yourself, however, you can set up a transaction such that either both table's
rows are processed or neither is.
I don't know what kind of database you are working with but your question
leads me to believe the design may be suspect.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Say I have two tables in a DB called TABLE1, and TABLE2.

TABLE1
[IDNUMBER] [FIRSTNAME]
1 Ryan
2 Jennifer
3 Monica

TABLE2
[IDNUMBER] [LASTNAME]
1 Johnson
2 Snipes
3 Smith


My question is would I be able to use SQL in code to Edit, Delete or Insert
data to both tables in one string rather than one table at a time?

For instance what if I want to change both tables with a [IDNUMBER] = 2 to a
[IDNUMBER] = 4, OR change the [LASTNAME] and [FIRSTNAME] records which have a
[IDNUMBER] = 2 to "Graduated" OR Delete those instances where they equal
that criteria ([IDNUMBER] = 2) all together.

Any help or references are much appreciated!
Click to expand...

What are these tables? What Entity (real-life person, thing or event) do they
represent? Most critically, why are you (apparently) using two tables to
contain what appears to be the same kind of data?

More details please but... I'm pretty sure you have a design flaw which is the
source of your difficulty.
 
Ok let me change it up a little to explain my question a little better.

If I had a input box ask a user's name it would add a first name to table1
and add the last name to table2. Using a SQL string would I be able to do
this without using .Addnew for every table I am editing? What if I had 10
relational tables in an Access Database that needed to be edited (with the
same IDNUMBER) at once? I'm just trying to cut down on repetitive work.

Rather than coding:

Public Function Add_A_Name(FN as String, LN as String)

Dim ThisDatabase as DAO.Database, FirstRecordset as DAO.Recordset, _
SecondRecordset as DAO.Recordset

Set ThisDatabase = CurrentDB
Set FirstRecordset = ThisDatabase.Openrecordset("Table1")
Set SecondRecordset = ThisDatabase.Openrecordset("Table2")


'Add the first name to Table1
With FirstRecordset
..AddNew
!("FIRSTNAME") = FN
..Update
End With

'Add the last name to Table2
With SecondRecordset
..AddNew
!("LASTNAME") = LN
..Update
End With

FirstRecordset.Close
SecondRecordset.Close
ThisDatabase. Close

End Function
 
Ok let me change it up a little to explain my question a little better.

If I had a input box ask a user's name it would add a first name to table1
and add the last name to table2. Using a SQL string would I be able to do
this without using .Addnew for every table I am editing? What if I had 10
relational tables in an Access Database that needed to be edited (with the
same IDNUMBER) at once? I'm just trying to cut down on repetitive work.
Click to expand...

Some multitable queries can indeed be edited; you will either need an
Autonumber in the "parent" table, and a corresponding long integer foreign key
in the child table. Both ID fields must be included in the query, and you must
not append anything to either table.

However, this suggests that you're taking one record with an IDNUMBER primary
key and scattering its fields across two (or ten!) tables. This would be very,
very peculiar database structure! It's also a very odd way to add data;
inputboxes are a clunky inconvenient user interface, compared to a Form for
the parent table (with as many textboxes or other controls as needed for its
fields) with a Subform for each child table.

It sounds like your data entry needs are specialized and uncommon; if you
could tell us clearly what they are, perhaps someone could give you better
advice.
 
I am designing a Excel Worksheet to function and look like an Access form
with the addition of the various functions and tools available from both
Excel and Access. The client isn't comfortable enough with using Access
solely. So with Excel as the front-end and Access as the back-end I am
accessing a relational database from VBA. Data from the cells in the
worksheet are being transferred to various tables in Access depending on
their category and vice versa. Of course if I were working in Access solely
it would be pretty easy but that's not the case on this one. I'm looking for
some SQL techniques to shorting my code up a bit.

Also, is it possible to display a Access report in Excel? It would be alsome
if I could.
 
I am designing a Excel Worksheet to function and look like an Access form
with the addition of the various functions and tools available from both
Excel and Access. The client isn't comfortable enough with using Access
solely. So with Excel as the front-end and Access as the back-end I am
accessing a relational database from VBA. Data from the cells in the
worksheet are being transferred to various tables in Access depending on
their category and vice versa. Of course if I were working in Access solely
it would be pretty easy but that's not the case on this one. I'm looking for
some SQL techniques to shorting my code up a bit.

Also, is it possible to display a Access report in Excel? It would be alsome
if I could.
Click to expand...

I've had very little experience with interfacing with Excel so I'm really not
the right person to advise. If my suggestion of a query containing both the
link fields doesn't work, please post back with the SQL of the query and the
actual error that you're getting; otherwise you might want to start a new
thread explicitly mentioning the Excel link - others have done this I'm sure.
 
Back
Top