Editing more than 1 table at a time with SQL.

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!
0
Utf
4/29/2010 7:15:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

7 Replies
718 Views

Similar Articles

[PageSpeed] 6

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


"The Flash" wrote:

> 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!
0
Utf
4/29/2010 9:54:01 PM
On Thu, 29 Apr 2010 12:15:01 -0700, The Flash
<TheFlash@discussions.microsoft.com> wrote:

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

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

             John W. Vinson [MVP]
0
John
4/30/2010 12:45:35 AM
John W. Vinson wrote:

>More details please but... I'm pretty sure you have a design flaw which is the
>source of your difficulty.

hmm... wait, I resemble that remark!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1

0
PieterLinden
4/30/2010 1:19:39 AM
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



0
Utf
4/30/2010 4:18:01 AM
On Thu, 29 Apr 2010 21:18:01 -0700, The Flash
<TheFlash@discussions.microsoft.com> wrote:

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

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

             John W. Vinson [MVP]
0
John
4/30/2010 5:03:17 AM
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. 


0
Utf
4/30/2010 6:03:02 AM
On Thu, 29 Apr 2010 23:03:02 -0700, The Flash
<TheFlash@discussions.microsoft.com> wrote:

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

             John W. Vinson [MVP]
0
John
4/30/2010 4:02:13 PM
Reply:

Similar Artilces:

2 Questions on Pivot Tables
Any assistance appriciated. (Excel 2003) 1) I have a simple Pivot table showing 6 groups with their totals and a subtotal eg: America 10 UK 15 Oz 22 etc. Grand Total 125 I want to Pivot Graph this data, no problems. I want however an extra line to show the total on the Secondary Axis. I have almost got it by placing the column in a second time as a Running Total and then as a Line on my chart, hiding all the values until the last point. However I would like if possible the Total as a horizontal line across the chart? 2) I h...

Any word on CRM 1.2 release date?
Has anyone heard a firm release date yet for CRM version 1.2? And will CRM 1.2 work with SBS2003 that was just released? -kw "Jim" <jim@nospam.com> wrote in message news:ehxGoPvnDHA.644@TK2MSFTNGP11.phx.gbl... > Has anyone heard a firm release date yet for CRM version 1.2? > > This depends on where you are based. I've just got home from an MS course, and we told that in Australia it will release to the partners at the conference late next month, and will be released to customers on 12/1/04. It was also confirmed that it will run on SBS2003 - it will ru...

SRM 3.0 for SBS
Trying to "test" install CRM 3.0 for SBS on a SBS 2003 Premium R2 server. Installation gives option to install a sql Report Server or use existing. The one it tries to install is SQL 2000 and the screen says it is not compatible with other versions of SQL. So will the install allow you to connect to the SBS 2003's SQL 2005 Workgroup Report Server? Can;t find this answer anywhere! Thanks, KBB In answer to your first question, yes. You have to install SQL 2005 Reporting Services, then connect to this during CRM setup "KBB" <kbbnospam@dontspamme123.org> w...

Duplication of labels within pivot table
I am using a cvs file to copy & paste data into a workbook (not saving the file as an excel document and then coping & pasting into the workbook). This workbook is updated weekly from the same hard format reporting system with current information only - all other aspects of the report remain the same. I am seeing duplication of the labels within my pivot table. It seems that the information is being recorded as stacked data not as continuious data within my data base. Could this be caused by the copy & paste directly from the cvs file rather then from an excel file. At this tim...

Delete doesnt seem to work in SQL
Can someone possibly tell me why my delete statement in sql is not deleting anything. Table1: CurUser | Inventory John Stalls, Machine John Stalls, Pinball John Stalls, Golf Harry Simpson, Tires Harry Simpson, Boats //--------------------------------------------------------------// String FileName = System.IO.Directory.GetCurrentDirectory() + "\\test.db"; string ConnectionString = string.Format(@"Data Source={0};Version=3;New=False", FileName); SQLiteConnection conn = new SQLiteConnection(ConnectionString); conn.Open(); SQLiteCommand cmd = conn.C...

Unhide Tables in Acc2k7
Hi! How do I unhide tables in the Acces2k7, please? When I try: Navigation Pane, Access Options, Navigation Options, the Groups for "Tables and Related Views" is empty. Thanks in advance. an Do they show up if you select Object Type instead? Try Office Button, Access Options, Current Database, Navigation Options button. When that window opens, select all the Display Options at the lower left of the window. If that doesn't work, make a copy of your database and hide it away in a safe place. Then do a compact and repair. If that doesn't work, open a...

