Moving to SQL (1 Viewer)

[pekajo]

Hi,
Hope you can help on 2 questions.
1. What are the pros and cons of migrating my ms access tables to sql and moving to an sql server. and using ms access as a front end. (forms, queries and reports)
2. Advice on the easiest way of doing the conversion.
Thanks for any help.
Peter

 

[CJ_London]

Pro’s

• Can store more data
• Can have more than 255 concurrent users
• Better data security (can’t just copy the be file and login more secure if required)
• Use of stored procedures can be more efficient
• Some more efficient sql expressions for more complex queries so vba functions not required

con’s

• Cost - unless using sql server express which has some limitations
• Performance can be slower - depends on your network, how sql server is managed and how efficient your tables are

otherwise
Managing the health of the database (rebuilding indexes and tables) still needs to be done, but perhaps by someone else

I can’t answer the 2nd question at this point but take a look at this link - once the issues there have been resolved, it should be pretty straightforward

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

 

[Josef P.]

Other Pro:
* Creation of backups is possible during use. (This is a very important advantage for me.)

Performance can be slower

Pro:
* Performance can be faster (when using the capabilities of the SQL server)

 

[Minty]

Con:
In most cases when I have made a conversation from Access to SQL Server, as the backend because the Access app wasn't designed with SQL in mind, some re-engineering is required to make the most of the capabilities. You have to allow for this.

Pros:
Stability
Much more powerful tools available to you for data operations.
Learning T-SQL

 

[amorosik]

Hi,
Hope you can help on 2 questions.
1. What are the pros and cons of migrating my ms access tables to sql and moving to an sql server. and using ms access as a front end. (forms, queries and reports)
2. Advice on the easiest way of doing the conversion.
Thanks for any help.
Peter

Pro
- no corruption of data (this is an improvement that alone justifies all the work)
- frequently hot-backup of main db
- some work may be done on server side, with great performance benefit

Cons
- the work that needs to be done to adapt the code
- installation and maintenance of db server

 

[ebs17]

when using the capabilities of the SQL server

The first and absolutely essential layer of data processing is centralized and controllable.
Any number of different frontends from different programming worlds get the same pre-processed database and can then implement their own tasks.

 

[GPGeorge]

Please don't expect an answer to the request for the "easiest" approach, though. Easy is a relative, subjective judgement, subject to a number of qualifying conditions. The only possible answer for "easiest" is, therefore, it depends.

 

[Steve R.]

I would recommend migrating to an SQL server, such as MariaDB. Importing my MS Access databases into MariaDB was easy.

However, there are deeper implications in planning for this migration. I would advocate that you implement a web based solution. This would mean that you would be interacting with your database using a browser, such as Firefox. Implementing such an approach would involve installing programs such as Apache and PHP. Moreover there would be a significant learning curve associated with learning HTML, CSS, JavaScript, etc. As another advantage this would "free" you from the MS Windows environment for database management. Assuming that you have the time, implementing a web based solution would be a more modern and better approach.

But consider this. If you are already locked into an MS Access and MS Windows environment, it may be better to stick with it.

 

[Pat Hartman]

Over the years, I have converted a sufficient percentage of my Jet/ACE BE's to SQL Server or other RDBMS that I always build the FE with that in mind. Jet/ACE are quite happy working with good SQL Server techniques but vice versa isn't true. So, always building for SQL Server gets you fine performance using the desktop database engines and it minimizes the trauma of a conversion if it comes to that.

SSMA is the easiest way to convert the data. Just be aware that you can control the data types so I always avoid the newer ones that are not handled by the default SQL Server driver. Even the newest versions of Access distribute a very old driver for SQL Server, pre SQL Server 2005. You can of course always install newer drivers but it is a PITA unless you can get IT support to do it automatically for you because every user needs the same SQL Server driver.

There is no easy way to convert the FE. If you used old fashioned Jet/ACE techniques such as local filters when you designed your forms, there is no alternative but to fix that problem or your forms will be very slow. Forms should be bound to queries that have WHERE clauses. This always limits the number of rows returned from the server and lets the server to the heavy lifting. The old methods and what you see in many samples is to bind the forms to tables or to queries without WHERE clauses. This method causes Access to request ALL rows and all columns from the table be brought across the LAN to memory on your local PC.

 

[ebs17]

This method causes Access to request ALL rows and all columns from the table be brought across the LAN to memory on your local PC.

Views, i.e. planned queries, can be linked as tables in an Access FE. A significant increase in quality can thus be achieved.
Sorry, but a platitude can be answered with a platitude.

 

[Pat Hartman]

Views, i.e. planned queries, can be linked as tables in an Access FE.

Of course they can. So to be PRECISE, the sentence should be -- Forms should be bound to queries OR VIEWS that have WHERE clauses. However, we typically require variable criteria so my queries might use tables or views.

 

[Isaac]

PRO, one of my biggest is just the IDE and dev process.

Imagine for second that you are coding in VBA. While your code runs, you can keep typing more code. While your code runs, you can save your file. While your code runs, you can begin executing 12 other subs and testing them too. Imagine add-ins that are uber-intelligent in making code suggestions. Loops (if absolutely needed) that process 10x faster than a DAO recordset.

Just the benefit of the multi-threaded environment (I'm probably saying that wrong) are massive..

That's if you go ahead and do some of your work in T-SQL, which I would recommend