INSERT to two tables using Access db

I have two tables, need to Insert data into table 1 and preserve the ID(autonumber) of that record and Insert information into table 2 depending on that ID.

czmeltonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AzraSoundCommented:
Before you get on a trend, note that at this site, when you ask a question, you are expected to respond to expert input by either

A) requesting more information if your question was not fully answered

B) closing out the question once an answer has been accepted


Repeat those steps above until B has been reached.  Note your first two questions asked at this site require your attention:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20512817.html
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20467014.html
0
MaxOvrdrv2Commented:
umm... ok.. how about this:

RS.Open "SELECT * FROM Table1"
RS.MoveLast
RS.AddNew
Add all the info needed to the first table, then when you get to that id that you need... simply copy it into a variable as well:
RS("NewID")=FormVariable/Value
TheID=RS("NewID")
RS.Update
 close the recordset
RS.Close
open it to the  new table:

RS.Open "SELECT * FROM Table2"

RS.MoveLast
RS.AddNew
add all info needed and then assign the TheID variable to the respective field
RS("NewID2")=TheID


and the rest is obvious...

if... however... you would like to get the AutoNum or whatever of the last record entered in your database (table 1) and use this value into table 2, then i suggest this:


RS.Open "SELECT * FROM Table1"
RS.MoveLast
RS.AddNew
Add all the info needed to the first table,
RS.Update

do not close the recordset, but save the value of the AutoNum into a variable first:

RS.MoveLast
TheID=RS("AutoNumField")

then you close the recordset and start with table 2

RS.Close

RS.Open "SELECT * FROM Table2"

RS.MoveLast
RS.AddNew
add all info needed and then assign the TheID variable to the respective field
RS("NewID2")=TheID
RS.Update
RS.Close

Hope this helps!

MaxOvrdrv2
0
MaxOvrdrv2Commented:
or do your insert instead of AddNew... the code above is for logic purposes only...

MaxOvrdrv2
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

gladxmlCommented:
czmelton,

You can try to check out the link... might help...

http://www.aspalliance.com/remas/VFAQ/IdentityColumn/


For soluiton 1

strSQL = "INSERT INTO tblSample1 (Name,Email)"
strSQL = strSQL & "VALUES ('Remas Wojciechowski','remasw@yahoo.com')"
objCon.Execute(strSQL)

strSQL = "SELECT @@Identity FROM tblSample1"
Set objRS = objCon.Execute(strSQL)

'assuming that the autonumber is the first column number varNewID = objRS(0)


strSQL1 = "INSERT INTO tblSample2 (Fieldname)"
strSQL1 = strSQL1 & "VALUES (varNewID)"
objCon.Execute(strSQL1)


HTH...

HAppy programming...






0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gladxmlCommented:
czmelton,

You can try to check out the link... might help...

http://www.aspalliance.com/remas/VFAQ/IdentityColumn/


For soluiton 1

strSQL = "INSERT INTO tblSample1 (Name,Email)"
strSQL = strSQL & "VALUES ('Remas Wojciechowski','remasw@yahoo.com')"
objCon.Execute(strSQL)

strSQL = "SELECT @@Identity FROM tblSample1"
Set objRS = objCon.Execute(strSQL)

'assuming that the autonumber is the first column number
 
varNewID = objRS(0)


strSQL1 = "INSERT INTO tblSample2 (Fieldname)"
strSQL1 = strSQL1 & "VALUES (varNewID)"
objCon.Execute(strSQL1)


HTH...

HAppy programming...






0
apolloisCommented:
Max/czmelton,

In general the solution Max provided will work.  However there are two corrections that need to be made:

>>>RS.MoveLast<<<

Do NOT issue this statement.  As soon as you issue the RS.Update, the autonumber field will be available.  If someone saved a new record AFTER the UPDATE but before the MoveLast, then you would get the wrong ID.

>>>RS.Open "SELECT * FROM Table1"<<<

This will return ALL records from the table when you don't need any records.  So, issue a SELECT that will not return any records.  Something like:

RS.Open "SELECT * FROM Table1 WHERE ID < 0"

(replace "ID" with the name of your autonumber field)

However, my preference is to use a SQL INSERT statement.

Best Regards,
>apollois<
0
whammyCommented:
After reading AzraSound's post, I wouldn't even answer this question, since it's very important to finish what you start.

So I won't answer it although I know the answer...
0
apolloisCommented:
whammy,

>>>After reading AzraSound's post,

I'm not sure I understand your post.  
Both of the questions referenced by AzraSound are closed.

Let's not be to quick to judge.

If you still have concerns, please post a Q with CS, or you can EMail me (see my profile).

Best Regards,
>apollois<
0
AzraSoundCommented:
Yes, it appears czmelton took note of my comments and properly closed out his previous questions.  Thanks czmelton!
0
whammyCommented:
Ahh, I am used to what has been posted in a thread, I see I have some learning to do with the way EE is run (and why don't they fix the refreshing problem? That's a newbie webmaster fix - no offense intended, but I'm sure that could easily be fixed at least for users that have ).

0
whammyCommented:
oops... see there's a good example... :|
0
whammyCommented:
P.S. I see that I was wrong though, regarding the closing of the questions - I apologize. :-)
0
czmeltonAuthor Commented:
Thanks for the assistance, gladxml!
Also, thanks to AzraSound! and whammy well....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.