Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 9th, 2009, 11:38 AM
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
Cool VBA Passthrough Query

Hello all,

I use Access 2007 to connect to a hosted AS400 database (iSeries 5). I used to sit and wait nearly 17 minutes for the queries I had set up to create local tables to run an populate.

Then... oh, then I found the joy of Pass Through Queries. *pause for the angels to stop singing* Now, I have it so that my old 17 minute wait is cut down to just over 2 minutes, and that's with more queries than before.

I've even learned how to create and store personal views on the AS400 so that some of my Excel files can get up to date info (before I would run my pass through queries and let Excel pull from my local temp tables.

Here's my new dilemma. I am finding the need to run some of my pass through queries with parameter qualifications. Also, there are rare cases that I need to do an update, for which I took my old select pass through and edited it to update. But when the data update has to go to several tables... well, that's a pain and opt to have errors.

So, finally, here is the meat and potatoes of this post...
How, in the name of all that is Holy, do I set up a pass through query in VBA that will connect to the AS400 and run it or them with parameters?

And I suppose you need to know that I'm connecting through IBM's Client Access ODBC driver, although I'm finding more and more info on iSeries Access ODBC driver, but I don't have that yet. I have been to the connection string website to get the proper connection string to use with Client Access ODBC, but I have no idea what to do with it.

If anyone can help me, I will be eternally grateful.
Scott
__________________
<b>Rood67</b>
Reply With Quote
  #2 (permalink)  
Old July 14th, 2009, 08:37 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to post more information than this.

An example of a SELECT query would be something like:

Code:
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
 
sSQL = "SELECT * FROM MyTable"
 
Set cn = New ADODB.Connection
cn.open "DSN=MyDSNName" 'from your ODBC setup
 
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
 
Do Until rs.EOF
'do something iterative with the data...?
 
 
rs.MoveNext
Loop
or

Code:
sSQL = "INSERT INTO MyTable(Username, LastName, FirstName) VALUES('mmcdonal', 'Mike', 'McDonald')"
...
 
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic 'this line does the insert
 
cn.Close

Then if you want to use variables...

Code:
Dim sUsername As String
 
sUsername = Me.Username 'referring to a form control
 
sSQL = "SELECT * FROM MyTable WHERE Username = '" & sUsername & "'"
 
...
rs,Open sSQL, cn, ...
Did any of that help?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old July 14th, 2009, 12:09 PM
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
Default

Hi Mac,
Great to read your reply, I was getting worried no one could help me. I apologize for not inserting code in the first post, and that had to be a deterrent to others posting I'm sure.

I'm going to include links to some pictures as well as post some code of what I have now. Maybe from that you can further assist in getting me started in the right direction. Actually, you have gotten me started, but my dilemma now is that I feel just enough dangerous to blow something up without knowing how to stop it or fix it if things get away from me. Well, I can always end process here locally, and log in green screen on the AS400 and kill the active job, so let me get to it and see if you can finalize my tutelage in converting these mass amount of queries to a more simplified VBA so I can make changes and updates easier.

Case in point for making life easier... our AS400 host got me the latest iSeries ODBC, and it took me nearly an hour to open each pass through query and update the connection string. If I had this in VBA with a global variable set up for the connection string, it would have been one quick edit and all the queries would have been good to go.

********************
Screen shot of my cluster-hump of queries:
http://files.getdropbox.com/u/145326...yList_Form.jpg

Now, taking the first set of queries that works with the CUPMMOF table, I have the following sequence of events.
Clicking the check box to create the table runs the following from DoCmd in VBA.
  • 01a-qry_MakeCUPMMOF_d (deletes old data from local table)
  • 01a_qry_MakeCUPMMOF_pt (this is the pass through that runs on the AS400 to create a subset of data)
  • 01a-qry_MakeCUPMMOF (this is an append query that reads the pass through and inserts the subset into my local table)
