Converting Access to MS SQL

you could use Microsofts migration tool:

https://docs.microsoft.com/en-us/sql/ssma/access/sql-server-migration-assistant-for-access-accesstosql

However, if you're not entirely comfortable with the tool, you may want to set up an EE Gig for something like this. It'll save you a lot of headaches in the future.

Thanks Big Monty, I will have a look into it

If you are using the SQL migration Assistant, I encourage you to do the following:

1.  Ensure that every table in your Access database contains a primary key (EVERY TABLE).
2.  Somewhere in the migration assistant, I cannot remember where, there is a checkbox, that reads something like "Add a TimeStamp column to each table" or "Add a RowVersion column to each table".  Make sure you check that checkbox in order to add the RowVersion column.

You will not be able to modify any table from Access which does not have a PK in SQL Server, and in some instances, you won't be able to modify records which don't have the RowVersion (TimeStamp) column, so you should just do make sure that every table has both.

Dale

it's easy to migrate data from Access to MS SQL.

just open the SQL Server Management Studio, right click the target database and select Task > Import Data

then select the Access as the source.



from the same wizard, you can specify the field mapping when necessary.

In addition to the above migration you'll likely have to rewrite a wompload of Access SQL into SQL Server T-SQL, and this article I wrote will give you a head start in doing that.  

Good luck.

Hi all and thanks for the feedback, I have contacted the host (www.smarterasp.net), the MSSQL solution they are offering is NOT cloud based but on local servers.

They create the MSSQL db for you, then you have options of:
Restore Database
Attach MDF file to Database
Run .SQL File

Suggestions?

Unless that server is on your LAN, Access will not perform well and that includes using a VPN.  As I said earlier, do not be surprised if swapping Jet/ACE for SQL Server actually makes your application SLOWER.  If you did not develop the app using good client/server techniques, you will have to modify the app to bind forms to queries with criteria at a bare minimum.

They create the MSSQL db for you, then you have options of:
Restore Database
Attach MDF file to Database
Run .SQL File

Suggestions?

if you want to create a clean db, perhaps you could try Run .SQL file (with the scripts to create your db objects) OR if you wish to restore a db, try option #1: Restore Database

some recent discussion with option #1 and #2:

Backup SQLServer database with MDF file and only .Bak file
https://www.experts-exchange.com/questions/29073698/Backup-SQLServer-database-with-MDF-file-and-only-Bak-file.html

With the amount of work moving over at this stage, I have decided to stay with Access. Cheers all for help