Access DB has become troublesome - Please try to help!

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 a

Ra 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.

Ra 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

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_fi...edLocation.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/TWickera.../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/d...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

"Fred" wrote in message
...
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 a

Ra 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.

Ra 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

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".


"Fred" wrote:

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 a

Ra 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.

Ra 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

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Fred" wrote:

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 a

Ra 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.

Ra 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

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


"Dorian" wrote:

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".


"Fred" wrote:

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 a

Ra 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.

Ra 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

"Jerry Whittle" wrote in message
...

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".

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

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


"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Fred" wrote:

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 a

Ra 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.

Ra 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

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

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

Fred wrote:

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/corr...uptobjects.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/