simple function record into array

[Geagleeye]

Hi,
Im a rookie in vba, and therefor got a problem.
i do have folwing records
F G
---------------
2 3
2 5
6 7
2 3
and those i realy wanto plug into a array, but cant remember how to do
this in VBA
can any one help me ? plzz.

[rkc]

Geagleeye wrote:

F G
---------------
2 3
2 5
6 7
2 3
and those i realy wanto plug into a array, but cant remember how to do
this in VBA
can any one help me ? plzz.

Both DAO and ADO have a GetRows method of the recordset object.
GetRows retrieves a specified number or rows from a recordset
and places them into an 2-D array.

[Anthony England]

"Geagleeye" <Ur********@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi,
Im a rookie in vba, and therefor got a problem.
i do have folwing records
F G
---------------
2 3
2 5
6 7
2 3
and those i realy wanto plug into a array, but cant remember how to do
this in VBA
can any one help me ? plzz.

I assume you want to get this data from a table in the database and that you
are not asking about how to get those 'hard-coded' values into a
2-dimensional array.
As rkc, points out you can use DAO or ADO coding. The ADO version is shown
here since I do not have to specify the number of rows returned - by default
all records are retrieved:
Public Sub TestArray()

On Error GoTo Err_Handler

Dim avar As Variant
Dim lngCount As Long

avar = CreateArray("SELECT F, G FROM MyTable")

For lngCount = LBound(avar) To UBound(avar)
MsgBox CStr(avar(0, lngCount))
Next lngCount

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Public Function CreateArray(strSQL As String) As Variant

On Error GoTo Err_Handler

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open strSQL, CurrentProject.Connection

CreateArray = rst.GetRows()

Exit_Handler:

If Not rst Is Nothing Then
If rst.State <> adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

[Lyle Fairfield]

For lngCount = LBound(avar, 2) To UBound(avar, 2)

?

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

For lngCount = LBound(avar, 2) To UBound(avar, 2)

?

When I saw that you had replied to this post I was so sure you were going to
dispute the need to explicitly close and set to nothing the ADO recordset -
I hadn't spotted that somewhat more fundamental mistake.
Even worse, I had tried the function with, not one, but two test rows in my
table (just to double check it) and under those conditions it performed
great, but I now concede that if the table has more than two rows, your
version would be better.

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

For lngCount = LBound(avar, 2) To UBound(avar, 2)

?

When I saw that you had replied to this post I was so sure you were going to
dispute the need to explicitly close and set to nothing the ADO recordset -
I hadn't spotted that somewhat more fundamental mistake.
Even worse, I had tried the function with, not one, but two test rows in my
table (just to double check it) and under those conditions it performed
great, but I now concede that if the table has more than two rows, your
version would be better.

[Geagleeye]

Hi guys. Thanky for all help :)

But i need som explanation to understand all this. :(

this is the code :

avar = CreateArray("SELECT X, A, B, C, D, E, F, G Tmp")

For lngCount = LBound(avar, 2) To UBound(avar, 2)
' MsgBox CStr(avar(0, lngCount))
Next lngCount


And how can i read data from the B234 ? or c234 and how can i write
into F234 ?

[Anthony England]

"Geagleeye" <Ur********@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi guys. Thanky for all help :)

