My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.
A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.
Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.
Anyone have any experience with doing things like this?
Approach 1
elect into a new table then drop the old table.
Approach 2: Keep the old "standard" import database and delete from the standard table, then select into it from the new database (that is, instead of renaming the existing object, just select into the desired destination object) then delete/archive the random-named database.
In the past I would have used DAO and the tabledefs collection therein to rename the table, but that is rather old-school these days. No guarantee that it would work.
|||Thanks, Dylan. I may give the DAO a try just for giggles, because the alternative is (for now) each client having their own copy of a relatively complex package.
No comments:
Post a Comment