Need some general thoughts, pointers, philosophies (1 Viewer)

[monkeytunes]

Hey buddays,

I recently started working for a large (!!!) company and successfully managed to switch my department over to a DB management system (using Access, since that is what I know) after years/decades of using spreadsheets.

My higher-ups have been so amazed at my parlor tricks, that they want to attempt some synergy between a number of departments. They want to remotely access a number of different data sources across a number of different databases, and they want me to manage the development process.

So, we're needing the following:

1. A VB application/function that will troll a large number of .txt files to pick up information pertaining to project demand.

2. ODBC links to 3, count 'em, 3 different ORACLE databases. These are going to be (by my decree) READ ONLY links. The different databases will tell us project information (1), supply information (2), and forecast information (3). I'm currently in the process of tracking down the administrative hoo-hah that goes along with this, i.e. who to talk to, how to get access, where the things are stored, etc.

Since I'm decent at stumbling around Access, my sups just wanted me to do everything in Access. I've explained that our IT prefers everything to be done in ORACLE (which I've NEVER used), and that Access has quirks and limitations, especially w/r/t networks and large data grabs and numbers of users, but my sups simply don't care; they like me, they know that I use Access, they know I have some (very limited) experience using SQL databases with an Access front end, so they said "just use Access".

But, after my repeated protests that we might be using a very small hammer for a BIG nail, have given me the authority to pass on VB coding/app building and ORACLE/SQL development to a lackey, who I'll manage. They're willing to let me by the project manager and pass the nitty-gritty to someone else.

My request is: Thoughts? Suggestions? Traps to watch out for? Considerations? Anything? I'd LOVE and APPRECIATE any and all discussion on something like this - I searched around a bit in the forums for something pertaining to my issue, but it's not a concrete "How do I do this?" sort of question. This is still in the germination stages, and could go a number of different ways, and as it's my first big project management opportunity, I want to cover all my bases. I've noticed Pat Hartman and The Doc Man both are knowledgeable about other DB platforms, and are spot on w/r/t using the right tool for the job and considering the nuances with the different choices available, but I'd appreciate all input. SUPER WICKED THANKS IN ADVANCE.

 

[Pat Hartman]

Seems like you've already decided not to do this with Access. I don't see anything wrong with using Access as your front end tool for this project. If the amount of data justifies it or the number of concurrent users justifies it, your tables can be Oracle. The process is transparent if you do it right. I develop all my db' with Jet tables first. Once they are complete, I relink the db to the RDBMS back end and retest everything with volumn data.

Jet attemts to make all queries against ODBC datasources pass-through queries. So the server does the bulk of the data manipulation and only the final recordset is returned to Access. You just need to be careful to not do something that will prevent Jet from passing the query to Oracle for processing.

I found Oracle to be less of a problem to work with than Sybase or SQL Server.

 

[greaseman]

Random thoughts (and guesses?)

Good luck on your endeavors!

01) Trolling for .txt files is not difficult using code. I have set up VBA code that grabs data from Excel files and checks for previous existing records, changes those records if necessary, and also adds records.

02) Links to multiple ODBC databases is relatively painless. What we've done is to set up a Reference table that contains all the repetitive info we need, and then using code, "grab and pass" the parameters needed to establish links. This re-establishes our links when needed so we don't have to worry about the links not being there.

It sounds like you're going to be having fun!! Well, you've come to the right place for info and help / advice. This forum has saved my sanity more than once!

 

[monkeytunes]

Seems like you've already decided not to do this with Access.

On the contrary; if the front end can be in Access, I'd probably prefer it, as I would be able to take a more pro-active role in development since I know a little (very little? !!!) about building front-ends for that platform.

I'm a little concerned about data-volume and performance, but if I can stay on top of users following proper Access behavior, i.e. closing the app correctly, always copying the front end to their personal hard-drives, etc., it sounds like it should be okay, since the servers would do the work. I'm wondering about grabbing data from such disparate sources though - 3 linked databases (probably from 3 different locations) and one HUGE mama (taken from trolling those text files) stored locally...you're saying Jet is up to those tasks? Because I've never put it through these kinds of paces.

The reasons I'm reluctant to do this with Access is I wasn't sure if it would be powerful enough to handle what we want, and I don't want to sever any ties with IT Support since they're all ORACLE-centric (i.e. "You want to do it that way, fine, but don't come to use for help!"). But I suppose there's nothing I could build in Access that isn't easily duplicated in C++ or VB for ORACLE/SQL, right? So if they wanted to upgrade/update down the road, they could use my work as a "blueprint" for those sorts of front-ends?

And by the way, Pat - muchos thanks!

 

[The_Doc_Man]

Some semi-random thoughts on this topic

Access as a front-end to ORACLE would work OK. ORACLE is a darned decent back-end server. {Egad, can't believe I said that about a Larry Ellison product.} So your queries in Access would only have to deal with a returned recordset that was generated on the ORACLE server, not the whole load of possibly included data that Access would have to filter and select for you.

Because the queries will be executed on the ORACLE box, the network would be far less affected than it would for an Access DB on a shared file server. So this environment is not the power hog you might fear. Also, the limit of the number of users (who can be logged on simultaneously and still be effective) is now based on ORACLE, not ACCESS, so again you aren't nearly so bad off as you might think. Our ORACLE can handle 35 interactive users and a dozen or so batch streams, no sweat. (We could handle more but on an HP Alpha, jobs don't last that long.) Design your project to let ORACLE manage all of the locking issues. Work with your ORACLE DBA to get that part right. Then turn off locking within Access itself except as required.

Unfortunately, you've got Larry Ellison AS WELL AS Bill Gates to contend with now. So the costs of adding users will stink up the place, first in the licenses for the per-seat copies of the ORACLE ODBC, and second in the number of user credits to add to each ORACLE DB Server license you pull data from. BUT... if the users ALREADY talk to ORACLE for some other purpose, and your set of users overlaps highly with the set of users already talking to ORACLE, you probably have such ODBC copies and license credits anyway. So in that case you could ride on the coat-tails of other applications for licensing and ODBC modules.

Now, as far as pulling data from .TXT files, you can do that. But if there is a way UP FRONT to design some Word-based or Excel-based FORMS that folks can just fill in for the data you wanted to capture, you can make this work even easier than trying to text-parse an arbitrary text file. I've done the latter but it is a pain in the toches. Ask a good Yiddish boy about toches if you don't know the word. Text parsing is tedious to design and requires you to consciously consider issues of finite-state automata. And let's NOT get into non-finite automata. Trust me, you really don't want to inflict that on anyone. The state diagrams get pretty nasty very quickly. If you want to do this quickly, shy away from explicitly needing to consciously apply automata theory.

See, if you can design a form in Word with some tables (boxes) to be filled in, or some forms in Excel with specific cells to be filled in, you can use Object Automation to open Word or Excel and DIRECT-ACCESS the data you want really fast (as compared to parsing). And there will be no issues in transcription or mis-parsing the input if you design it right. Just pick it up and fill a recordset with it.

As far as the size of the hammer and the size of the nail, don't forget that ORACLE is now the hammer. Access is merely the hand that guides it. If you did this in any other presentation tool (say, ORACLE Tools as a contrary example), you would be doing the same sort of thing anyway. Access, while surely not the best possible tool, is DECENT.

As to "trolling" your text files, if they exist already you have to parse them, but if you could get an agreement up front that the files would be converted or created in your specified format, you could make it look like it was really an organized operation. Further, Word can be programmed to let you fill in tables or fields with validation rules locally to the Word form. Which means that you can off-load some validation steps to form creation. And once you have the forms, the FileFind object allows you to specify Office files instead of or as well as general text files. So you could make the search operation pretty simple. Then your import is just a complex loop based on a For Each driven by the .Found collection of the FindFile object. Take a divide and conquer approach here. Break the project up into steps and define steps as generically as possible. The idea is to build and fill your own tool box of modules to help you along the way.

