vba 运行时错误“3052”。超出文件共享锁计数。增加 MaxLocksPerFile 注册表项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26542653/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run time error '3052'. File sharing lock count exceeded. Increase MaxLocksPerFile registry entry
提问by Dane I
I've been working on this database for a while now and have become stuck with a couple issues I am having with the database, this being one of them.
我已经在这个数据库上工作了一段时间，并且遇到了我在数据库中遇到的几个问题，这就是其中之一。
This code transfers a table into excel, putting each 1,000,000 records on a separate sheet. The current table I am attempting to transfer has just under 1.5 millions records and 7 fields.
此代码将一个表传输到 excel，将每 1,000,000 条记录放在单独的工作表上。我试图传输的当前表只有不到 150 万条记录和 7 个字段。
The coding works fine until it hits the Alter Table SQL. At which point it spits out this error. I have already increased the dbMaxLocksPerFile to 20 million, and this hasn't helped and I am stumped.
编码工作正常，直到遇到 Alter Table SQL。此时它会吐出这个错误。我已经将 dbMaxLocksPerFile 增加到 2000 万，但这并没有帮助，我很难过。
Any help I could get on this would be amazing :)
我能得到的任何帮助都会很棒:)
FYI This is the first lot of VBA programming I've ever done, and am self-taught (google taught), so my set out and such may be a bit messy. The code is below:
仅供参考，这是我做过的第一批 VBA 编程，并且是自学的（谷歌教的），所以我的出发点可能有点混乱。代码如下：
Private Sub EXPORT_TO_EXCEL_Click()
DoCmd.SetWarnings False
DAO.DBEngine.SetOption dbMaxLocksPerFile, 20000000 'That's 20 million!!!
'DTable is the file name, and is input by the user in earlier coding under a public string
Call CreateNewFolder("O:\Folder Location\" & DTable & "")
Dim strWorksheetPathTable As String
'----Set File Path
strWorksheetPathTable = "O:\Folder Location"
strWorksheetPathTable = strWorksheetPathTable & "" & DTable & "\" & DTable & ".xlsb"
'----SPLIT DATA TABLE IN ACCESS THEN EXPORT THESE SMALLER TABLES (Splits if over 1,000,000 records)
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Dim rowcount As Long
Dim tblcount As Integer
Dim i As Integer
Dim tblx As String
Dim dbsDatas As DAO.Database
Set dbsDatas = CurrentDb
SQL = "SELECT * INTO tmpdata FROM [" & DTable & "]"
DoCmd.RunSQL SQL
SQL = "ALTER TABLE tmpdata ADD COLUMN id COUNTER"
DoCmd.RunSQL SQL
SQL = "SELECT count(*) as rowcount from [" & DTable & "]"
rs.Open SQL, cn
rowcount = rs!rowcount
rs.Close
tblcount = rowcount / 1000000 + 1
For i = 1 To tblcount
SQL = "SELECT * into tmpdata" & i & " FROM tmpdata" & _
" WHERE id<=1000000*" & i
DoCmd.RunSQL SQL
SQL = "DELETE * FROM tmpdata" & _
" WHERE id<=1000000*" & i
DoCmd.RunSQL SQL
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel12, _
TableName:="tmpdata" & i & "", FileName:=strWorksheetPathTable, _
hasfieldnames:=True, _
Range:="Data" & i & ""
DoCmd.DeleteObject acTable, "tmpdata" & i & ""
Next i
DoCmd.DeleteObject acTable, "tmpdata"
DoCmd.SetWarnings True
MsgBox ("Report saved at the following location: " & strWorksheetPathTable & "")
End Sub
采纳答案by Dane I
I'm unsure if anyone will find this helpful, but my method of getting around this was to copy the table to a txtfile and then copy it from here 1,000,000 records at a time into separate excel sheets.
我不确定是否有人会觉得这有帮助，但我解决这个问题的方法是将表复制到txt文件中，然后从此处一次将 1,000,000 条记录复制到单独的 Excel 表中。
EXPORT TO TXT
导出到 TXT
Private Sub EXPORT_TO_TEXT_FILE_Click()
Dim txtFile As String, rs As DAO.Recordset, j As Integer, strFld As String, strData As String
txtFile = "O:\GData\Downstream\DWN Data Mgmt\CEDAL\Reports\" & NewFileName & ".txt"
Set rs = CurrentDb.OpenRecordset("" & NewFileName & "")
For j = 0 To rs.Fields.Count - 1
strFld = strFld & vbTab & rs(j).Name
Next
Open txtFile For Output As #1
Print #1, Mid(strFld, 2)
Do Until rs.EOF
For j = 0 To rs.Fields.Count - 1
strData = strData & vbTab & rs(j)
Next
Print #1, Mid(strData, 2)
strData = ""
rs.MoveNext
Loop
rs.Close
Close #1
TRANSFER TO WORKBOOK
转移到工作簿
Private Sub Build_Data_Sheets_Click()
Dim txtSplitTextFiles As String
txtSplitTextFiles = "O:\Gorgon Data\Downstream_LNG POC\DWN Data Mgmt\CEDA Lite\Reports\" & NewFileName & ".txt""
Dim strWorksheetPathTable As String
strWorksheetPathTable = "O:\GData\Downstream\DWN Data Mgmt\CEDAL\Reports\" & NewFileName & "..xls"
Const LINES_PER_SHEET As Long = 1000000
Dim ResultStr As String
Dim FileName As String
Dim FileNum
Dim Counter As Long, r As Long
Dim arr()
FileNum = FreeFile()
Open txtSplitTextFiles For Input As #FileNum
Counter = 0
r = 0
ReDim arr(1 To LINES_PER_SHEET, 1 To 1)
Do While Not EOF(FileNum)
Counter = Counter + 1
r = r + 1
Line Input #FileNum, ResultStr
arr(r, 1) = ResultStr
If r = LINES_PER_SHEET Then
ArrayToSheet xlWB, arr
r = 0
End If
Loop
If Counter Mod LINES_PER_SHEET > 0 Then ArrayToSheet xlWB, arr
Close #FileNum
ARRAY TO SHEET SUB "CALLED"
数组到工作表子“调用”
Sub ArrayToSheet(wb As Workbook, ByRef arr)
Dim r As Long
r = UBound(arr, 1)
With wb.Sheets.Add(after:=wb.Sheets(wb.Sheets.Count))
.Range("A1").Resize(r, 1).Value = arr
End With
ReDim arr(1 To r, 1 To 1)
End Sub
回答by Suman kumar
i hope you got the answer, but you can try the below steps also
我希望你得到了答案，但你也可以尝试以下步骤
- Open empty access application.
- Select File >> Open>> Browse and select the Database file.
- Click dropdown on Open button in the browse window.
- Select "Open Exclusive" option.
- 打开空访问应用程序。
- 选择文件>>打开>>浏览并选择数据库文件。
- 单击浏览窗口中打开按钮的下拉菜单。
- 选择“打开独占”选项。
The database file will be opened in unlocked state. Now execute the script, it should work without any error.
数据库文件将以解锁状态打开。现在执行脚本，它应该可以正常工作而不会出现任何错误。
回答by John
Answer is here:
答案在这里：
http://www.anysitesupport.com/access-maxlocksperfile-file-sharing-lock-count-exceeded/
http://www.anysitesupport.com/access-maxlocksperfile-file-sharing-lock-count-exceeded/
Actually looking at it closer, this is a better answer for me
实际上仔细看，这对我来说是一个更好的答案
http://support2.microsoft.com/kb/815281
http://support2.microsoft.com/kb/815281
put this code in your script: DAO.DBEngine.SetOption dbmaxlocksperfile,15000
将此代码放入您的脚本中：DAO.DBEngine.SetOption dbmaxlocksperfile,15000
But then set back to 9500 after, apparently it is important
但是后来又设置回9500之后，显然很重要