But i need som explanation to understand all this. :(

this is the code :

avar = CreateArray("SELECT X, A, B, C, D, E, F, G Tmp")

For lngCount = LBound(avar, 2) To UBound(avar, 2)
' MsgBox CStr(avar(0, lngCount))
Next lngCount


And how can i read data from the B234 ? or c234 and how can i write
into F234 ?

We are happy to help but you need to explain something:
Should the data come from database tables or will you write the values in
code?
Are you sure you need to use arrays? Perhaps if you explained what you are
doing - the answer might be to use recordsets not arrays.

[Geagleeye]

first of all, i would like say thanks to everyone for helpin me (a
rookie in vb)

the problem is that i have som code where im using to many
rec.movelast, rec.moveprevius, do until rec.eof and so on for only one
table.
my table is 8 * 1100 and it takes 4 min to get true the code.

Therefor i supposed that the problem is im reading and witting to much
to the table in my database, so why get the data into a array and do
everything there
and when finnished i could get i all into the table again.

my opinion is that a array is much faster end a recordset ? is it ?????
if you like to look at the code wil glady post it in here , and maybe
you could see if you could transfer to a array

[Geagleeye]

Private Sub btn_interpolere_Click()
Dim x As Double
Dim rec As Recordset
Dim qry As QueryDef
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM TmpGraf "
Set qry = dbs.CreateQueryDef("", strSQL)
Set rec = qry.OpenRecordset()
For i = 2 To 8
rec.MoveFirst
Do Until Not IsNull(rec.Fields("mpa" & i))
rec.MoveNext
Loop
Do Until rec.Fields("mpa" & i) = 0
If IsNull(rec.Fields("mpa" & i)) Then
x = rec.Fields!Procent
DeltaY = fmmDeltaY(y1, x1, x, i)
DeltaX = fmmDeltaX(x2, y2, x, i)
res = (y2 - y1) / (x2 - x1) * (x - x1) + y1
rec.MoveFirst
While Not rec.Fields!Procent = x
rec.MoveNext
Wend
rec.Edit
rec.Fields("mpa" & i) = Format(res, "#0.000")
rec.Update
End If
rec.MoveNext
Loop
Next i
MsgBox "Interpolering er fuldført "
rec.Close
Set qry = Nothing
End Sub
Function fmmDeltaY(y1, x1, x, i)
Dim rec As Recordset
Dim qry As QueryDef
Dim strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
strSQL = "SELECT * FROM TmpGraf "
Set qry = dbs.CreateQueryDef("", strSQL)
Set rec = qry.OpenRecordset()
While Not rec.Fields!Procent = x
rec.MoveNext
Wend
While IsNull(rec.Fields("mpa" & i))
rec.MovePrevious
Wend
y1 = rec.Fields("mpa" & i)
x1 = rec.Fields!Procent
rec.Close
Set qms = Nothing
End Function
Function fmmDeltaX(x2, y2, x, i)
Dim rec As Recordset
Dim qry As QueryDef
Dim strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
strSQL = "SELECT * FROM TmpGraf "
Set qry = dbs.CreateQueryDef("", strSQL)
Set rec = qry.OpenRecordset()
rec.MoveFirst
While Not rec.Fields!Procent = x
rec.MoveNext
Wend
While IsNull(rec.Fields("mpa" & i))
rec.MoveNext
Wend
x2 = rec.Fields!Procent
y2 = rec.Fields("mpa" & i)
rec.Close
Set qms = Nothing
End Function

[Anthony England]

"Geagleeye" <Ur********@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Private Sub btn_interpolere_Click()
Dim x As Double
Dim rec As Recordset
Dim qry As QueryDef
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM TmpGraf "
Set qry = dbs.CreateQueryDef("", strSQL)
Set rec = qry.OpenRecordset()
For i = 2 To 8
rec.MoveFirst
Do Until Not IsNull(rec.Fields("mpa" & i))
rec.MoveNext
Loop
Do Until rec.Fields("mpa" & i) = 0
If IsNull(rec.Fields("mpa" & i)) Then
x = rec.Fields!Procent
DeltaY = fmmDeltaY(y1, x1, x, i)
DeltaX = fmmDeltaX(x2, y2, x, i)
res = (y2 - y1) / (x2 - x1) * (x - x1) + y1
rec.MoveFirst
While Not rec.Fields!Procent = x
rec.MoveNext
Wend
rec.Edit
rec.Fields("mpa" & i) = Format(res, "#0.000")
rec.Update
End If
rec.MoveNext
Loop
Next i
MsgBox "Interpolering er fuldført "
rec.Close
Set qry = Nothing
End Sub
Function fmmDeltaY(y1, x1, x, i)
Dim rec As Recordset
Dim qry As QueryDef
Dim strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
strSQL = "SELECT * FROM TmpGraf "
Set qry = dbs.CreateQueryDef("", strSQL)
Set rec = qry.OpenRecordset()
While Not rec.Fields!Procent = x
rec.MoveNext
Wend
While IsNull(rec.Fields("mpa" & i))
rec.MovePrevious
Wend
y1 = rec.Fields("mpa" & i)
x1 = rec.Fields!Procent
rec.Close
Set qms = Nothing
End Function
Function fmmDeltaX(x2, y2, x, i)
Dim rec As Recordset
Dim qry As QueryDef
Dim strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
strSQL = "SELECT * FROM TmpGraf "
Set qry = dbs.CreateQueryDef("", strSQL)
Set rec = qry.OpenRecordset()
rec.MoveFirst
While Not rec.Fields!Procent = x
rec.MoveNext
Wend
While IsNull(rec.Fields("mpa" & i))
rec.MoveNext
Wend
x2 = rec.Fields!Procent
y2 = rec.Fields("mpa" & i)
rec.Close
Set qms = Nothing
End Function


OK - first thing to say is what you are trying to do - and judging from
previous posts it is this:
"Calculate from a set of data stored in a table, some missing elements by
using interpolation and then write these values back to the table"

Now I don't know whether using an array will be faster in the end. Sure,
they are faster to work with generally but you have to load it from the
table, edit the data and then write it back to the table. It might make
little difference to use a recordset which you can edit directly - I suppose
it depends on your algorithm. One thing is for sure, there is definitely
too much moving around the recordsets and opening and closing new recordsets
based on the whole table. This must be wrong!

If I knew exactly what the interpolation was supposed to be for for example
a data table with 3 columns to identify measurement number, x value, y value
and that you wish to find missing y values. Perhaps also you can be sure
that there are never 2 or more consecutive missing values and that you wish
to use simple linear interpolation between the two points either side of the
missing value. Perhaps then we could offer a solution.

However, you don't make it that clear - your recordset has 8 or more
fields??? Secondly, it is a while since I have done any interpolation of
data (I seem to remember fitting straight lines, curves, polynomials, etc
while I was in college, but that was a while ago)

I thought this was a straight-forward array question which I could answer,
but I'm not sure I have the time at the minute for anything harder. Perhaps
someone else will.

[Geagleeye]

The end and the sollution.

I made all my code inside an array instead of records.

Before the changes it toke about 4 min, now 3 sec.
so there is loth diffrence, because we are not writting to disk, and
instead we are
writting to memory.

so would to thank all