Access DB migrate to SQL Server

[eugzl]

Hi All.
I have the Access DB with a huge amount of macros that manage queries and files in explorer. The database file has almost reached its size limit (2 GB). I have an idea to migrate it to SQL Server. Questions:
1. Is it worth to do it?
2. If yes. Should I migrate all tables or queries as well? I certainly understand that I will have to recode queries to SQL Server rules in that case.
3. If I will migrate tables and queries and then connect the Access DB to SQL Server. Will all my macros and other VBA codes work?

Thanks

 

[CJ_London]

1. maybe - you can't exceed the 2Gb limit, but have you compact/repaired? are there records that can be deleted? Are you using multivalue fields or tables with attachments? The former won't work in QL Server, the latter should be stored outside the db and you store a path instead
2. certainly tables, queries possibly. If you use domain functions or crosstab queries they will need a major rewrite
3. probably - in principle you may need to tweak them, all depends on what they do

 

[eugzl]

1. maybe - you can't exceed the 2Gb limit, but have you compact/repaired? are there records that can be deleted? Are you using multivalue fields or tables with attachments? The former won't work in QL Server, the latter should be stored outside the db and you store a path instead
2. certainly tables, queries possibly. If you use domain functions or crosstab queries they will need a major rewrite
3. probably - in principle you may need to tweak them, all depends on what they do

Hi CJ_London. Thanks for reply.
I cleaned the database and left only what is necessary. And size of DB indicate 1.8GB.
An other questions. In case if database will migrated. Can I expect that it will work faster? Also, I very worry about how will work macros and VBA codes if tables and queries will located on SQL Server?

Thanks

 

[theDBguy]

Is your database split? How big is the FE? How big is the BE?

 

[eugzl]

Is your database split? How big is the FE? How big is the BE?

Hi theDBguy. Thanks for reply.
The database not split. What mean FE and BE?

Thanks

 

[theDBguy]

Hi theDBguy. Thanks for reply.
The database not split. What mean FE and BE?

Thanks

Try splitting it up first. Maybe you won't need to use SQL Server yet. You split the database by putting all the tables into a BE file and leave all the forms, reports, queries, and code in the FE file. BE means BackEnd, and FE means FrontEnd.

 

[CJ_London]

In case if database will migrated. Can I expect that it will work faster?

no - at best it will be about the same but more likely to be slower. Comparison depends on where your app is located - if on your machine, it will definitely be slower

The database not split. What mean FE and BE?

FE means Front End (all forms, reports, queries*, macros and vba
BE means Back End and is just tables - this is the bit that gets migrated to SQL Server

*you can continue to use the queries in the FE but it may be more efficient to move them to the BE if they are complex or SQL Server has more more efficient functionality not available in Access (such as recursion). See this link
https://www.access-programmers.co.uk/forums/threads/addressing-performance-issues.291269/



Since your app is not split, presumably it is not multi user