INSERT to two tables using Access db

czmelton
czmelton used Ask the Experts™
on
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.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
or do your insert instead of AddNew... the code above is for logic purposes only...

MaxOvrdrv2
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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






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






Commented:
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<

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

Commented:
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<
Yes, it appears czmelton took note of my comments and properly closed out his previous questions.  Thanks czmelton!

Commented:
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 ).

Commented:
oops... see there's a good example... :|

Commented:
P.S. I see that I was wrong though, regarding the closing of the questions - I apologize. :-)

Author

Commented:
Thanks for the assistance, gladxml!
Also, thanks to AzraSound! and whammy well....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial