Trying to Determine if SQL Server 2008 R2 Express can Solve My DataBase Needs
I have been plagued with reaching the MS Access 2 GB file limitation for far to long and I need a solution; I understand that there is a FREE version of SQL Server 2008 R2 Express and was wondering if it could be used a stand alone back end, with Access 2010 as the front end.
The 2GB file size limitation is my only problem with access, the data I use is static and there is only a single user; what I do is import TXT, CSV files into Access, parse the data in various ways to create and Export Canned Reports (.RTF) and Excel Spreadsheets. I then burn the Access Database, Reports and Excel Spreadsheets to a DVD and send it to the Data Requester.
It would be very helpful to get away from multiple databases and linking and back to a single database; can I do this with the free version of SQL Server 2008 R2 Express?
The 2GB file size limitation is my only problem with access, the data I use is static and there is only a single user; what I do is import TXT, CSV files into Access, parse the data in various ways to create and Export Canned Reports (.RTF) and Excel Spreadsheets. I then burn the Access Database, Reports and Excel Spreadsheets to a DVD and send it to the Data Requester.
It would be very helpful to get away from multiple databases and linking and back to a single database; can I do this with the free version of SQL Server 2008 R2 Express?
Chris Mangus
I see no reason why you can't do this.
It can do what you say you need, but you have to consider that SQL Server Express has a storage limit also (10 GB/database), bigger, but it's still a limit.
Source: http://www.microsoft.com/s
Source: http://www.microsoft.com/s
And while SQL Server Express is more robust than MS Access, you should not consider it as an upgrade from MS Access. It is a different animal and as such requires a different approach to what you are used to doing. Ignore this warning at your own peril as you will become frustrated and disappointed if you attempt to use the same MS Access code with MS SQL Server Express.
ASKER
acperkins,
Just how much reinventing of the wheel will I have to do? At the beginning all I want Server Express to do is be the database; My access code must still process all the data as originally designed. As time goes on I can move toward Serve Express and take advantage of what it can do.
LIONKING,
So far the largest data file I have ever had was 5.97 GB; so 10 GB should be more than adequate.
Just how much reinventing of the wheel will I have to do? At the beginning all I want Server Express to do is be the database; My access code must still process all the data as originally designed. As time goes on I can move toward Serve Express and take advantage of what it can do.
LIONKING,
So far the largest data file I have ever had was 5.97 GB; so 10 GB should be more than adequate.
Good to know that 10 GB is good enough for you (for the moment).
And regarding your question to acperkins, I'm gonna go ahead and say that "in theory" your code "should" behave pretty much the same, but that depends on the level of complexity and the tools that you've used to accomplish your tasks.
Since (like acperkins states) SQL Server is a more robust tool than Access, there will be better ways of doing things (like querying your tables and collecting data), than what you have in Access.
The first thing you should do is set up a test environment where you direct all your code to the (new) SQL Server Database and test the functionality, if something fails then you're most likely gonna have to change the approach by using a SQL Server oriented logic.
Hope that helps a bit, although I'm sure acperkins can contribute a lot more.
And regarding your question to acperkins, I'm gonna go ahead and say that "in theory" your code "should" behave pretty much the same, but that depends on the level of complexity and the tools that you've used to accomplish your tasks.
Since (like acperkins states) SQL Server is a more robust tool than Access, there will be better ways of doing things (like querying your tables and collecting data), than what you have in Access.
The first thing you should do is set up a test environment where you direct all your code to the (new) SQL Server Database and test the functionality, if something fails then you're most likely gonna have to change the approach by using a SQL Server oriented logic.
Hope that helps a bit, although I'm sure acperkins can contribute a lot more.
ASKER
LIONKING,
Nothing fancy, not using any special tools or custom designed objects or DLL's. Just your default MSO Pro 2010 install and whatever Access 2010 "Out-of-the-Box" capabilities are.
Nothing fancy, not using any special tools or custom designed objects or DLL's. Just your default MSO Pro 2010 install and whatever Access 2010 "Out-of-the-Box" capabilities are.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
acperkins,
Here is what I am planning, the current day-to-day work still has to be done regardless. So if Server Express runs slow, slower or slowest, that really does not matter as I will be Re-Coding to move to SQL Express.
Of Course I am making the "assumption" that I can run day-to-day operations using Access with Server Express as just a database and at the same time Rewrite what I have to use Server Express's capabilities. Can this be done?
Here is what I am planning, the current day-to-day work still has to be done regardless. So if Server Express runs slow, slower or slowest, that really does not matter as I will be Re-Coding to move to SQL Express.
Of Course I am making the "assumption" that I can run day-to-day operations using Access with Server Express as just a database and at the same time Rewrite what I have to use Server Express's capabilities. Can this be done?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Time to test instead of chat. Thanks I appreciate your commentary.