Advice on upsizing from Access/VBA

[MePenguin2000]

Hi,

I'm currently maintaining a smallish MS Access database app (frontend-backend split, ca 1M records) which is sporadically used by about 50 people worldwide. It's mainly analytical in nature so the software features have taken priority over database design (i.e. db is not particularly good!). Data is added centrally to the backend and then updates distributed to users, as are frontend updates.

http://www.bugscep.com/help/help_toolsoverview.html

We're at a point where we would like to turn the whole thing into a multi-user database, net accessible, add GIS features and the ability to link with related databases (scientific). We haven't decided on either a web based interface, or a standalone frontend which can store data in the field to upload on connection... or both (users vs administrators).

I've been recommended PostgreSQL and PostGIS, as an alternative to the VStudio Express, MS SQLServer and (eg.)ArcGIS route initially intended. I've had a look at phpPgAdmin, created a simple db and played a bit with PostGIS. It all seems good, but is clearly a level above my somewhat amateurish attempts in Access. I'm a VBA coder and a scientist really; not a db expert.

So my questions are:
1) As a VBA/Access programmer is it going to be a seriously long haul to convert everything to Postgre?
2) Would I be better off sticking with Visual Studio and SQL Server (this is an educational project, so MS is cheap even if not OpenSource)?
3) Would the combination of VStudio to develop the front end (VB, ODBC) and Postgre for the backend and GIS be sensible? Assuming I could get people to help me with the heavy duty db bits.
4) Are there any other options/combinations that would be easier?

Bonus question:
If I was to start a completely new and much larger project: multiuser db, GIS, advanced interfaces, with a couple of developers to help what setup would you recommend?

Sorry for the ramble, and thanks in advance,

Phil

 

[stoolpigeon]

At my previous employer - I wrote a time and attendance system to replace a manual system that involved clocking in - literally - with time cards.

I did a postgresql database, a php interface for users, and vb apps for administration and reporting.

Postgres ran on linux - the odbc driver worked great on Windows and I had no problems integrating the previously mentioned technologies. I used Crystal Reports for my reporting and had no problems there as well.

I don't know anything about GIS stuff so I'm no help there.

Are there advantages to running a compete MS stack? Sure. SQL Server and Visual Studio play very nice together. You lose the ability to use other platforms - and are restricted to Windows of course, and there is the money - but I'm sure it could work.

But should you want to use MS development tools, and PostgreSQL (on windows or linux) I don't think you will have any problems. What I think you gain is lower cost and more flexibility with regard to platform.

PostgreSQL is a wonderful product. In the years I used it in that environment it was rock solid and extremely capable.