The two fields that make FullFilePath are both text and set to 255 chars.įyi: The joins below are setup that way (inefficient) because of the possibility that different customers could send the same SeptemberClaims.xls file. It is derived in the query that sets up the recordset (near the top of code and pasted below.) The field "FullFilePath" is not in a table. Fields("FileDate") & "#" & " WHERE ((FilePath) Is Null) " 'Debug.Print "Update complete" End If. Fields("FileSize") & ", FileTabName='NoTab', FileDate=#" &. Fields("FileDate") & "#" & " WHERE ((FilePath) Is Null) " CurrentDb.Execute "UPDATE tblImport SET FilePath='" &. csv 'Debug.Print "Import Completed" 'Debug.Print "Updt Statement: " & "UPDATE tblImport SET FilePath='" &. Fields("FullFilePath"), False 'This is setup for. Fields("FullFilePath") ' import the text file to the table DoCmd.TransferText acImportDelim, "ImportSpec1", "tblImport". Fields("FileDate") & _ "#" & " WHERE FilePath Is Null " ' else, if this is not an Excel file, Else 'Debug.Print "Else" 'Debug.Print "Path to import: " &. Fields("FileSize") & _ ", FileTabName=""" &. Fields("FileTabName") CurrentDb.Execute "UPDATE tblImportXLS SET FilePath='" &. Fields("FileName") If LCase(Right(.Fields("FileName"), 3)) = "xls" Or LCase(Right(.Fields("FileName"), 4)) = "xlsx" Then ' import the this worksheet to the table DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportXLS". EOF ' if this is an Excel file, 'Debug.Print "Record selected " &. Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly Do While Not. LVBarnes Public Function ImportFiles() Dim strSQL As String Dim rst As New ADODB.Recordset strSQL = "SELECT tblImportClaims.FilePath, tblImportClaims.FileName, tblImportClaims.FilePath+tblImportClaims.FileName AS FullFilePath, tblImportClaims.FileTabName, tblImportClaims.FileDate, tblImportClaims.FileSize, tblImportClaims.Imported FROM tblImportClaims INNER JOIN tblImportClaimFilesLocal ON (tblImportClaims.FileSize = tblImportClaimFilesLocal.FileSize) AND (tblImportClaims.FileDate = tblImportClaimFilesLocal.FileDate) AND (tblImportClaims.FileName = tblImportClaimFilesLocal.FileName) AND (tblImportClaims.FilePath = tblImportClaimFilesLocal.FilePath) WHERE (((tblImportClaims.OkToImport)=1) AND ((tblImportClaims.Imported)=0)) " 'Debug.Print "rst statement " & strSQL With rst. I shortened the filenames above by removing spaces and was able to get them imported.but setup this question to find out what the limits were. The full file path for the mapped drive portion is another 39 characters (which would be additional to the character count below). The user filepaths are very long (working on changing that.) Here are some sample paths.
0 Comments
Leave a Reply. |