Importing text string > 255 characters

I am importing a record or records from an Excel file.  One of the fields in the file contains more than 255 characters.  When I import the file the field is truncated at stops at 255 characters.  Here is my VBA code:

    'Import Excel file into tblTempImport
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempImport", Me.FileList, True
 
    'Code to delete any import error tables
    Dim tblDef As TableDef
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.Name, "ImportError") > 0 Then
        DoCmd.SelectObject acTable, tblDef.Name, True
        'DoCmd.PrintOut
        DoCmd.DeleteObject acTable, tblDef.Name
        Beep
        'MsgBox "There was an error importing all of your records." & vbCrLf & vbLf & "An error report was sent to your default printer." & vbCrLf & vbLf & "The error report will detail the error reason for each field and row number for each record that was not successfully imported from your file." & vbCrLf & vbLf & "Please correct all errors and import your data again.", vbInformation, "Import Errors"
    End If
    Next tblDef

    'Use tblTempImport to add records to table named tblImportRecords
    Dim db As Database
    Set db = CurrentDb

    db.Execute "apndtblqryImportRecords", dbFailOnError

    'Run query to delete records from tblTempImport
    db.Execute "delqryDeleteRecordsFromtblTempAppealsImport", dbFailOnError

    MsgBox "The import has been executed."
    DoCmd.Close , ""

Open in new window

SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
What is the data type of your Access field?  A "Text" field has a maximum of 255 characters.  A complete list can be found at the end of this page:  https://www.w3schools.com/sql/sql_datatypes.asp.  You may need to change it to a MEMO field, which will work through 65535 bytes.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
It was set to Short Text.  I changed it to Long Text and it worked fine.
0
Dale FyeCommented:
steve, don't forget to close this out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.