DB splitter error

[Newbie]

I have database splitter errors (subscript out of range and invalid procedure
call) and have found similar problems in the newsgroup, tried the solutions
but failed. So I want to split the database manually so that I can bypass the
problems. Am I recommended to do so? I am going to do the following:

1. Copy the database. The new file will be served as the back-end database.
2. In the back-end database, I will remove all objects except tables.
3. In the original database, I will remove all table objects and establish
links for them.

Thank in advance.

 

[Newbie]

I have already done the plan below. But I found that the ado object no longer
support the index function. It worked before. The only change is that the
underlying table becomes a linked table. Is that true a limitation of using
ADO with linked table? If so, I think the approach of splitting Access is not
practical.

Am I correct? Please comment.

 

[Allen Browne]

No problem. It's so easy (and much more flexible) to split the database
yourself that I don't think I've ever used the wizard.

The process you outlined works fine, though I prefer to create the front end
as the new mdb. That helps solve corrupted code which is common during
development. Additionally, using the original version as the back end save
you having to redo the Relationships diagram.

Typical steps:

1. Make sure Name AutoCorrect boxes are unchecked under:
Tools | Options | General.
Explanation:
http://members.iinet.net.au/~allenbrowne/bug-03.html

2. If you have been doing lots of development, decompile a copy of 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"

3. Compact:
Tools | Database Utilities | Compact.

4. Make a copy of the mdb file to use as the back end.
Delete everything except the tables from this copy.

5. Create a new database to use as the front end.
Turn off Name AutoCorrect, and set just the references you need.
Attach the tables from the new back end:
File | Get External | Link
Import any tables you want in the front end (e.g. zip code lookups).
Import all the other objects:
File | Get External | Import.
Set minimal references.
Check that the code compiles.

Notes:
- Make sure you link or import all tables in the front end before you import
the queries.

- If you are unsure what references you need, see:
http://members.iinet.net.au/~allenbrowne/ser-38.html

- Personally, I develop most of the database unsplit: tables, queries, form,
reports, and code. Then split once things are all in place, before the
testing, debugging and documenting stages. I find this is easiest if there
are any changes needed to the tables as you develop.

 

[John Vinson]

I have database splitter errors (subscript out of range and invalid procedure
call) and have found similar problems in the newsgroup, tried the solutions
but failed. So I want to split the database manually so that I can bypass the
problems. Am I recommended to do so? I am going to do the following:

1. Copy the database. The new file will be served as the back-end database.
2. In the back-end database, I will remove all objects except tables.
3. In the original database, I will remove all table objects and establish
links for them.

Thank in advance.

Sounds good. Compact both databases when you finish. Be sure that you
have correctly established Relationships between the tables in the
backend before linking.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Newbie]

Allen, thank you very much. For the linked table problem, should I create a
connection object to the back-end database directly for any recordsets that
require index function in the vba code?

 

[Newbie]

John, thank you very much. For the linked table problem, should I create a
connection object to the back-end database directly for any recordsets that
require index function in the vba code?

 

[Allen Browne]

If you have a procedure that needs to use an Index, you can OpenDatabase()
directly on the back-end file.

In practice, though, it is usually easier to create a SQL string that
returns only the record(s) you need, just the field(s) you need, and use the
ORDER BY clause to get them sorted correctly. The coding and maintenance are
much easier than messing with the index, so unless you are opening and
closing the thing repeatedly in a loop (not a good design), the performance
loss is not significant.

 

[John Vinson]

John, thank you very much. For the linked table problem, should I create a
connection object to the back-end database directly for any recordsets that
require index function in the vba code?

I usually don't bother: just File.... Get External Data... Link. I do
have VBA code from the Access Developer's Handbook to relink the
backend if the tables are missing or misplaced.

There's generally no need to worry about indexing; the indexes (and
relationships) exist in the backend, and JET will use them correctly.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps