I’m hacking away at our database at work, attempting to automate the daily data imports we use to bring orders into our order management system. We have three distinct data sources for each merchant identity, and of course these three sources employ diferring schema. I have chosen not to worry about normalizing the data at import in any significant way, rather storing the data in one flat-file table with fieldnames that segment the unique datasource elements. This makes it very easy to pull out the original source data in the format it was passed to us.

The associated merchant IDs are not written into the export files; this, determining the associated merchant ID is implicit and cannot be established by testing the data structure.

What I want to do is automate the import process such that my operator svaes the daily downloads in a given appropriate folder. Currently we have been saving them in files tagged with the data provider’s name and the date. I beleive I will change this to incorporate the data provider, merchant ID, and date shortly.

However, MS Access does not natively support automating data import with a dynamic file name – the Text Transfer function requires a hardcoded path and file. I think I could set up macros that useTextTransfers that import from a fixed-location file and that then create a new exported file incorporating additional data such as date of import and merchant ID, but again I find myself bumping up agains the fixed-file-name issue in Text Transfer.

I’m sure there must be a way to use VB to construct the filepath from user input selected via drop-downs, but I’m not there yet. Argh!