Heres the info for the delete query:
http://files.getdropbox.com/u/145326...eteCupmmof.jpg
which has the Access2007 generated code of
Code:
DELETE tbl_cupmmof.ItemCode
     , tbl_cupmmof.Format
     , tbl_cupmmof.Title
     , tbl_cupmmof.Y
     , tbl_cupmmof.StreetDate
     , tbl_cupmmof.Form
     , tbl_cupmmof.Box
     , tbl_cupmmof.Genre
     , tbl_cupmmof.Studio
     , tbl_cupmmof.Rating
     , tbl_cupmmof.Rank
     , tbl_cupmmof.Theme
     , tbl_cupmmof.QtyOnOrder
     , tbl_cupmmof.Type
FROM tbl_cupmmof;
Here is the pass through that I wrote:
Code:
SELECT trim(MOFICD) as MOFICD
     , trim(MOFMNM) as MOFMNM
     , trim(MOFTLE) as MOFTLE
     , MOFSTD
     , trim(MOFFRM) as MOFFRM
     , trim(MOFBOX) as MOFBOX
     , trim(MOFGNR) as MOFGNR
     , trim(MOFSDO) as MOFSDO
     , trim(MOFRTG) as MOFRTG
     , trim(MOFRNK) as MOFRNK
     , trim(MOFTHM) as MOFTHM
     , MOFQOO, MOFTYP
FROM PRODTA.CUPMMOF
WHERE MOFSTD > '12/31/2007'
Heres the info for the append query:
http://files.getdropbox.com/u/145326...endCupmmof.jpg
which has the Access2007 generated code of
Code:
INSERT INTO tbl_cupmmof ( ItemCode, Format, Title, Y, StreetDate, Form, Box, Genre, Studio, Rating, Rank, Theme, QtyOnOrder, Type )
SELECT [01a_qry_MakeCUPMMOF_pt].MOFICD AS ItemCode
     , [01a_qry_MakeCUPMMOF_pt].MOFMNM AS Format
     , [01a_qry_MakeCUPMMOF_pt].MOFTLE AS Title
     , Right([MOFFRM],2) AS Y
     , [01a_qry_MakeCUPMMOF_pt].MOFSTD AS StreetDate
     , [01a_qry_MakeCUPMMOF_pt].MOFFRM AS Form
     , [01a_qry_MakeCUPMMOF_pt].MOFBOX AS Box
     , [01a_qry_MakeCUPMMOF_pt].MOFGNR AS Genre
     , [01a_qry_MakeCUPMMOF_pt].MOFSDO AS Studio
     , [01a_qry_MakeCUPMMOF_pt].MOFRTG AS Rating
     , [01a_qry_MakeCUPMMOF_pt].MOFRNK AS Rank
     , [01a_qry_MakeCUPMMOF_pt].MOFTHM AS Theme
     , [01a_qry_MakeCUPMMOF_pt].MOFQOO AS QtyOnOrder
     , [01a_qry_MakeCUPMMOF_pt].MOFTYP AS Type
FROM 01a_qry_MakeCUPMMOF_pt;
Now, this is the simple one, so you can look at it and help me get started converting it to a pure VBA database connection - recordset processing procedure.

The fun ones come in when they call functions that I've written to concatenate the date (some of the AS400 tables have each date piece in its own field because they have indexes and views that are based on pulling info soley by year, etc...)

Here is the code from the Sales Transaction pass through, append, and function.
Code:
SELECT CNMB
     , INVNO
     , ORDN
     , ORDLN
     , ORDMM
     , ORDDD
     , ORDYY
     , INVMM
     , INVDD
     , INVYY
     , SHPMM
     , SHPDD
     , SHPYY
     , CMOR
     , trim(IMCD) as IMCD
     , QTY
     , COST
     , CHENB
     , SLS#
     , CRORT
     , IMDEPT
     , IMALL
     , TYPTRN
     , SAACPR
     , PRICE
