Problem with access database !! (1 Viewer)

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
Hi there,
I hope someone could help me!

I have an access 2003 database with linked tables and lots of forms and queries.

I tried to create a new database with importing the forms and the rest but only queries are imported without problem and for the rest I get the following errors :

The search key was not found in any records.
Path not found : VB......temp.
Name conflicts with existing module, project or object library.
No current record.

The program works fine but I can not import any form or module anywhere else.

I tried to open the database with access 2007 and then saved it as 2003 , but again I have the same problem.

Any ideas??
Appreciate your help.
Lana
 

isladogs

MVP / VIP
Local time
Today, 18:54
Joined
Jan 14, 2017
Messages
18,186
Did you 'import' the linked tables first?
 

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
I don't remember the first time. Maybe not.
But later I tried importing the tables first . still not working.
 

isladogs

MVP / VIP
Local time
Today, 18:54
Joined
Jan 14, 2017
Messages
18,186
The following assumes your old database isn't corrupted
If you have any corrupted data in one or more tables, you will need to fix that first
If the code itself is corrupted, first make a copy, decompile it then recompile & finally compact it.

If you don't know how or why to decompile, see this link:
http://www.fmsinc.com/MicrosoftAcces...Convention.asp

Assuming the old database is now usable, try the following in a new database in this order:

1. In the old database, change navigation settings to show all hidden & system objects
Note all other settings e.g. startup form, overlapping or tabbed windows etc for later use

2. Create a NEW empty database
Add all VBA references identical to those in the old database

3. Import all tables EXCEPT for system tables starting with MSys.
Make sure import options are as shown on attached image.
Do NOT import other objects YET

4. Compact the new database - close & reopen it.

5. Import all other objects (forms/reports/queries/macros/modules) at the same time
This time deselect all import options to prevent duplicates

6. Compact the new database - close & reopen it.

7. Enter other settings from old database as in 1) above

Hopefully I haven't forgotten anything ....

====================================

If errors still occur, try again but importing tables one at a time.
After that import the other objects again one at a time.
In each case, note the item(s) causing problems
This will allow you to identify the object(s) giving issues.

If necessary, first make a copy of the original database then empty the tables before importing them.
If that works, try appending all table records from the original database to the new one

If it still fails, can you remove most / all of the records, zip it & upload?
As a last resort, I'll see if I can do it for you
 

Attachments

  • ImportObjects1.PNG
    ImportObjects1.PNG
    12.3 KB · Views: 56
Last edited:

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
Thanks you so much for your time.

First I am using access 2003.
Second I don't understand ADD VBA Refrences??!
 

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
I tried what you suggested.

All the queries are imported without any problem.

BUT all the forms, modules , reports, micros give the same error as I mentioned before.
 

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
I tried to zip the file but the size is more than 2MB and I can not attach here.
 

isladogs

MVP / VIP
Local time
Today, 18:54
Joined
Jan 14, 2017
Messages
18,186
OK. I'll try to answer all the above points at once ...

You've not actually said why you want to create a new copy of your db.
This is usually only done when the old db is experiencing issues or unexpected behaviour. The most likely reasons for this are:
a) corrupted data in one or more tables
b) corrupted code or corrupted database objects (form/report etc)
c) missing VBA references

The fact that only your queries import without error suggest a) and/or b) are true
Also you've tried running your db in Access 2007 before reverting to 2003. This may well mean that c) is true

You say that you did all the steps I listed but the fact that nothing has changed suggests to me that you omitted some or all of this part:

If you have any corrupted data in one or more tables, you will need to fix that first
If the code itself is corrupted, first make a copy, decompile it then recompile & finally compact it.

If you did, that was a mistake... you NEED to check / do all of this

BTW: I've added one extra step to my original post - in RED. I'll explain this in a minute ....

OK back to your comments

1. Access Version
First I am using access 2003.
That's not relevant here.
Everything I wrote should apply to all versions as far as I'm aware

2. VBA References
Second I don't understand ADD VBA Refrences??!
ALL Access databases use a number of reference libraries in order to run VBA code.
Some are installed by default but developers often add more...
See screenshot for a typical VBA reference list

When you change to a newer version of Access, the references are usually upgraded. However reverting to an earlier version doesn't work so well as the older version may not recognise the reference. If so, the 'broken' reference is flagged as MISSING and some program code may not work

