We help IT Professionals succeed at work.
Get Started

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

Last Modified: 2018-01-15
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

Open in new window

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?
Watch Question
Distinguished Expert 2017
This problem has been solved!
Unlock 3 Answers and 14 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE