Convert tables from Access to SQL

[azhar2006]

Hello guys.
I am trying to convert tables from Access to SQL using the update added by Microsoft which is SSMAforAccess_9.5.0.msi and I think it will save a lot of time in creating tables and queries in SQL but honestly I don't know much about dealing with SSMAforAccess_9.5.0.msi and I think it is the latest version 9.5 and I have some questions why are the keys imported as well? Is there a detailed explanation in the forum about SSMAforAccess_9.5.0 I hope that the forum administrators will open a window in the forum for this tool to be added to the general, tables, reports and forms. It is just a suggestion I hope that the forum administrators will look into this topic. I think that Microsoft will add this tool in the future within Access itself or within the Office suite.

 

[xavier.batlle]

Hello guys.
I am trying to convert tables from Access to SQL using the update added by Microsoft which is SSMAforAccess_9.5.0.msi and I think it will save a lot of time in creating tables and queries in SQL but honestly I don't know much about dealing with SSMAforAccess_9.5.0.msi and I think it is the latest version 9.5 and I have some questions why are the keys imported as well? Is there a detailed explanation in the forum about SSMAforAccess_9.5.0 I hope that the forum administrators will open a window in the forum for this tool to be added to the general, tables, reports and forms. It is just a suggestion I hope that the forum administrators will look into this topic. I think that Microsoft will add this tool in the future within Access itself or within the Office suite.

Perhaps this video helps you:

 

[GPGeorge]

Hello guys.

Spoiler:

I am trying to convert tables from Access to SQL using the update added by Microsoft which is SSMAforAccess_9.5.0.msi and I think it will save a lot of time in creating tables and queries in SQL but honestly I don't know much about dealing with SSMAforAccess_9.5.0.msi and I think it is the latest version 9.5 and

I have some questions why are the keys imported as well

Spoiler:

? Is there a detailed explanation in the forum about SSMAforAccess_9.5.0 I hope that the forum administrators will open a window in the forum for this tool to be added to the general, tables, reports and forms. It is just a suggestion I hope that the forum administrators will look into this topic. I think that Microsoft will add this tool in the future within Access itself or within the Office suite.

You want to know why Primary Keys are included in a migration from Access tables to SQL Server tables?

Tables in a Relational database require Primary Keys. Related ("child") tables require Foreign Keys.

Under what circumstances would you expect, or prefer, not to have Primary and Foreign Keys migrated with their tables from Access to SQL Server?

 

[GPGeorge]

Spoiler:

Hello guys.
I am trying to convert tables from Access to SQL using the update added by Microsoft which is SSMAforAccess_9.5.0.msi and I think

it will save a lot of time in creating tables and queries in SQL

Spoiler:

but honestly I don't know much about dealing with SSMAforAccess_9.5.0.msi and I think it is the latest version 9.5 and I have some questions why are the keys imported as well? Is there a detailed explanation in the forum about SSMAforAccess_9.5.0 I hope that the forum administrators will open a window in the forum for this tool to be added to the general, tables, reports and forms. It is just a suggestion I hope that the forum administrators will look into this topic. I think that Microsoft will add this tool in the future within Access itself or within the Office suite.

I would also suggest that you NOT migrate Access queries in bulk. There may, or may not, be advantages to migrating Access queries as views in SQL Server. It is probably wiser to do so on a case-by-case basis. In any event, only select queries could be migrated, so any action queries will remain in the Access front end, or will need to be recreated as Stored Procedures in SQL Server.

 

[Pat Hartman]

why are the keys imported as well?

Why wouldn't they be? If you have relationships defined and data in the tables, then you need to add the PK values in order to maintain the relationships.
If the tables are empty, it makes no difference.

I agree with @GPGeorge Do not automatically migrate your queries. Most of your queries will run fine just as they are against the linked tables. Remember, Access attempts to "pass through" every query. This is not quite as speedy as an actual passthrough query but you have tradeoffs to make. Your Access queries will be updateable as they are but once you turn them into Passthrough queries they are no longer updateable and you will need to rewrite your code behind the forms to handle this. Some queries may have to be modified if you are using UDF's or VBA functions since those cannot execute on the server. These will be very inefficient,

So, once your conversion is done, you can look for slow queries and deal with them one by one. Sometimes making a view on the server will speed up a query so instead of the join being in the Access query, the Access query selects the view which will normally be updateable as long as the PKs are part of the select clause.