looping through multiple databases

[dj59]

I need to create a loop that changes two items and I don't know how:
1 the variable in the sql - (c.cnty_cd = '11')
2 the database in the sql - (salti_person11)

so that each time the sql runs a new item from the array is inserted
for each of these.

Any help would be appreciated.

    Dim strSql As String
    Dim myCtyArray As Variant
    Dim myDbArray As Variant
    Dim x As Integer
    myCtyArray = Array(1,2,11,B2)      'define CtyArray
    myDbArray = Array(salti_person01,salti_person02,salti_person11,salti_personB2) 'define DbArray
 
    strSql = "SELECT sp.deceased_dt, sp.gender_cd, sp.birth_dt, sp.deceased_dt_source_cd, sp.ms_pmi, c.cnty_cd," & _
            "sp.south_index_id, sp.hispanic_cd, sp.south_ssn, p.estimated_birth_dt," & _
            "[an.LAST_NAME] & ', ' & [an.FIRST_NAME] & ' ' & [an.MIDDLE_NAME] & ' ' & [an.NAME_SUFFIX] AS personname" & _
    "FROM salti_person11 AS p, sw_south_person AS sp, sw_alias_name AS an, sw_county_pid AS c" & _
    "WHERE p.south_index_id = sp.south_index_id" & _
      "And an.south_index_id = sp.south_index_id" & _
      "and c.south_index_id = sp.south_index_id" & _
      "and (sp.hispanic_cd is null" & _
      "and p.hispanic_cd is null)" & _
      "and c.cnty_cd = '11'" & _
      "and sp.deceased_dt is null" & _
    "ORDER BY [an.LAST_NAME] & ', ' & [an.FIRST_NAME] & ' ' & [an.MIDDLE_NAME] & ' ' & [an.NAME_SUFFIX]);" 'define sql
 
For x = LBound(myArray) To UBound(myArray) 'define start and end of array
'run sql with c.cnty_cd as 01 and ssisPerson11 as sssisPerson01
Next x ' Loop!

 

[pbaldy]

You set the SQL string inside the loop, concatenating the value from the array into it. Not sure what you're trying to do, but you can't "run" a SELECT query, only action queries.

 

[dj59]

I am not sure what way to do this.
I'll give some back ground.

I have a query that this sql runs, which produces a report.
This query needs to run multiple times, each time pointing to a different database (myDbArray) and with a different ctyCd (myCtyArray).


My goal is to automate this so that with a click of the button on a form in Access it will:

• run the query,
• export the results to a location,
• run the query again with the next item in each array,
• export the reults to a location
• run the query again with the next item in each array,
• export the reults to a location

So, my problem is I don't know how to use the loop either in the query or in the vba sql statement. It seems like I'm making this harder than it has to be, but I'm stuck at this point.
Another thought is forget about the myDbArray and in the vba code call the query to run, but first open the odbc connection to that db and leave the 'salti_person' with no number on the end, so it is the same for all db's.

okay. I might delete this post since I am not sure what I'm doing yet...

 

[pbaldy]

Okay, post back if you get stuck.

 

[dj59]

I am confused on what forum this belongs on, but I have come a bit further on this issue it is posted at:
http://www.access-programmers.co.uk/forums/showthread.php?t=273734

On the vba forum. Thanks for any help.