troubleshooting Question

Importing text string > 255 characters

Avatar of SteveL13
SteveL13Flag for United States of America asked on
Microsoft Access
3 Comments1 Solution104 ViewsLast Modified:
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 , ""
ASKER CERTIFIED SOLUTION
Bill Bach
President and Btrieve Guru

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 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 1 Answer and 3 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