Populate an array from record set
This is just a question out of interest....
Is there a better way to populate the arrays below then i dodgey way i am doing it thus far???
Function myForecast(myValue, intArrayY, intArrayX) As Double
myForecast = Excel.WorksheetFunction.Fo
End Function
Function getValue(myValue As Double)
Dim intArrayY() As Double
Dim intArrayX() As Double
Dim i As Integer
Dim strSQLY As String
Dim strSQLX As String
Dim rstX As Recordset
Dim rstY As Recordset
Dim dbs As Database
Set dbs = CurrentDb
strSQLY = "SELECT tblYourTable.SQ FROM tblYOurTable;"
strSQLX = "SELECT tblYourTable.Age FROM tblYOurTable;"
Set rstX = dbs.OpenRecordset(strSQLX,
Set rstY = dbs.OpenRecordset(strSQLY,
'populate x array
rstX.MoveFirst
rstX.MoveLast
'make sure we are at the first record
rstX.MoveFirst
'initalise the array with the number of entries in the recordset
ReDim intArrayX(rstX.RecordCount
'looping mayhems - add the record data into the array
For i = 1 To rstX.RecordCount
intArrayX(i - 1) = rstX(0)
rstX.MoveNext
Next
'populate y array
rstY.MoveFirst
rstY.MoveLast
'make sure we are at the first record
rstY.MoveFirst
'initalise the array with the number of entries in the recordset
ReDim intArrayY(rstY.RecordCount
'looping mayhems - add the record data into the array
For i = 1 To rstY.RecordCount
intArrayY(i - 1) = rstY(0)
rstY.MoveNext
Next
'get the value
getValue = myForecast(myValue, intArrayY, intArrayX)
End Function
Cheers,
Flav's
Is there a better way to populate the arrays below then i dodgey way i am doing it thus far???
Function myForecast(myValue, intArrayY, intArrayX) As Double
myForecast = Excel.WorksheetFunction.Fo
End Function
Function getValue(myValue As Double)
Dim intArrayY() As Double
Dim intArrayX() As Double
Dim i As Integer
Dim strSQLY As String
Dim strSQLX As String
Dim rstX As Recordset
Dim rstY As Recordset
Dim dbs As Database
Set dbs = CurrentDb
strSQLY = "SELECT tblYourTable.SQ FROM tblYOurTable;"
strSQLX = "SELECT tblYourTable.Age FROM tblYOurTable;"
Set rstX = dbs.OpenRecordset(strSQLX,
Set rstY = dbs.OpenRecordset(strSQLY,
'populate x array
rstX.MoveFirst
rstX.MoveLast
'make sure we are at the first record
rstX.MoveFirst
'initalise the array with the number of entries in the recordset
ReDim intArrayX(rstX.RecordCount
'looping mayhems - add the record data into the array
For i = 1 To rstX.RecordCount
intArrayX(i - 1) = rstX(0)
rstX.MoveNext
Next
'populate y array
rstY.MoveFirst
rstY.MoveLast
'make sure we are at the first record
rstY.MoveFirst
'initalise the array with the number of entries in the recordset
ReDim intArrayY(rstY.RecordCount
'looping mayhems - add the record data into the array
For i = 1 To rstY.RecordCount
intArrayY(i - 1) = rstY(0)
rstY.MoveNext
Next
'get the value
getValue = myForecast(myValue, intArrayY, intArrayX)
End Function
Cheers,
Flav's
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Heer...
getting an error :
Can't assign to array, Each element of an array must have its value assigned individually. This error has the following causes and solutions:
You tried to assign a whole array to another array. For example, if Arr1 is an array and Arr2 is another array, the following two assignments are both invalid:
Arr1 = Arr2 ' Invalid assignment.
Arr1() = Arr2() ' Invalid assignment.
whixh is what i was doing...
Recommends i loop again...
getting an error :
Can't assign to array, Each element of an array must have its value assigned individually. This error has the following causes and solutions:
You tried to assign a whole array to another array. For example, if Arr1 is an array and Arr2 is another array, the following two assignments are both invalid:
Arr1 = Arr2 ' Invalid assignment.
Arr1() = Arr2() ' Invalid assignment.
whixh is what i was doing...
Recommends i loop again...
also getting the error with the fleshed out example I posted?
ASKER
Yep....
My bad left your () in the declaration of the array, it should just be a variant. Corrected:
Dim intArrayX As Variant 'int does not make much sense anymore
Dim strSQLX As String
Dim rstX As Recordset
strSQLX = "SELECT tblYourTable.Age FROM tblYOurTable;"
Set rstX = currentDB.OpenRecordset(st
'To get the proper record count
rstX.MoveLast
rstX.MoveFirst
intArrayX = rstX.GetRows(rstX.RecordCo
Dim intArrayX As Variant 'int does not make much sense anymore
Dim strSQLX As String
Dim rstX As Recordset
strSQLX = "SELECT tblYourTable.Age FROM tblYOurTable;"
Set rstX = currentDB.OpenRecordset(st
'To get the proper record count
rstX.MoveLast
rstX.MoveFirst
intArrayX = rstX.GetRows(rstX.RecordCo
ASKER
NOpe need the "()" , Type mismatch...
Put them back and it still doesnt like the arrray...
It may not be posible,....
Put them back and it still doesnt like the arrray...
It may not be posible,....
ASKER
Ok, finally got it working, however, now that its not a double the Excel function doesnt like it
Function myForecast(myValue, intArrayY, intArrayX) As Double
myForecast = Excel.WorksheetFunction.Fo
End Function
Function myForecast(myValue, intArrayY, intArrayX) As Double
myForecast = Excel.WorksheetFunction.Fo
End Function
Have tested this successfully on my machine and have used getrows before. Where do you get the type mismatch?
Probably when you feed the arrays to Excel, the array format returned by getrows is a bit odd (two dimensional). Have never used it to feed Excel.
Fromt the help:
Remarks
Use the GetRows method to copy records from a Recordset. GetRows returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number. For example, intField represents the field, and intRecord identifies the row number:
avarRecords(intField, intRecord)
To get the first field value in the second row returned, use code like the following:
field1 = avarRecords(0,1)
To get the second field value in the first row, use code like the following:
field2 = avarRecords(1,0)
Probably when you feed the arrays to Excel, the array format returned by getrows is a bit odd (two dimensional). Have never used it to feed Excel.
Fromt the help:
Remarks
Use the GetRows method to copy records from a Recordset. GetRows returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number. For example, intField represents the field, and intRecord identifies the row number:
avarRecords(intField, intRecord)
To get the first field value in the second row returned, use code like the following:
field1 = avarRecords(0,1)
To get the second field value in the first row, use code like the following:
field2 = avarRecords(1,0)
ASKER
Its all fine now, except the excel bit...
Only really wanted to use it to use excel so i guess its a bit pointless....
Only really wanted to use it to use excel so i guess its a bit pointless....
Yep, now you can loop an array to feed another array instead of looping a recordset.
ASKER
Is that really that much faster??
I just did a test on 60,000 records and it took "bee's" to do it...
Just though it may be usefull to know... and one day it might
I just did a test on 60,000 records and it took "bee's" to do it...
Just though it may be usefull to know... and one day it might
Set varArray = recordset.GetRows (numrows)