Now, the way to prevent re-use of files depends on what you are allowed to do with the files. If they give you copies of the files and the names are designed to be unique, then you could put the untrolled files in a folder and when done with them, RENAME them to another folder or delete them. In any case, you could just keep track of the supposedly unique file names to prevent re-use. Or not, if that is the way you want it.

If the issue is that you would always just manually point to the file you want to troll, you can search the forum for articles on the FileOpen dialogue box. Pat has at least a couple of samples showing the knowledge base article on how to do this without having to use the ActiveX (.OCX) file, which some security folks really don't like. They tend to twitch a bit when you say ActiveX - it's a reflex thing, I'm sure. I don't have my notes on this handy. Or you could search the knowledge base your self for FileOpen dialog and workarounds using the Win32 library routines.

The biggest trap is to do something like this in isolation. You don't work in a vacuum. (No man is an island? Pick your favorite cliche here.) Don't hesitate to consult with the DBAs, network administrators, system administrators, and (if you have a separate department for this) your security administrators. It is incredibly important to discover the security and operational rules that already apply, and it is equally important to discover the style of rules in case you have to propose a few yourself. You want to be consistent with your environment. The more you diverge from that environment, the fewer smiling faces you will see as the project nears completion. The more you integrate your product into the company's "style" (whatever it happens to be), the more you look like a team player. I don't think I have to tell you the value of that appearance.

Finally, some simple rules to consider in your interfaces:

1. Be consistent. (This is where a judiciously applied company logo and attention to "look-and-feel" standards is important.)

2. Be informative. (The whole point of an MIS is to make information available. So MAKE information available.)

3. Easy to read = easy to use.

4. Show, don't tell. (Don't say "I've got 5 records." Spring up the sub-form right away showing the 5 records in line-item fashion, even if you have to do something special later to show more details.)

5. Do, don't ask - unless necessary. I.e. if the user clicks OK, don't ask "Are you sure?" unless there is a style or data-safety issue.

6. Dialogue, not monologue. You are providing the answers to questions. So let the questioner question and then you can provide an answer.

7. Let the driver drive. You don't know and cannot program every possible direction a user can go. So let the user make as many decisions as possible consistent with point #5 & #6. When you have enough to know what to do next, DO it. But until then, the user is the one for whom this app was written.

There, that ought to fry your brain for a while contemplating that lump.

 

[monkeytunes]

01) Trolling for .txt files is not difficult using code. I have set up VBA code that grabs data from Excel files and checks for previous existing records, changes those records if necessary, and also adds records.

You have? Gimme gimme gimme!

Seriously, though, this was done by another department using a HUGE VB function written into an ancient Excel spreadsheet. The code is LONG and TEDIOUS and, since it's using outdated commands, it only runs on this pokey Win95 machine using old versions of Office. The poor girl in that department then made all the changes manually before uploading everything into an Access DB.

If you could post some code, I'd be ecstatic!

02) Links to multiple ODBC databases is relatively painless. What we've done is to set up a Reference table that contains all the repetitive info we need, and then using code, "grab and pass" the parameters needed to establish links. This re-establishes our links when needed so we don't have to worry about the links not being there.

I haven't done that much with ODBC recently, so this will be a re-learning experience for me. Hopefully it will be easy. I'm worried about details like automating logins (even convincing them to add 1 extra login for our Access DB might be a challenge), and convincing different departments to give us access to their prized databases at all.

It sounds like you're going to be having fun!! Well, you've come to the right place for info and help / advice. This forum has saved my sanity more than once!

Tru dat! I love this forum - I troll it all day long, much to my boss' chagrin. "Are you looking at that thing AGAIN?"

 

[greaseman]

Way to go, DocMan!!

True words! Sounds like you earned your title the hard way! I just printed your comments, since here I work will be encountering these identical issues soon. It made for interesting reading!

 

[monkeytunes]

There, that ought to fry your brain for a while contemplating that lump.

Hahaha - LOTS to think about, Doc, but definitely worthwhile, and your points make a lot of sense!

W/r/t the ORACLE licenses - My company is BEEEG. I'm 99.9% you've heard of us, and I'm sure we have up-close-and-personal-agreements with Mr. Gates and Mr. Ellison. And if we don't, I'm sure we can afford them.

