Using an Excel VBA file as a front end to an Access Database

[flortzweich]

Hello!

I have written three "database" type programs in MS Excel that make Work Orders, Inventory, and Training information easy to use and automates much of the process. The upside to this is Excel VBA is easy to program in, the downside is only one person can use the sheet at a time.

What I would like to do is use Access as the datasource and maintain the excel files as the "front end" of the program. This should allow for multiple people to use the system at a time (rarely if ever would people be editing the same data at the same time, normally someone just leaves their copy running while they are away from their computer and it locks the file for everyone)

I have read/heard about using Access and Excel in this way, but I don't know how to start. I have made a few attempts but they all ended in miserable failure.

Can someone assist me in learning how to use an Excel VBA sheet as the front end to a database file? Thanks!

[zmbd]

IMHO: Don't do it.
Either make the jump to Access, or don't use Access. These hybrid integrations are difficult to maintain and break easily.

First thing I would do if I were you is take a look at the available templates in Office, from the MS Website, and from the various freeware templates on the net. More than likely you'll find one that works OTS or needs only a little modification to meet invoice numbering or what have you. If you can program VBA in Excel, you are well on your way to doing the same in Access. You might just need to learn a little bit: MS Access 2010 Tutorials will give you the basics. One thing to keep in mind:
Access - "Macro" is one of two languages not to be confussed with:
Access - "VBA" which is basically what Excel uses for "Macros"; however, with the tools/object model needed to work with recordsets.
IN Access the two terms are not interchangable!

And, we're here to help if you get stuck.

As for your current pickle:
In the short term take a look at this code. I've used variations of this in the past with good results for closing a file on idle time: Timed Closing Of A Workbook This will take care of your employee that leaves the file open. Figure out the average time it takes to do get the invoice etc... done. I wouldn't set the timer to less than 10 to 15 minutes of idle time as it's too easy to get caught in a phone call.

Next take a look at:Application Automation as you will need to understand how this works to get the best from what you want.
You will then need to understand how to connect to the database and use a query: Use Microsoft Query to retrieve external data (This is why, IMHO, you should just jump ship and move to an Access appliation (^-^) ).
You'll have to have a way to validate information between the frontends and the database or there WILL be issue with duplicated orders and other messes.

and that is just for starters. (0_0)

(yes, I tried this once... ouch... not one of my best moments)

Once you build your database, you'll split it so that there is a frontend and backend (not any different than what you're going to do with excel). Each user will have their own copy of the front end on thier local PC and the Backend will reside on the network.
Yu can then make changes to the frontend if needed and then distribute the new one in various manners... email, automated checks etc...

NO MATTER WHAT YOU DO... BACKUPS are a MUST!
NEVER edit the production copy of the frontend or backend, ALWAYS make a copy and edit it!
NEVER edit the backend during business hours if possible. If you must, then lock the users out, take the backend off-line... and even then, work on a copy of the file! This makes sure that your new backend is up to date with entries.
-z