Using Access for Database Conversions

[Marz]

I have decent knowledge of Access for some data conversions I've done however only used it as a stepping stone so to speak. But I am curious if I can handle everything within Access from a conversion standpoint. I am a database/conversion analysis on Mainframe systems (IBM os/400 or the I Series as they are now). I program in RPGILE and have been doing this for 17 years. Currently when I get data from a client, I use Access to cut cross references for fields in my programming. Example is a patient's religion. On whatever system they are coming from it could be spelled out as Baptist (just throwing one out there) or as BAP etc. On our system it's a 2 character code and could be BT. I use Access to query the file and select all the codes being used on their system and export those out into a new table with 2 fields, one being the code I just pulled, the 2nd just a blank text field. I typically dump these into excel as cross reference files are generally small. I think the largest will be doctor numbers as they can go into the hundreds. When they send them back I use excel to isolate the fields to upload to our development box and my programs to handle accordingly. As the code simply will take the old code, chain to the x-ref file, then take the new code and build the record in our table.



Now what I would like to know, can Access with using VBA be able to handle everything from a programming/conversion development standpoint? I am further developing my skill set with both access and Visual Basic and thought this could be a good project to learn/grow from.

Now more details, I'll throw out 1 table that is part of a conversion. There are many and most especially in dealing with Account receivables that deal in calculations updating fields in a table, and exceed millions of records which throws MS Excel out the window. A payment/adjustment or any other transaction file can easily be 2 million or more records being processed. However to play with this and develop my skill set along the way I want to start with a simple demographic file. I feel this will give me the answers I need if it can be done and the amount of time it will take to process it in relation to my programs on the os/400.

So with that said, and hope I made sense I want to take a incoming table of a patient demographic record. It'll contain his/her name, address, city/state/zip, phone info, email info, family physician information, medical record/chart number, ER contact information, race, sex, religion, martial, contact relationship to patient codes (all these would need to be x-reffed by program unless query can handle), then using that information in a sense map and drop into a new table using VBA to modify patient name (as in some cases it comes first, last, middle however on our system it's last, first, middle), and using VBA to strip out any unwanted codes in address information, making everything uppercase etc.


Can Access handle this? And information on where to look to learn how? As I do have smaller files that are handle in financials that I may reach out to Access to use instead of writing RPG programs if it will be better suited.

Thanks

[orange]

Access is often used as a front end to other databases. I have used Access and its query and reporting capabilities with Oracle and MySql databases(ODBC).
With vba and some experience you can manipulate data in various ways.

I think Access can do much of what you describe, but as is always the case --the devil is in the details.
I have never written/used RPG.

Good luck.

[KathyL]

“Now what I would like to know, can Access with using VBA be able to handle everything from a programming/conversion development standpoint? “

My answer would be, “Yes”. I support an application which has several hundred each of tables, queries, forms, and reports. I have links to other Access databases and ODBC links to SQL files.

"Now more details, I'll throw out 1 table that is part of a conversion. There are many and most especially in dealing with Account receivables that deal in calculations updating fields in a table, and exceed millions of records which throws MS Excel out the window. A payment/adjustment or any other transaction file can easily be 2 million or more records being processed. However to play with this and develop my skill set along the way I want to start with a simple demographic file. I feel this will give me the answers I need if it can be done and the amount of time it will take to process it in relation to my programs on the os/400."

In years past I had tables in an Access database that were several million records. It will all depend on the total size of your database/application. Check out the limits in Access here: http://office.microsoft.com/en-us/ac...010341462.aspx

Best way to find out is to try an import of your largest table into an Access database, and begin working with it.

“Can Access handle this? And information on where to look to learn how? “

One doesn’t become an Access expert overnight. College classes, looking at examples of others, internet research, books, tutorials… are all things you could consider. Google a simple problem/question, and you’ll probably find some ideas.

Kathy/Access I.T. Consultant

[Marz]

Thank you Orange and KathyL.

Kathy, thanks for that info. Think I have a hand on what I needed. Access will not handle everything that I do. The 2 GB limit kills it as I have some tables that can easily exceed 10GB alone (historical collection notes/transactions etc). I've tried linking tables through odbc and passing variables but the processing time is horrible. Plan B was to create a front end with VB and use ADO to access the data on the i5/os-400, and call functions/programs on the system. This may work better. The company does something similar to this with Java. However this is all a self learning project for me. Something to expand on and enjoy on my free time.

Thanks again for the help.

[KathyL]

“Access will not handle everything that I do. The 2 GB limit kills it as I have some tables that can easily exceed 10GB alone (historical collection notes/transactions etc). I've tried linking tables through odbc and passing variables but the processing time is horrible. Plan B was to create a front end with VB and use ADO to access the data on the i5/os-400, and call functions/programs on the system. This may work better. “

The single fact that you connect through ODBC may not be the issue. I just went and browsed through some of my ODBC links and compared to linked tables to another Access DB, and both were tables with about a half million records, and the browsing speed was nearly identical.
What I suspect though, and what I’ve discovered, is you may not have as much control of the ODBC linked tables in terms of indexes, they’re probably set by your other application. Indexes or lack of indexes can make a big difference in speed. In my applications, sometimes I wanted to relate a ODBC table with an Access table and if there weren’t indexes on both in my linked fields, it would be slower.
So what I did, is set up some external processes in another Access DB, where I download the whole large ODBC table into an Access table, by purging my Access table first, and then appending records in. I do this at night after midnight. Since I have design control of my Access table, I set the indexes to what I want. And when I append it, it rebuilds the indexes I want. I can also select a subset of records when I download, like by a date range. Then in other parts of my application, I use my downloaded table and get the higher speed links I want. Just be careful, there also is a point where you can have too many indexes or indexes on fields which make no logical sense to index.

Always 2 ways to skin a cat.


Kathy/Access I.T. Consultant

[Dal Jeanis]

Marz - Yes, Access can handle everything, for some values of the word "everything".

It's been years since I did any RPG, but my impression is that VBA/Access is better for more complicated/subtle things, whereas RPG is more of a "Jeep". YMMV, and on the likely assumption that RPG has upgraded to an object-oriented flavor nowadays, perhaps called RPGILE, then my impressions expressed herein should be studiously ignored.

When you are dealing with fixed style records, and millions of them, the bandwidth of midrange or mainframe systems has an edge. If the records don't have a significant relationship to each other that you need to discern, then Access may not have enough of a technical advantage to make it worth the extra effort. My advice is, just try it and see.