Access DB has become troublesome - Please try to help!

[Fred]

Have done gazillions of databases in Access 2000 & mostly in Access 2003
(2000 file format) and Access has always been very reliable. Any problems
could always be fixed with compact and repair.

Now I have an DB which is continuously misbehaving, and on different
computers. (just one .mdb file that I move around) Most problems are with
a tabbed "Menu" type form (with lots of buttons to open forms and queries)
although I have deleted and rebuilt the form and the problems persist.
Compact and repair has not fixed. I think that the only things I did
differently with this DB are using a tabbed form, (a new thing for me) ,
possibly opened it once Access 2007. and it gets moved around a lot on a
thumb drive.

I'm not good at code, so there' nothing fancy in there. Just buttons (made
with wizards) which open queries and forms. Problems are:

Rare: Tried opening objects (eg forms, queries) which Access shows in the
list, and then it says they don't exist

Common: A button that previously worked fine, opening a form, now crashes
Access. The form opens fine directly.

Common get "You tried to roll back a transaction" error message when
closing the "main menu" form after working on it.

Common: Button wizard creates code sections that have the button ID but no
code

Common: I think that several times buttons that I just I put into the form
are not there when I go back.

Rare: Just trying to see the code behind a form crashes Access.

1. Any guess on what the problem is?

2. Any ideas on how to fix it? I have a lot of time invested in this DB.
I could import the data into a new DB, but would I have to recreate every
query, form etc? Or just copy them over?

Thank you in advance.

Fred

 

[Gina Whipp]

Fred,

Make a back-up IMMEDIATELY, if you don't already have one.

I would start by copying all the objects into a new clean database. Make
sure Name AutoCorrect is turned off. Anyone opening it in 2007 should have
the database in a Trusted Location, see...
http://www.regina-whipp.com/index_files/TrustedLocation.htm

If the database is accessed by more then one person at a time then it should
be split, see... (ESPECIALLY, if they are sharing the same front end with
different versions of Access)

http://www.members.shaw.ca/AlbertKallal/Articles/split/

http://allenbrowne.com/ser-01.html


You may also want to take a look at MVP Tom Wickerath's article...

http://www.accessmvp.com:80/TWickerath/articles/multiuser.htm


To handle the update of multiple front ends see...

http://www.autofeupdater.com/


If each user needs Access you can install the Runtime...

http://www.microsoft.com/downloads/...d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dorian]

Here's what I would do:-
(1) Make a backup copy and don't touch it.
(2) Create a new empty database and import all the objects into it.
(3) Run a compile
Hopefully this is either a single-user database or is a split database and
all users have their own front end. Also, hopefully, you make regular backups
or at a minium before you make any changes.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Jerry Whittle]

You can import all the objects, including the forms and queries, into a new
database file. If something won't import, it's probably corrupted. You'll
have to rebuild it from scratch unless you have a good, recent backup. You do
have a recent backup, don't you?

You have to be careful with Access and removeable storage like flash drives.
Make durn sure that you have completely closed both the database and Access
before removing the flash or portable hard drive. Even then wait a couple of
seconds. Depending on the computer, it could have a delayed write and take a
while. Back in the days of floppy or Zip disks, there wasn't a faster way to
corrupt an Access database than ejecting the disk before Access was done
writing to it.

 

[Fred]

Hello Dorian,

Thanks for the response. It IS a single user unsplit database. Just one
mdb file.

By "compile" do you mean "compact and repair"? If not, could you tell me
how to compile?


Thanks again.


Fred

 

[Dirk Goldgar]

You have to be careful with Access and removeable storage like flash
drives.
Make durn sure that you have completely closed both the database and
Access
before removing the flash or portable hard drive. Even then wait a couple
of
seconds. Depending on the computer, it could have a delayed write and take
a
while. Back in the days of floppy or Zip disks, there wasn't a faster way
to
corrupt an Access database than ejecting the disk before Access was done
writing to it.

This is a very important piece of advice, as the flash drive was the first
thing I thought of when I read that Fred was moving the database around a
lot on a flash drive.

