import from excel file to database problems with memo fields (1 Viewer)

[megatronixs]

Hi all,
I created a macro to import from excel file into a table. It works great, but only till the point of having the excel cell with a lot of text and not getting it correct into the table.
It seems the command to import from excel is from access 2000 and it was never updated after that. The excel I try to import from, has 4 fields with text and these will not have always the same amount of text and some of the fields in access just get cut because of it.
How can it be fixed that it will always import all text into the table?

This is the code I have so far:

Private Sub cmdImportFile_Click()
Dim dbs As DAO.Database
Dim strFileName As String
Dim strFile As String
Dim iRowCount As Integer
Dim iCaseCount As Integer
Set dbs = CurrentDb
 
If Len(Me.txtFilePath.Value & vbNullString) = 0 Then
    MsgBox "Choose file for import!", vbCritical
    Exit Sub
End If
strFileName = Me.txtFilePath.Value
On Error GoTo Err_cmdImportFile_Click
If Right$(strFileName, 1) <> "\" And Len(strFileName) > 0 Then
        strFile = Split(strFileName, "\")(UBound(Split(strFileName, "\")))
End If
DoCmd.SetWarnings False
 
DoCmd.TransferSpreadsheet acImport, , "tblImportedCases", strFileName, True

CurrentDb.Execute "Query1"
'iCaseCount = dbs.RecordsAffected
'
' 'Format([Date_uploaded],"dd/mm/yy")
'
dbs.Execute "DELETE * FROM tblImportedCases"
'iRowCount = dbs.RecordsAffected
'
DoCmd.SetWarnings True
'
DoCmd.Close acForm, "frmImportCases"
'
'MsgBox "You have successfully imported " & iCaseCount & " Cases!" & vbCrLf & _
'        "Import has identified " & iRowCount - iCaseCount & " double counts which where not imported!", vbInformation
Exit_cmdImportFile_Click:
    Exit Sub
Err_cmdImportFile_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportFile_Click
    
End Sub

Greetings.

 

[sneuberg]

Are the fields being truncated to 255 characters as discussed in this thread?

 

[megatronixs]

Hi,
Yes, the fields are being truncated :-(

Greetings.