Increase MaxLocksPerFile registry

R

Rod

I get a:

Run-time error '3052' File sharing lock count exceeded. Increase
MaxLocksPerFile registry entry

when I rung this code:

Private Sub btnFixDNC_Click()
'Determine & assign the proper number AREA_CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPrefix As String
Dim strNumber As String
Dim strData As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT RecOrder, DNCData, Number " & "FROM
tblDNCRawData ORDER BY RecOrder")
With rs
.MoveFirst
Do Until .EOF
strData = .Fields("DNCData")
'If Len(strData) = 10 Then
If Len(strData) = 14 Then
strPrefix = Mid(strData, 2, 3)
'strNumber = strData
strNumber = strPrefix & Mid(strData, 7, 3) & Mid(strData, 11, 4)
Else
'strNumber = strPrefix & strData
strNumber = strPrefix & Left(strData, 3) & Mid(strData, 5, 4)
End If
.Edit
.Fields("Number") = strNumber
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

'Replace the old tblDNC data with the newly formatted data from
tblDNCRawData
CurrentDb.Execute "delete * from tblDNC", dbFailOnError
CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select Number from
tblDNCRawData", dbFailOnError
End Sub

Can someone please explain what this means and show me how to resolve it?

Thanks in advance.

Rod
 
D

Douglas J. Steele

Not sure why you'd be getting that error message, but be aware that using a
recordset in VBA to change data on all records is seldom (if ever) as
efficient as using a query.

Just create a function to format your phone number and use that function in
an Update query.

Incidentally, you're using strPrefix even when strData isn't 14 characters
long. How do you know that it's a legitimate value?
 
R

Rod

This code was given to me as a way to assign area codes to data that looks
like this:

(217) 111-1111
111-1112
111-1113
(224) 111-1000
111-1101
(312) 111-2000
111-2001

and format it so it looks like:

2171111111
2171111112
2171111113
2241111000
2241111101
3121112000
3121112001

It is usually about 75000 records.

I'm not sure if I answered your question given that I was dropped into this
problem.
 
D

Douglas J. Steele

Does sorting by RecOrder return the data correctly? If not, there's
unfortunately nothing you can do...

If it does, then your code should work. Do you have other queries, forms or
reports open when you try running the code?

One thing you might try is renaming the field from Number. Number is a
reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square brackets
around it in your Insert statement:

CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select [Number] from
tblDNCRawData", dbFailOnError
 
R

Rod

Yes, the sorting works fine.

No, this is just a button.

I'll look into the field name change. Any thought on code to resolve the
initial error?

Douglas J. Steele said:
Does sorting by RecOrder return the data correctly? If not, there's
unfortunately nothing you can do...

If it does, then your code should work. Do you have other queries, forms or
reports open when you try running the code?

One thing you might try is renaming the field from Number. Number is a
reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square brackets
around it in your Insert statement:

CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select [Number] from
tblDNCRawData", dbFailOnError




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rod said:
This code was given to me as a way to assign area codes to data that looks
like this:

(217) 111-1111
111-1112
111-1113
(224) 111-1000
111-1101
(312) 111-2000
111-2001

and format it so it looks like:

2171111111
2171111112
2171111113
2241111000
2241111101
3121112000
3121112001

It is usually about 75000 records.

I'm not sure if I answered your question given that I was dropped into
this
problem.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

As I said, your code looks as though it should work.

Afraid I have no other ideas.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rod said:
Yes, the sorting works fine.

No, this is just a button.

I'll look into the field name change. Any thought on code to resolve the
initial error?

Douglas J. Steele said:
Does sorting by RecOrder return the data correctly? If not, there's
unfortunately nothing you can do...

If it does, then your code should work. Do you have other queries, forms
or
reports open when you try running the code?

One thing you might try is renaming the field from Number. Number is a
reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square
brackets
around it in your Insert statement:

CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select [Number] from
tblDNCRawData", dbFailOnError




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rod said:
This code was given to me as a way to assign area codes to data that
looks
like this:

(217) 111-1111
111-1112
111-1113
(224) 111-1000
111-1101
(312) 111-2000
111-2001

and format it so it looks like:

2171111111
2171111112
2171111113
2241111000
2241111101
3121112000
3121112001

It is usually about 75000 records.

I'm not sure if I answered your question given that I was dropped into
this
problem.

:

Not sure why you'd be getting that error message, but be aware that
using
a
recordset in VBA to change data on all records is seldom (if ever) as
efficient as using a query.

Just create a function to format your phone number and use that
function
in
an Update query.

Incidentally, you're using strPrefix even when strData isn't 14
characters
long. How do you know that it's a legitimate value?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I get a:

Run-time error '3052' File sharing lock count exceeded. Increase
MaxLocksPerFile registry entry

when I rung this code:

Private Sub btnFixDNC_Click()
'Determine & assign the proper number AREA_CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPrefix As String
Dim strNumber As String
Dim strData As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT RecOrder, DNCData, Number " &
"FROM
tblDNCRawData ORDER BY RecOrder")
With rs
.MoveFirst
Do Until .EOF
strData = .Fields("DNCData")
'If Len(strData) = 10 Then
If Len(strData) = 14 Then
strPrefix = Mid(strData, 2, 3)
'strNumber = strData
strNumber = strPrefix & Mid(strData, 7, 3) &
Mid(strData,
11,
4)
Else
'strNumber = strPrefix & strData
strNumber = strPrefix & Left(strData, 3) & Mid(strData,
5,
4)
End If
.Edit
.Fields("Number") = strNumber
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

'Replace the old tblDNC data with the newly formatted data from
tblDNCRawData
CurrentDb.Execute "delete * from tblDNC", dbFailOnError
CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select Number
from
tblDNCRawData", dbFailOnError
End Sub

Can someone please explain what this means and show me how to
resolve
it?

Thanks in advance.

Rod
Click to expand...
Click to expand...
Click to expand...
 
R

Rod

I fouond the answer:

http://support.microsoft.com/kb/815281
DAO.DBEngine.SetOption dbmaxlocksperfile,100000
This temporarily sets the MaxLocksPerFile value to 100000

Thanks for your efforts Douglas.

Douglas J. Steele said:
As I said, your code looks as though it should work.

Afraid I have no other ideas.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rod said:
Yes, the sorting works fine.

No, this is just a button.

I'll look into the field name change. Any thought on code to resolve the
initial error?

Douglas J. Steele said:
Does sorting by RecOrder return the data correctly? If not, there's
unfortunately nothing you can do...

If it does, then your code should work. Do you have other queries, forms
or
reports open when you try running the code?

One thing you might try is renaming the field from Number. Number is a
reserved word, and should never be used for your own purposes. For a
comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put square
brackets
around it in your Insert statement:

CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select [Number] from
tblDNCRawData", dbFailOnError




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


This code was given to me as a way to assign area codes to data that
looks
like this:

(217) 111-1111
111-1112
111-1113
(224) 111-1000
111-1101
(312) 111-2000
111-2001

and format it so it looks like:

2171111111
2171111112
2171111113
2241111000
2241111101
3121112000
3121112001

It is usually about 75000 records.

I'm not sure if I answered your question given that I was dropped into
this
problem.

:

Not sure why you'd be getting that error message, but be aware that
using
a
recordset in VBA to change data on all records is seldom (if ever) as
efficient as using a query.

Just create a function to format your phone number and use that
function
in
an Update query.

Incidentally, you're using strPrefix even when strData isn't 14
characters
long. How do you know that it's a legitimate value?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I get a:

Run-time error '3052' File sharing lock count exceeded. Increase
MaxLocksPerFile registry entry

when I rung this code:

Private Sub btnFixDNC_Click()
'Determine & assign the proper number AREA_CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPrefix As String
Dim strNumber As String
Dim strData As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT RecOrder, DNCData, Number " &
"FROM
tblDNCRawData ORDER BY RecOrder")
With rs
.MoveFirst
Do Until .EOF
strData = .Fields("DNCData")
'If Len(strData) = 10 Then
If Len(strData) = 14 Then
strPrefix = Mid(strData, 2, 3)
'strNumber = strData
strNumber = strPrefix & Mid(strData, 7, 3) &
Mid(strData,
11,
4)
Else
'strNumber = strPrefix & strData
strNumber = strPrefix & Left(strData, 3) & Mid(strData,
5,
4)
End If
.Edit
.Fields("Number") = strNumber
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

'Replace the old tblDNC data with the newly formatted data from
tblDNCRawData
CurrentDb.Execute "delete * from tblDNC", dbFailOnError
CurrentDb.Execute "insert INTO tblDNC (DNCNumber) select Number
from
tblDNCRawData", dbFailOnError
End Sub

Can someone please explain what this means and show me how to
resolve
it?

Thanks in advance.

Rod
Click to expand...
Click to expand...
Click to expand...
 
A

AG

add the following line before Set db = CurrentDb

DBEngine.SetOption dbMaxLocksPerFile, 80000 'change the 80000 to
whatever works for you
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Status on status line 1
Get External Data2 1
Import Error 1
Not Responding when VBA code is Run 2
Edit/Update results in badly fragmented file 9
System resource exceeded 1
Strange annoying problem - long chunk of code 1
Strange annoying problem - long chunk of code 2

Top