Viewing Attachments in Access Database

[RootMason]

Hi All!



I am a Linux/Network Admin recently tasked with figuring out why our business' Access database loads so slow over the network. They have a Sharepoint server which is currently not being used, so I thought I would see if I could convert their old database (mdb) into a web-access database and publish it, in an attempt to see if this test will decrease load time. I have saved the mdb database into accdb format, and now I'm trying to go through all the errors involved in importing this into a blank web-access database.

Being new to Access, I am at a loss looking at some of these errors. The specific one to ask about today is ACCWEB 107022, Property value should to be empty or set to 'auto' to be compatible with the Web. On visiting Microsoft's Web-Compatibility Check page, I see that this is a fairly accurate description of the problem.

Would any of you folks be so kind as to either explain or provide a link that will help me to resolve this issue? I am also stumped as to why, when I open the subdatasheets, I see the same entry listed again. There are about 7 layers down for each entry that all show the same data. I would be willing to provide screenshots, or whatever you might require, to those kind enough to help out. Let me know what you need to see.

Thanks much!

Steve Clark

[June7]

Is this slow down something new?

Title of thread referenced attachments. Does this db have attachment type fields with embedded objects? Attachments can bloat a db and could possibly slow it down.

Access has a 2GB size limit and attachments can chew it up.

Sorry, I've never used Sharepoint.

[ItsMe]

To add to what June mentioned..

The thing with the Subdatasheet is probably you looking in a table and seeing what is sometimes referred to as child tables. This is there because there was a relationship created in the Relationships Window. Depending on how the DB was created, the DB may require these relationships to be there in order to function correctly. They sometimes enforce referential integrity rules and may enable cascading deletions.

I have never used SharePoint either. I don't understand how this could speed things up though. I understand your desire to convert the mdb. You want it to be compatible for use with the available server/hardware. I do not know your network security needs or size but why not dust off an old PC that has an Intel NIC and load W7 Pro on it. Or load anything on a box and put it on a VLAN. All you need with that mdb is a file server. Is your bottleneck at the current server?

I would concentrate on building front end applications in accdb format for the users. I would start one department at a time.

[RootMason]

Wow, thanks all for putting your two cents in! As background on the problem, this business had an IT & Access guy a few years ago. He left and they tried to coast on what they had until most items on their network either weren't working properly or just not working at all. I was hired a few months ago to get their network & Linux servers back in order, which has been completed. Now their biggest problem is this database, which loads quickly in the office, but ridiculously slow over the network (like with a VPN connection). Also, data gets randomly moved from say, one person's timesheet to another person, with no way of tracking it.

I love to be as multi-faceted as I can, so 1 book and a few hours of YouTube tutorials, later I opened their mdb and started sorting through what I can. They are using a company database (mdb) as their backend and then using an individual mdb for each employee as the frontend. These are served from a Debian Wheezy server via SAMBA share. Employees access the database by connecting to the share and opening their individual timesheet or expense sheet.

I have made a copy of the entire database, so my newbieness doesn't affect their business as I try and work through these problems.

Anyways, my first idea was to try and truncate (I don't know if that's the right word for it in Access) the data being shown. For example, one timesheet shows recorded hours back to May. Without knowing what effect this would have, I decided to keep looking. I came to the idea that the best way to access this data would be to have it available by the web, rather than by SAMBA share. They have SBS2011 with Sharepoint, and I thought that was something that might speed up connecting to the database AND be something I was fairly comfortable with.

[RootMason]

To June7, the attachment title is in keeping with moving their database to a Sharepoint server. In order to import their accdb database into a blank web-database, I must go through each problem with "Web Compatibility Issues". This is the first one on the list. Below is the list of errors I need to fix for this to work. Above is a screenshot of the many subdatasheets I found on the table that relates to error #1.

Also, I don't know if this slowdown is recent or not, but I will ask around.

[June7]

Don't understand the first image. Why are there 7 nested tables with identical fields and data?

Post an ERD of the db or provide the db itself for analysis.

Changing primary keys and relationships could be a significant effort.

[RootMason]

