Viewing Attachments in Access Database

[RootMason]

Also, please let me know if you need the link to stay up to the accdb database, or if you need to see the mdb instead. I am interested in being as helpful as I can, but am naturally apprehensive with having this database publicly accessible. I will take it down by tomorrow afternoon, unless you have any need for it to remain there.

[ItsMe]

June will probably want to take a look at it still.

As for the relationships, the only one that I can see that matters is the one to many that has the referential integrity. I would start by interviewing the accountant and study how they interact with the DB. See what they do with the data that is inside the DB. This will begin to give you insight to the relationships.

Take a look at your network. There has to be a way to use UNC to link the tables. Then develop a plan for distributing the FE to the users so they have the latest version etc. Maybe have a version for the VPN clients and a version for others. I saw something about Employee ID hardcoded into a query. Gives me the impression of groups. Maybe accounting can give insight...

[June7]

I tried downloading files at home but it was going to take over an hour so I abandoned.

[ItsMe]

I took another brief look at your DB today. The thing that jumped out at me this time, other than the fact there are a lot of orphaned objects and code, is the fact that any employee can access another employees expense report and time card. If you spend some time building an interface for employees to login, you will learn a little about Access and also benefit the application a great deal.

Just using the interface provided and a couple guesses on an employee ID, I can cause a great deal of mischief.

[RootMason]

June7, I suppose that's the big complaint with this database!

ItsMe, thanks much for taking a look at it. I am actually underway with building them a new one. Using templates & some Linux knowledge (I was surprised to see that query syntax looks surprisingly the same) I'm certain I can accomplish it. I have plenty of questions, but I was curious if my questions would better be posted here, to you, or as new threads in the forum? A question of etiquette, but I get in depth with people's projects on Linux forums and prefer not to lose the project in different threads.

As you said with the problems you found, how would I do I go about finding and fixing these orphaned objects? I'll understand if it's too complicated for the purpose of this discussion... but I would love to know.

I was also thinking that, because of it's size (72MB on the server), that must cause a bit of problems over the network, right? A lot of times I have VPN'd to the site & opening employees individual db's will cause Access to freeze, giving the Not Responding prompt, and after a minute or two will then come up. Clicking any of the links has the same effect, as does trying to link the tables. Or could this be due to the orphaned objects trying to load unsuccessfully? I was thinking, if it was a matter of size, that I could reduce a lot of the records in expenses & timesheets data dating back to 1998 (I was working as a brick mason back then, and by-night-Linux nerd!) by making a backup and then deleting all the entries before 2013. Anyways, I'd love to hear your advice on the subject!

I have taken off the link to the old database as of a few minutes from now, thanks again for looking, or even trying to download!

I have both a timesheet template I plan to use (Desktop time sheet) and an expenses template (Desktop expense reports). Do you have any suggestions?

I appreciate the time both of you have taken to talk with me about this. Keeping me engaged has helped me to learn quickly, and even though I was disappointed to see that the new templates I am using were not web-compatible (Sharepoint), that I was able to read & fix the errors I originally posted with a fair amount of ease. Though, without knowing the exact effect of the changes, I decided not to save them, even as a copy. Lot of learning to be done, thanks much!

Steve

[June7]

I have a split database. Backend on file server is over 150MB. The frontend is almost 45MB. A copy of the frontend is installed on each users workstation. Speed is not an issue. VPN is another matter, never used it.

[ItsMe]

I wouldn't worry too much about the orphaned forms or tables at this point too much at this point. There are not that many objects to begin with anyway. If you study the workflow, they will become apparent. That is what I focused on this last time. I spent twenty minutes interacting with the application. The copy I used did have some missing fields in one of the tables that was in an orphaned table. The program would not function until after this missing field was created in the correctly named table.

I still recommend interviewing the accounting team to see what their needs are and how they currently interact with the application. You will likely discover a need of theirs that the current DB does not satisfy. This is necessary information when implementing a ground up approach.

By adding another column to the Employee table, you can store passwords. Having a user login using a password will limit their access and possibly improve performance by not querying the entire DB for every user transaction. Learning how to and creating a login form may show you some techniques that you could use in other areas of the application.

As you consider different techniques and discover new tools, you can post new threads so we can help you through any questions you will have about them.

As June mentioned, the download was not possible for her. I was thinking she may have been using a dial up connection . I did not have an issue with my connection. It indicates that you may not be connected to the Outside at the ATM layer. I would look into the type of service the ISP is providing. You may not have a guaranteed bandwidth. Just guessing here. Could be many things. And it is not going to help if a user is trying to connect with bad service at their location.

Other areas you may be able to improve performance would be to expand on the idea of querying relevant information as it is needed. Loading the entire table or tables into a form's memory is not always the best approach. This is where SQL and VBA will come into play. Consider how a web page will prefetch data and store it into memory for a pulldown. The developer decided it is a good idea to bring all options into the form's memory at the time the webpage loads. This provides a more satisfying experience for the user. The user does not see any delay when the click on the pulldown because the form already queried the options from the DB. The difference is your forms query everything and when a user interacts with the form, it has to query everything again.

Just query the data that is relevant to the user at the time. This is where user groups may come in handy. The accounting department may not see a significant performance hit if they are working over a gigabit connection. A user in a remote and rural area may need a scaled down version or the option of a scaled down version. The President and VP may want to see historical expense reports for the entire company, etc.

[RootMason]

Sorry for not responding sooner, it's amazing how much kids, homework, soccer, boy scouts & dinner can take out of a day!

I interviewed the office manager/accountant to find out what she needs and you're right, that was a big piece of the puzzle. I heard in one of the tutorials I watched "begin with the end in mind" or "start from the final reports". It certainly does make more sense in what is needed, thanks much for the advice.

Thanks for the idea of creating a password column, that's a fantastic idea. I will look into that.

As much more of a network admin, I thought the same thing: that June7 must've trying to download via DSL. I appreciate her help regardless... thanks again, June7! We have Comcast Small Business with 30Mbps up and down in the office, but the link was posted externally on their Inmotion Hosting server.

Thanks also for the idea of also creating queries for only relevant data, which is my assumption of one of the biggest problems even without knowing Access very well (loading the entire database every time).

I have already begun trying to piece together what exactly I will need to build a new database, and I will be posting more questions soon about how to design & bring it all together.

Thanks again for all the wonderful input!

Steve Clark