I have a legacy workflow that I would like to optimize and automate (at least partially). The current workflow goes like this. I receive a MS Access .mdb file containing a single table. This table is then loaded into an an existing Access db that contained some macros ,which I will reference later, and renamed BTMUA. To the table BTMUA several fields are manually inserted into the middle of the table. Additionally two fields data types are changed from text to date to more accurately reflect the data they contain. Once these changes have been completed a series of queries, via two macros, are run to parse, trim, and concatenate data from the original fields into the new fields. Specifically Field A id parsed into fields A1 A2 using a left and right functions, likewise field B is parsed into field B1 and B2. Any whitespaces are removed from fields A1, A2, B1, and B2 fields are removed. Finally fields A1, A2, B1, B2, and C are concatenated in one of 7 different ways depending on which fields actually contain data. Here ends the data modification and my use of Access.
I then use FME Desktop load this data into an existing table in my MS Sql based SDE GDB (after first truncating the table).
I would like to redesign this workflow and remove Access from the equation although I cannot change the fact that the data will originate in a .mdb file. I would also like to automate the process as much as possible so it can run either with a few mouse clicks or on a fixed schedule.
Basically I am trying to figure out if it is best to use FME to load the data from the .mdb into my GDB and use either ArcCatalog and Model Builder or MS SQL Server management Studio an T-SQL to transform the data. Or is it more efficient to utilize FME Desktop to do the data trans formations.