Hi all,

I am opening a database and simpy trying to alter the size of a text
column. The database I am testing with has about 160,000 records in
it. This code has worked successfully on other databases, but with
less records.

I open it like this:
Set cnData = New adodb.Connection
cnData.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
DataDBLocation & ";Jet OLEDB:Database"

And alter the table like this:
cnData.Execute "alter table MeasurementsT alter column Measurement2
char(250)"

When working with the 160,000 record table, I get the error message:
File sharing lock count exceeded. Increase MaxLocksPerFile registry

Now, I can certainly edit my own registry and increase this value, but
it would be very difficult and time consuming for me to do this on all
of my clients' computers across the country.

Is there some setting in my code that I can set so that they do not
get this error?

I'm using an Access 2002 database by the way.

Thanks in advance,

David

You can change the current session's setting in vba code without permanently
changing the registry setting.

DBEngine.SetOption dbMaxLocksPerFile, 200000

If 200000 isn't enough, try 300000 or even higher. Set it back to the normal
9500 when you're done with that table operation so you get good performance
with any other queries.

Chris
Microsoft MVP


quickemr_groups@yahoo.com wrote:
>Hi all,
>
>I am opening a database and simpy trying to alter the size of a text
>column. The database I am testing with has about 160,000 records in
>it. This code has worked successfully on other databases, but with
>less records.
>
>I open it like this:
>Set cnData = New adodb.Connection
>cnData.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
>DataDBLocation & ";Jet OLEDB:Database"
>
>And alter the table like this:
>cnData.Execute "alter table MeasurementsT alter column Measurement2
>char(250)"
>
>When working with the 160,000 record table, I get the error message:
>File sharing lock count exceeded. Increase MaxLocksPerFile registry
>
>Now, I can certainly edit my own registry and increase this value, but
>it would be very difficult and time consuming for me to do this on all
>of my clients' computers across the country.
>
>Is there some setting in my code that I can set so that they do not
>get this error?
>
>I'm using an Access 2002 database by the way.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Using VB6, tried the suggestion starting at 50,000 then tried
100,000 , 200,000 , and finally 1,000,000. Still get the error.

-David

Back up the db because you're going to make some changes. Your next choice
is to rename the original table to something else, then copy the table
structure as a new table with the original table's name but make the
Measurement2 column 250 chars. Append the records from the original table
into the new table. Copy all relationships from the original table to the
new table and delete the original table (and its relationships if you have
track name autocorrect turned on).

Chris
Microsoft MVP


quickemr_groups@yahoo.com wrote:
>Using VB6, tried the suggestion starting at 50,000 then tried
>100,000 , 200,000 , and finally 1,000,000. Still get the error.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Is there an easy way to do that programmatically? I can't do that one
each of my clients databases. It would take me weeks!

You're paying the penalty for making a structural data mistake at the
beginning and fixing it long after the game already started. Why should that
be easy to fix? Haven't you heard of the software development life cycle?
Bugs fixed early in the life cycle are simpler, quicker and cheaper to fix
than bugs found later in the life cycle.

It's not going to take you weeks to fix one column in one table for every
customer, it's just going to seem that long.

Chris
Microsoft MVP


quickemr_groups@yahoo.com wrote:
>Is there an easy way to do that programmatically? I can't do that one
>each of my clients databases. It would take me weeks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

It won't take you that long, really. Try to code it and if you need help,
post a new question in the modules vba dao group.

I'd suggest building a quick utility db app to back up the customer's db,
import the empty table with the right structure into it (transferdatabase
method), append the records from the original table, add the relationships,
then delete the original table and relationships.

Chris
Microsoft MVP


quickemr_groups@yahoo.com wrote:
>Is there an easy way to do that programmatically? I can't do that one
>each of my clients databases. It would take me weeks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Thanks for the help Chris. I was just hoping there was some setting I
could use in my code that would prevent that error from happening. I
read about the "DBEngine.SetOption dbMaxLocksPerFile" and was hoping
there was another alternative since it didn't seem to be working for
me.

-David

You can try bumping it up to 2000000 but I honestly don't think that will
help. You have so many records in that table that the transaction is filling
up the 2GB temp db file before it can complete the column change.

Chris
Microsoft MVP


quickemr_groups@yahoo.com wrote:
>Thanks for the help Chris. I was just hoping there was some setting I
>could use in my code that would prevent that error from happening. I
>read about the "DBEngine.SetOption dbMaxLocksPerFile" and was hoping
>there was another alternative since it didn't seem to be working for
>me.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

If I manally edit the registry key and change it to 50,000, it works!
The setting from VB6 does not seem to do the trick, however.