Using ADO from Excel form to relational Access Db

[bluegrassstateworker]

Hello all:
I successfully use ADO to upload form data from an Excel spreadsheet
to an Access db but would now like to take it to a new level: Instead
of opening just one single table, I would like to add information to
other tables. For example, part of the spreadsheet would upload to an
ORDER_HEADER Table and another part of the same spreadsheet would
upload order details to the ORDER_DETAIL Table. These two tables are
related of course. My thought is: establish the value of the primary
key in the first part of the upload then use that value obtained to
open another table and upload with that primary key value.
I think the concept is valid but the details are elusive. Havent
seen any postings covering relational input. Any info on this subject
appreciated.

 

[Jim Thomlinson]

Your plan is sound. The trick is how are you generating the key? If you are
using auto number in your Access DB then you just need to read (query) that
value back once you have written the info to the DB. To query back that
record I tend to write both my NT username into the database along with a
time stamp. Additional to making a nice audit trail it allows me to query
back the last record I wrote. Knowing that you just need to use it to write
to your details table. Nice and simple(ish).

If however your key is based on one or more fields in a record then it is
even easier as you do not have to read back the key value.

Often the biggest trick is to avoid writing duplicate records into your
tables. That one depends entirely on your circumstances though...

 

[Ronald R. Dodge, Jr.]

In these kinds of situations, you will often times need to look at
transactions. For addtional information on this, you can go into Access's
help file and look up the "About batch transactions", and go from there.
You can also setup links within the front end portion of an Access database
(provided you have and you should split the database into 2 parts, back end
and front end), so as you can stay within Access to use the batch
transaction tools. This will help keep your data validated should something
happen in between the start of transfer time and end of transfer time, so as
you don't have orphaned data or some other issue of data missing.

Sincerely,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Tim Zych]

If the primary key is an AutoNumber, you can also use @@Identity.

Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
http://support.microsoft.com/default.aspx?scid=kb;en-us;232144

 

[Abhilash]

Hi Andy,
I'm new to excel programming.
I got similar task to do but don't know from where to start.
It would be great if you help me out by sending the sample code which
you used.
Thanks in advance.

Regards,
Abhilash