Access table or Sql Table

[tagteam]

I have a database table that is at 450,000 rows and will probably grow by 50,000 per year. It is currently around 330MB. Should I try to move this table to Sql and use access as the front end



OR

Is it better to keep them in access and just split them into different tables or maybe 5 years per table?>
Thanks

[June7]

Assuming table grows by 50,000 records a year, the 2GB Access limit will be used up in 39,993 years.

Another way to look at it: 450,000 rows used 330MB. That means 6060.6 * 450,000 rows to use up 2GB.

Somebody double check my math!

[tagteam]

Other than the need for space are there good advantages for using SQL?

1. With SQL you can set some permissions I believe
2. Will there be better speed?
3. Does it get more complex when you start to work with SQL or can you just use queries like normal?
Thanks

[June7]

I have never set up a SQL database. So sorry, cannot authoritatively answer those questions. I will say I have seen enough discussions about SQL backend to indicate speed and complexity are comparable with Access backend. Speed has more to do with the file server/network software and hardware than the database.

[tagteam]

Thanks for your input. I am mainly concerned because I need to add some fields to the database and my client is not the kind that likes to archive old data. Seems like if it was in SQL Server then it would be able to grow so much that I wouldn't have to worry about splitting the database in the future.

[June7]

Maybe depends which version of SQL. I think the freebies (MySQL and SQLServerExpress) also have file size limits.

[tagteam]

Yeah, 10GB for SQL Server Express 2008 R2 but I have access to my clients full SQL server that they use for their accounting system. I was also thinking it might be nice because that is already on a good backup schedule and rotation. I was thinking that if I can learn to SQL server as the back end then I can have larger data sets and maybe do more with security. Seems like I could offer my clients a better service overall if the data was in a more advanced database.

Thanks for your input.

[pbaldy]

Other than the need for space are there good advantages for using SQL?

1. With SQL you can set some permissions I believe
2. Will there be better speed?
3. Does it get more complex when you start to work with SQL or can you just use queries like normal?
Thanks

1) Yes
2) It depends
3) You can simply use Access queries against SQL Server linked tables. You of course gain the potential to use SQL Server tools like stored procedures, views and functions to speed up processes that take too long using Access tools alone.

By the way, have you done a compact/repair on the back end? It may not really be that large.

[tagteam]

Yes, I compact/Repair once every couple of weeks. The main reason I am trying to determine the best way is I am going to start adding more fields to the database and I am concerned it might start to fill up faster.

I also really like the idea of being able to do some work with the permissions to block off sections of the data. Right now I have to link another access db and put a password on that one.