FROM PRODTA.SAPITRN
WHERE SAACPR > '0712'
Code:
INSERT INTO tbl_sapitrn ( AcctNr, InvNr, OrderNr, OrderLnNr, Ordered, Invoiced, Shipped, CorDorO, ItemCode, OrdQty, Cost, zPrice, Total, EnteredBy, Salesman, Type, ItemDeptNr, Allocated, TranType, AcctPeriod )
SELECT Acct([CNMB]) AS AcctNr
     , [01c-qry_MakeSAPITRN_pt].INVNO AS InvNr
     , [01c-qry_MakeSAPITRN_pt].ORDN AS OrderNr
     , [01c-qry_MakeSAPITRN_pt].ORDLN AS OrderLnNr
     , DateConcat([ORDMM],[ORDDD],[ORDYY]) AS Ordered
     , DateConcat([INVMM],[INVDD],[INVYY]) AS Invoiced
     , DateConcat([SHPMM],[SHPDD],[SHPYY]) AS Shipped
     , [01c-qry_MakeSAPITRN_pt].CMOR AS CorDorO
     , RTrim([IMCD]) AS ItemCode
     , [01c-qry_MakeSAPITRN_pt].QTY AS OrdQty
     , [01c-qry_MakeSAPITRN_pt].COST AS Cost
     , IIf([CorDorO]="C",[PRICE]*-1,[PRICE]) AS zPrice
     , [OrdQty]*[zPrice] AS Total
     , [01c-qry_MakeSAPITRN_pt].CHENB AS EnteredBy
     , [01c-qry_MakeSAPITRN_pt].[SLS#] AS Salesman
     , [01c-qry_MakeSAPITRN_pt].CRORT AS Type
     , [01c-qry_MakeSAPITRN_pt].IMDEPT AS ItemDeptNr
     , Trim([IMALL]) AS Allocated
     , [01c-qry_MakeSAPITRN_pt].TYPTRN AS TranType
     , [01c-qry_MakeSAPITRN_pt].SAACPR AS AcctPeriod
FROM [01c-qry_MakeSAPITRN_pt];
Code:
Function DateConcat(MM As String, DD As String, YY As String) As Date

    Dim CD_01 As String
    
    If MM = "0" Then
        MM = "12"
        DD = "31"
        YY = "2099"
    End If
    
    If Len(RTrim(MM)) = 1 Then
        MM = "0" & MM
    End If
    If Len(RTrim(DD)) = 1 Then
        DD = "0" & DD
    End If
    If Len(RTrim(YY)) = 1 Then
        YY = "200" & YY
    End If
    If Len(RTrim(YY)) = 2 Then
        YY = "20" & YY
    End If
    
    CD_01 = MM & "/" & DD & "/" & YY
    
    DateConcat = CDate(CD_01)

End Function
********************

Now, what all of this desire to learn is geared toward, is instead of having to go in and edit individual queries that select subsets based on a date or maybe based on a particular item code, I could use my form with some modifications to pass parameters to the queries and make them truly dynamic instead of a complete pain to work with. Several hours of changes and modifications once vs the way it is now, I'll opt for the one time and keep some BC powerds handy.

I know it's very long, but hopefully this will be what you need. If not, let me know.

Thank you,
Scott
__________________
<b>Rood67</b>
Reply With Quote
  #4 (permalink)  
Old July 14th, 2009, 12:43 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What are you doing with the recordsets once you get them?

To make the queries dynamic, you have to add the controls for your users to select parameter values.

For example, If I wanted a user to select a customer ID, I would create a combo box that was populated with valid customer ID's with some meaningful data like customer name displayed. Then when they clicked the button to run the report, if Customer ID was required, the code would be:

Code:
Dim lCustID As Long
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
 
cString = conn() 'cString is a public variable, and conn() is a function that has my global connection information conn = "DSN=MyDsnName" for example.
 
If IsNull(Me.cboCustSelect) or Me.cboCustSelect = 0 Then
   MsgBox "Please select a Customer", vbInformation
   Me.cboCustSelect.SetFocus
   Exit Sub
Else
   lCustID = Me.cboCustSelect
End If
 
sSQL = "SELECT * FROM tblCustomer WHERE CustomerID = " & lCustID
 
Set rs...
The only difference in your requirements is what type of query you are running determines how you process the resulting recordset.

If you are doing a select query, where do you want the data to go (form, report, table?)

If you are doing an insert, just opening the recordset does the insert. Do you want to check to make sure it was successful? The same with an Update or Delete query (I never allow my users to do those.)

Also, I would not process data while I was running the ADO connection, as with your date correction. I would bring my data over, and then apply the fix on the local recordset, or in the actual report Details section On Format event. Although THAT can take some time geven the amount of data. I would not call that function from the report, but put it in the report perhaps, or do a local Update query.

I would change the delete query to this(DELETE * FROM tbl_cupmmof), store it locally and then run this code:

Code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "01a-qry_MakeCUPMMOF_d"
DoCmd.SetWarnings True
I am not sure what you are doing with the select query.

The Append query you show is an Insert query. You can take variables from a form for that and build the SQL string in your code from those variables as needed.

Can you post a single immediate issue that we can resolve? Then we can move on to the next...
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #5 (permalink)  
Old July 14th, 2009, 04:19 PM
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
Default

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,
Scott
__________________
<b>Rood67</b>
Reply With Quote
  #6 (permalink)  
Old July 15th, 2009, 08:28 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It's still a little vague: "how do I code the setup to start a VBA pass through query and how to get started with a recordset."

It sounds like you want to get data from the AS400, then process it locally and create reports from the local tables. Is that correct?

If I wanted to retrieve the Employees table from a SQL Northwind database, and populate a local Employee table in an Access Northwind database (a copy since there are PKs in the real table), and I was using a DSN called "SQL Northwind", but I only wanted employees from the UK based on a combo box on my form called cboCountry, that was not required, I would do this:

Code:
 
Dim sSQL As String
Dim sSQL_Local As String
Dim rs As ADODB.Recordset
Dim rs_Local As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sCriteria As String
 
'Take parameter
If IsNull(Me.cboCountry) or Me.cboCountry = "" Then 'Country is text field
   sCriteria = ""
Else
  sCriteria = " WHERE [Country] = '" & Me.cboCountry & "'" 'assume UK was selected
End If
 
sSQL = "SELECT * FROM Employees" & sCriteria
 
'Clear out old data
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_Employees"
DoCmd.SetWarnings True
 
'Open connection to SQL Server Northwind
Set cn = New ADODB.Connection
cn.Open "DSN=SQL Northwind"
 
'Open recordset on Employees table in SQL Northwind
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic
 
sSQL_Local = "SELECT * FROM Local_Employees"
 
'Open recordset on Access Northwind table (local connection)
Set rs_Local = New ADODB.Recordset
rs_Local.Open sSQL_Local, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 
'Check for data and then transfer from SQL to Access Employees table
If rs.EOF = False Or rs.BOF = False Then
 
   Do Until rs.EOF
      rs_Local("LastName") = rs("LastName")
      rs_Local("FirstName") = rs("FirstName")
      rs_Local("Title") = rs("Title")
      ...
     rs_Local.Update
 
   rs.MoveNext
   Loop
 
End If
 
cn.Close
 
DoCmd.OpenReport "rptMyCountryReport"...
This assumes that I don't need the EmployeeID.
Is that the sort of thing you want to do?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #7 (permalink)  
Old July 15th, 2009, 05:16 PM
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
Default

Hi Mac,

Yes, that is exactly what I want to do. THANK YOU very much.

I want to open a "read" connection to the AS400, a "write" connection to local Access table. As I step through the AS400 recordset, i can perform calculations, my custom concatenation to join date parts back together, etc...

And the other big, BIG thing, is to write back to the AS400 small changes to text fields. Case in point, if we add a movie title mid way through the order taking period, we denote that by putting the title into the system, but the product name has ZZZ added so the proprietary order program will sort it to the end of the weeks products.

But when we get ready to ship, so the customer doesn't see something like ZZZ Haunting In Connecticut or ZZZ Knowing, I have to go in and manually edit the title to remove the ZZZ.

What I have been hoping for is a way to pull up titles with the ZZZ in the name, and programatically change the name field in the three different tables it resides in.

Another problem that this will eliminate is strictly for my OCD... when I manually edit the name field, the proprietary software defaults the name field to all caps, so ZZZ Knowing becomes KNOWING. By updating using 3 different pass through queries in Access, I've gotten around this. But it's still editing 3 different queries. That's 3 chances to make a typo. If I pick it from a list of recordset results, and write back right([Name],len([Name])-4), then there's no chance for a screw up.

You truly hit the proverbial nail on the head with this last coding example. Now to see if I can get some simple select queries written to start testing and building my way up to the read in non-local table / write out local table, and then try to write back to the AS400.

My only remaining problem, concern, question is this... is there a way to signify in VBA code that my queries to the AS400 are pass through type? Because as I've stated before... normal select queries to the AS400 from Access are SLLLOOOOWWWWW. I am pretty confident this is because Access is reading in the entire AS400 table into local memory, then culling down the displayed result set based on the query criteria.

But the pass through, as defined by selecting the Pass Through option in query builder window, and supplying the DSN that point to the AS400 is tens to hundreds times faster, again, I am pretty confident in the reason why being the nature of the beast it runs the query on the AS400, and only passes back the filtered result set.

So, how in VBA code to I specify that my query is a pass through and not just a select query? Or am I just missing the simple point that by doing the query as a result set it is a pass through by default because the connection is established with a DSN?

Thanks again for all the info and help,
Scott
__________________
<b>Rood67</b>
Reply With Quote
  #8 (permalink)  
Old July 15th, 2009, 05:40 PM
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
Default

Hey Mac,

Trying to be proactive in my learning, I was searching for more and more info and examples. One forum had a post that the person said this VB6 book on ADO would work for VBA (their close enough I suppose). What would you say about this book while there's still some copies available to get?

http://www.amazon.com/ADO-Programmin.../dp/0130858579

Scott
__________________
<b>Rood67</b>
Reply With Quote
  #9 (permalink)  
Old July 16th, 2009, 07:54 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would not do any data corrections until I had the data in the local table. I would not concatenate the date fields, for example, until I got the table locally. So pull the data, then run your concatenation on the local table. This should speed things up.

You can do a Select query on the AS400 table like: "SELECT * FROM MoviesTable WHERE Title Like '%ZZZ%'" This will pull up all those records with ZZZ somewhere in the title. But if you know you only want movies whose title starts with ZZZ, then use 'ZZZ&'. Then just do this:

Code:
Dim sTitle As String
...
   sTitle = rs("Title")
   sTitle = Right(sTitle, Len(sTitle - 4)) 'removes the "ZZZ " to the left of the title
   rs("Title") = sTitle
   rs.Update
 
rs.MoveNext
...
There I would do the processing on the server side since you are only modifying data on the server database.

As to the last issue, DSN's can be slower, but they are more solid than using a Provider statement. I use DSN's mostly to prevent people from running my apps on an unauthorized machine. Provider statements are very portable.

Not being familiar with AS400, can you build the query on the AS400? I do that with SQL Server since it optimizes the query, as you say. I use Enterprise Manager, and create the View that way. I don't use Access to do that. Then you can even connect (link) the view directly as a Table object instead of a Query, if you want. Otherwise I use a Stored Procedure and then just pass variables to it. That is a little more complicated for starters.

Did that help?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #10 (permalink)  
Old July 16th, 2009, 08:03 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

As to the book, there are a lot of Access VBA books at Wrox. I have Access 2000, and 2003 VBA, and Access Application Development by Wrox, for example. I would supplement that with a Microsoft Press Windows Scripting Guide (sorry Wrox) like Windows 2000 Scripting Guide, Advanced Scripting for Windows Administrators, and IDG's Windows Scripting Secrets, which all have database scripting pointers.

I have VB6 and VB.Net books for those platforms, since VBScript, VBA and VB are all syntatically similar, but not exact. But the Wrox VBA references are more spot on than Visual Basic. The Access development books at Wrox are the best, that is why I have a 24x7 subscription, although I am always learning stuff from this forum.

See: http://www.wrox.com/WileyCDA/WroxTit...764556592.html

http://www.wrox.com/WileyCDA/WroxTit...764559036.html

And the still fresh: http://www.wrox.com/WileyCDA/WroxTit...764559044.html

Did that help?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to open an existing query in VBA michael193nj Access VBA 4 March 26th, 2008 05:09 PM
passthrough password BlueSkies Access 2 August 28th, 2006 06:29 AM
Help With SQL Query in VBA Paul_Tic Access VBA 4 May 30th, 2006 06:34 AM
VBA Query Problems LiamBFC Access VBA 2 April 11th, 2006 06:26 AM
passthrough query Snowingnow Access 3 November 23rd, 2004 11:47 AM



All times are GMT -4. The time now is 11:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.