View Single Post
  #5 (permalink)  
Old July 14th, 2009, 04:19 PM
Rood67 Rood67 is offline
Authorized User
Join Date: Mar 2004
Location: Knoxville, TN, USA.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Rood67

Thanks again for the quick reply Mac.

Here's the process that I do now.
  • Use the form (refer to first screen shot of first reply) to run the queries you see on the left side of the picture.
  • The order of operation on those is to run the delete query to empty my local Access tables.
  • Run the insert query to repopulate the local tables (the insert gets its data from my current pass through queries that run against the AS400).
  • There's one set of queries for each table, a delete, a pass through, and an insert.
The VBA code behind the form runs these inside a Sub() that does turn off warnings before each query set is run, and turns warnings back on after the run is done. I have it set that way so that I can chose to update all the local tables or just pick and chose, and always ensure the warnings get toggled off then back on.

Now, more to your question. What I want to do with the recordsets is the process above but by getting away from have 6 dozen queries to do it.

In my mind, I foresee that I can set up a Sub() or each table, and when I pull the recordset, I will have much greater flexibility to process the returned data through other Sub() or Function() and insert the adjusted data into my local tables.

That would let me get away from calling Function() inside my insert queries. And oh the headache of trying to make parameter changes, and having to open one to maybe a half a dozen queries to make those changes. And the ones that have IIF() statements in them... using VBA code on the recordset would make life so much simpler.

I want to layout a form, have some text boxes for typed input, some combo / list boxes, some radio buttons, and some check boxes to facilitate on the fly parameter changes, and have locally stored data that matches my criteria.


All of your code samples for the ADO are helping my limited knowledge expand greatly. You may have it over simplified compared to what I have looked up online for creating a recordset, but your's seems to make more sense.

My main question here for this round of post is - how do I code the setup to start a VBA pass through query and how to get started with a recordset.

I know some may look at this and go, "oh, that's so simple" and I hope that by next week, I can have that response. But today, I want to go ram my head into the grill of an oncoming semi-truck, because I can't figure out what it is that I don't understand, which is keeping me from asking the proper questions, which is a vicious cycle.

If there was a good book that dealt with this in detail instead of just glancing over it and heading back off to show how to make a Macro I'd get it. But I've looked at all kinds of books from more authors and publishers than I can recall. But none of them really seem to delve into connecting to external databases and functional use of recordsets.

But again, your code snipets are helping me grasp what I need to know so I can ask you a more direct set of questions on...
1. How does that actually work?
2. What do I need to put here to get this to stop erroring out?
3. In your snipet, you showed blah blah, can you give an actual expample of that in detail?

If I knew enough to get set up to play with it, I'd take off and run with it and get it working. By the Grace of God, I have been blessed with rapid learning once someone gets me started. Right now, the tracks closed and the starter blocks are in a vault.

I truly appreciate your help,
Reply With Quote