If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DB splitter error
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. |
#2
|
|||
|
|||
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. "Newbie" wrote: 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. |
#3
|
|||
|
|||
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Newbie" wrote in message news 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. |
#4
|
|||
|
|||
On Mon, 13 Dec 2004 22:51:01 -0800, Newbie
wrote: 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 |
#5
|
|||
|
|||
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? "Allen Browne" wrote: 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Newbie" wrote in message news 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. |
#6
|
|||
|
|||
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? "John Vinson" wrote: On Mon, 13 Dec 2004 22:51:01 -0800, Newbie wrote: 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 |
#7
|
|||
|
|||
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Newbie" wrote in message ... 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? "Allen Browne" wrote: 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. "Newbie" wrote in message news 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. |
#8
|
|||
|
|||
On Tue, 14 Dec 2004 00:07:04 -0800, Newbie
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange error messages in reinstalled Office | Hans Näslund | General Discussions | 2 | September 22nd, 2004 08:32 PM |
Error message different in MDB and MDE | david epsom dot com dot au | General Discussion | 1 | September 21st, 2004 12:47 AM |
Help, i'm gettiing error: Access has encountered a problem and needs to close... | betsy | General Discussion | 0 | September 14th, 2004 08:20 PM |
Expression - calculating running total | Kathy | Running & Setting Up Queries | 26 | June 22nd, 2004 10:14 PM |
Custom Error Messages | DMc2004 | Database Design | 4 | June 11th, 2004 11:16 PM |