Fred: Are you opening the database on the flash drive, or just copying it
around? If you're opening it on the flash drive or copying it to the flash
drive, it is particularly important to use the "Safely Remove Hardware"
application to shut down the flash drive before removing it, unless you are
willing to wait a substantial length of time first. Running that
application should force Windows to complete any "lazy writes".

 

[Fred]

Jerry,

Thanks for the response. This is a DB I have been and am still developing
for someone as a favor. (The favor is turning out bigger than I planned
)

I have lots of backups going back to when it was almost nothing. The
question (actually, dilemma) is how far back to revert to. I already
reverted once and it apparently wasn't far back enough.

Sincerely,
Fred

 

[Fred]

Dear Dorian, Jerry, Dirk and Gina,

Thank you all sooooooooo much! To recap my answers to your questions:

This is a DB I have been and am still developing
for someone as a favor. (The favor is turning out bigger than I planned
). It's a single .mdb file, Access 2000 file format being developed on
Access 2003. I've been doing everything with it on the thumb drive.
Opening, modifying, copying to and copying from.

I have lots of backups going back to when it was almost nothing. The dilemma
is how far back to revert to. I already reverted once and it apparently
wasn't far back enough.

It sounds like I should copy all of the objects over to a new DB, and, not
that I go unplugging it while the file is open, I should be a lot more
careful.

Is there any better way to copy than the one that I know is one by one and
retyping / recreating the names of each when I paste them?

Thanks again

Fred

 

[Fred]

Scratch that last "is there a better way" question....I found it. If I
knew it was tht easy I wouldn't have been avoiding it.

Thanks again

Fred

 

[Tony Toews [MVP]]

Now I have an DB which is continuously misbehaving, and on different
computers. (just one .mdb file that I move around) Most problems are with
a tabbed "Menu" type form (with lots of buttons to open forms and queries)
although I have deleted and rebuilt the form and the problems persist.
Compact and repair has not fixed.

Compact and repair really only fixes tables, indexes and
relationships. It generally doesn't do much for other objects such as
forms, reports and VBA code. For those you either need to import into
a new database file or try SaveAsText.

Corrupt Objects within a Corrupt Microsoft Access MDB
http://www.granite.ab.ca/access/corruption/corruptobjects.htm

The little documented SaveAsText and LoadFromText may help if an
object is corrupted or otherwise behaving weirdly.
At the debug/immediate window type:
Application.SaveAsText acForm,"MyForm","c:\form.txt
You can load the file into a new MDB.
Application.LoadFromText acForm,"MyForm","c:\from.txt"

Sample code at http://www.datastrat.com/Code/DocDatabase.txt for
saving all objects in an MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Larry Linson]

Back in the days of floppy or Zip disks, there wasn't a faster way to
corrupt an Access database than ejecting the disk before Access was done
writing to it.

<CHUCKLE> Depending on the release of Access and the complexity of the
database, I'd have to disagree with you, Jerry. Just opening a database and
trying to use it was the fastest way, in many cases, though not perhaps
quite as reliable at corrupting as being too quick to eject a floppy or ZIP.

Larry

 

[Larry Linson]

Back when tabbed forms were new to Access, I tried using a tabbed form
navigation scheme on a simple database done on subcontract for a customer. I
found it more work than the traditional switchboard, and the users found it
no easier to use. Neither the prime contractor nor the customer suffered,
because that's one of the few times I ever did a "fixed price" after the
prime and I agreed on what doing it in traditional fashion should cost, and
the customer also reported that it wasn't any _harder_ to use, either...
just a wash. But I spent between 2 and 3 times as long as it would have
taken to create with a switchboard.

I'm not sure what a "tabbed menu type form" is, so we may not be talking
about the same thing.

I see you have apparently already solved your problem, but couldn't resist
making the comment on tabbed form navigation -- it has its place, but
application navigation isn't that place, IMNSHO.

Larry Linson
Microsoft Office Access MVP

 

[Tom Wickerath]

Hi Fred,

I haven't seen anyone mention using the undocumented /decompile switch. Make
a backup first (as others have already said). If you have just one version of
Access installed, you can click on Start | Run and enter:

