Forum Discussion

lucsfar1a's avatar
lucsfar1a
Copper Contributor
May 18, 2022

little question

hey guys, 

 

I just started an internship as a data analist and I'm wondering about if is worth it to switch from using acess to an SQL server. About the office that I'm working: Small investiment office but currently in expassion to became a medium level office, for now Acess is totally worth it, but MAYBE in the future we may be forced to switch because of the data volume.

The question is: May I tell my bosses that is better to switch right now or I wait to see how the company grows?

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    if access can handle the volume of your data, stick with it.
    switching to sql server will not guarantee that db will become much faster, although it will expand your storage.
    on the mean time, try learning sql server with ms access and do a Parallel test.
  • SQL Server will provide additional security and stability which will be increasingly important if the number of concurrent users is more than 15-20

    Access will allow up to 2GB file size but anything above 1GB or so may become slow and prone to corruption. The free Express version of SQL Server allows up to 10GB but as already stated, your app won't necessarily run faster unless configured properly
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    lucsfar1a First, it's good to plan ahead, so plus one on that point.

     

    I'd say think long term. Access can carry you a long way. However, data volume is only one component. More important, IMO, are better data security, scalability and robust processing power in SQL Server.

     

    Over the long run, most organizations can eventually benefit from those factors by migrating their data to SQL Server. But in the short run, there's not much to be gained. So, I'd say a good strategy would be to make your Access relational databases as solid and you can in the short run and begin preparing for the migration by installing an instance of SQL Server Express on your own computer so that you can begin learning how best to incorporate it. Eventually, you'll have a decision point about upsizing and being experienced with SQL Server will help you make a good decision. 

     

    Keep in mind that whatever you do in the long run, your Access relational database applications must be split, with a copy of the interface accdb on each users' computer, and the data accdb on a network server. Your users won't experience much change in their Access interfaces if and when you do replace the data accdb with a SQL Server database as long as you plan for and prepare the transition well.

Resources