Hi,
I'm referring to
1. https://www.accessforums.net/showthread.php?t=86392 (Import data from a specfic Worksheet from Excel) and
2. https://www.accessforums.net/showthread.php?t=86344(Import data from Excel to Access long text preserving source formatting)
I would like to incorporate the SQL update query from thread#2 (86344)...
Code:
UPDATE tblTransaction
SET tblTransaction.Comment = Replace([comment],Chr(10),Chr(13) & Chr(10))
WHERE (((tblTransaction.Comment)<>""));
...into the VBA code from thread#1 (86392)...
Code:
Private Sub btnImportData_Click()
Dim fDialog As FileDialog
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String
On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set db = CurrentDb
With fDialog
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
If .Show Then
strFileName = .SelectedItems(1)
sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
" SELECT * FROM (SELECT * FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
db.Execute sqlStr
MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"
Else
MsgBox "Import selection was cancelled."
Exit Sub
End If
End With
exitHere:
Set fDialog = Nothing
Set db = Nothing
Exit Sub
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub
...as these two procedures could logically easy be unified.
What would be the best approach? Create another variable for the UPDATE statement and place it between below?
Code:
sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
" SELECT * FROM (SELECT * FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
sqlStr1 = "UPDATE tblTransaction
SET tblTransaction.Comment = Replace([comment],Chr(10),Chr(13) & Chr(10))
WHERE (((tblTransaction.Comment)<>""))"
db.Execute sqlStr, sqlStr1
The approach would work (assuming the code is correct), but of course it would be enough to simply update the 'Comment' field for the imported records and not every time for the whole data set. Can this probably be achieved by altering the INSERT INTO statement accordingly?