W/r/t the text files - The text files are generated from CAD drawings from dozens of planners and designers. Since my company is BEEEG, I won't be able to institute sweeping changes in how this is done (probably). Plus, we have nearly 5 years worth of these .txt files (1 file per project) with which to contend, which amounts to (and I kid you not) 5,255 files by today's count (more are added every week), each of which can be anywhere from 1k to 20k of item quantities for that project only. I've got a sneaking suspicion that either choice -- i.e. converting all the current and future files or parsing the raw text file -- is going to be a hefty and depressing chore.

But, Doc, thanks again. This is terrific food for thought. Anything else that coems to mind, I beg of you to post it! Thanks!

 

[Oy Vey]

Make this book your bible. I picked it up a few months after I left a company where a project I worked on had failed. In going through the quiz in Chapter 2 I found that the project scored around 36 points on a hundred point scale.

 

[greaseman]

Here's some code for you....

Here's the code in a ZIP file that I lightly mentioned: it's in a ZIP file and has 3 parts: one part is the Back end that has some referral tables. The next part is a library of code modules and forms that we've somewhat standardized. The third part is the project's front end.

When you attempt to run this project, it WILL die on you, since obviously, your network links and assignments are different from what we use. However, if you can "walk through" the code, you should be able to grasp how it functions.

Let me know if it helps you out at all.

By the way, the password for the "ISDev" user id is "Brooke". Don't ask, that's just what was decided upon here.

Oh - in case you didn't notice in the style of the code, it was initially developed by a guy who had a heavy background in VB, not VBA or Access.

 

[greaseman]

Monkeytunes,

my file was too large to upload. If you'll pm me or send me an e-mail address, I'll try to get it too you, OK? It's about 665 kb in size, after compacting.

 

[dan-cat]

I've explained that our IT prefers everything to be done in ORACLE (which I've NEVER used), and that Access has quirks and limitations, especially w/r/t networks and large data grabs and numbers of users, but my sups simply don't care; they like me, they know that I use Access, they know I have some (very limited) experience using SQL databases with an Access front end, so they said "just use Access".

I've never known a supervisor yet who doesn't care about having to wait too long for a report to come off. They may not care now - but they will do if they have to wait a while to gain their data.

I would ignore what they do and don't care about at this point in time - believe me they don't know any better if they are unconcerned about how your db is going to be set-up.

Try to foresee where this project is going and what may be asked of you down the line. If you develop the front-end in access - are you going to be able to provide all the services they may ask for in the future?

Do you use DAO - the default now again in Access 2003 - because ADO wasn't popular enough amongst access developers?
What if they start talking about web integration - you may change your mind and think vb.NET is a good idea.
How much control do you want on the front-end - access provides all the wizards for linking tables - but do you want to be force-fed these things - perhaps you may want to learn how to make remote connections via ADO yourself?

Forget about what your supervisors want now - because I can guarantee you what they want now - is not what they really want - arm yourself with the most powerful tools you can learn. Anything that a wizard does - learn how to do yourself through code - otherwise you will quickly come unstuck when the wizard runs out of ideas.

I know I sound a bit heavy on supervisors but I have not yet met a customer who actually knows what they really want in an application from the word go.
Your supervisors sound ambitious - and if they are then you are going to worked hard.

Good luck!

 

[Kevin_S]

Just out of curiousity:

In a company as large (or BEEG if you prefer) as you state that already has similar developed VB FE's to ORACLE BE's why wouldn't your department have the IT Department work on this project instead of one person with (as you say) limited MS Access skills..? Not trying to bash you hear but coming from the IT side I would think this project should be handled my the Application Development Group of your IT Department... You'd be surprised how easy it is to crash servers from infinite loops and other pro VBA coding...

just food for thought...

Other then that - if you use MS Access make sure (1) your using it just for the FE link to ORACLE and (2) you streamline your data grab as much as possible. Search this form for keywords "performance hits" or "optimize performance" for tips/tricks on how to do this...

Good Luck,
Kev

 

[monkeytunes]

