Hello All,
after I have migrated all tables to SQL server from MS access, i have deleted all the tables and linked the database to the tables in the SQL server,
I noticed that all the relations has been removed after linking the database to the sql tables,
can i recreate the relations in the ms access, or I have to re-create the relations between the tables in the SQL SERVER?
second question is, do i need to migrate the Queries as well, or it is ok to keep them in the ms access?
regards
After Migrating tables to SQL from MS access how to recreate relations
-
- BronzeLounger
- Posts: 1333
- Joined: 01 May 2016, 09:58
-
- Lounger
- Posts: 44
- Joined: 13 Jun 2024, 12:24
Re: After Migrating tables to SQL from MS access how to recreate relations
First, it's not clear why the relationships between tables in your Access accdb could have been lost. That begs the question, "How did you migrate the tables"? Did you use SQL Server Migration Assistant (SSMA)? Or did you do it a different, manual way?
Depending on what you are actually dealing with, and if the relationships were indeed lost, then you'll need to create them again in the SQL Server database, probably using SSMS. There is an alternative to SSMS called DBeaver https://youtu.be/V03v6ZlYeBY?si=Rsme4L2uj2wkIFoE, which may be more user-friendly for some. I still prefer SSMS because it's familiar. I throw that out there because DBeaver also handles Access back end accdbs, so it is potentially more consistent for some tasks like creating Relationship Diagrams that are ugly when done in Access.
You should NOT migrate most queries. Only in cases where you have non-parameterized SELECT queries should you consider doing so. For the most part Access SQL queries are pretty similar to SQL Server SQL queries, but for anything very complex, it's probably more efficient to start with SQL Server for your views. You can later link to those views and Access will treat them as it does other SQL Server linked tables.
My guess is that the relationships didn't actually get lost if you used SSMA to migrate the tables from Access to SQL Server. Access simply might not be showing them to you in the same way you're used to seeing them. In fact, you can't add or modify relationships between linked tables anyway. As I noted above, that can only be done in the same database where the tables reside. (That's true of Access tables, btw, in a linked Back End accdb).
First, use SSMS to verify that the relationships were not created during the migration.
Depending on what you are actually dealing with, and if the relationships were indeed lost, then you'll need to create them again in the SQL Server database, probably using SSMS. There is an alternative to SSMS called DBeaver https://youtu.be/V03v6ZlYeBY?si=Rsme4L2uj2wkIFoE, which may be more user-friendly for some. I still prefer SSMS because it's familiar. I throw that out there because DBeaver also handles Access back end accdbs, so it is potentially more consistent for some tasks like creating Relationship Diagrams that are ugly when done in Access.
You should NOT migrate most queries. Only in cases where you have non-parameterized SELECT queries should you consider doing so. For the most part Access SQL queries are pretty similar to SQL Server SQL queries, but for anything very complex, it's probably more efficient to start with SQL Server for your views. You can later link to those views and Access will treat them as it does other SQL Server linked tables.
My guess is that the relationships didn't actually get lost if you used SSMA to migrate the tables from Access to SQL Server. Access simply might not be showing them to you in the same way you're used to seeing them. In fact, you can't add or modify relationships between linked tables anyway. As I noted above, that can only be done in the same database where the tables reside. (That's true of Access tables, btw, in a linked Back End accdb).
First, use SSMS to verify that the relationships were not created during the migration.
-
- Lounger
- Posts: 44
- Joined: 13 Jun 2024, 12:24
Re: After Migrating tables to SQL from MS access how to recreate relations
Oops, I failed to include a comment on Action Queries (Updates, Inserts, Deletes).
These can't be migrated to SQL Server. You will need to replicate their functionality in SQL Server using Stored Procedures.
Alternatively, for most tasks, a local action query based on linked tables would be effective and efficient. Given that, you may or may not even need to create SQL Server versions using Stored Procedures. If you do, you can execute Stored Procs using Passthrough Queries.
These can't be migrated to SQL Server. You will need to replicate their functionality in SQL Server using Stored Procedures.
Alternatively, for most tasks, a local action query based on linked tables would be effective and efficient. Given that, you may or may not even need to create SQL Server versions using Stored Procedures. If you do, you can execute Stored Procs using Passthrough Queries.
-
- BronzeLounger
- Posts: 1333
- Joined: 01 May 2016, 09:58
Re: After Migrating tables to SQL from MS access how to recreate relations
thank you for all the good information, its like i missed alot to learn
yes i used Ms sql migration tool for migration but for some reason it didn't recreated all the relationships i believe because of the field size as i tried to re crate them manually showed me that mismatch filed size.
regards
yes i used Ms sql migration tool for migration but for some reason it didn't recreated all the relationships i believe because of the field size as i tried to re crate them manually showed me that mismatch filed size.
regards
-
- Lounger
- Posts: 44
- Joined: 13 Jun 2024, 12:24
Re: After Migrating tables to SQL from MS access how to recreate relations
"... i tried to re crate them manually showed me that mismatch filed size."
That doesn't seem right. If the fields on which you want to enforce Referential Integrity don't have the same field size in SQL Server after the migration, they couldn't have been the same field size in Access before the migration. That means they couldn't have had Referential Integrity enforced on them in Access to begin with.
Something is missing in this scenario.
Which fields, specifically, would you want to use for the relationships and what are their datatypes?
That doesn't seem right. If the fields on which you want to enforce Referential Integrity don't have the same field size in SQL Server after the migration, they couldn't have been the same field size in Access before the migration. That means they couldn't have had Referential Integrity enforced on them in Access to begin with.
Something is missing in this scenario.
Which fields, specifically, would you want to use for the relationships and what are their datatypes?
-
- BronzeLounger
- Posts: 1333
- Joined: 01 May 2016, 09:58
Re: After Migrating tables to SQL from MS access how to recreate relations
thank you very much for the reply,
yes i have check the table relationship in access and field siezes as well see the screenshots below
yes i have check the table relationship in access and field siezes as well see the screenshots below
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 189
- Joined: 22 Feb 2022, 09:04
Re: After Migrating tables to SQL from MS access how to recreate relations
So, as the error message states, make them the same size? 

Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1333
- Joined: 01 May 2016, 09:58
-
- Lounger
- Posts: 44
- Joined: 13 Jun 2024, 12:24
Re: After Migrating tables to SQL from MS access how to recreate relations
Thanks G'man.
@siamandm. Short Text fields in Access are not quite the same thing as text fields in SQL Server. And the rules about enforcing RI on them are looser.
In SQL Server, on the other hand, text fields can be one of a number of datatypes. Char, NChar, VarChar and NVarChar.
A field defined as NVarChar(100) isn't the same as one defined as NVarChar(150).
That's where your problem lies, as Gasman pointed out. (And why I asked to see the fields in question.)
There are other cases where datatype differences, although they may seem subtle at first, can make migration from Access to SQL Server more challenging.
Just keep in mind that the rules for what is acceptable in SQL Server are generally more restrictive. Not always, but quite often.
@siamandm. Short Text fields in Access are not quite the same thing as text fields in SQL Server. And the rules about enforcing RI on them are looser.
In SQL Server, on the other hand, text fields can be one of a number of datatypes. Char, NChar, VarChar and NVarChar.
A field defined as NVarChar(100) isn't the same as one defined as NVarChar(150).
That's where your problem lies, as Gasman pointed out. (And why I asked to see the fields in question.)
There are other cases where datatype differences, although they may seem subtle at first, can make migration from Access to SQL Server more challenging.
Just keep in mind that the rules for what is acceptable in SQL Server are generally more restrictive. Not always, but quite often.
-
- BronzeLounger
- Posts: 1333
- Joined: 01 May 2016, 09:58
Re: After Migrating tables to SQL from MS access how to recreate relations
Dear George,
thank you very much for your reply and information, could you please have a look on this relation in SQL and see if there are any in correct things in the relations? for this relation i get this error from a combo box trying to add a product.
thank you very much for your reply and information, could you please have a look on this relation in SQL and see if there are any in correct things in the relations? for this relation i get this error from a combo box trying to add a product.
You do not have the required permissions to view the files attached to this post.
-
- Lounger
- Posts: 44
- Joined: 13 Jun 2024, 12:24
Re: After Migrating tables to SQL from MS access how to recreate relations
Your tables are not properly normalized, and the relationships between them are not correctly designated.
You defined a composite Primary Key for tbl_Product, despite the fact that it already has that Identity field, which is sufficient on its own to work as the Primary Key for that table.
That's inefficient, but it also has the unfortunate effect of forcing you to include all 3 fields as part of the Foreign Key in related tables.
Hence, "tbl_OrderDetail" has to have the ID field from tbl_Products, along with the ProCode and ProName fields.
As an aside, tbl_OrderDetail has to have its own Primary Key. The fact that you have accepted the default name "ID" for fields means its hard to know exactly what is going on between tables. It appears, though, that "ID" in tbl_OrderDetail should be the Identity field Primary Key for that table. You still need another field to hold the part of the Foreign Key which is the "ID" from tbl_Products.
This leads to a side discussion about avoiding ambiguity. You have accepted the default "ID" name in Access. That leads to confusion in situations like this, where ID could mean any one of a number of fields in different tables. For that reason, most experienced developers prefer names like "ProductID" and "OrderDetailID" to make it very clear which table that field belongs to.
Here, it's not clear to me what the "ID" in tbl_OrderDetail means. Is it part of the composite Foreign Key from tbl_Products, or is it the Identity field Primary Key for tbl_OrderDetail?
In any event, this is behind the error you report. The Primary Key for tbl_Product is a composite requiring "ID" from tbl_Products as well as ProCode and ProName when you enter a related record in tbl_OrderDetails. You have to enter all three values in tbl_OrderDetail, based on what I see here. That's also what the error message is telling us.
I would resolve this by removing ProCode and ProName from the composite PK in tbl_Products. Make the Primary Key ID, but rename it to ProductID for clarity. In tbl_OrderDetails, you then need only that ProductID field for the Foreign Key.
By the way, what is the purpose of the table called "tbl_froshtn_detail"? It is the one-side in a one-to-many relationship with "tbl_products". However, the Foreign Key fields in tbl_Products that could be part of that relationship appear to be a combination of two non-Primary Key fields from tbl_froshtn_detail: ProCode and ProName.
Because I don't understand what the related table, "tbl_froshtn_detail" is doing, I can't be sure what exactly to say to correct that problem.
You defined a composite Primary Key for tbl_Product, despite the fact that it already has that Identity field, which is sufficient on its own to work as the Primary Key for that table.
That's inefficient, but it also has the unfortunate effect of forcing you to include all 3 fields as part of the Foreign Key in related tables.
Hence, "tbl_OrderDetail" has to have the ID field from tbl_Products, along with the ProCode and ProName fields.
As an aside, tbl_OrderDetail has to have its own Primary Key. The fact that you have accepted the default name "ID" for fields means its hard to know exactly what is going on between tables. It appears, though, that "ID" in tbl_OrderDetail should be the Identity field Primary Key for that table. You still need another field to hold the part of the Foreign Key which is the "ID" from tbl_Products.
This leads to a side discussion about avoiding ambiguity. You have accepted the default "ID" name in Access. That leads to confusion in situations like this, where ID could mean any one of a number of fields in different tables. For that reason, most experienced developers prefer names like "ProductID" and "OrderDetailID" to make it very clear which table that field belongs to.
Here, it's not clear to me what the "ID" in tbl_OrderDetail means. Is it part of the composite Foreign Key from tbl_Products, or is it the Identity field Primary Key for tbl_OrderDetail?
In any event, this is behind the error you report. The Primary Key for tbl_Product is a composite requiring "ID" from tbl_Products as well as ProCode and ProName when you enter a related record in tbl_OrderDetails. You have to enter all three values in tbl_OrderDetail, based on what I see here. That's also what the error message is telling us.
I would resolve this by removing ProCode and ProName from the composite PK in tbl_Products. Make the Primary Key ID, but rename it to ProductID for clarity. In tbl_OrderDetails, you then need only that ProductID field for the Foreign Key.
By the way, what is the purpose of the table called "tbl_froshtn_detail"? It is the one-side in a one-to-many relationship with "tbl_products". However, the Foreign Key fields in tbl_Products that could be part of that relationship appear to be a combination of two non-Primary Key fields from tbl_froshtn_detail: ProCode and ProName.
Because I don't understand what the related table, "tbl_froshtn_detail" is doing, I can't be sure what exactly to say to correct that problem.
-
- BronzeLounger
- Posts: 1333
- Joined: 01 May 2016, 09:58
Re: After Migrating tables to SQL from MS access how to recreate relations
Dear Georg,
Thank you very much for your kind reply. I believe I need to redesign the tables and relationships. I'll work on that and give it another try.
Thank you very much for your kind reply. I believe I need to redesign the tables and relationships. I'll work on that and give it another try.