Manual DB splitting (1 Viewer)

[kodiak385]

So I've got another topic which has been practically beaten to death, but again, I'm having trouble finding my answer for the same reason. I've read up a bit on the advantages of splitting a database for multi-user environments, and while I'm a little nervous about it (having never tried it before), I'm pretty sure that's what I want to do.

The problem I'm running into is when I try to use Access's database splitting wizard, I get an error saying "Subscript out of range," followed immediately by "Invalid Procedure Call or Argument." I've compiled all of my VB and compacted/repaired the database just to be safe, and I'm still having the same issue. I don't know how else to eliminate this error, so I'm thinking in order to split my DB, I'm going to have to do it manually. Given this, I want to check first on here (best. resource. EVER. ) and get a little input before I try to proceed.

If I'm understanding correctly, all it REALLY takes to split a database is to make a copy of it to be the Back End, delete all queries/forms/reports/code, etc. in that DB, delete the tables in the original DB (the new Front End), and link the BE tables to the FE, basing all forms and reports on local queries. Is this right? Is there anything else to keep in mind when splitting the DB?

As more of an afterthought than anything, is there anything the Access DB splitting wizard does that you can't do manually? I just want to make sure I'm not missing something before I move forward tearing my DB a proverbial "new hole." hehe

Any input would be greatly appreciated here. Thanks in advance!

 

[RuralGuy]

You've described the splitting process to a T.

 

[Mike375]

I make a copy of the data base and put it on one of the computers.

Then I delete the tables that have changing data. In other words tables that supply drop down lists or tables that might have data like insurance rates and are only changed at odd times, I leave.

Then go to Get Externa Data and then Link.

Once done, I make a copy of the "linked db" and put it on the other computers. There is no referencing in the link to where the linked db resides.

Depending on your set up it can be better to leave the forms, queries, modules, reports and macros on the "home db". You might have some queries that are long running and these will run quicker on the non linked data base and especially if you have your network running on wireless. I think it is also better to have a data base that is complete and with frequent copies made.

 

[ChrisO]

I would not advise splitting it manually simply in order to overcome a problem.
Try to fix the problem again… decompile, import all objects into a new container, blah blah blah.
When you have it working correctly then split it manually.

 

[RuralGuy]

Chris has a good point. Have you perhaps applied the Office SP3 and not applied the HotFix that corrected many of the problems caused by the SP?

 

[kodiak385]

Ooooookay. First of all, thank you for the replies, and for confirming that I'm on the right track as far as my splitting process goes.

As for Chris's suggestion, I've jumped through all the necessary hoops to get Office up to date, and still have the same issue. I'm very much a beginner with VB (writing what I can, when I can), and I fear I'd have to rebuild every form and report in the database one piece at a time until I can isolate the error in order to resolve this problem. Database design and administration isn't even CLOSE to being in my job description, but it needs to be done nonetheless, so I'm having to do what I can whenever I can to get it done. Meanwhile, it's expected that this database is up and fully functional for multiple users, NOW. Joy.

Anyway, this leaves me with two questions: First, does anyone have any other ideas on how to fix this error w/o rebuilding every object in the database, and second, at this point, is it going to HURT anything if I move forward splitting the DB?

Thanks again to you guys for helping with this.

 

[kodiak385]

Follow up time. I MAY have found a workaround, but again, I'd like some advice on whether this is just a surface fix or an actual solution.

Chris, you suggested that I "import all objects into a new container, blah blah blah." Correct me if I'm wrong, but I interpreted this as saying I should create a blank database and import to it all tables/queries/forms/etc. (as I understand it, these are all referred to generically as "objects" in Access, but I'm new enough to this that I don't want to go assuming I know exactly what you mean just yet). Anyway, I took your advice (I think, haha) and created a new DB, importing all objects from my other database (paying extra attention to my blah blah blah ). I had no trouble splitting the new DB, and it SEEMS to work just fine, but I'm nervous that I may be overlooking something not immediately obvious. Something that will give me trouble later, after I've already built additional heaps upon it. I'm not a fan of this variety of "something."