I've never known a supervisor yet who doesn't care about having to wait too long for a report to come off. They may not care now - but they will do if they have to wait a while to gain their data.

Good point. But then again, the answers I'm getting here are pretty informative, and it sounds like an Access FE might just be what we need.

Try to foresee where this project is going and what may be asked of you down the line. If you develop the front-end in access - are you going to be able to provide all the services they may ask for in the future?

Do you use DAO - the default now again in Access 2003 - because ADO wasn't popular enough amongst access developers? What if they start talking about web integration - you may change your mind and think vb.NET is a good idea.How much control do you want on the front-end - access provides all the wizards for linking tables - but do you want to be force-fed these things - perhaps you may want to learn how to make remote connections via ADO yourself?

Here's the big zinger...I don't know how to use DAO at all. Everything I do in Access is done using the development tools within Access; all my coding is poached off this forum.

That being said - in the past 3/4 months I've been here, I've been able to produce everything they've asked for, completely re-orchestrate their protocols and management system, and move all their tracking from outdated spreadsheet maintenance to Acces databases. In a completely subjective observation: they love it.

Since we were able to coordinate things so well among ourselves, what they want here is just to be able to view and compare all the info from other disparate departments. Dept A keeps a list of projects and their product needs, Dept B keeps a list of product descriptions and their quantity, Dept C keeps a list of how often we get certain products and what manufacturers provide them etc. and right now NONE of them are talking to one another. The whole thing sounds like a relational database on its own; it's just that each table of data is stored in a different part of the building! What we want is a window to all of it, so you can look at a product and see what it projects need it, you can look at a project and see what product it needs, or look at a time frame and see all the projects underway and so on. Our goal is entirely possible and, I think, relatively simple, and as long as I keep it tacked above my desk in easy English...the rest is just details, right?

Good luck!

THANKS.

 

[monkeytunes]

In a company as large (or BEEG if you prefer) as you state that already has similar developed VB FE's to ORACLE BE's why wouldn't your department have the IT Department work on this project instead of one person with (as you say) limited MS Access skills..? Not trying to bash you hear but coming from the IT side I would think this project should be handled my the Application Development Group of your IT Department... You'd be surprised how easy it is to crash servers from infinite loops and other pro VBA coding...

No bashing taken; I know the extent/limits of my skills. That's why this discussion and these forums are so valuable; I learn more EVERY DAY.

W/r/t the project - this is where the idea of possibly having me manage it and let the grunts do the work comes into play. My sups all agree on the WHAT, the goal, i.e. Synergize the Data from (currently non-communicating) disparate Departments. They don't necessarily understand HOW to do it, the concepts behind the joining of this data...but I do (I hope), i.e. network links to various data-sources, what fields/identifiers we need from all sources to relate the data across departments, and what tools will be needed to physically/electronically do it. Then we come to my current dilemma: use the tools I know how to use (Access), or enlist the help of someone with more tools in their belt?

Metaphors work well here: I can make a functional coffee table using some 2x4s and a plank, but sometimes you need a carpenter to do all your cabinetry and casework. You still manage the carpenter, tell him what you need done, and how it should work. I can play upright bass, but sometimes you need a fully orchestrated symphony. As a conductor, you still tell your trumpet players and flute players and cellists what to play and how to play it, even though they probably play much better than you do.

That being said - your comment is something I need to make special note of. We have a big ol' meeting with the IT head this week, and I'll be sure to ask what sorts of resources he has available to me in manpower and otherwise.

Thanks for your input! Appreciated!

 

[Kevin_S]

That being said - your comment is something I need to make special note of. We have a big ol' meeting with the IT head this week, and I'll be sure to ask what sorts of resources he has available to me in manpower and otherwise.

Good to hear - IT managers are much, much more approachable when you say "how can you help use accomplish this task" vs. "we are going to do this ourselves and we only want your input where needed" - type of thing... who knows - they may even offer to do the lions share and you can project manage + get the credit for the innovative idea behind the scene... makes getting permissions to data sources much easier too

Good luck and if you need help continue to post to this forum...

