Thread: Suggestions on stay with access or move to sql

Keeping in mind that the max file size of an Access database is 2 GB, and you're importing 70,000 records each month, I would strongly consider moving to SQL Server.

Well, you could move the data to and SQL Server, and just leave the front end stuff, like queries, in Access -- access the data via OLE.

The queires however, should be able to port ofer the SQL Server without too much foofaw -- some of the functions may change however.

And then, if your queries are aggregating the data to serve up the reports (i.e. you don't have a need (or rarely need) to service up a detail line), then why store all 70,000 records in the main table? Aggregate the data and store it in a reporting table that should have much less than the 70K records. The reports will run much faster too! This is how I manage to get reports to run (in less than 3 hours anyway) on my 9M record tables. And as a final thought, you could store the detail records in SQL Server and the Aggregate data in Access.

But didn't you say that you want to operate on the web? You can't use Access forms that way, anyway, without using ASP or .NET, both of which have somewhat steep learning curves. You can migrate the application to SQL Server as the others have said, probably fairly easily, if you only need to operate on a LAN, but if you want to use a browser on the Internet, that's a whole different challenge.

I think that you will find that migrating to SQL Server will be the easy part. Getting the web interface will be the harder. As Don said, ASP and ASP.NET have a pretty steep curve if you have not done a lot of OOP before. And THAT said, I would opt for a .NET solution since that is where MS effort is these days. (And you CAN get a free version of VisualStudio from them as well.

I'd do some research before making this decision. Going to a browser interface is a MAJOR change in interfacing and if you have very many forms and/or reports, could take even an experienced professional a LONG time to develop. If you are thinking of doing this just so that users on your LAN who don't have Access installed can use the system, there's a MUCH easier way to go, just create an .mde (or .accde, for Access2007) and distribute it with the free Access runtime module. See Download details: Access 2007 Download: Access Runtime and Microsoft Access - Wikipedia, the free encyclopedia

Yeah, I gotta agree with Don. I was thinking more along the lines of some kind of reporting (i.e. Read and Display) kind of interface. If it's even slightly more complicated (input forms for example), then it would take some effort to learn ASP.NET and develop the site in a reasonable time. However, if you need to sub-contract the work....I am still unemployed

I'll have to respectfully disagree with both Don and Wolffy here. Yes, ASP.Net would be quite a learning curve, however, this is posted in the ASP development forum, which means she would likely use Classic ASP. Also, the benefit of having one place to maintain code as opposed to requiring the users to upgrade to the latest MDE makes it far more worth it to me. Building a web interface for this would hardly be daunting work.

OK, I kind of agree with JMH as well. I was, however, assuming that she had no experience with either ASP or ASP.NET -- which is probably incorrect. I'll stand with the suggestion though that if you know neither ASP or ASP.NET, then go with .NET since that is the technology that MS is active with. And what...displaying the result set of a query on a page is about 5 lines of code?