Decimal to time conversion
How do i convert 75.2 minutes to show 75 minutes & 11 seconds (75:11) or 4511 seconds to show 75 minutes & 11 seconds (75:11) in Excel can it be done or am i being thick???:confused: :confused: ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Try tips posted here http://www.exceltip.com/show_tip/Excel_Time/Converting_time_to_decimals_in_Microsoft_Excel/96.html ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...

Publishing Free/Busy Time
I was reading in the Help section that I can set our PCs to publish each person's Free/Busy time to our local server. Can anyone give me any extra information on hos I do this? I went to Tools/Options/Calendar Options/Free-Busy Time and foudn where I can setr it to do that. THe question is how exactly do I specify where to publish the information to, and how do I get all my PCs to be able to retireve the informatino to make it useful to everybody? THere's no Browse button so do I need to just type in a folder such as (X:\schedule) or something different? Just can't get it to wo...

Error Message 550 5.7.1
i recive the following message - i use outlook 2002:- our message did not reach some or all of the intended recipients. Subject: FW: test Sent: 12/15/2003 4:21 AM The following recipient(s) could not be reached: Osama Haiba (osama.h@york-egypt.com.eg) on 12/15/2003 4:21 AM 550 5.7.1 Unable to relay for osama.h@york- egypt.com.eg Set your incoming server to authenticate using your outgoing server credentials. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual ...

How do I display daily duty timings (day/night) of employees for .
I am looking for a template that would permit me to display the work timings of my 30 employees. They either work the day shift or the night shift. ...

surveys and pivot tables
I have reinvented the wheel by making a paper survey and then entering all the answers into a spreadsheet. the format of the spreadsheet is; the first column is the id of every person surveyed and the next 9 columns are their response to each of the 9 questions (responses are numbered so each response is a number from 1-8)each row is in reference to the answers that the person gave. so a typical row would be: PCA 1 6 1 2 2 1 5 8 1. pca being the ID and the other numbers being the response to the corresponding row's question. The question is: how do i set up a pivot table to analyze ...

Switching companies using SQL Passthrough
I have an application that uses SQL_Passthrough. As part of the code you must execute a statement that uses the appropriate database. The code looks like this: set SQL_Statements to "use MYDB"; status = SQL_Execute(SQL_connection, field SQL_Statements); This works fine, but my application can be used for any number of databases. At first, I modified it to use the Dex.ini file, which works. Here is the modification: dbname = Defaults_Read("SQLDB"); dbopencommand = "use " + dbname; set SQL_Statements to dbopencommand; status = SQL_Execute(SQL_connection,...

How do I convert time format to text?
I have a cell with a formula of =text(c3-b3,"h:mm") as the difference from cell b3 and c3. Assume the result is 5:00. I need to minus another cell value that is formatted as general text. eg. =text(c3-b3,"h:mm")-G3 Thanks....Andrew Hi Andrew- Try the VALUE() fx and format that cell with your choice of Time Formats. HTH |:>) "Andrew" wrote: > I have a cell with a formula of =text(c3-b3,"h:mm") as the difference from > cell b3 and c3. Assume the result is 5:00. I need to minus another cell value > that is formatted as general text. ...

pivot table changes when data is refreshed.
Hi, I have a pivot table with grouping based on a field called "period which is of date type. I have grouped it into months and quarters However when i refresh the pivot table whenever the data (which is in seperate sheet) is changed, the groupings are going out. I want t preserve the groupings as they are like a template. How do i stop th changing the format and layout of the pivot table whenever i refres the data? any ideas? please help. regards Kiran:mad ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages d...

Run-time error 2465 after database import
I'm trying to tidy up a database by starting with a fresh, blank database (Access 2000) and importing everything from the original database. Here are the steps I've gone through: 1. Started with a blank database. 2. Set all the properties to be the same as the original database. I've set the properties in: - File / Database Properties - Tools / Startup - Tools / Options - Visual Basic Editor - Tools / database Properties - Visual Basic Editor - Tools / Options - Visual Basic Editor - Tools / References 3. Selected File / Get External Data / Import and ...

Jet SQL force write
Is there a way to force JET to write its buffer data to the MDB? I am using SQL statements to update the table and want to make sure the data is written. On Sat, 03 Nov 2007 14:31:41 -0700, Pete <pete.beatty@gmail.com> wrote: DBEngine.Idle dbFreeLocks -Tom. >Is there a way to force JET to write its buffer data to the MDB? I am >using SQL statements to update the table and want to make sure the >data is written. ...

