Colby, John
JColby at dispec.com
Thu Aug 12 14:23:50 CDT 2004
ROTFL. I'm here. And yes, write a class, which you will then put in your framework. Use SysVars to turn it on and off. If you have any questions what I am referring to go to: www.colbyconsulting.com Click C2DbFW3G. Start reading the lectures on classes and frameworks. Speaking of classes, one of the places where classes make a lot of sense is in building data structures that know how to do things. I am building a system to export (insurance) data to a mainframe. The mainframe apparently already has a system for importing data and I have to match that. The data is in a fixed width format, records of about 3000 characters, horribly denormalized. The client company gave me a field map. something like: LuminexFldName FldStart FldEnd Length LX-POLICY-GROUP 1 20 20 LX-TYPE-COV(1) OCCURS 16 TIMES 25 27 3 LX-TYPE-PLAN(1) OCCURS 16 TIMES 73 92 20 LX-EFFDATE 449 456 8 LX-LASTNAME 457 471 15 LX-FRSTNAME 472 486 15 LX-MI 487 487 1 LX-ADDR1 488 517 30 LX-CITY 518 537 20 LX-STATE 538 539 2 LX-ZIP 540 548 9 LX-GENDER 549 549 1 LX-DOB 550 557 8 LX-SSN 558 566 9 LX-SALARY 1551 1561 11 LX-ADDR2 1612 1636 25 LX-COUNTRY 1657 1664 8 LX-INITIAL-EFFDATE 1825 1832 8 LX-FILLER2 2397 2799 403 LX-END-CHAR 2800 2800 1 The field map included a TON more fields, but these mapped to fields in our database where they wanted to get data from us. So I Imported the entire field map into a table inside of Access. I then added additional fields to hold our matching field name as well as the "format" that they wanted the data field output, if it is a date it has to be "yyyymmdd", if currency it has to be "XXXXX.YYYY". I wrote a query to pull all of the records for fields where they want data. This allows us to add / subtract fields exported just by filling in our field name. If our field name exists in the table then they want that data. Since they tell me where in this 3000 character string each piece of data should be placed I have the tools to build an export system. I wanted to have it "table driven", i.e. if the client said they wanted new data in "their field Y" then I just add that data to my query and place my query field alias name in their field map table and it would export the next time the report ran. I built a class that holds one record from tblFldMap. I built a "supervisor" class that opens the select query that pulls all the "active" records from the field map, and loads an instance of clsFldMap passing in their field name, our field name, the start and start positions and the field length and the format. I save each instance of clsFldMap in a collection, keyed on our field name. clsFldMap then knows where in the string the data goes, how long it's data has to be, and the format that it has to get the data into before placing it into the string. I built a DataInsert class method that formats the data, pads it to the correct length, and inserts it into a string passed in. The supervisor then opens the recordset that pulls our data out, builds a big 3000 character string of spaces, looks up the clsFldMap in the collection using the field name it is processing, and calls the DataInsert method passing the data to insert and the string to place the field in. The supervisor iterates through all the fields in the query, for each record in the query, passing the data and the string, over and over, for each field in our data. By the end of processing a single record the supervisor now has a 3000 byte string with data embedded in it in various places. The supervisor now writes that string to a text file on the disk. Next record to export. Continue until EOF recordset. The point here is that by using classes and breaking the labor down into logical units, the system just works and expanding the system is just a matter of adding (or deleting) a field to a query that pulls the data to export, and adding my query's field name into the client's field map. The clsFldMap knows how to format the data it is given, how to pad it to get it to the right length, and how to drop it into the big string. The clsSupervisor knows how to load the set of clsFldMap into a collection, open the data query, process each field of the data query, passing the data and the output string to the clsFldMap, and when all fields are processed, write that string to a disk file. BTW, 635 records each with 28 fields, each record inserted into a 3000 character string with about 296 possible fields, written to a disk file of approximately 1.3 mbytes takes about 3 seconds on my 2.4g desktop system. BTW2, Classes are an extraordinary tool for breaking what appear to be complex processes down into manageable pieces, placing the processing close to the data and building the system back up as modules that each do their own part. JWC -----Original Message----- From: Arthur Fuller [mailto:artful at rogers.com] Sent: Thursday, August 12, 2004 11:57 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Instant If Then Else Any second JC will respond with a message suggesting that you use a class for this! Write it once, use it everywhere. JC, where are you? A.