Unanswered: Overview of VB Front End w/ Access Back End
I have a client with some fairly robust applications that are built entirely in Access. Thier needs are starting to push the envelope of what Access is capable of, so I'm thinking about migrating them from a 100% Access setup to a VB front-end/Access back-end setup.
Rather than asking all of the individual questions here, I'm hoping to find a simple overview or tutorial that describes the ins & outs of this. Does anyone know where one is?
I'm pretty experienced using VBE inside Access & other programs, so I'll be ok once it comes down to nuts & bolts. I just need to get some basic concepts down..
(Before you say it - yes, I would like to go so far as to use a better database platform (SQL Server, Oracle) but due to corporate politics, it's unlikely that we'd get the necessary support from their IT department to make that possible.)
Access is quite a good client application - you can develop applications much faster than using a development language such as VB. If you want to write a VB app to replace your access front end, you will have to start from scratch. VB front ends offer more flexibility, but this does come at a price...
I'd suggest posting the issues you have with your access solution - there might be a quick fix. I've seen very complex HR applications written with an Access front-end/SQL Server back end.
If your issue is with speed rather than functionallity, a VB front end is unlikley to solve any of your problems - you either need to upgrade to SQL Server or look at creating indexes/redesigning your database. Free versions of SQL Server (SQL Server Express/MSDE) are available - you will also find information online about upsizing to SQL Server. A free version of Oracle (Oracle Express) is available, but there is no easy upgrade path to this platform.
The Access database engine is usually fine for a handful of users, depending on the database size and network connectivity. If you just need a more flexible front end, using an Access database file with a VB front-end is perfectly acceptable. Make sure you really need a VB front-end though as it's likely to be a time consuming project. Also note that other users in your organization are more likely to have Access skills than VB skills.
I don't really have specific issues with the older Access apps I've built. I've sucessfully pushed the envelope before without too much of a headache, but this one's a little different..
For one thing, in the apps where I've 'pushed it', I was actively working in the app each day as part of my regular job. If any problems came up, I was right there to fix it. In this case, it's a client that I can only help via email or once-a-week visits.
Next thing - Their previous apps have only involved 3 users in the local office reading/writing data and printing reports. The current app I'm working on involves a few more users manipulating data and folks printing reports over their corporate WAN. Tracking down people from all over the state to make sure that they're out of the database when I need to make changes is getting to be a problem. As if that wasn't enough, some people will occasionally be using a wireless connection to the WAN to get to the database.
Third - I'm starting to see more crashes and corrupt files than I'm used to. None of them involve any complex programming or anything, just simple form navigation will occasionally kill parts & pieces of the file.
All of this together is making me a little nervous about the reliability of what I've built them.. The lady I'm working on is already on a bit of a limb in doing this. If she has something that's constantly crashing, then nobody will use it. That's bad for her and for me.
Speed is no problem unless a wireless connection is in play, but I am interested in the possibility of migrating to SQL Server Express. I've got no experience in what's involved in that though, so there will be some learning curve.. (Related to that - would you mind taking a look at my question in the SQL Server section?)
Hi - I suspect your problem is more to do with Access as a database rather than Access as a front-end.
Access databases frequently corrupt when you have multiple users connecting to the database. The locking mechanisms used by access to support multiple users isn't very robust. Access is really a personal database - although it supports more than 1 user it's expected that the number of users will be quite small and working on a high speed LAN.
Users connecting over a WAN would make me quite nervous. When you query an access database, the query must be resolved locally - if the database is large, you will can generate a lot of network traffic. SQL Server by comparison will resolve the query and return the results to the client - this will generate much less network traffic.
I'll check out you post in the SQL Server section later...