Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import info to multi-table database (XP pro)

    I am attempting to create a horological book database. I have based my efforts on the "craft" database included with Helen Feddema's Access Inside Out. The crafts database has separate tables of authors and for books joined by a table [BookAuthors] that contains only TitleID and AuthorID. The data input form uses a combo box linked to the author table to enter author information for each entry. The author table contains AuthorID, FirstName, LastName, + additional fields.

    The data I would like to import (more than 4500 documents) has fields for FirstName, LastName combined with all the other document information. I can import the information into my database by adding the additional fields for FirstName and LastName and I can also use the author information from the imported data to add authors to my author table. Where I am stuck is finding a way to create the correct entries in the [BookAuthors] table other than one entry at a time using the combo box on the data entry form..

    This would seem to be a common problem (i.e. importing "flat data" into a multi-table database) but I have been unable to find any solution to the problem in any of the several Access books I have.

    Can anyone suggest how I might solve the problem?

    Bob Holmstr

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import info to multi-table database (XP pro)

    What I have usually done is import the data into a *new* flat table in the database and allow Access to create an autonumber ID in the import. Then I add fields to that table to hold the keys assigned to those records as they are imported from the flat table into relational tables. That gives me a fallback position, so that I can go back and see where the data came from and what other records it is related to. Use a series of queries to pull data out and put it into the appropriate relational tables, one table at a time. When you finish a table, you use an update query to match the records and insert the primary key for the new relational records into the appropriate field in the flat table. That lets you create the join records you may need, because you have all the join IDs in your flat table after you finish.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import info to multi-table database (XP pro)

    Create a backup copy of your database before doing the following.

    1. Create a query based on your flat table that returns only the author info (FirstName and LastName); set the Unique Values property of this query to Yes. Change it into an append query that inserts the records into the Authors table. The AutoNumber AuthorID field will be filled automatically. Run this query.

    2. Do something similar for the fields that provide info about the books. This query will insert records into the Titles table. Run this query too.

    3. Create a query based on the flat table and the Authors and Titles tables. Link the flat table to the Authors table on FirstName and LastName, and link the flat table to the Titles table on (for example) book title (and perhaps one or more other fields). Add the AuthorID and TitleID fields to the query grid. change this query into an append query too. It will insert records into the BookAuthors table. Run this query.

    You may have to do some manual cleaning up, but this certainly beats entering each book/author combination manually.

  4. #4
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    Thank you Charlotte and Hans!

    I used the method suggested by Hans - it worked quite well. I now understand why a field in addition to title was suggested - several books have the same title!

    Bob Holmstr

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    Hans,
    I am trying to complete this project and I have run into a problem. Your reccommended method works but if I use only the title, I get authors incorrect for books with the same title as you predicted. When I add SubTitle as an additional field, I get the correct author for entrys in the database that have both [Title] & [SubTitle] but when [SubTitle] is empty(?) for the record, [Title] matching is not sufficient to generate an entry in the BookAuthors table - i.e. my resulting appended table has the correct author for records that have both title and subtitle match but not (no author) when titles match and subtitle is empty. Any suggestions on how I might resolve this problem?

    Thanks for your help.

    Bob Holmstr

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import info to multi-table database (XP pro)

    The alternative would be to write VBA code that uses ADO or DAO to loop through the records of the flat table and populate the three relational tables.

    How many problematic records are there? If the number is limited, it might be easiest to correct them by hand. If that is prohibitive, I or one of the others can help you with the code, but you would have to provide the names of the tables and fields involved.

  7. #7
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    The problem effects approximately 1/2 of the database or ~2500 records so manualy correcting records would take a bit of time.

    The flat file has been added as a table [BooksFlat] to the new database. There are four tables: Authors, Books, BooksFlat & BookAuthors. Authors contains AuthorID, LastName, FirstName, + additonal fields. Books contains BookID, Title, SubTitle, + additional fields. BooksFlat contains LastName, FirstName, Title, SubTitle, + additional fields. BookAuthors contains only AuthorID, & BookID. Running the suggested append query works for entries in Books and BooksFlat that have entries in Title and SubTitle but fails for entries where the SubTitle field is empty.

    Bob Holmstr

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import info to multi-table database (XP pro)

    The attached code uses DAO (it's easier to do this using DAO than using ADO, although that would be possible too). So you must set a reference in Tools | References... in the Visual Basic Editor to the Microsoft DAO 3.6 Object Library. You must paste the code into a standard module, and you must then modify it. Since I don't know what the "additional fields" are, I just included a field "BookAuthor" to go into the Books table and a field "AuthorOther" to go into the Authors table. You must adapt the lines copying these fields, and add others as appropriate. You can run the code by clicking somewhere in the procedure and pressing F5. Save the module before doing so, and make sure that the target tables are empty.
    Attached Files Attached Files

  9. #9
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    Thank you Hans!

    It took me a while but I am able to make the code work - I am a beginner at this as I assume you guessed. As a start, I just remarked out the references to other fields, emptied the tables and ran the code. One error occurs after the loop is complete - "Syntax error (missing operator) in expression". The complete database update process completes correctly and all data seems correctly entered. I did fix what seemed to be a minor error in the code i.e. it appears to me that the third line after !Clean up should read set Rst BookAuthors = Nothing not set RstAuthors = Nothing. The error message still occurs however.

    One more question at the moment - how do you reset the autonumber counters for tables after you empty them? - at the moment new data starts counting from the last used number.

    Thanks again

    Bob Holmstr

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    Bob

    Select Tools, Database Utilities, Compact & Repair

    John

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import info to multi-table database (XP pro)

    Yes, I forgot to modify one line I copied and pasted. Soory about that, it is not essential to the operation of the code, however.

    If you want to find out where the error occurs, make the line On Error GoTo ErrHandler into a comment by placing an apostrophe ' before it. When you run the code, and the error occurs, you'll get three options: End, Debug and Help. Select Debug, and you'll be taken to the Visual Basic Editor, with the offending line highlighted. You can inspect the value of variables by hovering the mouse above them. One possibility is that there is an author without first name (or last name); I assumed that first and last name would be present in all records.

    In itself, it is not necessary to reset the autonumber fields; they are meaningless numbers. But if you want to be neat (I do <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), do Tools | Database Utilities | Compact and Repair Database... after emptying the tables. This re-creates table indexes, resets autonumbers and removes unused space from the database. In fact, it is a good idea to compact all your databases from time to time. If you like, you can tick "Compact on Close" in the General tab of Tools | Options... (this is a per database setting.)

  12. #12
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    Thanks for quick response! Compact and repair fixed numbering problem as suggested. The number does seem to be relatively unimportant - The number of documents currently in the database is correctly shown independent of auto number.

    The suggestion to comment out the error handler worked very well. One record had quotes around an author's nickname! The code handled missing first, last, and both names without a problem.

    This has all worked so well, I am going to try to add creating a publisher list as well.

    Thanks!!!!!!!!

    Bob Holmstr

  13. #13
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import info to multi-table database (XP pro)

    I am making progress but it turns out that blank FirstName fields are causing problems i.e. multiple entries into BookAuthor table. There are too many blank FirstName entries to fix manually. My code knowledge was insufficent to rewrite the example code that Hans supplied so I solved the problem in a different way. I used Find and Replace to put a null string in the blank fields - i.e. find is null and replace with "" after allowing zero length strings in the tables. This works well in that duplicate entries in the BookAuthor table have been eliminated.

    Question: Will the presence of zero-length strings in the FirstName field cause problems for me in the future? Should I go back now and replace the zero-length strings with is null ?

    Bob Holmstr

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import info to multi-table database (XP pro)

    Replacing zero-length strings with Nulls again is slightly neater, but it is not really important, I think.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import info to multi-table database (XP pro)

    I think I would have used a placeholder instead of a null string. Something like "Undetermined" or "None Given" or simply "NFN". That is usually safer than a zero-length string, which can get confusing because it *looks* empty. I usually have a default value of a string like those I referenced when I want to be sure it is obvious that I don't have a value for that field. It also keeps out duplicates, since Nulls don't count in unique keys when No Duplicates is set to true. Since nulls can't be compared, there is no way to prevent multiple records with Null in a field of a unique key.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •