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
1