June7, thanks much for the willingness to help! Yes, I understand that changing the primary keys & relationships will be a significant effort, but it is both good for me to learn and for the functionality of the business, so I willing to throw my hat in on it. I'm not even against rewriting the database altogether, which may be simpler than repairing it. If you are interested in helping further, I would be more than happy to post the entire backend. I looked through the data contained, and nothing seems to be a security concern other than first & last names of employees. The file itself is 71.9MB, will I be able to upload it?

[ItsMe]

Changing primary keys and relationships could be a significant effort.

Diddo - I don't even want to imagine what that first image is of.

Are there multiple concurrent connections to any one front end file? I am thinking your time trying to get this on SharePoint would be better spent sorting through the basics instead.

[June7]

500KB limit for uncompressed files, 2MB zip allowed. How big is the frontend? Both pieces would be nice to have. The non-web version.

[RootMason]

June7, each employee's frontend is around 1.7MB, the backend is 71.9MB, so I won't be able to post it here... much as I'd love to be able to monopolize your time! If you're interested, I could post it on the web & give you the link to download it. I could provide either the original mdb, or my newly saved accdb, I don't know which would be better to have.

ItsMe, no, each employee has their own frontend.

Thanks to both of you for your willingness to help! You guys are great!

[RootMason]

June7, after compacting & zipping, it is still just above 10MB.

[June7]

Did you run Compact & Repair before zipping? Remove data? Leave a few records for test purposes.

Alternative is upload to fileshare site such as Box.com and post link to the file.

[RootMason]

Posted the backend & frontend here:

http://www.gsanalysis.com/access

Thanks again for all your help! If I could just figure out why this loads so slow and loses data randomly would be fantastic. Thanks much for your help, please let me know if there is anything you need from me. Thanks again!

[ItsMe]

I didn't spend too much time looking at it. What I see is a rather simple DB. Nothing is jumping out at me as to why records are not getting created correctly, but I will guess that it has to do with design. For the most part, the wizard was used to create these DB's. I would have to study the workflow and like June mentioned, the relationships would have to be analayzed.

There is one relationship in the relationships window that requires referential integrity. Did not see this relationship in any of the saved queries so I will guess it is in one of the subforms. All the others are inner joins without enforcement so....

I guess the macro did not convert over to the accdb format. I guess it is supposed to remove a filter. Don't see any controls that are applying a filter. Only see controls that open another form and evidence of many failed attempts of creating other controls. Could possibly improve performance by rebuilding the forms.

There are many queries that do not seem to be doing anything at all.

There are forms and querries in the back end. Are users opening the back end directly? Maybe you can start by rebuilding an app for a specific group. How many users do you have? Can you break off a small group to get started?

Considering you should probably rebuild the forms from scratch anyway and everything was built using the wizard, I would build custom FE apps for different groups and get rid of all of the forms and queries as well as the relationships in the back end

[RootMason]

Thanks so much for taking a look at it! Some of the problems you may be seeing could have to with the fact that these are mdb, which were saved today, by me, as accdb. Unfortunately, I'm not knowledgeable enough (yet) to say whether or not there are conflicts with converting from one file type to another.

The business has around 40 employees currently, with about half of them being local and half being remote, some as far away as Chile & China. I don't see any way of splitting off employees from the group to test, but I am certainly considering making an entirely new database as a test, where I could make fictional employees and demonstrate the validity of switching over to this new model.

I do beleive the users are accessing the database directly, but I'm sorry, I don't have the experience to say for sure. I have what could be best called a lazy handle on Access terminology. I have had to learn to relink the databases for employees any time they use this from a different computer, which leads me to beleive that they are accessing the database directly... unless by directly, you mean that they actually open the Gsa.mdb directly, which they do not. They open their own "frontend" by name, enter a password (which I removed for you) and then link to the database.

So... if I could talk you into another question (which may be better posed as a new thread): do you have any links or advice on how to dissect the relationships properly so I can begin rebuilding the database? The fact is that the accountant is having no problems using this, and I consider her the hub of this entire database. Ease of use for employees comes secondary in my book, but again I'm a Linux/network admin... I could be looking at that wrong. I see that, if I screw up her ability to use this database, I have pooched the whole system. Nobody gets paid. And I maybe get fired for all my good intentions...

Anyways, thanks so much for helping me sort through this mess! You're A#1 in my book! Please let me know if there's anything I can do for you in the future.