Link to home
Create AccountLog in
Avatar of qholmberg
qholmberg

asked on

Loop through systems executing SQL

I'm trying to loop through 6 systems executing the same SQL statement on each one. I'm using code that I've used for years previously to query single systems. There is something I'm missing in regards to looping through them.

Here is the snippet of code ...

   For x = 1 To 6
    'SET SYSTEM IP FOR CONNECTION STRING AND LIBRARY OF FILE
    Select Case x
        Case 1
            system = "192.168.1.1"
            lib = "QS36F"
        Case 2
            system = "192.168.3.1"
            lib = "REMOTF"
        Case 3
            system = "192.168.4.1"
            lib = "REMOTF"
        Case 4
            system = "192.168.7.1"
            lib = "REMOTF"
        Case 5
            system = "192.168.8.1"
            lib = "REMOTF"
        Case 6
            system = "192.168.9.1"
            lib = "REMOTF"
    End Select
   
   'BUILD SQL STATEMENT
   SQL = "select poplt plant, ponum po#, povend vendor, podate date "
   SQL = SQL + "from " & lib & ".pofile "
   SQL = SQL + "where postat in(' ','B') "
   
    'OPEN DB CONNECTION AND RETRIEVE RECORDSET
    DBConn.Open "provider=IBMDA400;data source=" & system
    Set Command.ActiveConnection = DBConn
    With Command
        .CommandText = SQL
        .CommandType = adCmdText
    End With
    Set RS = Command.Execute
    
    'ON FIRST RUN THROUGH
    If x = 1 Then
        'CLEAR AND POPULATE DATA WORKSHEET
        Call ActivateSheet("Data")
        Call PopHeader(RS, "Data")
        'INITIALIZE GLOBAL ROW VARIABLE
        row = 2
    End If

    Call PopData(RS, "Data", row)

    row = Range(("A2"), Range("A1").End(xlDown).Address).Count + 2
    
    'CLOSE DB CONNECTION
    DBConn.Close
    Set RS = Nothing
   Next x

Open in new window


That's no the whole program, of course. It's just the looping routine to go through each system. It works for the first loop. It dies with an Automation error on the ...
Set RS = Command.Execute

Open in new window

... statement (line 36 above).

Is there something I need to do to reset the connection? I'm at a loss.

Thanks for you help.
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of qholmberg
qholmberg

ASKER

Experts ... In the end, that 'lib' varialbe was the problem. It's the library for the SQL statement as it is different between the systems. I spelled it wrong.<br /><br />While none of you directly gave me the solution (nor could have any of you), you all gave me information I integrated into my program.<br /><br />Thanks for you help.