Check your references by opening the Visual Basic Editor (VBE)
e.g. using the keyboard shortcut Alt+F11.
Then go to Tools ... References and check whether any are MISSING
If so, you will need to browse for these to reinstall a working reference

3. Import Errors
Almost certainly, you have corruption somewhere

Check each table in turn looking for record where data looks 'odd' e.g. the text may appear to be in an unknown foreign language rather than standard characters.
If you find anything like that you'll need to delete the affected record(s) BEFORE importing to the new db

Alternatively make a copy then EMPTY the tables so you can import without that being an issue

Similarly with forms, reports etc.
If any are corrupted they may cause errors on importing.
EITHER open each item in turn in the old db & check ...
OR import one object at a time so you know which item(s) cause the errors


4. Decompile, recompile then compact
Decompiling is used to remove compilation code e.g. if its corrupted...
To do so you need to open Access in a special decompile mode.
See the previous link for instructions

Compiling - this is necessary to check that all the code will run without error.
To do this open the VBE, click Debug then Compile
This should only take a few seconds if all is OK
If it fails, you'll need to fix the error - the problem code will be shown in RED

COMPACT
When items are removed from a db, the space taken isn't recovered automatically
Compact & repair the database to reduce its size.
I'm not using Access 2003 but its probably in the Tools menu
Again it won't take long and the db may well shrink significantly

In fact after you've done all the above your db may well be perfect and the need for a new copy become redundant !

5. Uploading the db
I said I would look at this as a last resort.
In other words after you've tried EVERYTHING yourself

There are 2 reasons for this
a) if you fix it yourself, you will understand the cause of the problems better
b) I'm using Access 2010/2016.
If I fix your db, you will probably have to fix the references again ! (see above)

To reduce the file size, you will NEED to remove almost all of the data anyway & then compact the db again
See this link for further info
https://access-programmers.co.uk/forums/showthread.php?t=140587

Hopefully the above hasn't put you off ...
If it has, you can always pay a specialist company to do the job for you but it will cost a tidy sum!

Let me know how you get on
 

Attachments

  • VBAReferences.PNG
    VBAReferences.PNG
    18 KB · Views: 57

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
Hi Ridders,

Thank you for your hep.

I have done everything you mentioned, one by one. BUT still I can not import any form or module or report from the old database.

The reason I want to do this is that one of my forms performs very slowly (but no errors) and I decided to make a new database with all the objects in it except that form and I create the form again.
This is when I realized the problem.
The problem is not from the tables because they are all emptied.
The problem is not from ONE form because I can not import any of them.
I decompile and recompile the VAB code but there is no error.

Than you so much for your time.
LANA
 

isladogs

MVP / VIP
Local time
Today, 18:54
Joined
Jan 14, 2017
Messages
18,186
OK - if you can shrink it to 2MB then do upload & I'll have a look.

Otherwise I'd just recreate the 'bad' form & delete the original
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 14:54
Joined
Jun 21, 2011
Messages
5,901
Is it possible this database is 2 gig and not 2 mb? The reason I ask is the error you are getting suggest either that or corruption. And when was the last time you ran Compact & Repair?
 

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
Thank you all.
My database is 7424 kB . This is only the program not the tables. Table are linked and the size is 1384 kB. I run the compact all the time (actually when I see the file is getting unusually big).
Appreciate your help.
 

GinaWhipp

AWF VIP
Local time
Today, 14:54
Joined
Jun 21, 2011
Messages
5,901
Hmm, okay let's try this... instead of trying to import all the Objects into a clean database open the existing database (hold the Shift Key down) and export the Objects out to a clean file. Do this on a copy of the database!
 

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
Hi,
Thanks GinaWhipp.
I tried what you suggested but only a few forms were exported and the rest gives the same error.
 

GinaWhipp

AWF VIP
Local time
Today, 14:54
Joined
Jun 21, 2011
Messages
5,901
Then those Forms have some sort of corruption. Have you tried exporting them one at a time AND can you open them in Design mode?
 

lana

Registered User.
Local time
Today, 23:24
Joined
Feb 10, 2010
Messages
92
Yes I tried but again it gives the same error.

Every form opens in design mode and the program works fine but I can not export the forms, modules.. / only queries are exported without any error.
 

GinaWhipp

AWF VIP
Local time
Today, 14:54
Joined
Jun 21, 2011
Messages
5,901
Well, can't think of anything else to try... If you find a way to zip up the file and upload we could then give it another try.
 

Users who are viewing this thread

Top Bottom