Splitting Database - error messages

[Rich J]

I am trying to split an Access 2000 database that was converted from 97. I
have tried several different scenarios but keep getting two error messages.
First - ' subscript out of range ' after pressing 'OK' I get
Second - ' invalid call or argument '
The splitting function does create the _be.mbd file prior to the error msg's.

My database has 35 forms, 33 queries, 32 tables and 6 reports so far. Is
this exceeding the ability of the table splitting function ?
Not sure if the first error message is from that or a VB routine that the
program doesn't like.
I think I can manually split the tables but this will be very time consuming
as I make changes.

Thanks

 

[Allen Browne]

The size of your database is not the problem.

The "subscript out of range" error typically represents a problem with the
wizards. For general guidance with solving wiz. problems, see:
http://support.microsoft.com/?id=303769

It is also possible that there is a corruption in the database. Here's a
standard sequence for dealing with that:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[Ken Snell \(MVP\)]

The wizard that does the splitting may need repair. But splitting a database
is actually very easy to do manually.

1) Make a backup copy of your original database.
2) Create a new ACCESS database.
3) From that new database, use File | Gete External Data | Import to import
the tables with data from the original database.
4) Close the new database.
5) Open the original database.
6) Delete all the tables from the original database.
7) Use File | Get External Data | Link to link to all the tables in the new
database.

You're now done splitting the database.

 

[Rich J]

Thank you both for your suggestions. I will sort through all that in
morning. There may very well be some corruption because it also won't convert
it to an .mde file.
I'll let you know how all that works. New computer installed next week so
clean version of ACCESS will be available.

 

[Rich J]

I'm still having trouble with the split but I think ACCESS program is
corrupt. I will wait for new computer next week. I understand what Ken said
about splitting manually if I have to go that route.
The decompile worked great though and now am able to convert to .mde so I
can distribute the database once I make a few more improvements.

Thanks again.

 

[Rich J]

As a follow up: The decompile worked well. Thanks Allen. I was having big
problems because of not being able to split the database. Doing it manually
lost some functionality that I had to restore each time.
I finally imported all the objects in my database to a blank one. The wizard
was then able to split the new database.
After I brought the pre-split file home to work on and try again I got the
same error messages again.
I redid the process on my work computer and it's working fine again. Access
at home must be causing the problem.

 

[Allen Browne]

Good news.

Rich, if you are using different versions of Access (say 2000 at work and
2003 at home), you should decompile whenever you switch versions, especially
going from 2003 to 2000.

Each version of Access uses a different binary for the compiled code.
Explicitly decompiling and then compacting discards the unusable binary, and
avoids a stack of apparently senseless errors that you can otherwise
experience.