The spreadsheet I'm making is designed specifically for unique needs of the company I work for. If I were to give all the details for what I'm trying to do it would most likely just confuse the heck out of everybody, but without enough detail you won't be able to understand what I'm asking. So I'm going to do my best to give just enough info to get the general idea across, but should you have any questions by all means ask away.
The long and short of it is I'm using Excel to create a data base that can be accessed by multiple users at the same time over a network. I wanted to run my game plan by you guys in case anyone has any suggestions for improvements before I start.
Step 1:
I'll create what I'm calling a "dashboard". Basically this will be an Excel file that each user will have on their hard drive and will be used to access the data. Each dashboard will be the exact same file for everyone. Expect that when any particular dashboard is used to modify any data it will take note of which computer was used. There will be multiple tabs for different functions. Example functions are "Create/View Quote", "Enter/View Timesheet", "Enter/View Material Invoice", "Run Job Cost Report" and many many more. The tab for each function will be set up like a form. The user simply enters the required information then presses a button. A macro runs that checks the info for any errors, then enters the info into the data base, and finally clears the form so it's ready for a new entry. No data will ever be stored in the dashboard itself. Everything will be stored in a separate file explained in step 2
Step 2:
As I mentioned above every user will have there own dashboard on their hard drive. But no data will ever be stored to that file, or even on their hard drive for that matter. Everything will be stored in either a separate Excel file or possibly a text file. Haven't decided on that yet. Excel will do doubt be easier to work with and code the necessary macros. But text files may use less memory. Whatever I go with these files will be stored on a server accessible to everyone. The user will never directly access these files. They will use the dashboard to do that for them. This means that the actual data files should never be open for more then a second or two. I'm expecting the dashboards to be open all the time. Which is why everyone will have there own. Hopefully this will mean I don't have to deal with the "read only" issues from people trying to access the same file at the same time. Sure it will be possible that 2 dashboards will try and access a file at the same time, but I can code error catching that will recognize if a file is open and loop until it's closed. My macro's will be the only thing that ever opens a data file and it will close each file as soon as it's done with it. So everything should be fairly instant. But even if not a few seconds max. Don't think that will be an issue.
So in a nutshell I've got two main issues.
One I need for multiple users to be able to access the info at the same time. This is why the actual data is on the server and the dashboards are on the individual hard drives. And 2nd I don't want it to take forever when a macro runs.
I've got a single user version of this already up and running. But everything is stored in one file. One BIG file. Over 30Mb's. It takes forever to open/save and although multiple people can open it at once. Only one can make changes. Everyone else is read only. I'm wanting my next upgrade to fix these two very annoying issues.
I know this method will work. I'm just looking for anyone else out there familiar with VBA who might be able to offer any suggestions. Specifically on how to maximize my memory usage. I want this guy to run as fast as possible. Also this will be my 1st time using a macro to access a network. I can't see why I'd have any issues doing this, but you usually don't until you run face 1st into one. So any suggestions there will be greatly appreciated too.
Oh and for what it's worth I don't believe Access is an option. Granted I'm brand spanking new to Access, but from the little research I've done I'm pretty sure Excel is a better choice. I can't really explain why without going into a ton of details, but basicly I can make Excel do whatever I want. Although Access was designed to automate a ton of this (no writing macros WooHoo!!!) it forces you to stay within it's boundaries. I don't like boundaries. But if you love Access please be sure to tell me why. Maybe I'll look into it more if I hear a bunch of good things about it.
... 
GuG-Points: 264
Last Seen: 09/07/2008 - 2:21am
I am: A Man