Re: Database Structure Question ... Serious Help Only Pls

Maybe simply appending with a query won't work... (I'm open to other
suggestions!)

The only problem you might have is determining the primary key
information so that you know where to write each record's data. (Well,
so you can relate the data back to a single parent record after
splitting up your Excel data.) I found this in one of Steve Arbaugh's
old posts:
<SNIP>
On your recordset object after you have updated the recordset issue a
command such as:

MyRecordset.Move 0, MyRecordset.LastModified

and then just read the value of the field.

HTH

Steve Arbaugh
ATTAC Consulting Group
</SNIP>

So you could open a static recordset of the Excel data, loop through
it, and append the parent table data, grab the PK as above and store it
in a variable in your code. Then you could do the appends to all the
child tables and pass that same value as the foreign key. Might look
ugly in code, but it's a lot faster than doing the whole thing
manually. Essentially, you would be adding each normalized record to
one or more tables (Parent table first), and then adding the children
and the Parent table's primary key to the child tables. Everything
would turn out nice and normalized, and once you got the code working,
you could run it and forget about it.

Pieter

.

Relevant Pages

  • RE: Code to Update Recordset only works intermittently
    ... A server-side cursor must be used for the Recordset. ... See http://www.QBuilt.com for all your database needs. ... > that the reference to the index is set differently and the primary key in ... > Dim cnn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • RE: Deleting a record deletes item from combo boxs underlying tab
    ... You changed the primary key for one of the tables - I know I'm stating the ... I would be concerned that the form's recordset type is Dynaset (inconsistent ... can you post the SQL of the query underlying the form? ... The forms recordset type is Dynaset ...
    (microsoft.public.access.formscoding)
  • RE: Code to Update Recordset only works intermittently
    ... Thank you for responding. ... A server-side cursor must be used for the Recordset. ... > See http://www.QBuilt.com for all your database needs. ... >> that the reference to the index is set differently and the primary key in ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help needed with ADO Update
    ... No primary key on the tablereferenced. ... I've only ever seen that message when trying to update a recordset ... SQL statement was valid via the response.write test prior to executing the ... I read the reference materials from Bob's pointer re: ...
    (microsoft.public.data.ado)
  • Re: need append query SQL help
    ... You are not appending a value to the primary key value in the table ... You are appending records where the key value is duplicated. ... The first query works properly, so I created a second query to contend with ... there were 6 "key violations". ...
    (microsoft.public.access.queries)