msaccess /decompile

Then navigate to your database. Hold down the Shift key the entire time it
is opening (to prevent the possibility of any startup code from executing).
Then do a compact and repair (Tools | Database Utilities... | Compact and
repair database) again while holding down the Shift key. Finally, open any
code module (create a new one if you need to--you won't need to save it when
prompted) and click on:

Debug | Compile {ProjectName}

where {ProjectName} is the name of your VBA project.

Access MVP Allen Browne offers recovery steps here:

http://allenbrowne.com/recover.html

As for how far back you will need to go, none of use can tell you that.
You'll simply need to experiment. Along with the SaveAsText and LoadFromText
methods already mentioned, I've also had luck at times simply copying all
controls on a form, section by section, to a new form. Then set the various
section properties (height, backcolor, etc.) the same. This has actually
worked for me at times when the SaveAsText / LoadFromText methods did not
work.

I recommend using the USB thumb drives for backup only. Attempting to open a
database file on a thumb drive is about as much fun as watching paint dry,
because it can be really slow compared to typical hard drive speeds.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jerry Whittle]

Would that release happen to be Access 95? << shudder >>

I still have repressed memories of that one!

 

[Fred]

Tom,

Thanks,

I keep hearing about "decompile" but what you said here is the cloest I've
seen to a "how to".

I understand code behind forms, but otherwise (despite lots of reading,
buying the wrong books etc. trying to learn fundamental code things like
this) I really don't understand what a "code module" is or how to start one.


Sincerely,

Fred

 

[Larry Linson]

Would that release happen to be Access 95? << shudder >>

I still have repressed memories of that one!

Ouch! That was certainly one of the culprits; Access 2000 in its initial
release version was another (though after 3 SPs it stabilized). Now, if
you'll pardon me, I have to schedule an urgent hypnosis and intensive
psyschotherapy session to try to forget them again. Otherwise, I'll the
nightmares may start up again. <GRIN>

Larry

 

[David W. Fenton]

If the database is accessed by more then one person at a time then
it should be split

I would argue that if the database is going to be run in A2007
sometimes, it would be better for it to be split even if only one
person ever uses it, because that insulates the data tables from any
problems that might occur in the VBA project when opened in A2007.
It would be easy enough to have code that relinks to a data file in
the same folder as the front end so there'd never have to be any
manual relinking.

 

[David W. Fenton]

I've been doing everything with it on the thumb drive.

This could be the source of some problems if you're pulling the
thumb driver out of the USB port without properly shutting it down.
I open MDBs from a thumb drive every now and again, but I don't
really edit anything in them. Certainly, that does write to the
file, but it doesn't do anything major. I wouldn't want to make any
significant changes in anything opened directly from a thumb drive
-- there's just too much chance of an accidental removal (you can
bump a thumb drive and have it temporarily disconnect, which could
wreck an open file).

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

You have to be careful with Access and removeable storage like
flash drives. Make durn sure that you have completely closed both
the database and Access before removing the flash or portable hard
drive. Even then wait a couple of seconds.

You should never pull a flash drive until you've used the SAFELY
REMOVE HARDWARE tool to disconnect it.

I would not recommend working in an Access database direct from a
thumb drive, except very occasionally. There's just too much chance
of the drive getting knocked accidentally and unplugging on you when
you don't intend it.

 

[David W. Fenton]

Back when tabbed forms were new to Access, I tried using a tabbed
form navigation scheme on a simple database done on subcontract
for a customer. I found it more work than the traditional
switchboard,

A tabbed form is not a replacement for a switchboard -- it's a
replacement for a multi-page form, i.e., on that all the controls on
it don't fit on one screen.

Almost all my major data entry forms are tabbed, and have been since
A97. I was using an OCX tab control in Access 2 before that, because
tabbed interfaces are much more efficient in their use of screen
real estate, and also very easy for users to understand.

All my "wizard-style" forms use hidden tabs driven by the
Previous/Next buttons.

I can't imagine how anyone could get along without tabbed forms.

And I've never had any corruption from tabs at all -- it's a
complete red herring and has zero connection to the corruption
problem.