Mistery in macro code

[dimitrijus99]

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

[June7]

Agents_Daily_ImportSpec is not a table, it is an import specification - a set of parameters import wizard will use.

An import specification can be created first time an import operation is done manually. Save the specification then it will be available for future reference in subsequent imports of same source.

[dimitrijus99]

If I took this file from previous user who was creator and couldn't now contact with him, how I could find those import specification and where I could find them?

[June7]

On External Data tab there are buttons "Saved Imports" and "Saved Exports".

[dimitrijus99]

It's empty. There aren't those information about import. Export also empty. Other ideas?

[June7]

Import/Export specifications are in a hidden system table. Can query that table:

SELECT MSysIMEXSpecs.* FROM MSysIMEXSpecs;

My table shows 3 records but nothing listed in I/E dialog.

If they no longer show in the I/E dialog, I have no idea how to recover. I don't really use specifications.

I expect will have to build new spec yourself as I already described.

[dimitrijus99]

Find it. But there are just table, without column names. When I tried change column name in database it's showing me error message that system couldn't find those column name. Doe's somwhere there are described column names also?

DateDelim	DateFourDigitYear	DateLeadingZeros	DateOrder	DecimalPoint	FieldSeparator	FileType	SpecID	SpecName	SpecType	StartRow	TextDelim	TimeDelim
/	0	0	0			0	26		0	1		:
.	-1	-1	0	,	;	437	13	Agents_Daily_ImportSpec	1	1		:
.	-1	-1	0	,	;	1257	22	Info_Daily_ImportSpec	1	0		:
.	-1	-1	0	,	;	1252	10	KLAC_CDR_Daily_20200301 Import Specification	1	1	"	:
.	-1	-1	0	,	;	1252	11	KLAC_CDR_ImportSpec	1	1	"	:

[dimitrijus99]

There are also relationship between some uknown tables for me.

[dimitrijus99]

Find it!
Instead of SELECT MSysIMEXSpecs.* FROM MSysIMEXSpecs; I wrote SELECT MSysIMEXColumns.* FROM MSysIMEXColumns; and find described columns with specID. Change information there. Only one problem that in report was 54 columns and new one was added in the middle of report, so had recalculate START and WIDHT values
Thank you for help

[June7]

Congratulations for figuring that out. I learned something new too.

[dimitrijus99]

Thank you for help
Also I think this could be useful for others