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

Similar Articles

[PageSpeed] 19

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:

how do I change picture resolution of all pictures at one time?
I have a large catalogue with over 150 pictures. I have the high res pictures in the file for commercial printing, however would like an e-mailable catalogue and in order to do this I need to change the resolution of all the pictures. Is there a way of doing this all at once? Thanks, Stephen print the cat. to a pdf and email the pdf to your clients. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Stephen" <Stephen @discussions.microsoft.com> wrote in message news:344EE8B6-8748-4271-B8C6-C77663F7AB80@microsoft.com... > I have a large catalogue...

5.7.1 smtp;550 5.7.1 Access denied
I'm having the following email issue with one of my clients, they are getting. The following recipient(s) could not be reached: xxxxxxx@netonecom.net on 12/29/2003 11:58 AM You do not have permission to send to this recipient. For assistance, contact your system administrator. <clarkserv01.ClarkNational.com #5.7.1 smtp;550 5.7.1 Access denied> The wierd thing is that I am not getting a Application event log event I.D. for this on our server??? Could the problem be on the netonecom.net server?????? Hi Jeff, The problem could possibly be caused by t...

More than one Table
I have an Old table TABLE 1 in an application which has a check fields CHK1, CHK2 in it. I have recently upgraded the application so that the check field are now a series of records in a sub-form TABLE 2. QUESTION I want to increment through TABLE 1 one record at a time, then run a conditional statement to see if CHK1, CHK2 is yes in each respective check field and if it is I want to open TABLE 2 and add record to TABLE 2 so that reflects the respective CHKn. My question is can 2 recordsets be open simultaneously and if so how do I swap between the 2 so that my VBA applic...

Averaging Duration of Time: Duration Longer than 24hr
Im trying to average duration of time, and have not found the correct way to format the cell. First, I have a start and end time. I need to calculate the difference between the two in hours, and the duration usually exceeds 24 hours. Then I need to average the time durations. Sample: A1 (start time): 3/27/2008 8:35 Custom format: m/d/yyyy h:mm B1: (end time) 3/30/08 13:00 Custom format: m/d/yyyy h:mm C1: (duration) formula B1-A1 - value = 76:25 Custom format: [h]:mm (this seemed to be the only format that returned the duration in hours appropriately) I average the values in colum...

Print View or Print Paper No start time or end time
How come when i print view or print hard copy of my monthly calendar I dont see the start and end time? "dearcc" <dearcc@discussions.microsoft.com> wrote in message news:0B8CACE7-6A38-4652-8010-B92713583CAE@microsoft.com... > How come when i print view or print hard copy of my monthly calendar I dont > see the start and end time? Outlook version? -- Brian Tillman [MVP-Outlook] They only show if the cell is wide enough. Try Landscape format. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solut...

Importing Journal Entries Using Table Import
Hello I am doing some research to see which methods would work best for importing journal entries created in a third-party application into Great Plains. So far I've completed the process using the integration manager and now I am researching the table import method. The table import process is causing me some problems because I cannot get the account numbers to import. There is a typo in the GL Transacton Entry SDK document I have, so I am trying to work around it. I am not an Engineering, my background is more of end-user. If someone has some suggestions on how to map to the ac...

get end user agreement every time i sign on to outlook must accept
must accept end user agreement every time i get on outlook since i downloaded windows 7 and reloaded office 2003 Start Outlook or any other Office 2003 application with administrator privileges once and accept the EULA. For step-by-step instructions see; http://www.msoutlook.info/question/166 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "accept end user agreement before submitt&qu...

how to rotate excel table in word
I insert a excel worksheet in word and like to rotate it Select the area in the worksheet you want to move to Word. While hold down the shift key, pull-down Edit > Copy Picture. In Word, just paste. Because the item is now a picture, you can use the drawing toolbar to rotate it to any desired angle -- Gary''s Student "dro" wrote: > I insert a excel worksheet in word and like to rotate it Thanks, its working just fine for me. "Gary''s Student" wrote: > Select the area in the worksheet you want to move to Word. While hold down > the ...

VB reference to table field
I've got this code, but I need to figure out the syntax for the !COMMENT part. I want it to refer to the master table's COMMENT field. This code, however is in the main form. I've tried [Tables]!Table1.Comment among others, but it gives an error. Any help? Thanks!!! With Me.RecordsetClone .AddNew !Name = Me.Name !DOB = Me.DOB !COMMENT = .Update "Mark1" <Mark1@discussions.microsoft.com> wrote > I've got this code, but I need to figure out the syntax for the !COMMENT ...

Crystal Reports
Has anyone been able to set up an SQL View in Crystal so that one report can contain data from several companies? I need to create a Crystal Report that will give me the AP Open Invoices for all of our companies. I am on Ver. 8 of GP and Ver. 9 of Crystal. Thanks, -GK Dear, You will need to use something look like the following view, replace Company1 with the first company and Company2 with the second company and so on: SELECT Company1.dbo.GL20000.ACTINDX, SUM(Company1.dbo.GL20000.CRDTAMNT) AS Credit, SUM(Company1.dbo.GL20000.DEBITAMT) AS Debit, SUM(Company1.dbo.GL20000.ORC...

Multiple CRM Sites (child domains) on 1 Server
I would like to install multiple CRM sites, from different child domains, on 1 CRM server. I believe this is possible. Are there any things I should be aware of? Any critical issues that will arise? Is this covered in the implimentation guide....off to get it now. Thanks. "Scott" <wikayaker@gmail.com> wrote in message news:1141061562.354599.267210@u72g2000cwu.googlegroups.com... >I would like to install multiple CRM sites, from different child > domains, on 1 CRM server. Not possible, sorry. I believe I read MS is considering this for the next version, to f...

I'm having Publisher editing problems
I've upgraded from Publisher 2000 to 2003. When I try to edit any of my previous 2000 documents I'm getting huge delays in the display of the content. I've created many documents using the 2000 version, one of which is a youth calendar containing lots of graphics. I'm still using 2000 and I suspect what you are experiencing is Publisher 2003 converting the 2000 pub file to a 2003 file. Do you get the same delay if you open a 2000 file, save it as a 2003 file, then open the 2003 file to do editing? -- Don Vancouver USA "OfficeBarb" <OfficeBarb@discuss...

graph changed from the last time I worked with it
I have a simple line graph and when I went back to it the dates in the date column had changed to sequential numbers eg. 39088, 39089, . . . and Date now appears in the legend. I tried to get back to hoe I set it up with Wizard but only am able to get wizard to take me to a new setup. Hi, I'm not clear on the question, but here are a few ideas: 1. You can't get back into the charting wizard unless the chart is selected. 2. If you are asking about the number 39088 - these just need to be formatted as dates, Excel stores dates as integers and times as decimals, so together y...

Time and Billing report
Is there a system for tracking Time and Billing for Activities in CRM. system should be able to flag the activity as billable, and time entered for the activity would be multiplied by billing rate. Then a report would be generated for all billable time for a given period. Rreport must be a detailed report of activity with notes, and with totals by Customer. Anything like that available? I think there is no function in CRM for this. My company just coded a billing system. "WebDesignGeek" wrote: > Is there a system for tracking Time and Billing for Activities in CRM. > &...

Drive Space for Web Server and SQL Server
Hello, How much drive space is necessary for the CRM install on the web server and for a separate SQL server? I have read through the implementation guide and cannot find where it give disk space requirements. Thanks! JED JED, The web server install is fairly small (less than 200MB). The SQL footprint is completely dependant on the amount of data you choose to store. If you are going to be storing a lot of attachments, then you are going to need a lot more space. Matt "JED" <Jbyrd76@hotmail.com> wrote in message news:%23EQZwM4hDHA.1688@TK2MSFTNGP10.phx.gbl... Hello, Ho...

annoying updating time! #2
Ok the first solution posted worked for me. I want a time stamp for a LOT of cells. the scenario is student give their ID number. they type it in and i the next cell it stamps the time. bearing in mind there could be hundred students. So do i put a range in the ""? at teh moment its "b3 do i enter "b3-B1003" ?? thanks so far guys/gal -- AJSulliva ----------------------------------------------------------------------- AJSullivan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1652 View this thread: http://www.excelforum.com/showthread...

Connection string to SQL Server to 2005
Hi, We have a software that we need to install on our users machines. During the installation process looks like it is trying to create a datbase in our sql server 2005. Here is the connectiong string that is generated by during the insallation process Persist Security Info=False;Integrated Security=true;Initial Catalog=DriverAccess;Server={ip address};Network Library=dbnmpntw;Application Name=Radiolinks Driver Access Manager When I include the IP address and move on and complete the installation it says that the installation was successfull. I see it available under Start/Pr...

Add/edit Hyperlink in shared excel workbook
I have a shared Excel work book containing many hyperlinks. The users add or edit the hyperlinks every time during the day. The problem is that in order to add or edit a hyperlink one has to take off the shared workbook. Can any one help me out here I want to share the file and add and edit the hyperlinks without kining out other users. Thanks sana This is from xl2002's help: Features that are unavailable in shared workbooks You can access the following features only if you stop sharing the workbook. ...... Insert or change hyperlinks Existing hyperlinks continue t...

WORD: Table of Contents with latin numeration
If have got a document dividedin sections. The first sections use latin page numbering and the rest normal arabic numbering. For each section this is working all fine. The only trouble appears when I create the TOC. The reference to the page numbers of those sections that use latin page numbering appears with arabic numbering. THe example will surely clarify the problem: It appears as ****************** Table of contents..................................2 Foreword ...................................4 Summary ...........................................5 1 Introduction ......................

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

Pivot Table Settings #2
Hello, Is there a way to have the Pivot table fields set to sum (or any other calculations)instead of count before you start to create? Thank you, Denise ...

cannot restore pst files.... heartattack time
For the first time I archived Outlook Today and my Personal Folders, date of today. I did not know it all would be swept away into the dungeon... I also have a separate (automatic) Outlook.pst file (304.049 KB years of research) which is still keeping up with the clock... but I cannot restore any of them. The only thing has been the duplicating of Outlook Today Folders (NOT Personal folders), but all folders are empty except for Contacts and Calendar. I have used the wizard and also followed the instructions below... no postive results. I use Windows ME and Outlook 2000. please help! jopie ...

How to import a table on a Web page to Excel?
Hello How to import a table on a Web page to Excel? Columns in the table are delimited by several spaces, not by a htm table tag. fist use < data-get>external data (importdata) using a webquery into excel then data-texttocolumn using space as delimiter you must know the no. of the table in the webpage which can be found out from the source html page counting <table> tag or by trial and error -- remove $$$ from email addresss to send email ========================= "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message news:#WI#g9Q5FHA.724@TK2MSFTNGP14.phx.gbl.....

Time Offset by One Hour
This one's strange to me. I have one client in my organization that when he sends a meeting request out or (to test this theory) he sends himself an email it shows that it showed up using the correct date but it says it showed an hour earlier. His system time is set correctly (we're in CST) and when we look at the properties of his Outlook client\Calendar Options it too shows he's in CST and it gives up the correct time/date. Where am I not looking to get this fixed? Any reply would be greatly appreciated - the digitaljanit...

Exchange 5.5 Server System Time
Hi All Our Exchange servers system time is out about 20 minutes. Will adjusting the time have any adverse effects? I am worried that changing the time will have a negative effect on the databases. Thanks for your reply, Rene Nope won't hurt a thing. Might want to set up a time sync though... "Rene Ouellette" <ouellette@nospam.com> wrote in message news:1Agbc.900$B16.781@edtnps89... > Hi All > > Our Exchange servers system time is out about 20 minutes. Will adjusting > the time have any adverse effects? I am worried that changing the time will > hav...