Converting Access to MS SQL

Hi there, I have been considering converting my Access DB to MS SQL for a while now (security and speed are the main reasons), however I have no idea where to start!

I have a host (SmarterASP.NET) which supports MS SQL, however do not know how to 1 convert from Access to MS SQL and then upload that Data

Anyone able to help me?

Thanks in advance
Graemewebber4technologiesAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
If you are thinking about hosting the SQL Server BE in the cloud, think again.  This will almost certainly require a rewrite of the application.  Access works very well (if properly designed) with SQL Server (or other RDBMS) on your LAN.  But it will perform poorly over the internet.

Even a strait conversion from Jet/ACE to a LAN based SQL Server will be disappointing if you have used old style Access techniques rather than client/server techniques when developing your app.   A primary reason for switching to SQL Server is to increase data efficiency but if you are using forms bound to tables or queries that have no criteria which you then filter locally, you have gained NO efficiency.  In fact, you have lost efficiency because Access working with Jet/ACE is much smarter than Access working with SQL Server.  The concept is that you will need to make changes to the application that reduce the rows returned from the server by always using queries with criteria.  No user really needs to page through 100,000 records and it is far more efficient to retrieve 30 records one at a time than to retrieve all 100,000 and then filter locally.  Once you switch to using queries with narrow criteria, you can actually make performance gains because you are now letting the server to the heavy lifting.

Almost all of the apps I create have SQL Server (or Oracle or DB2, etc) BE's.  As long as they run on a LAN, you can easily use bound tables and the same Access querydefs you are currently using.  Occasionally you might need to use views to speed up joins and even more rarely, you would need to use stored procedures to make complex reports and batch processes more efficient.  If you are going to use an Azure (I wouldn't bother with SQL Server) in the cloud, you will almost certainly have to switch to unbound forms and a lot of stored procedures.

If you need remote connectivity (you did not say that you did), Citrix and RDP are the best options for running an Access app remotely.  Either can be used without changing the Access app.  Other solutions require rewriting the Access app as a web app.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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.
1
 
Graemewebber4technologiesAuthor Commented:
Thanks Big Monty, I will have a look into it
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Dale FyeCommented:
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
0
 
Ryan ChongCommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
Graemewebber4technologiesAuthor Commented:
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?
0
 
PatHartmanCommented:
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.
0
 
Ryan ChongCommented:
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
0
 
Graemewebber4technologiesAuthor Commented:
With the amount of work moving over at this stage, I have decided to stay with Access. Cheers all for help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.