DAO - Fill in foreign key with correct PK index from a string-data table 
Author Message
 DAO - Fill in foreign key with correct PK index from a string-data table

Hi,

I am using DAO to automatically import text data into a relational
MDB.  I have some string tables, and some relation tables as below.  I
am trying to find the best way to set the BookData.ISOID and
BookData.Pub_ID foreign keys to match an entry in the Publisher and
Lang tables.  (Table design is still in the works, don't worry too
much about them unless something about them really gets on your
nerves)

So far, I have found: Open a recordset as a table and use .Seek()
which does not seem well suited to the task.  Open a dynaset of the
table and use one of the Find() functions.  Or open a dynaset "SELECT
* FROM... WHERE..." using the string data.  If one is found in any
case, use the *_ID field to fill in the BookData foreign key field.

All of the needed data (Publisher and Lang tables) has already been
imported on a previous pass, so there will never be (should never be)
a case where the data is not found.  The database is local (same
computer).

I just need to know if there is a simpler way, hints preferred over
raw code so I can understand rather than copy and paste.  I will have
at least 100,000 records to fill in, and it seems unnecessary to use
so many SELECT statements to make sure the foreign key is filled in
correctly.

So, any other way?  Which might be preferred for speed?  I could make
a string/id cache so the database does not have to search more than
once for each publisher, and then fill in from cached info for each
table.  But that would take a large bit of memory.  And it defeats the
purpose of a relational AutoIncrField (COUNTER) to require filling out
the index for foreign key data.  Seems there should be some
IndexOfForeignKey() function, or maybe I have to write that myself.

Paul
--

CREATE TABLE Publisher (
  Pub_ID     COUNTER,
  Name       TEXT(64)  CONSTRAINT UKName UNIQUE,
  Abbrev     TEXT(8)   CONSTRAINT UKAbbr UNIQUE,
  CONSTRAINT PKPub     PRIMARY KEY (Pub_ID)
);

// language/country

CREATE TABLE Lang (
  ISO_ID     COUNTER,
  Name       TEXT(34)  CONSTRAINT UKName UNIQUE,
  Abbrev     TEXT(8)   CONSTRAINT UKAbbr UNIQUE,
  CONSTRAINT PKISO     PRIMARY KEY (ISO_ID)
);

CREATE TABLE BookData (
  BookID     COUNTER   CONSTRAINT PKGameID PRIMARY KEY,
  Name       TEXT(128),
  Genre      INTEGER,
  ISO_ID     LONG     CONSTRAINT FKISOLID REFERENCES Lang(ISO_ID),
  Pub_ID     LONG      CONSTRAINT FKPub_ID REFERENCES
Publisher(Pub_ID)
);



Sat, 09 Oct 2004 15:58:25 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. ADOX.Indexes/ADOX.Key/Foreign Keys

2. Q: How to get foreign key target from Index in VB/Access97

3. How to retrive PK fields and Foreign Fields in Oracle DB

4. Request for help - limitation of 32 indexes including foreign indexes

5. Getting foreign keys from a SQLServer table

6. Getting foreign keys from a SQLServer table

7. How to insert data from un-indexed tabel to indexed table

8. Re-Indexing a PK field

9. Primary key, Foreign key, referential integrity, etc?

10. finding index and pk columns via ADOX

11. DAO and Adding Index to a Table

12. Deleting Table Index Keys and Relations

 

 
Powered by phpBB® Forum Software