HTH,
Kev

 

[Oy Vey]

Do you use DAO - the default now again in Access 2003 - because ADO wasn't popular enough amongst access developers?

Just a nitpick with that statement; its not so much that DAO is the default database access object as it is that MS decided to enable it alongside ADO in new projects.

http://support.microsoft.com/default.aspx?scid=kb;en-us;825796

Anyway, back on topic - plan, get input, plan some more, get more input. I can't tell you how many times I've gotten in trouble because I created something that I knew was the right way to do it when what the boss/users really wanted was for me to read their minds.

As far as platforms, stick with what you know for production stuff but don't stop tinkering with new technologies. For your setup I would highly recommend exploring VB.Net and ASP.Net, but Access should be sufficient for the time being as long as you don't overpromise.

 

[dan-cat]

Just a nitpick with that statement; its not so much that DAO is the default database access object as it is that MS decided to enable it alongside ADO in new projects.

http://support.microsoft.com/default.aspx?scid=kb;en-us;825796

Whoops - you're right - hehehe

What I meant was that they put the DAO default reference back in 2003 'cos people didn't want to let it go. This isn't a good sign though because it means there are plenty of people not willing to learn ADO. More fool them because they are missing out on alot of good stuff

Anyway, back on topic - plan, get input, plan some more, get more input. I can't tell you how many times I've gotten in trouble because I created something that I knew was the right way to do it when what the boss/users really wanted was for me to read their minds.

This is so true - users/bosses give you so little to go on at the start thats its next to impossible not to make all the decisions for them. What I find I have to do is actually build something which I'm guessing is what they want. Then prepare myself for them to rip it to pieces when you put it in front of them. They never seemed to be bothered to plan what they actually want until you give them something that they don't. I think that makes sense

 

[Pat Hartman]

monkeytunes,
Search the http://support.microsoft.com/default.aspx?scid=fh;[ln];kbhowto] - Microsoft Knowledgebase for the key words client server. There are a number of articles that give you dos and don'ts for using Access with ODBC connections. It is VERY IMPORTANT that you base ALL forms and reports on querydefs that include selection criteria. You do not want to end up with a form, in particular, that acts like an open pipeline - pumping record after record across the network for no reason. The Oracle DBA's will HATE you and cause you nothing but grief and curse the name of Access. Many people who have been developing Access apps with Jet tables use tables as the RecordSources for their forms unless they need to use a join to bring data from multiple tables and even when they use a query, they don't specify selection criteria. They just filter the form. This tecninque works fine with a Jet back end but is an absolute NO-NO for ODBC back ends. With an ODBC back end it is IMPERATIVE that you restrict the number of rows you request from the server. I usually do this by creating filter forms that let the user specifies criteria that I use to build an SQL string. You can get really fancy with this depending on your requirements. I pass the SQL string as the OpenArgs argument of the OpenForm method. Then in the Open event of the form, I replace the current RecordSource with the passed one.
Me.RecordSource = Me.OpenArgs

Do you use DAO - the default now again in Access 2003 - because ADO wasn't popular enough amongst access developers?

- I don't think that lazyness is the reason unless you want to blame Microsoft's lazyness. Microsoft never changed how their internal code works and at least as late as XP (and I think 2003 also), the RecordsetClone of a form, for example, is a DAO object. Therefore, if you need to write code that works with the RecordsetClone, it MUST be DAO code.

DAO is optimized for use with Jet and ADO is optimized for use with ODBC databases so given that monkeytunes is using Oracle, he should be using ADO except where he has to use DAO because ADO doesn't provide the necessary functionality. See this article - INFO: What does DAO have that ADO/ADOx/JRO do not have (and might never have!)

 

[Kevin_S]

Mods/Mile -

this is a pretty common theme that most more experienced developers seem to reiterate over and over again during forum interaction with less advanced users (Topic: Access optimization as ODBC FE or ODBC Connectivity). Could you possibly post this in the knowledge base or FAQ? Basically, everything that Pat just said!...

Actually, could be just request Pat posts her book to the FAQ then we'd all be better off!!

Thanks!
Kev