Pivot Chart Created from Pivot Table is Blank
HELP!!! I am following a tutorial in an Excel 2007 book step-by-step. In one of the lessons I was able to successfully and easily create a pivot chart from a pivot table. In my next lesson, I am following the exact same steps (even have started over a couple of times), and when I create a pivot chart from the pivot table in this lesson, it creates a blank chart. WHY DOES IT DO THIS??? I know I have followed the steps correctly. Any ideas on why Excel isn't cooperating with me on this lesson would be soooo appreciated. Hi, Blank charts usually result from having your cursor in...

Windows Installer Configuration EVERY TIME!!!
Ok, so I am sure this sounds pretty familiar. Every time I open an office application, or an office document, I get a pop up of the windows installer and it starts running some mystery configuration process. I have tried uninstalling/reinstalling. I have tried repairing the registry permissions. I have ripped all office traces out by force and done a clean install. I have rebuilt the registry, modified it, added some conditional entries, but to no avail. I have done it in the user account, and the built in administrator account. I have done both custom and full installs. ...

Pivot Table Wizard causes crash
Hello, I'm using Excel 2003(11.8316.8221) SP3 When I use the Pivot Table Wizard and press the "back" button to redefine the range of data. Excel crashes. I've tried with no other files open. I can use the wizard to create now pivot tables, but not redefine this existing PT. The file is about 1 meg. Thanks in advance. Mike Just after posting this I found a reference to the problem being the result of having "frozen windows" on the sheet with the PT. I "unfroze" the windows and everything works just fine. Mike "mike in...

How Do I create a Timing Tool
All, I wonder if I can call on your expert advice. I am looking to create a Timing Tool within Excel which will be able t capture timings of specific user defined areas. For Example looking to time specific sections of say for a telephon call and the interaction the agent has with the Computer System..ho long specific actions take...and then from that be able to submit th data into a sheet..and move on to another one. This is so that summary data can be captured and analysed. All help appreciated. Regards Tim Hardin -- Message posted from http://www.ExcelForum.com Tim, It depends upo...

Possible Repost: MS CRM 1.3 Exams Revamp
Received this email yesterday: Improved Microsoft CRM Exams Microsoft Business Solutions will release three improved Microsoft CRM certification exams. The exams will become available worldwide through VUE testing centers on August 15, 2004 and Prometric testing centers on September 15, 2004. The improved exams are: Applications Professional, Customization, and Installation and Configuration. With the demand and volume of exam attempts as well as market feedback, the improvements incorporate updates and changes that are designed to better measure of the proficiency threshold for Micro...

2 seperated cluster of bars on 1 bar graph?
I would like to have the data for the moths of 2004 and then the same months for 2005 on the one bar graph BUT all 2004 together on say the left of the X-axis and then a space and the 2005 group clustered on the right of the x-axis. is that possible? Mike OK - I have got the seperation I needed by reworking the data so that the 2004 months are all together(before each 2004 month had its 2005 partner next to it) and then having a blank column followed by the months 2005 data. BUT how can I het the same colour for the month in 2004 and 2005 the same ?i.e november 2004 and 2005 say red ,...

JET & SQL
Is the SQL Server engine essentially a version of JET? If so, can one access an SQL Server database using VBA? "Bill" <stanton@jps.net> wrote in message news:yTwli.7012$Od7.4121@newsread1.news.pas.earthlink.net... > Is the SQL Server engine essentially > a version of JET? If so, can one > access an SQL Server database > using VBA? > No, SQL Server is a completely different database engine. You can connect to SQL Server tables via linking and then refer to the tables just like regular Jet tables within SQL statements, either as queries or in VBA code. Car...

Time calculation #2
I am trying to figure out a production problem that deals with job completion and shifts. There are 3 shifts working: 1st shift: 07:30-15:30 2nd shift: 15:30-23:30 3rd shift: 23:30-07:30 A job can be started and completed in 3 possibilities. 1) Starts and finshes on 1 shift >= 1 hour before end of a shift. 2) Starts and finishes on 1 shift < 1 hour before end of a shift. 3) Starts on 1 shift and ends on another shift. I will have a column for job start time and a column for job finish time. I need a third column to show the following possibilities: 1st shift (job was started 1st s...

Pasting in Excel has started taking a long time.
Copying or cutting then pasting on same or another sheet a range of cells in Excel has started to take ages. Nothing complicated just data. Does this happen with a new workbook or only specific workbooks? try opening a new workbook and perform a copy and paste with a samll amount of data. Yo umnay have a problem with the memory associated with the clipboard or your temporary drive on your PC. does the problem happen with other applications besides excel? -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecode...