Splitting a database

[jon]

Hi
I have a data base that is now about 25 mb, 6 years old (this has also where
I have learnt so there could be beginners errors in abundance in it) and
used by several people at the same time, at the moment it is in access 97
but we are upgrading to 2003 shortly when all the permissions have been
sorted.
When I run the splitter in 97 it goes so far then brings up a "Subscript out
of range" and then the error is "An error occurred while copying
relationships" after that its back to the start.
I have converted a copy to 2000 and 2003 format and get the same error.

Any ideas or advise or what out information do you need?
Thanks

Jon

 

[Jeff Boyce]

Jon

Make a backup copy (or 2 ... or 3!) if you haven't already.

Open a copy of the A'97 database. Open a code module. Run the
Debug/Re-compile to see if all of the parts are working correctly in the
A'97 version. I suspect something isn't quite right.

After the '97 version compiles, use the Compact & Repair feature. Maybe
twice!

Try opening THAT copy with Access 2003 and converting.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Agree with Jeff about the debugging.

Another way to approach this would be to open 2003 and create a new
database. Then use the File - Get External Data - Import menu option and
import all of the objects from 97 into 2003.

Probably the biggest difference between 97 and 2003 is that 2003 uses ADO as
the default data access method, not DAO. Once you have your new database in
2003, open the VBA window (Ctrl-G) and select the Tools-References and
select the Microsoft DAO 3.6 (actually it may be > 3.6 with the latest
service pack) object library from the list. Once it is checked, use the
priority buttons to move it above the ActiveX Data Objects (or you could
just uncheck the ADO object library)

If you keep both the DAO and ADO libraries, then you will need to
disambiguate your codes (so Access does not have to assume what you mean).
Because both DAO and ADO have similar object libraries, you need to make
sure that your code explicitly defines which library you want to use when
you declare certain object types. Search your code for any instances where
you dimensioned a database, recordset, or querydef, and change the code so
it reads simiilar to the following:

Instead of: Dim db as database
Use: Dim db as DAO.database

Instead of: Dim rs as Recordset
Use: Dim rs as DAO.Recordset

HTH
Dale