Solved Importing records from Old Access Database into Access 365

[Benginner2212]

I am trying to re-create a database for equipment tracking at my work. The problem that I am not sure how to handle is importing some of the data from the old database into the new database. The old database has four tables that I have to get data from but some of the data that I need from each table is linked to data in another table. For example I need to import the field LastNames from the table title Manufactures, but the field LastNames in a one to many relationship with the manufacture field on a table titled Assets.

I have been playing around with importing the Manufacturers and Asset tables then using an append query to move the data to correct tables in the new database I am creating, but I am not sure if that will keep the relationships between the table and records in the tables intact or not.

Thank you for any help in advance.

 

[theDBguy]

Hi. Welcome to AWF!

I would create the new table and establish their relationships. Then, I would import the data one table at a time, starting with the parent tables.

 

[Benginner2212]

Hi. Welcome to AWF!

I would create the new table and establish their relationships. Then, I would import the data one table at a time, starting with the parent tables.

When I import the parent tables, should I import the relationships from the old database or just the data from the parent table?

 

[theDBguy]

When I import the parent tables, should I import the relationships from the old database or just the data from the parent table?

The way I do it, if I am creating a different schema, is to manually create the new tables in the new database and only import the data.

 

[Benginner2212]

Alright, I have imported the four tables from the old database that I need, and the relationships are intact.

Now I need to manipulate the data that I have imported. Can you give me a refresher on the difference between the a parent table/child table and PK/FKs ?

My understanding is that a parent table can have multiple child tables, but a child table can only have one parent table. When I am looking at the relationships between my tables, I see that some tables have the number one by the primary key of the table and other tables have an infinity sign that is by the field that is associated with the primary key of the other table.


The tables where I see the number one by the primary key field are the parent table and the table that has the infinity symbols by a field are the child tables.



Is that correct or do I have it backwards?

 

[Cotswold]

All of my old work was in Access97 or Access2000. I have not had the slightest problem in importing from dozens of different databases, either into Access2010 or Access2019, depending on the original version.
I import everything, Data, Forms, Queries, Modules without problem. Also, if your version of Access will open the old database, then you can choose to save it as the current version. I have never found the need to create new tables first. After importing or saving everything always worked just fine. I'll often compact after importing.

 

[Benginner2212]

All of my old work was in Access97 or Access2000. I have not had the slightest problem in importing from dozens of different databases, either into Access2010 or Access2019, depending on the original version.
I import everything, Data, Forms, Queries, Modules without problem. Also, if your version of Access will open the old database, then you can choose to save it as the current version. I have never found the need to create new tables first. After importing or saving everything always worked just fine. I'll often compact after importing.

I decided to create new tables for a couple of reasons. First, the tables from the old database contain a lot of information that I don't need to import and I thought it would be easier to create new tables with only the relationships that I needed vs importing the data and having to possible fix a lot of records and tables because of broken relationships. The second reason I created new tables is because I have to manipulate some of the data that I am importing and because this database rather crucial to my work, I wanted to leave the original database intact to avoid breaking/corrupting the original database file.

 

[Benginner2212]

I know that I over complicated this a bit but fortunately I am still in a spot where I have imported the data that I need and I could just strip away the data from the tables that I don't need. Then I would have to worry about making sure the records match up when after I do the append and update queries.

I think that is what I am going to do at this point.

 

[Benginner2212]

If the relationships specify cascade-delete, when you delete a parent record, the child records are also deleted. If you don't specify cascade delete, you won't be able to delete a "parent" record that has "children". You would have to delete the "child" records first.

I didn't specify cascade-delete when I created the relationships between my tables. I've always gotten the impression that specifying cascade-delete can make corrupting the database a lot easier.

 

[Benginner2212]

No. Cascade-delete is there because it is a valid use of RI. You just need to understand when to use it since not all relationships should be set to delete child records. Its use is when the relationship is a hierarchy and the child records have no meaning in their own right. So Order-orderdetails is the typical example. If you delete an order, there is no reason to keep the details. However, you would never specify cascade delete from customer to order because that might delete active orders. But, if you keep a separate address table, then Customer-address would use cascade-delete. Most apps don't allow deleting customers so the point is moot but as you can see, a table can be a parent in multiple relationships and cascade delete can be specified on some but not all.

Thank You for the help, I was able to get the data imported and manipulated to match the new database.