Is Access right for my project?

[hokiewalrus]

Hello all. I have a new project to build and it's significantly bigger than my other ones. It's replacing an old VB program so we were thinking of developing in the new VB.net environment, but I'm finding making the switch from VBA to full VB a lot tougher than I had anticipated. So it's gotten me thinking about trying in Access, but I was wondering if I was barking up the wrong tree.

The program is going to have it's own database (currently about 800MB from 5 years of use, though there will be no migration, new one will only be about 12 tables, though many many rows) and will have to access a separate SQL database very frequently. There will be about 40 users with roughly 20 using the program at any given time.

My options seem to be:
VB.net front end with MSSQL back end
Access front end with MSSQL back end
Access front and back end

I'm very concerned about Access's ability to handle that many concurrent users, but obviously it's the environment I'd prefer to use if I can.

Any advice would be greatly appreciated.

 

[ByteMyzer]

If you want a Rapid Development environment for a front-end, with a robust backend for data storage, then an Access front end with MSSQL back end is the way to go. Just make certain that each User has his/her own copy of the front-end, and that the front-end code manages the connections and data exchanges to MSSQL efficiently.

 

[KathyL]

I don’t think 20-40 concurrent users would be the problem. I work in an environment of about 25 users which all can be connected at certain times. I’d be very comfortable going to much higher numbers.

I’ve previously worked with Access tables which had 500,000 records per table.

The 800MB will be an issue. You’d probably have to break it up.

Access works very nicely with external databases using ODBC connectivity. I’ve done a lot of ODBC connectivity, never had a problem. There is a max limit on the number of fields in a single table that access will recognize in an ODBC linked table... I forget the number at the moment.

 

[wiklendt]

actually, 2 Gb is the access limit on database size. if you've gotten to 800 Mb after 5 years, i think you'll be pretty safe.