file sharing lock count exeeded, increase maxlocksperfile registry

[pam0566]

I am trying to take an old, sad out of shape datea base, with about 98k records, [on access 2003] and clean it up using table analyzer. I keep getting this error "file sharing lock count exeeded, increase maxlocksperfile registry". I found the instructions to do this, and changed the registry to 1000000-- but keep getting the same error. this data base needs to be cleaned up bad! but it wont let me. i am trying to create the query that will allow all my reports forms etc to work, since i use this daily. Anyone have an idea of how to fix this?

 

[khawar]

What are you trying to do may be there is a simple alternative to that

 

[pam0566]

i am trying to use table analyzer to split one large table into some smaller tables for efficiency and to speed up the data base. the data base tables have grown over the years as business has, and not in the most efficient manner.

 

[khawar]

Why dont you use an append query which has no such limit

 

[pam0566]

because i dont want to just add records, but split 1 table into a few seperate tables.

 

[EraserveAP]

I run into this problem when trying to add an Autonumber field to large tables in table design view. My method to get around that was to use vba and a form to add my Autonumber field. Perhaps you could clean your table as you desire through vba instead of the analyzer?

 

[khawar]

Even if you want to split table stil it could be done through queries
If you tell me in detail I can help you

 

[EraserveAP]

Even if you want to split table stil it could be done through queries
If you tell me in detail I can help you

Quite true and undoubtedly easier than using VBA.

 

[pam0566]

ok i have tried splitting tables individually, instead of all i want to do at once, most work, but one of them isnt. how can i split using queries, and will it split the data into 2 tables? so the program runs faster? i am not sure exactly what you mean.

 

[EraserveAP]

I would use a Make Table query with the table you want split. It should have an ID field so you can tie the new tables together.
Place your fields and the ID field in the Make table query and run it. Then repeat the process until you have all of your fields duplicated into the new tables you need.

 

[EraserveAP]

Mine may not be the best solution, but I do not see why it would not work.

 

[khawar]

Can you tell in detail what are the fields in your table and on what basis you want to split the table and any other details which are helpful in solving your problem

 

[pam0566]

i have a table that is called "vehicleinfo"/ in that i have some fields that i want to split off from the vehicle info table. they include sold, sold date, seller, sold notes. i want to make 2 talbes. I can use the table analyser to split one by one diff tables for other items, but for some reason when i try that on my sold data i only get part of my records. the orig table still has them all , but my record count for a period goes from 52 to 28.., is there a better way to split the data? I am not opposed to vba, but in this case using table analyzer think i would lose the same data anyway-- so would rather find a way to doit where i keep my records that are sold--

 

[pam0566]

ok, after some more research via google and the forum, i have raised some other questions. My table is a vehicle info table, including vehicle make, model, year, submodel, miles, color, blue book value, and also including [for ex] sold (y/n), sold date, sold notes, uw (y/n), uw date, uw notes, uw reason, RTS (take out of system a Y/N), RTS date, rts notes, Consignment (y/n), consignment date, consigment completed (y/n).. you get the gist. Anyway, what i was tryin to do was split the orig table to include one for vehicle info, and split the SOLD / RTS/ UW/ CONSIGMENT (and maybe few other 2-3 field areas that are related. My question is, should i even do this, since the info in each new table relates to one car? i thought maybe the data base would run faster. i am going to split my DB into FE and BE, and am in teh process of creating a new DB to pull my records into, but figured i should split the records first. Will splitting to all these tables make my DB Run Faster? should i even bother with this? or keep all in 1 table. Selling Dealer is a sep table / so is the selling agent. But those tables include unique info like addresses etc, so i know they need to be seperate. Advice appreciated as to weather or not splittin this big table will improve speed or efficiency! Thanks!