Forms Slow to Save and go into Design View

[NeoPa]

I'm not really sure TBH. I would consider Compact/Repair regularly of course.

Otherwise I suspect you may have something else wrong with your setup which may be allowing these corruptions to occur. As the scope for that is almost infinite there's little I can say to help with that I'm afraid.

BTW When I refer to the setup I'm not restricting this simply to your Access configurations (unfortunately - That would be simpler).

[Megalog]

Is this database being used by multiple people at the same time? If so, is it a split (front end/back end) setup? If not, that can encourage corruption to happen.

If it is already split, and the end users are running their own copies of the front end.. then the only other recommendation I can give is to have a good backup system in place. We use a winzip backup, that's run on a pc 24 hours a day with the system scheduler. This way, we can go back to any given date and pull a good copy to restore any form or report that happened to corrupt itself in the master copy.

[NeoPa]

See Front-End / Back-End (FE/BE) for stuff about Front End / Back End.

[iheartvba]

Firstly to Neopa's point regarding setup:

Backend: access database with only tables

FrontEnd Forms: All unbound forms so I don't think there would be a situation where the database can be close whilst a record is actualy being edited, all edits, additions and deletes of records are done via Update, Append and Delete queries respectivley.

FrontEnd Queries: Most queries are made via Access query builder but now creating much more via RunSql and ADO functions of VBA due to the greater flexibility. Also for very complex queries I first use a Make Table query to make a query into a table and the query that table (if that makes sense), the purpose of this is to increase the speed of running very complex queries mainly for reports (I'm not sure if this could be causing corruption)

FrontEnd Reports: All made via Access Report Builder

3rd Party Products: The only 3rd party product being used in the database is Eztwain which is an addon to be able to Scan via VBA.



Now to answer Megalog's comment:

1. I have split the DB

2. I do do backup's but when it got corrupt, I didn't want to revert to the last working backup, because it may still have been partly corrupt, so I thought might aswell overhaul the whole thing into a new shell. Any suggestions whether I was right in thinking that?



The main thing I can think of that would corrupt things is that many times when I am designing a Form, Report or especially a query, access just hangs, then I have to shut it down via Ctrl+Alt+Del, and that happens a lot when I am designing something new.

Thanks Guys

[NeoPa]

Having no experience of Eztwain I would suggest checking that out (try without for a while).

I should also bring to your attention that the setup is not just that which runs within Access, but also to that within which Access runs. Hence the comment about it being almost infinite in scope. It may be worth testing doing some of your development on an alternative PC.

Obviously we cannot hope to take you through this as it is specific to your own PC environment about which we can know very little.

[iheartvba]

Fair Enough, I agree w/you 100% I have just got a new PC about a month ago which is much faster, and obviously now it crashes less because the PC can handle the load much better, so I guess much less chance of corruption.

Thanks

[NeoPa]

I wouldn't expect the speed or performance to have so much of an effect regarding corruption, but a PC after all, is an enormous container of so many configuration options. Who can ever know that their PC is set up without any problems. I know mine have some - I just try to keep them to a minimum.

[iheartvba]

Just on the note of doing a compact and repair on a regular basis: Is there a way I can Automate this so Compact and Repair is done @ a specified time every night, and more importantly would this be recommended, I mean would it be harmfull to do Compacting and Repairing daily?

[NeoPa]

It can be done. I would recommend making a backup copy at the same time. Sometimes, on rare occasions I believe, the Compact / Repair can corrupt your database. This may only be in older versions, but I would. Just to be safe.

Would you like me to dig up something on automating from the OS Scheduler? It does involve some fiddling in code as well as command line options etc.

I suppose with a tag like yours you won't be afraid of some coding ;)

[iheartvba]

Hi NeoPa,
Yes! Defiantley I do heart vba, Bill Gates didn't become rich by accident Ms Access and the whole MS office suite is really something amazing, and an empowerment for small business to be able to compete with the big guys.

With my database, it is currently used in multiple sites so I can't compact and repair them all my self. Everyone seems to recomend doing the compact and repair on a regular basis so I think it would be worth the time spent on it. What would you suggest?

in short I don't mind the coding, if you think it is a good idea to do compact and repair on a regular basis, then yes, I would love some direction on how it would be done.

[iheartvba]

Also NeoPa, in relation to your comment #22 of this post, where would I start looking of config issues of my PC? Also it's just on a peer-to-peer network so would it be an idea to get a Server OS? I would prefer not to as we don't want to increase our IT costs.

Thanks

[Megalog]

See now, here is where I'm still a bit confused..

You say you have a split db.. yet you want to automate a compact repair on this? But your corruption lies in the front end, not the back end data. Are you distributing copies of the front end to all users, or are they all running the same networked front end file? That simultaneous usage of the same FE file is what could cause the corruption, in my mind.

You should have a design master FE file.. it can link to the same live BE or a test copy of the BE file. When you update the FE, then you would push out a new version of it to a client accessable area, preferably in a read-only format (MDE/ACCDE). Doing what you can to minimize the chance of corruption occuring in the first place should be your top priority.

[NeoPa]

Also NeoPa, in relation to your comment #22 of this post, where would I start looking of config issues of my PC? Also it's just on a peer-to-peer network so would it be an idea to get a Server OS? I would prefer not to as we don't want to increase our IT costs.

Thanks

Let me respond first to this post as it is a little off topic (although relevant it won't help to discuss it here).

This is such an open-ended subject that I'm afraid the best advice I can give is to keep your eyes open. You can post a question in our Windows forum if you like, but as the symptoms are so unclearly defined, I feel the best you can expect is some general good advice. I certainly see no obvious solutions you could get from the web.

Alternatively you could consider getting a professional in to check it out, but that costs, and with such problems there's no guarantee they'll find the actual problem anyway. They're likely to offer you some good advice though (if they're any good) which will probably help even if they don't manage to find the specific cause(s) of your current problem.

I'm sorry I can't be more help with this aspect.

[NeoPa]

Before I proceed with the automation of the Compact / Repair, Megalog makes a good point. Can you say how your FE & BE are set up, both for the development stage and the roll-out (where do your users find their databases)?

[iheartvba]

I Develop the Front End on my PC then when it is ready for distribution, I place a Front End on each users desktop which links to the bank end (No User shares a Front End With another User). all the Front Ends link to 1 back end. The link is done programatically with the network path saved in a table.

Thanlks