Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stubborn Array won't Populate from single ADODB.Connection Query
I previously posted that I could not figure out how to populate VBA
Variables from ADODB.Connection results. I was able to get some help on figuring that out. That time I was running multiple queries that each had only one result returned, so I incremented the array, populating it with: For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x I've reused this same base project code several times over... But now I have one query that has multiple results returned. In the test I'm doing I get 13 records returned, each one consisting of a single (DEPT) field. And for the life of me I can't figure out how to get anything besides the first record. I am using the values for two separate tasks, so I decided I wanted both an array populated with the values, and a string that can be used as part of the WHERE HBM_DEPT IN (*) portion of a larger query sent back later in the code. 'Find SQL Server data for the active user. This data is used in the main QueryCommandText statement Set Conn = New ADODB.Connection Conn.Open "seassql08", "administrator", "[*****]" For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x 'Find SQL Server count of depts in the users domain Let QueryDeptDomain = "SELECT COUNT(*) FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" For y = 0 To 0 Let SQLquery = QueryDeptDomain Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomainCounter = RecSet.Fields.Item(0).Value 'Populate value into field Next y 'Build list of depts within the user's domain for use in QueryCommandText statement ReDim DeptDomain(0 To DeptDomainCounter) As String Let QueryDeptDomain = "SELECT DEPT FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" For z = 0 To DeptDomainCounter Let SQLquery = QueryDeptDomain Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomain(z) = RecSet.Fields.Item(0).Value 'Populate value into field Select Case z Case 0 Let DeptDomainList = "'" & DeptDomain(z) & "'" 'Populate value into field Case Is 0 Let DeptDomainList = DeptDomainList & ", '" & DeptDomain(z) & "'" 'Populate value into field Case Else DeptDomainList = MsgBox("Something Didn't Work", vbOKOnly, "Alert") End Select Next z RecSet.Close Conn.Close And if you need to see the larger context of what I'm doing, including the variable names and subsequent queries, see the full code below. If you're going to go that far, please note that some of the attempts to make the array populate here have caused the subsequent QueryTables.Add method to throw an invald SQL Statement error on the last property ".Refresh BackgroundQuery:=False" in the WITH block. This may not be a relevent issue since addressing the population of the array might nullify any problems here. '-------------------------------------------------------------- Option Explicit Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Dim UserName As String '-------------------------------------------------------------- Function CurrentUserName() As String UserName = Space(255) GetUserName UserName, 255 UserName = Left(UserName, InStr(UserName, Chr(0)) - 1) CurrentUserName = UserName End Function '-------------------------------------------------------------- Sub RetrieveNPGHeadcountFromCMSLive() 'Import Data From SQL Server to populate headcount table based on the office 'and department of the user opening the spreadsheet as populated from QueryCommandText. Dim Conn As ADODB.Connection 'SQL Server Connection Dim RecSet As ADODB.Recordset 'SQL Server RecordSet Dim SQLquery As String 'SQL Server Query holder Dim ActiveUser As String 'Holds login information for looking up other values Dim QueryUserInfo(0 To 5) As Variant 'Field Queries For: EmpID, EmpName, Offc, Dept, Login, Position Dim UserInfo(0 To 5) As String 'Field Values: EmpID, EmpName, Offc, Dept, Login, Position Dim QueryCommandText As String 'SQL Server query to retrieve ultimate target data set Dim QueryDeptDomain As String 'SQL Server query to retrieve number of depts under active user's domain of control Dim DeptDomainCounter As Integer 'Resultant dept list count Dim DeptDomainList As String 'Resultant dept list Dim x As Integer, y As Integer, z As Integer 'Incremental counters to populate QueryUserInfo and UserInfo arrays Let ActiveUser = "HOWEM" 'Let ActiveUser = CurrentUserName Let QueryUserInfo(0) = "SELECT EMPLOYEE_CODE FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(1) = "SELECT EMPLOYEE_NAME FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(2) = "SELECT OFFC FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(3) = "SELECT DEPT FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(4) = "SELECT LOGIN FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(5) = "SELECT POSITION FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let x = 0 And y = 0 And z = 0 'Find SQL Server data for the active user. This data is used in the main QueryCommandText statement Set Conn = New ADODB.Connection Conn.Open "seassql08", "administrator", "[*****]" For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x 'Find SQL Server count of depts in the users domain Let QueryDeptDomain = "SELECT COUNT(*) FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" For y = 0 To 0 Let SQLquery = QueryDeptDomain Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomainCounter = RecSet.Fields.Item(0).Value 'Populate value into field Next y 'Build list of depts within the user's domain for use in QueryCommandText statement ReDim DeptDomain(0 To DeptDomainCounter) As String Let QueryDeptDomain = "SELECT DEPT FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" Let SQLquery = QueryDeptDomain For z = 0 To DeptDomainCounter Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomain(z) = RecSet.Fields.Item(0).Value 'Populate value into field Select Case z Case 0 Let DeptDomainList = "'" & DeptDomain(z) & "'" 'Populate value into field Case Is 0 Let DeptDomainList = DeptDomainList & ", '" & DeptDomain(z) & "'" 'Populate value into field Case Else DeptDomainList = MsgBox("Something Didn't Work", vbOKOnly, "Alert") End Select Next z RecSet.Close Conn.Close 'Build main query based on user data Let QueryCommandText = "SELECT HBM_PERSNL.EMPLOYEE_CODE as EmpID, HBM_PERSNL.EMPLOYEE_NAME as EmpName," & " " & _ "HBM_PERSNL.OFFC as Offc, HBM_PERSNL.DEPT as Dept, HBM_PERSNL.LOCATION as Loc," & " " & _ "HBM_PERSNL.LOGIN as Login, HBM_PERSNL.PHONE_NO as Phone, HBM_PERSNL.POSITION as Position," & " " & _ "HBL_PERSNL_TYPE.PERSNL_TYP_CODE as TypeID, HBL_PERSNL_TYPE.PERSNL_TYP_DESC as TypeName," & " " & _ "TBM_PERSNL.RANK_CODE as Rank, TBM_PERSNL.PARTIME_PCNT as FTE" & " " & _ "FROM (dbo.HBM_PERSNL INNER JOIN HBL_PERSNL_TYPE ON" & " " & _ "dbo.HBM_PERSNL.PERSNL_TYP_CODE = HBL_PERSNL_TYPE.PERSNL_TYP_CODE)" & " " & _ "INNER JOIN TBM_PERSNL ON TBM_PERSNL.EMPL_UNO = dbo.HBM_PERSNL.EMPL_UNO" & " " & _ "WHERE HBM_PERSNL.INACTIVE='N' and HBM_PERSNL.PERSNL_TYP_CODE NOT IN ('PERKI','RESR')" & " " & _ "and HBM_PERSNL.LOGIN NOT IN ('','15REC','ZZZZA','EVENTS','SPALA','PZZZX','DCGU 1','INTAPPADMIN','LAGU1','TECHS','DR0NE')" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'%TEMP%' and HBM_PERSNL.LOGIN NOT LIKE'TRANS%'" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'TRON%' and HBM_PERSNL.LOGIN NOT LIKE'POGU%'" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'BIT%' and HBM_PERSNL.LOGIN NOT LIKE'DPC%'" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'PERK%' and HBM_PERSNL.LOGIN NOT LIKE'CMS%'" & " " & _ "and HBM_PERSNL.DEPT IN('" & DeptDomainList & "') " & _ "ORDER BY HBM_PERSNL.DEPT, HBM_PERSNL.EMPLOYEE_NAME" 'Import target data via Excel's native QueryTables.Add functionality using QueryCommandText Range(Range("A5"), ActiveCell.SpecialCells(xlLastCell)).Delete With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=seassql08;Description=seassql08;UID=admi nistrator;PWD= [*****];APP=Microsoft Office 2003;WSID=SEAD502366;Network=DBMSSOCN;Address=se" _ ), Array("assql08,1433")), Destination:=Range("A5")) .CommandText = QueryCommandText .Name = "PGMembership from seassql08" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With 'Sort result data by office, department, & employee name Range("A5", ActiveCell.SpecialCells(xlLastCell)).Sort _ Key1:=ActiveSheet.Columns("C"), Order1:=xlAscending, _ Key2:=ActiveSheet.Columns("D"), Order2:=xlAscending, _ Key3:=ActiveSheet.Columns("A"), Order2:=xlAscending, _ Header:=xlYes End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stubborn Array won't Populate from single ADODB.Connection Query
You need to use a movenext. I still think you should fix your DQL like I
recommended last time so you don't have to loop 5 times. It significantly slows down the code. Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Do until RecSet.Eof For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x RecSet.MobeNext Loop "Damian Carrillo" wrote: I previously posted that I could not figure out how to populate VBA Variables from ADODB.Connection results. I was able to get some help on figuring that out. That time I was running multiple queries that each had only one result returned, so I incremented the array, populating it with: For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x I've reused this same base project code several times over... But now I have one query that has multiple results returned. In the test I'm doing I get 13 records returned, each one consisting of a single (DEPT) field. And for the life of me I can't figure out how to get anything besides the first record. I am using the values for two separate tasks, so I decided I wanted both an array populated with the values, and a string that can be used as part of the WHERE HBM_DEPT IN (*) portion of a larger query sent back later in the code. 'Find SQL Server data for the active user. This data is used in the main QueryCommandText statement Set Conn = New ADODB.Connection Conn.Open "seassql08", "administrator", "[*****]" For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x 'Find SQL Server count of depts in the users domain Let QueryDeptDomain = "SELECT COUNT(*) FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" For y = 0 To 0 Let SQLquery = QueryDeptDomain Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomainCounter = RecSet.Fields.Item(0).Value 'Populate value into field Next y 'Build list of depts within the user's domain for use in QueryCommandText statement ReDim DeptDomain(0 To DeptDomainCounter) As String Let QueryDeptDomain = "SELECT DEPT FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" For z = 0 To DeptDomainCounter Let SQLquery = QueryDeptDomain Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomain(z) = RecSet.Fields.Item(0).Value 'Populate value into field Select Case z Case 0 Let DeptDomainList = "'" & DeptDomain(z) & "'" 'Populate value into field Case Is 0 Let DeptDomainList = DeptDomainList & ", '" & DeptDomain(z) & "'" 'Populate value into field Case Else DeptDomainList = MsgBox("Something Didn't Work", vbOKOnly, "Alert") End Select Next z RecSet.Close Conn.Close And if you need to see the larger context of what I'm doing, including the variable names and subsequent queries, see the full code below. If you're going to go that far, please note that some of the attempts to make the array populate here have caused the subsequent QueryTables.Add method to throw an invald SQL Statement error on the last property ".Refresh BackgroundQuery:=False" in the WITH block. This may not be a relevent issue since addressing the population of the array might nullify any problems here. '-------------------------------------------------------------- Option Explicit Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Dim UserName As String '-------------------------------------------------------------- Function CurrentUserName() As String UserName = Space(255) GetUserName UserName, 255 UserName = Left(UserName, InStr(UserName, Chr(0)) - 1) CurrentUserName = UserName End Function '-------------------------------------------------------------- Sub RetrieveNPGHeadcountFromCMSLive() 'Import Data From SQL Server to populate headcount table based on the office 'and department of the user opening the spreadsheet as populated from QueryCommandText. Dim Conn As ADODB.Connection 'SQL Server Connection Dim RecSet As ADODB.Recordset 'SQL Server RecordSet Dim SQLquery As String 'SQL Server Query holder Dim ActiveUser As String 'Holds login information for looking up other values Dim QueryUserInfo(0 To 5) As Variant 'Field Queries For: EmpID, EmpName, Offc, Dept, Login, Position Dim UserInfo(0 To 5) As String 'Field Values: EmpID, EmpName, Offc, Dept, Login, Position Dim QueryCommandText As String 'SQL Server query to retrieve ultimate target data set Dim QueryDeptDomain As String 'SQL Server query to retrieve number of depts under active user's domain of control Dim DeptDomainCounter As Integer 'Resultant dept list count Dim DeptDomainList As String 'Resultant dept list Dim x As Integer, y As Integer, z As Integer 'Incremental counters to populate QueryUserInfo and UserInfo arrays Let ActiveUser = "HOWEM" 'Let ActiveUser = CurrentUserName Let QueryUserInfo(0) = "SELECT EMPLOYEE_CODE FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(1) = "SELECT EMPLOYEE_NAME FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(2) = "SELECT OFFC FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(3) = "SELECT DEPT FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(4) = "SELECT LOGIN FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let QueryUserInfo(5) = "SELECT POSITION FROM HBM_PERSNL WHERE LOGIN =" & " '" & ActiveUser & "'" Let x = 0 And y = 0 And z = 0 'Find SQL Server data for the active user. This data is used in the main QueryCommandText statement Set Conn = New ADODB.Connection Conn.Open "seassql08", "administrator", "[*****]" For x = 0 To 5 Let SQLquery = QueryUserInfo(x) 'Define source query Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field Next x 'Find SQL Server count of depts in the users domain Let QueryDeptDomain = "SELECT COUNT(*) FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" For y = 0 To 0 Let SQLquery = QueryDeptDomain Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomainCounter = RecSet.Fields.Item(0).Value 'Populate value into field Next y 'Build list of depts within the user's domain for use in QueryCommandText statement ReDim DeptDomain(0 To DeptDomainCounter) As String Let QueryDeptDomain = "SELECT DEPT FROM _DeptHeadsDetail WHERE Position='BusManager' and Employee_Code='" & UserInfo(0) & "' and DEPT is not null" Let SQLquery = QueryDeptDomain For z = 0 To DeptDomainCounter Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value Let DeptDomain(z) = RecSet.Fields.Item(0).Value 'Populate value into field Select Case z Case 0 Let DeptDomainList = "'" & DeptDomain(z) & "'" 'Populate value into field Case Is 0 Let DeptDomainList = DeptDomainList & ", '" & DeptDomain(z) & "'" 'Populate value into field Case Else DeptDomainList = MsgBox("Something Didn't Work", vbOKOnly, "Alert") End Select Next z RecSet.Close Conn.Close 'Build main query based on user data Let QueryCommandText = "SELECT HBM_PERSNL.EMPLOYEE_CODE as EmpID, HBM_PERSNL.EMPLOYEE_NAME as EmpName," & " " & _ "HBM_PERSNL.OFFC as Offc, HBM_PERSNL.DEPT as Dept, HBM_PERSNL.LOCATION as Loc," & " " & _ "HBM_PERSNL.LOGIN as Login, HBM_PERSNL.PHONE_NO as Phone, HBM_PERSNL.POSITION as Position," & " " & _ "HBL_PERSNL_TYPE.PERSNL_TYP_CODE as TypeID, HBL_PERSNL_TYPE.PERSNL_TYP_DESC as TypeName," & " " & _ "TBM_PERSNL.RANK_CODE as Rank, TBM_PERSNL.PARTIME_PCNT as FTE" & " " & _ "FROM (dbo.HBM_PERSNL INNER JOIN HBL_PERSNL_TYPE ON" & " " & _ "dbo.HBM_PERSNL.PERSNL_TYP_CODE = HBL_PERSNL_TYPE.PERSNL_TYP_CODE)" & " " & _ "INNER JOIN TBM_PERSNL ON TBM_PERSNL.EMPL_UNO = dbo.HBM_PERSNL.EMPL_UNO" & " " & _ "WHERE HBM_PERSNL.INACTIVE='N' and HBM_PERSNL.PERSNL_TYP_CODE NOT IN ('PERKI','RESR')" & " " & _ "and HBM_PERSNL.LOGIN NOT IN ('','15REC','ZZZZA','EVENTS','SPALA','PZZZX','DCGU 1','INTAPPADMIN','LAGU1','TECHS','DR0NE')" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'%TEMP%' and HBM_PERSNL.LOGIN NOT LIKE'TRANS%'" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'TRON%' and HBM_PERSNL.LOGIN NOT LIKE'POGU%'" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'BIT%' and HBM_PERSNL.LOGIN NOT LIKE'DPC%'" & " " & _ "and HBM_PERSNL.LOGIN NOT LIKE'PERK%' and HBM_PERSNL.LOGIN NOT LIKE'CMS%'" & " " & _ "and HBM_PERSNL.DEPT IN('" & DeptDomainList & "') " & _ "ORDER BY HBM_PERSNL.DEPT, HBM_PERSNL.EMPLOYEE_NAME" 'Import target data via Excel's native QueryTables.Add functionality using QueryCommandText Range(Range("A5"), ActiveCell.SpecialCells(xlLastCell)).Delete With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=seassql08;Description=seassql08;UID=admi nistrator;PWD= [*****];APP=Microsoft Office 2003;WSID=SEAD502366;Network=DBMSSOCN;Address=se" _ ), Array("assql08,1433")), Destination:=Range("A5")) .CommandText = QueryCommandText .Name = "PGMembership from seassql08" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With 'Sort result data by office, department, & employee name Range("A5", ActiveCell.SpecialCells(xlLastCell)).Sort _ Key1:=ActiveSheet.Columns("C"), Order1:=xlAscending, _ Key2:=ActiveSheet.Columns("D"), Order2:=xlAscending, _ Key3:=ActiveSheet.Columns("A"), Order2:=xlAscending, _ Header:=xlYes End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stubborn Array won't Populate from single ADODB.Connection Query
Sorry Joel, this didn't work either. Its not a valid property.
On Aug 25, 1:31*am, Joel wrote: You need to use a movenext. *I still think you should fix your DQL like I recommended last time so you don't have to loop 5 times. *It significantly slows down the code. * *Let SQLquery = QueryUserInfo(x) 'Define source query * *Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value * *Do until RecSet.Eof * * * *For x = 0 To 5 * * * * * *Let SQLquery = QueryUserInfo(x) 'Define source query * * * * * *Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value * * * * * *Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field * * * *Next x * * * *RecSet.MobeNext * *Loop |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stubborn Array won't Populate from single ADODB.Connection Query
Never mind, there was a misspelling in the code. I'll give this
another try and report back shortly. I would try to consolidate the queries into one, but I didn't understand how what you had written before would accomplish the objective I was trying to reach. It was basically creating one long query string consisting of several partial queries, right? On Aug 25, 1:31*am, Joel wrote: You need to use a movenext. *I still think you should fix your DQL like I recommended last time so you don't have to loop 5 times. *It significantly slows down the code. * *Let SQLquery = QueryUserInfo(x) 'Define source query * *Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value * *Do until RecSet.Eof * * * *For x = 0 To 5 * * * * * *Let SQLquery = QueryUserInfo(x) 'Define source query * * * * * *Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value * * * * * *Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field * * * *Next x * * * *RecSet.MobeNext * *Loop |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stubborn Array won't Populate from single ADODB.Connection Query
Thanks Joel, the MOVENEXT method worked perfectly. I really
appreciate your assistance. On Aug 25, 9:17*am, Damian Carrillo wrote: Never mind, there was a misspelling in the code. *I'll give this another try and report back shortly. *I would try to consolidate the queries into one, but I didn't understand how what you had written before would accomplish the objective I was trying to reach. It was basically creating one long query string consisting of several partial queries, right? On Aug 25, 1:31*am, Joel wrote: You need to use a movenext. *I still think you should fix your DQL like I recommended last time so you don't have to loop 5 times. *It significantly slows down the code. * *Let SQLquery = QueryUserInfo(x) 'Define source query * *Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value * *Do until RecSet.Eof * * * *For x = 0 To 5 * * * * * *Let SQLquery = QueryUserInfo(x) 'Define source query * * * * * *Set RecSet = Conn.Execute(SQLquery, , 1) 'Retrieve query value * * * * * *Let UserInfo(x) = RecSet.Fields.Item(0).Value 'Populate value into field * * * *Next x * * * *RecSet.MobeNext * *Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADODB Connection | Excel Worksheet Functions | |||
How to populate VBA Variables from ADODB.Connection Results? | Excel Programming | |||
Set cnn = New ADODB.Connection | Excel Programming | |||
ADODB.Connection | Excel Programming | |||
ADODB.Connection | Excel Programming |