Solved Importing records from Old Access Database into Access 365 (1 Viewer)

[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.

 

[Pat Hartman]

You can open a new empty database and just import the four tables. If you check the options correctly, the relationships will also be imported. In any event, all the foreign keys will still work since the PKs of the parent tables will have been imported also.

If you ever find yourself in a position where you can't use Access to just import everything, you can still retain the relationships. The ONLY situation where an autonumber PK is "updateable" is if you use an append query. So, you can create a new table and append rows from the old table. Select the PK plus whatever columns you want. The existing PK will be transfered to the new table. That means that when you import the child tables (you don't need to worry about their PKs but i use the same technique as with the parent tables), the FK's will find a matching parent. When you do this manually, you MUST import the parent tables before you import the child tables so that RI will not break.

Once everything has been imported, check to see that all the relationships and indexes are in the new database.

It is amazing how many people don't realize that you can append rows with populated autonumbers to new or existing tables. As long as the autonumber value in the from table doesn't duplicate any value in the target table, the rows will be appended. For example, if you archive records and remove them from the primary table, you can put them back if you've made a mistake by just using an append query. You just have to append the parent record before you append any child records.

 

[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?

 

[Pat Hartman]

In the relationship, the 1 side is the "parent" and the infinity (many) side is the "child". Logic rules ALL tables should have primary keys. It is the PK of the 1-side that joins to the FK of the infinity side.

 

[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.

 

[Pat Hartman]

I thought you said you imported the old tables. If you create new tables, you can still append the data using the old PKs for the parent records. If you don't append all the parent records,then when you run the append query for the child records, all child records with missing FK values will also be discarded.

It really depends on your objective whether you end up with less work one way or the other.

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

You're welcome