Application Design Questions

[grndpa]

I think I'm giving my Microsoft 2003 Access application a migraine headache.

I wonder (1) if "import" is a better option than "link", (2) how to handle the ADODB.Recordsheets and SQL "Opens" efficiently, and (3)whether I should re-package my single .mdb module into multiple .mdb modules.

The client is converting from one third-party payroll system to another vendor. The source system provides a reasonably normalized workbook containing tabs for each source table. The target system requires a variety of (ugh) denormalized .csv files which I must derive from multiple tabs from the source workbook. I also have translation tables for, say, when the value for a given "military status code" on the source system is different than the target system requires.

My current approach is to create a single .mdb containing a class to create an Access 2003 table for each target file (One class dedicated to creating one output table) . I then export these tables to a spreadsheet (to retain the column headings) and then to a ".csv". I have also created a table for capturing errors.

I link to the individual tabs from the source workbook. And the same for my conversion spreadsheets. I have also created a number of "helper" classes to cleanse, reformat and perform other standard dirty deeds on my input fields.

For the ADODB.recordsets and open, since I don't really understand them, I've taken two approaches in my "helper" classes. In the first ones I wrote, I instantiate a new recordset every time I call the function. This, I believe, is probably exactly wrong (though I do use the same instance name and it doesn't seem to mind). The other approach that I'm using is to instantiate the recordset in the caller class and pass it "ByRef" to the helper class that needs it. (This offends my encapsulation sensibilities). A third option that I tried but didn't work (I suspect a skill deficiency here) was to open the ADODB.recordset in the helper class constructor.

In any case, every time I add a new link, the performance degrades noticably.

How would you approach this? Imports vs. Links, instantiate recordsets within the function or "by ref", keep in one .mdb load or break into multiple .mdbs -- if the latter is there a way to share my helper classes among .mdbs?

Whew!

Grandpa Brian

 

[lameid]

From what I remember from looking at another thread, this is more or less a one time migration. That being said, I would import tables as they will be faster native. But I would probably choose links in a repeatable exercise unless you hit a performance wall. Also native tables are easily manipulated.

As far as changing one value to another, I would use a simple table with the changed value as a FK to map them. Then Join them in a new query (proably an outer join) to get the desired results. You could then export the query or use it as a source for your code if you really need something that complex.

Also, I believe docmd.transfertext has a parameter to export column headings.

I find it hard to believe that you can't accomplish a data migration with little more than a series of queries (action and select).

 

[grndpa]

I find it hard to believe that you can't accomplish a data migration with little more than a series of queries (action and select)

Probably true, but "When the only tool you know is a hammer, ... (every problem is a nail)". Still almost every field has some operation I must inflict -- changing size, type, value conversions, field or full record elimination depending on criteria.

Thanks for your suggestions.

 

[lameid]

Changing type is easy so long it is an Implicit Conversion. Simply changing the type in the table design view.

As for the rest of it, everything can be handled by some sort of query and is really the easiest way to go.

You might not delete data but rather use a query with criteria for just the records you want.

It looks like you are approaching this from a VB project perspective. You should think of an MDB as a project. Everything should be in it unless there is a very good reason not to. Commonly in Access people split the data and application objects. But in a single user environment, the only advantatge is it is less likely to corrupt data and objects at the same time but is also rare in single user usage. I would just keep to one file and keep regular backups.

Finally, passing a recordset byref... I guess it depends on what you are doing with it. Editing the current record? Then sure, Byref is fine and of course you save overhead. At the same time there are bound to be situations where it is not recommended. I have this intangible sense that there are situations where it would be very bad but none come to mind.