TransferSpreadsheet: Out of Disk Space/System Resource Exceeded

[arctic_blizzard]

Hi all - I have an error in an Access app I am trying to develop that I can't seem to solve. Your help would be greatly appreciated.

The application simply pushes up data from a spreadsheet to a temp table in SQL Server (via linked tables in Access). The upload utilizes the DoCmd.TransferSpreadsheet method, which I have used successfully in another project (it is even the same code I am using now).

The issue: During TransferSpreadsheet, the data is being loaded into memory, I can see the progress bar on the bottom of the screen tick up. When it gets about 3/4 of the way full I get an error message saying "Your computer is out of disk space" and asking if I want to append the records. Clicking yes returns Error 3035 "System Resources Exceeded".

File specs:

• Size: 1.62MB
• Rowcount: 13,488

Here is what I have already looked into and implemented with no change in the error:

• Machine has plenty of space left
• Tried multiple machines
• Tried multiple versions of Access (2013 32-bit, 2016 64-bit)
• Tried loading by doing TransferText (using converted csv file)
• Tried repair/compact of the Access app
• Used regedit to add "WorkingSetSleep" to 1 after doing some research

I did try multiple files, and I was able to upload the same file cut down to 7,000 records. So it seems to be the record count that Access is having trouble with. I've done some other research and hit a brick wall. Has anyone hit this error before and solved it? I could break the file apart in code and load a little bit at a time, but would love to avoid that.

The code:

For Each varFile In .SelectedItems
            On Error GoTo CleanFail
                'CSV load file to temp db location
                'DoCmd.TransferText acImportDelim, , "dbo_Funds4", varFile, True
                
                'EXCEL load file to temp db location
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "dbo_Funds4", varFile, True
                
                'add to file count
                fileCount = fileCount + 1
                'check dailydata table and see if the day already exists
                Dim dailyCount As Long
                DoCmd.OpenQuery ("qryDailyCheck")
                dailyCount = DCount("*", "qryDailyCheck")
                DoCmd.Close acQuery, "qryDailyCheck"


                'if exists, notify user that data will be overwritten
                If dailyCount <> 0 Then
                    Dim Response
                    Response = MsgBox("Data for this day already exists, overwrite?", vbYesNo, "Overwrite Data?")
                    'if user agrees, run query
                    If Response = 6 Then
                        SaveDailyData
                    Else 'user skips
                        fileCount = fileCount - 1
                        'delete data from temp table (so it doesn't get added on the next run)
                        DoCmd.RunSQL ("DELETE FROM dbo_Funds4")
                        'DoCmd.OpenQuery ("qryDeleteTempData")
                    End If
                Else
                    'Daily data table has no conflicts, save data
                    SaveDailyData
                End If
                        
                'TODO: sql return error reporting
         Next

Thanks so much!

[CJ_London]

I did try multiple files, and I was able to upload the same file cut down to 7,000 records

did you load the whole file this way,? or just test the principle?

I suspect you have in your spreadsheet data an error of some sort which is causing access to fall over. So check your excel source - it might be that somewhere you have some text where a number is expected or an illegal character (from a database perspective) for example.

Manually link to your spreadsheet and check all fields for #error or similar. Try to sort the linked table - it may fail if there is an error.

Other options include manually importing the data (not copy/paste) then check for import errors - blank fields where there should be none

A few years ago I had to write a routine to import data from 15 excel files, in total around 1m rows - the error was a semi colon instead of a dot in a decimal number

[arctic_blizzard]

The failure is happening somewhere between 9950 and 9975 records. I brought it back to 9950 records and copied 25 known good records (processed before) and I got the failure. The columns are all set up as nullable varchar(MAX) in the database (not my design), so it should be able to take anything.

[John_G]

I suspect it is the size of your MS Access database file that is being exceeded - check its file size. Even though (in theory) the data is just being passed through Access to the SQL Server table, it is possible (even likely) that Access requires a lot of temporary space in the database file to perform the task.

Did you try a compact and repair before the import?

[arctic_blizzard]

Current file size is ~3.5MB. Not sure if I can see if its size bloats under operation.

I did do a compact/repair.

[CJ_London]

I brought it back to 9950 records and copied 25 known good records (processed before) and I got the failure.

that implies there is a problem with one or more of the fields in one of the 25 records.

[arctic_blizzard]

that implies there is a problem with one or more of the fields in one of the 25 records.

I would have thought so too, but in a subsequent file I replaced those 25 records with a copy of records 1-25 from the same file (which are known to load just fine). With those replacements I still got the same error.

[CJ_London]

regret I am out of ideas

[arctic_blizzard]

regret I am out of ideas

Not a problem - thanks for the help. I am currently looking into altering the Windows paging file size. One of the last things I dug up.

[CJ_London]

Just had one idea, rather than importing the spreadsheet to a table, then exporting to sql, why not just link to the spreadsheet and export from the linked table?

[isladogs]

I've experienced similar errors though not when exporting Excel files to SQLServer via Access.
My experience was mainly related to importing and processing very large and badly structured JSON files
So the following may or may not be relevant - try them & see if either or both help

1. Increase MaxLocksPerFile - see https://www.access-programmers.co.uk...axlocksperfile
2. Increase MaxBufferSize - see https://www.access-programmers.co.uk...d.php?t=298749