Hi,
I have excel and accsess connection through macro. When launching macro in excel he taking destination from excel going to accsess and based on destination import data to accses database agregate it and return back and refresh excel file.
I get this whole excel and accsess from last employee and now start strugling with code. We had change one of the report and additionally add one column. After that change I added one line in data table with same name as in Excel, but data not going there and at all data are wrong after we change this report (in excel).
I found that there are line in code: DoCmd.TransferText acImportDelim, "Agents_Daily_ImportSpec", "TBL_Agents_Daily", strfolder & "" & strFileName
If I undesrtand it correct there are Agent_Daily_ImportSpec table or something where are described what data from excel to accsess table should go. But problem is that I couldn't find this table or settings anywhere. Maybe you have ideas where it could be of course if I understood code correct?
That's the whole part for this import action:
Case "Agents Daily"
'Check if file has already been imported
If DCount("Filename", "TBL_Agents_Daily_ImportedFiles", "Filename=""" & strFileName & """") = 0 Then
DoCmd.TransferText acImportDelim, "Agents_Daily_ImportSpec", "TBL_Agents_Daily", strfolder & "" & strFileName
CurrentDb.Execute "INSERT INTO TBL_Agents_Daily_ImportedFiles (Filename) " & vbCrLf & _
"VALUES('" & strFileName & "')"
strDateFile = Replace(strFileName, "Agents_Daily_", "")
strDateFile = Replace(strDateFile, ".csv", "")
dt_datefile = Mid(strDateFile, 5, 2) & "/" & Right(strDateFile, 2) & "/" & Left(strDateFile, 4)
SQLquery = "UPDATE TBL_Agents_Daily SET [Start - Date]=#" & dt_datefile & "# WHERE [Start - Date]IS NULL"
CurrentDb.Execute SQLquery
icounter_new = icounter_new + 1
strLastFileImported = strFileName
strLastFileImportedDate = Format(Now, "dd/MM/yyyy")
Else
icounter_existing = icounter_existing + 1
End If
'Debug.Print strFileName