:confused: HELP! I am a strictly vb writer but I need help with creating an import on Access. It needs to be something an simple user can run (click a button, prompt for filename, and run).

I have a csv text file with records that need to be imported into two different tables in the same database. Basically, the records are as follows:

ordernumber,fullname,address,city,state,zip,quanti ty,product

now, ordernumber all the way thru zip would go to one table, Contacts, and ordernumber,quantity,product would go to another table, Order (the ordernumber links the two tables together).

I am using Access 2k. If you need more info, please let me know. Any code would be very helpful. Thank you so much in advance!

You need to run an append query for each table you need to append to.
Query1:
Select the order info and append to the order header table.
Query2:
Select the order detail and append to the order detail table.

You can run both queries in the click event of a button. If you are not familiar with code, the easiest thing to to is to let the wizard build the button to run the first query. Then modify the code to add just a single line of code to run the second query. Just copy the syntax of the first.

Thanks for the reply. I seemed to be lost at this :( . Now, some of the fields in the tables will be blank because not all the data will be imported, so it seems I will need to code in order to point data to correct field (ie, ordernumber to ContactsTable.Ordernumb). I know how to do this if it was a VB application I created, but cannot seem to know where to begin in VBA/Access.

Any ideas? Or is there a thread that already has some code I can "tweak"? I must not be the only one in this world that has encountered this :confused: .

Thank you again for all your help.

If you need to add entries to the contacts table, that's more of a problem. Are you sure that all orders are for new contacts? I would say it is unlikely so how will you distinguish new contacts from existing contacts?

The contacts table should have a unique index based on ALL of the following fields - fullname,address,city,state,zip. A primary key or index may be composed of up to 10 separate fields. Assuming that all contacts new or existing, create a totals query that includes ONLY the columns that will go into the contacts table. Turn this query into an append query. You will then need to create an unmatched query to compare the contacts table with the totals query. This will tell you if all contacts are present in the contacts table.

The append query for the order, should be changed to join to the contacts table on all the fields of the unique index. You can then obtain the primary key of the contacts table so that it can be appended to the order table where it functions as a foreign key.

The problems with this process:
1. You'll have to deal with missing info in the contacts fields before finishing the process since having nulls in any of the contact fields will prevent the join from working, you may need to establish dummy values.
2. There is no way to identify changes to contacts. Any contact data that doesn't completely match an existing record is considered new.