The only thing that I can immediately see is different is the startup option that called up my switchboard form, but that's easily fixed. Is there anything else I'm missing?

 

[RuralGuy]

The "Import" fix you employed can often fix some types of corruption and is always worth trying since it only takes a few minutes. It is safe to assume that you are now good to go!

 

[kodiak385]

Woohoo!

Weird how that stuff works sometimes. It's a challenge learning a lot of this stuff as it is, w/o Access going screwy and randomly corrupting things, lol. Anyway, I'll go ahead and move forward getting this thing split now. Thanks a ton for your help, guys.

 

[gemma-the-husky]

just one point

Then I delete the tables that have changing data. In other words tables that supply drop down lists or tables that might have data like insurance rates and are only changed at odd times, I leave.

SOUNDS REALLY DANGEROUS

you ought to move ALL your tables with volatile data into the backend. If you have lookup tables as you describe, and you amend them, you will have to find a way of reissuing these tables to all your users. Even more dangerous, two users could change their own copies of rates, and you wont know which is correct. One solution is to download these tables into your own dbs, as part of your startup - so you dont have to keep referring to them from the server, and not be able to edit them in your local copy

Having said that, I feel that the app may not work as quick if the server doesnt have all the lookup tables - if you are filtering based on a lookup table, and the server cant filter the record set, becaose the lookup table isnt on it, you will get more network traffic, and it will slow your system down.

is that right, guys?

 

[kodiak385]

just one point
SOUNDS REALLY DANGEROUS

you ought to move ALL your tables with volatile data into the backend. If you have lookup tables as you describe, and you amend them, you will have to find a way of reissuing these tables to all your users.

I thought the same thing, but I wasn't gonna argue. The only table I intend to leave in the front end is the switchboard items, as that should only really change when the front end needs to be upgraded anyway. As far as lookup tables go, they may not change often, but when they do, I don't want to redistribute an entire new front end for every user accessing the database.

Thanks for your input, gemma

 

[gemma-the-husky]

kodiak

you asked whether importing everything DID actually import everything. On the first screen is an options button, which determines whether you import relationships, file impex specs etc. You need to grab those as well. but assuming you did you got the lot

you only have to set the startup parameters as you discovered

 

[kodiak385]

Yeah, I found out the hard way that it doesn't import relationships, lol... I think I needed to take another look at them anyway. Good to know there's at least a way to do it for the future.

I'm not sure it affects me in this case, but out of curiosity, what do you mean by file impex specs? This is new to me.

 

[Mike375]

just one point



SOUNDS REALLY DANGEROUS

you ought to move ALL your tables with volatile data into the backend. If you have lookup tables as you describe, and you amend them, you will have to find a way of reissuing these tables to all your users. Even more dangerous, two users could change their own copies of rates, and you wont know which is correct. One solution is to download these tables into your own dbs, as part of your startup - so you dont have to keep referring to them from the server, and not be able to edit them in your local copy

Having said that, I feel that the app may not work as quick if the server doesnt have all the lookup tables - if you are filtering based on a lookup table, and the server cant filter the record set, becaose the lookup table isnt on it, you will get more network traffic, and it will slow your system down.

is that right, guys?

I have found my db work faster with less tables being linked. It is most noticeable on the computers that are hooked to the router by wireless.

The way the rates/presentations are done is completely self contained on each computer. In other words I could unhook the computer from the network. However, "the results" go into a linked table. When the rates change it is a fairly big deal to do it (they come from insurance company) and then I just export the table (s) but that is about .001% of the time to update the rates.

As a side note I have seen quite a few posts here that say Access should not be linked with wireless. I have had no problem and do get quite a few drop outs over the course of each week but it only stops the completion of a macro etc. I had an Access devoper company work on this data base because of difficulties converting A95 to A2003 but they never mentioned anything about wireless, although I did not ask them as I knew nothing of the problem at that stage. I have been using wireless for several years.

 

[MailMan]

I just encountered the "Subscript out of Range" error. It ended up being a relationship to a link in another database. When I deleted the relationship, the database split no problem. Once it split, I reestablished the relationship in the FE.