Access making copies of database.

[Secretgeek]

Good morning,

Am using an Access 2003 database that has had this issue for some time.

Basically it is creating copies of itself, seemingly at random. The database is, imaginatively, titled Access Request Database but the copies are all named db1, db2, db3 and so. If the copies are deleted Access just starts back at the beginning.

Sometimes it will do this a couple of times a day and sometimes not do it at all for several days.

Does anyone have any suggestions as to why this might be happening?

My own thoughts are that we have 6 users and it creates a copy when there's some kind of conflict.

Really what I'm looking to find out is, is this an Access issue or is it likely that this has been built into the database?

Thanks in advance for your help.

 

[Remou]

I would suggest it is set to compact on close, or at some stage, and that the compact & repair is failing because another user is locking the file. This is not a good situation.

http://lessthandot.com

 

[Remou]

(I hope you do not have 6 users on the same front-end, because it can deteriorate to fatal.)

http://lessthandot.com

 

[Secretgeek]

Thanks for your comments. Yes it's definitely set to compact on close as it's noticeable when you're the last user out.

As for your second point. Yes we do. :-(

Don't blame me - I didn't build it!

Thanks for your help.

P.S. Now I need to decide whether I report this to my manager (and end up being tasked with fixing it - being the only person that knows anything about Access in the office) or quietly whistling and walking away like I haven't seen a thing.

X-< Damn my conscience!

 

[Remou]

Good luck, and I hope you are rewarded or at least get time off for good behaviour

http://lessthandot.com

 

[MajP]

Fixing this should not take more than 15 minutes.

1) Make a new blank database
2) Import all tables into the new database
3) This is the back-end database. Put this on the network.
4) Make a new blank database
5) Import all forms, reports, modules, macros into this database
6) This is the new front end database
7) compact and repair both new databases
8) Use the link table manager to link the front end to the back end
9) Put the front end on your local machine.
10) Make 5 copies of the front end and give to your coworkers. They put the front end on their computer.

 

[Secretgeek]

Really, it's that simple?

I had visions of this being a bit of a nightmare to do.

Many thanks!

 

[MajP]

Should be that easy, but there may be more going on. But splitting the database into a front end back end has to happen. Only more bad things can happen if you do not split it.

 

[Remou]

@MajP
Do you have something against the Database Splitter?

http://lessthandot.com

 

[MajP]

Yes, in the fact that the front end remains the original database. This has been running as a multi user database. If it is not corrupt already, it sure is not clean. I would want to start with a clean slate. I would have no problem with it if, the database was not already showing signs of corruption.

 

[Remou]

It is still easier to copy the database, compact & repair, run the splitter and then copy the front-end objects into a fresh database.

http://lessthandot.com

 

[MajP]

Not really sure about easier. It is pretty simple either way. I imagine it is about the same amount of button clicks and time.

In my approach the user learns how to import objects into a clean database, which they need to know how to do.

I am just not real big into wizards. A lot of people on this site have no idea what to do when the wizard does not meet their needs.

 

[Secretgeek]

Thanks for your assistance.

I've split the database and have created an MDE for the front end but am running into some problems.

Firstly, the appearance of some of the forms has changed. Specifically the background colour of some of the buttons has changed from brown to white. Not a major issue I think but could it be symptomatic of an underlying problem?

Secondly and more importantly, Access is now crashing during certain actions. Specifically, several of the buttons on the front screen now cause a crash every time. I'm looking into what might be common across all the dodgy command buttons but it's also half 4 on Friday and time is short. :-/

 

[Secretgeek]

A quick update before I call it a night.

I've checked the properties of each of the buttons and they are all of the DoCmd.OpenForm "Thisform" type. About as simple as a button can get, so the issue has to be with the form that they're opening, yes?

|-( Why did i have to go and look at this on a Friday?

"Mr. Curiosity? Meet Mr. Cat.

 

[PHV]

AFAIK, you can't open a form in design view in a .mde

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Secretgeek]

Sorry I wasn't clear. You're right of course. I was working in the non-MDE copy of the front-end. It's behaving the same way as the MDE.

 

[Remou]

Can you open the problem form manually?

http://lessthandot.com

 

[trevorwilliams2]

It appears you have some corruption problems.
I would check the form /client side of the database first.

Try a decompile / recompile and then a compact and repair.

Decompiling a database is done through a command line switch; "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "path to your database" /decompile

When the decompile is done your database will open. Hit ctrl + g to open the code window. In the code window select Debug and the name of your DB should be the fist selection.

When done compiling perform a compact and repair.

If this does not work, there are companies online that can restore corrupt databases.

Try

http://www.everythingaccess.com/.

They fixed one for me. If I remember correctly it was about $150.

 

[Secretgeek]

Hi Trevor,

Have just tried your method.

Got as far as about to open the front end and received the following message from Access:

"The Visual Basic for Applications project in the database is corrupt."

I think that pretty much answers the question of what's wrong with the database then?