Importing Data Into Completed DB with Tables and Relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been working on a db for several months now. I
finally got it to the point that it can be used. I have
numerous tables and the relationships setup and forms and
subforms. Unfortunately, I need to get data imported from
excel spreadsheets and I don't want to enter or cut and
paste all the data separate. I have tried an update query
since you can use multiple tables with that but that did
not work. I have tried append queries but the only way
that works is if I delete the relationships. It gives and
error message that "It can not be done because it violates
a validation rule" None of my tables have validation
rules or text setup so I am not sure how I can get this
data into my db without doing it manually.

Any help would be appreciated
 
You need to enter the data to the main tables first so that when you import
to the relational table there is matching data in the main table. You may or
may not have to temporarily remove the relationships to import the data to
the tables and then reset the relationships. If they won't reset, then you
know there is a problem with one or both tables.
 
Is this a one time problem or will you be entering data from Excel
Spreadsheets as part of the application?

The solution is the same, but if it is a recurring part of the application
you will want to create macros or VB code to automate the process.

1. Get all of the data into Access. Use a temporary table(s) with no
relationships. Clean up any data problems here, before you try to add data
to your application tables. (J Smith, Jon Smith, Jonathan Smith?)

2. Create as many "unmatched" queries as needed to get the "one side data"
in your relationships. Group by your primary keys. For a one time
application, all the data will be unmatched, so you can skip the left join
of the group by results to the primary table to get the unmatched.

3. Append the "unmatched" data to your application tables.
For example: Customers, Products, Orders. First Customers to tblCustomers,
then Products to tblProducts, then Customers and Orders to tblOrders, then
you can add all Order Detail to tblOrderDetail without violating
relationships.

4. Delete Temporary Table or delete Temporary data, if you need to repeat
the process.
 
The best way is this. Link an excel spreadsheet into your db with link
option (right click in the tables background).

After this do a query Select * from myLinkedTable

sets the relationships with this query.

Are this what you needed?
Any Questions?

Good Luck
Giorgio
 
I tried this and I got the same error message that
because of "due to conversion and key violations" I
tried with the dates to start and test and it did not
work. Any suggestions? At this point it seems like I
will have to do manually.
 
Back
Top