Changing data type causes error

[Guest]

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.

 

[Guest]

Hi.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to.

It appears that you do need to increase your MaxFileLocks setting because of
the number of records in the table. However, this doesn't need to be a
permanent setting, so you aren't required to change the Windows Registry
(unless this is a common occurrence, and then you'd want to).

One may change the setting for the current session by changing the Jet
DBEngine setting. To do so, open the Immediate Window by opening the VB
Editor and typing <CTRL><G>. Type the following in the Immediate Window,
then press <ENTER>:

DAO.DBEngine.SetOption dbmaxlocksperfile, 15000

This setting is only temporary, so don't worry. Try running the query and
if you get the same error message, then type this into the Immediate Window
again, but this time increase the number. You may need to do some
experimenting to find how many locks is enough to handle this transaction.

If you'd like instructions on the Window Registry method to make it
permanent, then please see the following Web page:

http://support.microsoft.com/default.aspx?id=815281

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.

 

[Guest]

Thanks,

By chance, do you know if there is an ADO equivalent to this?

Hi.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to.

It appears that you do need to increase your MaxFileLocks setting because of
the number of records in the table. However, this doesn't need to be a
permanent setting, so you aren't required to change the Windows Registry
(unless this is a common occurrence, and then you'd want to).

One may change the setting for the current session by changing the Jet
DBEngine setting. To do so, open the Immediate Window by opening the VB
Editor and typing <CTRL><G>. Type the following in the Immediate Window,
then press <ENTER>:

DAO.DBEngine.SetOption dbmaxlocksperfile, 15000

This setting is only temporary, so don't worry. Try running the query and
if you get the same error message, then type this into the Immediate Window
again, but this time increase the number. You may need to do some
experimenting to find how many locks is enough to handle this transaction.

If you'd like instructions on the Window Registry method to make it
permanent, then please see the following Web page:

http://support.microsoft.com/default.aspx?id=815281

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.

 

[Guest]

Hi.

By chance, do you know if there is an ADO equivalent to this?

There is no equivalent in ADO. ADO is a generic library to handle
connections, data sets, record locking and transactions. It wasn't designed
for Jet. It was never meant to handle any particular database's settings,
for that matter.

Since this is a one-time thing, set a Reference to the DAO 3.6 Object
Library but place it below the ADO 2.x Object Library in the hierarchy, close
the References dialog window, and compile the database code. Run the code I
suggested earlier in the Immediate Window as many times as you need to during
your experimentation. When you are finished, get rid of the DAO Reference,
close the References dialog window, and compile the database code again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Thanks,

By chance, do you know if there is an ADO equivalent to this?

Hi.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to.

It appears that you do need to increase your MaxFileLocks setting because of
the number of records in the table. However, this doesn't need to be a
permanent setting, so you aren't required to change the Windows Registry
(unless this is a common occurrence, and then you'd want to).

One may change the setting for the current session by changing the Jet
DBEngine setting. To do so, open the Immediate Window by opening the VB
Editor and typing <CTRL><G>. Type the following in the Immediate Window,
then press <ENTER>:

DAO.DBEngine.SetOption dbmaxlocksperfile, 15000

This setting is only temporary, so don't worry. Try running the query and
if you get the same error message, then type this into the Immediate Window
again, but this time increase the number. You may need to do some
experimenting to find how many locks is enough to handle this transaction.

If you'd like instructions on the Window Registry method to make it
permanent, then please see the following Web page:

http://support.microsoft.com/default.aspx?id=815281

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.

 

[Guest]

Actually, I kept doubling the value up to 1,000,000,000 and I still get the
same error, from 15,000 on...

Hi.

By chance, do you know if there is an ADO equivalent to this?

There is no equivalent in ADO. ADO is a generic library to handle
connections, data sets, record locking and transactions. It wasn't designed
for Jet. It was never meant to handle any particular database's settings,
for that matter.

Since this is a one-time thing, set a Reference to the DAO 3.6 Object
Library but place it below the ADO 2.x Object Library in the hierarchy, close
the References dialog window, and compile the database code. Run the code I
suggested earlier in the Immediate Window as many times as you need to during
your experimentation. When you are finished, get rid of the DAO Reference,
close the References dialog window, and compile the database code again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Thanks,

By chance, do you know if there is an ADO equivalent to this?

Hi.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to.

It appears that you do need to increase your MaxFileLocks setting because of
the number of records in the table. However, this doesn't need to be a
permanent setting, so you aren't required to change the Windows Registry
(unless this is a common occurrence, and then you'd want to).

One may change the setting for the current session by changing the Jet
DBEngine setting. To do so, open the Immediate Window by opening the VB
Editor and typing <CTRL><G>. Type the following in the Immediate Window,
then press <ENTER>:

DAO.DBEngine.SetOption dbmaxlocksperfile, 15000

This setting is only temporary, so don't worry. Try running the query and
if you get the same error message, then type this into the Immediate Window
again, but this time increase the number. You may need to do some
experimenting to find how many locks is enough to handle this transaction.

If you'd like instructions on the Window Registry method to make it
permanent, then please see the following Web page:

http://support.microsoft.com/default.aspx?id=815281

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.

 

[Guest]

Hi.

Actually, I kept doubling the value up to 1,000,000,000 and I still get the
same error, from 15,000 on...

You may be hitting the ceiling on the size of the table or the size of the
database and no amount of file locks will allow you to proceed. If creating
a duplicate table in your database wouldn't put you over the size limit (2 GB
for Jet 4.0), then I'd recommend creating a copy of your present table (data
structure only), then altering the new table's text field into a double data
type. Append the records in the old table into the new table with an append
query. (Use the CDbl( ) function on the text field so that it's stored
correctly in the new table.) Rename the original table to something else,
and then rename the new table as the original table.

Of course, if you have relationships established between the original table
and any other tables, then you'll have to drop the relationships and then
reestablish them with the new table after the records have been added. And
if the database would exceed 2 GB with the additional table, then put the new
table in a different database, append to it, and import it back to the
original database as soon as the original is deleted.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Actually, I kept doubling the value up to 1,000,000,000 and I still get the
same error, from 15,000 on...

Hi.

By chance, do you know if there is an ADO equivalent to this?

There is no equivalent in ADO. ADO is a generic library to handle
connections, data sets, record locking and transactions. It wasn't designed
for Jet. It was never meant to handle any particular database's settings,
for that matter.

Since this is a one-time thing, set a Reference to the DAO 3.6 Object
Library but place it below the ADO 2.x Object Library in the hierarchy, close
the References dialog window, and compile the database code. Run the code I
suggested earlier in the Immediate Window as many times as you need to during
your experimentation. When you are finished, get rid of the DAO Reference,
close the References dialog window, and compile the database code again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Thanks,

By chance, do you know if there is an ADO equivalent to this?

:

Hi.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to.

It appears that you do need to increase your MaxFileLocks setting because of
the number of records in the table. However, this doesn't need to be a
permanent setting, so you aren't required to change the Windows Registry
(unless this is a common occurrence, and then you'd want to).

One may change the setting for the current session by changing the Jet
DBEngine setting. To do so, open the Immediate Window by opening the VB
Editor and typing <CTRL><G>. Type the following in the Immediate Window,
then press <ENTER>:

DAO.DBEngine.SetOption dbmaxlocksperfile, 15000

This setting is only temporary, so don't worry. Try running the query and
if you get the same error message, then type this into the Immediate Window
again, but this time increase the number. You may need to do some
experimenting to find how many locks is enough to handle this transaction.

If you'd like instructions on the Window Registry method to make it
permanent, then please see the following Web page:

http://support.microsoft.com/default.aspx?id=815281

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.

 

[Guest]

Thanks again, I will give this a try.

Hi.

Actually, I kept doubling the value up to 1,000,000,000 and I still get the
same error, from 15,000 on...

You may be hitting the ceiling on the size of the table or the size of the
database and no amount of file locks will allow you to proceed. If creating
a duplicate table in your database wouldn't put you over the size limit (2 GB
for Jet 4.0), then I'd recommend creating a copy of your present table (data
structure only), then altering the new table's text field into a double data
type. Append the records in the old table into the new table with an append
query. (Use the CDbl( ) function on the text field so that it's stored
correctly in the new table.) Rename the original table to something else,
and then rename the new table as the original table.

Of course, if you have relationships established between the original table
and any other tables, then you'll have to drop the relationships and then
reestablish them with the new table after the records have been added. And
if the database would exceed 2 GB with the additional table, then put the new
table in a different database, append to it, and import it back to the
original database as soon as the original is deleted.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Actually, I kept doubling the value up to 1,000,000,000 and I still get the
same error, from 15,000 on...

Hi.

By chance, do you know if there is an ADO equivalent to this?

There is no equivalent in ADO. ADO is a generic library to handle
connections, data sets, record locking and transactions. It wasn't designed
for Jet. It was never meant to handle any particular database's settings,
for that matter.

Since this is a one-time thing, set a Reference to the DAO 3.6 Object
Library but place it below the ADO 2.x Object Library in the hierarchy, close
the References dialog window, and compile the database code. Run the code I
suggested earlier in the Immediate Window as many times as you need to during
your experimentation. When you are finished, get rid of the DAO Reference,
close the References dialog window, and compile the database code again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Thanks,

By chance, do you know if there is an ADO equivalent to this?

:

Hi.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to.

It appears that you do need to increase your MaxFileLocks setting because of
the number of records in the table. However, this doesn't need to be a
permanent setting, so you aren't required to change the Windows Registry
(unless this is a common occurrence, and then you'd want to).

One may change the setting for the current session by changing the Jet
DBEngine setting. To do so, open the Immediate Window by opening the VB
Editor and typing <CTRL><G>. Type the following in the Immediate Window,
then press <ENTER>:

DAO.DBEngine.SetOption dbmaxlocksperfile, 15000

This setting is only temporary, so don't worry. Try running the query and
if you get the same error message, then type this into the Immediate Window
again, but this time increase the number. You may need to do some
experimenting to find how many locks is enough to handle this transaction.

If you'd like instructions on the Window Registry method to make it
permanent, then please see the following Web page:

http://support.microsoft.com/default.aspx?id=815281

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

I am using Office 2003 on Windows XP.

I am attempting to alter the data type of a single field in a stand-alone DB
that only has one table. The DB is in a private directory where only I have
access to it. The table is populated with 148,449 records. My SQL is:

ALTER TABLE [tablename] ALTER COLUMN [fieldname] Double;

Currently the field type is TEXT. When I run this SQL (from MS-Excel), I get:

-2147467259 (80004005)
File sharing lock count exceeded. Increase MaxLocksPerFile registry.

When I run the query in MS-Access, it runs for a while, then just hangs.

I really don't want to alter my registry settings (if that is what this is
saying) unless I really need to. Can someone tell me what is going on and how
to fix or work-around this?

Thanks much in advance.