Announcement Announcement Module
Collapse
No announcement yet.
PF updated via MS Access Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • PF updated via MS Access

    Now that Rebecca is back......... I am trying to replace some MS Access tables with AS/400 PFs and I'm interested in hearing any gotchas, been theres, or sage advice of any sort. Every step of the way has held major surprises. I am using linked tables connected via ODBC. Every field has DDS where it is defined with ALWNULL and DFT(*NULL). Autonumber functionality is being replaced with PF triggers that retrieve values from a standardized next number server (J.D. Edwards.) Given that a whole lot of work needs to be done in a little bit of time I am experimenting with using MS queries to give the fields back their old - long - Access names to lessen the number of changes. In other words, I hope to only have to find where tables are currently referenced and change the name to the new query. That part seems to be working. But I still have some headaches: 1) Access always seems to think that the fields are 'full'. I have tried every which way to trim the trailing blanks from the alpha fields without success. If I have a 5 character field with 'CATbb' (b=blank) in it, then Access doesn't want to let me key anything in the fourth position until I get rid of the invisible blanks. If I go into DBU in hex mode and change positions 4-5 to hex'00' then Access displays the field normally AND lets me begin keying in position 4. I keep thinking that other people have to have found ways around this before. 2) Is there any kind of trick that allows resequencing a linked table via Access queries? Sure, it can be done in a passthrough query but I have to assume that they aren't updatable. Plus, I fear that Microsoft's SQL won't always be the same if you just remove the semi colon from the end of the statement. 3) Is a project like this simply a pain in the beginning or does it end up creating headaches that will long outlive the conversion process?

  • #2
    PF updated via MS Access

    Tom, Let's try addressing your Pt#1 again. How about something like this:
    Sub Field5Char_GotFocus() Field5Char.SelStart = Len(TrimR(Field5Char)) Field5Char.SelLen = Len(Field5Char) - Len(TrimR(Field5Char)) End Sub 
    This should highlight the appended blanks in the field value and wipe them out as soon as you start typing. Bill

    Comment


    • #3
      PF updated via MS Access

      Bill - Thank you, sir. That does appears to work.

      Comment


      • #4
        PF updated via MS Access

        How many files are you moving and is it for an existing MS Access database? We did a lot of tables, it being a new development project, and the database is performing well, though it's still mostly in beta testing now. We left the project about two months ago, but, there are now non-AS/400 MS Access developers doing reports and user entry/maintenance forms over those linked AS/400 tables now. We accomplished this by using queries just as you describe wanting to, so yes, I highly recommend creating a query for each AS/400 PF and then restoring the field names and other formats and attributes that MS Access already knows for that information, and you should be pretty good to go. To your specific three questions: 1) We tried using null-capable fields to deal with some of the padding issues you mention, but abandoned it just because it didn't help once we had something in there (it still padded). Adding functions to fields and query cells can really bog down response time. Since we had users for this database over in Europe and response time was paramount, we couldn't take the route of trimming each before displaying them, either. Using variable length fields in your DDS, I realize now, may be another solution. But I don't have an AS/400 to play with right now, so I can't experiment! Next time (or you can try it, maybe and let us know?) In the end, we trained the users to work with those trailing blanks. In order to "empty" a non-null-capable field in one of these linked AS/400 PFs, you can type an empty string: "" (a set of double-quotes with no space between) and ENTER, which will cause the AS/400 to initialize it to blanks and allow the user to leave the field. We trained them that they cannot insert into an existing field unless they remove the trailing blanks. We trained them to tab into a field they want to replace so that it's highlighted, and then start typing, to replace what's there. Once they understood the rules, it was acceptable. Not a final solution necessarily, but still better than what we had before. We decided it wasn't a reason to stop the project, and we should move on to more core issues, revisiting this one later. (I wonder about those variable fields... ) 2) Regarding your question #2, I'm not sure I understand. Sorry. Can you rephrase and/or describe the goal of the resequencing? 3) Our database hasn't been in production long enough to know whether it'll be any more of a headache than any other system in the long run for sure. Some parts of it still aren't in production yet. We had some production output running for a full annual cycle and a significant pilot in beta that was performing very, very well when we left that project. I would do it again if I have the opportunity and a shop with the specific needs this one did, if that helps. The price was right (we already owned all the hardware and software we needed) and we had the skillset. We kept it simple: we didn't write much VB code at all and used simple SQL (via either DB2/400 LF's or MS Access queries) to do the work. A few tips off the top of my head: Keep it very simple, if response and performance are an issue. Our database had extensive links in multiple libraries on our system. We found a single data source with all the libraries was the best performance solution (vs several data sources, one for each key library). Avoid using underscores in your field names and especially file names! There is a setting for wildcard characters in the data source, but I won't get into that here. Don't use functions on fields you're bringing down from the host, if you can avoid it, and especially avoid doing so on a key field. Performance can take a hit and if you have a lot of records... Here's a really important one, during development especially: always completely relink your tables (not via the Linked Table Manager, but New, Linked, etc...) after you've made changes to the record format or some weird stuff can happen. We had some fun with library lists and trigger programs. I'm assuming you've already dealt with that. We had to make sure that all programs called by the trigger programs were in libraries defined in the data source. I'm rusty, so I hope this is going to help. How long have you been working on this thus far?

        Comment


        • #5
          PF updated via MS Access

          I have about 65 files to migrate. The goals are: 1) To consolidate a couple of similar Access databases from 2 different operating companies. 2) To put the data on the AS/400, make minimal changes to Access (hah), and have the in-house Java team rewrite the applications on the AS/400 at a later time. 3) To survive the learning curve (ok, it's only a personal goal.) Regarding my question about reseqencing (I mean sorting), when you write a query it appears that Access generates SQL. What I have seen so far is VERY similar to AS/400 SQL. I will eventually have end users who are only familiar with Access doing the reporting. As near as I can tell, Access does not like having an ORDER BY in SQL that is running against a linked table. But there is a query type called a 'Pass-Through' query that bypasses the MS Jet engine and runs directly on the AS/400. That works fine for me, but my end users will likely be using the point and click in Query Design rather than the SQL view that I use. So far the only difference I've seen in the generated SQL is that Access puts a semicolon at the end of the SQL which must be removed. I'll bet there are other differences in syntax that I just haven't discovered yet. I was wondering if your people ever had to re-sort the linked tables and had discovered some way to do it? If we just have to create a bunch of LFs, then we will. Thanks for the tips! Yes, I had a great deal of fun with the library list on the trigger program. I didn't realize that it was controlled via the ODBC data source. And the trigger program..... well I work pretty much exclusively on a package that doesn't use RPGIV - it's in RPG/400. I had to get past that hurdle too since every trigger example program I found was in IV. I never did figure out how to make the trigger program update the dummy autonumber field if someone tabs over it in Access prior to the add. I move the value into the field but the PF does not contain the value after the record is added. It seems to be related to nulls. On the data entry form I just make the default value zero and everything is fine. Just don't try entering a record in datasheet view (which is not allowed anyway.) How long, eh? Well, I lost a week of my life going to the California, then two weeks of full time futzing with a prototype. I'm one hurdle away from saying, "Yes, we can do this if you really want to."

          Comment


          • #6
            PF updated via MS Access

            Sorry I didn't get back sooner. I wanted to talk more about Access not liking to sort on a field that isn't a key field in the linked table (I think that's what you mean)... I do not currently have access (no pun intended) to an AS/400 host to refresh my memory and be detailed about what I'm saying, but I'm certain I figured out how to get around that order-by problem. One technique I adopted that proved to be very useful was doing as much of the database work as possible up on the 400 server. So, when we wanted a new sort order, we created a logical file on the AS/400 and then linked that to the Access databases to deal with what I understand your problem to be. This also greatly improved performance (especially for remote users).

            Comment

            Working...
            X