troubleshooting Question

Using MS Access 2013 to move a large input .txt file with fixed length fields to a SQL Server table

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL ServerSQL
14 Comments3 Solutions257 ViewsLast Modified:
I am developing an MS Access 2013 application with a SQL Server backend for a client.  As part of the project I have to write conversion logic to transform their legacy data into the new format, resident in SQL Server tables.

I wrote and tested my conversion routines with a subset of the legacy data provided by the client.  Everything worked fine.
The client is supplying the data as .txt file.  Within the txt file the fields are fixed length.  I created Import Specs for each data set being converted and then imported each file to be converted using this construct.
Public Sub importThisOne(passedImportFIle As String, _
                         passedImportTable As String, _
                         passedImportSpec As String, _
                Optional passedClearFileBeforeImporting As Boolean = True)
If passedClearFileBeforeImporting = True Then
    deleteString = " delete * from " & passedImportTable
    DoCmd.SetWarnings False
    DoCmd.RunSQL deleteString
    DoCmd.SetWarnings True
End If
DoCmd.TransferText acImportFixed, _
                   passedImportSpec, _
                   passedImportTable, _
                   passedImportFIle, _
End Sub
passedImportFIle – is the complete path, including the file name, to the .txt file to be imported
PassedImportTable – is the linked SQL Server table linked where the imported data will go
passedImportSpec – Name of the Import Spec for the table being imported
passedClearFileBeforeImporting -= Boolean flag.  If yes, the SQL table is cleared of data before the import

Everything worked perfectly with the subset of the data provided by the client for initial testing.  The client just provided a complete set of legacy data to test against my import and one very large input table is causing issues.

When the logic executes the above ‘Docmd.Transfer’ with the large table as input I get this error.

Import Error
My accdb is 1GB and my disk is not close to full.

It appears that I will have to change my technique for moving the large input txt files to their related SLQ table.  I think txt files can be linked to an application but I’ve never done that.

What method would you use to get this large, fixed length .txt input file to the SQL table?
Join our community to see this answer!
Unlock 3 Answers and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros