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]

Re: simple function record into array

Geagleeye wrote:
[color=blue]
> 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.[/color]

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]

Re: simple function record into array

"Geagleeye" <Uranium348@hot mail.com> wrote in message
news:1136896639 .819510.160140@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> 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.[/color]




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("SE LECT 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(str SQL 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]

Re: simple function record into array

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

?

[Anthony England]

Re: simple function record into array

"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:1136904262 .782265.297050@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> For lngCount = LBound(avar, 2) To UBound(avar, 2)
>
> ?[/color]


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]

Re: simple function record into array


"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:1136904262 .782265.297050@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> For lngCount = LBound(avar, 2) To UBound(avar, 2)
>
> ?[/color]


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]

Re: simple function record into array

Hi guys. Thanky for all help :)

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

this is the code :

avar = CreateArray("SE LECT 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]

Re: simple function record into array

"Geagleeye" <Uranium348@hot mail.com> wrote in message
news:1136969214 .647013.221800@ g47g2000cwa.goo glegroups.com.. .[color=blue]
> Hi guys. Thanky for all help :)
>
> But i need som explanation to understand all this. :(
>
> this is the code :
>
> avar = CreateArray("SE LECT 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 ?[/color]


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]

Re: simple function record into array

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]

Re: simple function record into array

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.CreateQuery Def("", strSQL)
Set rec = qry.OpenRecords et()


For i = 2 To 8


rec.MoveFirst
Do Until Not IsNull(rec.Fiel ds("mpa" & i))
rec.MoveNext
Loop


Do Until rec.Fields("mpa " & i) = 0
If IsNull(rec.Fiel ds("mpa" & i)) Then
x = rec.Fields!Proc ent
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!Proc ent = x
rec.MoveNext
Wend
rec.Edit
rec.Fields("mpa " & i) = Format(res, "#0.000")
rec.Update
End If
rec.MoveNext
Loop
Next i
MsgBox "Interpoler ing 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.CreateQuery Def("", strSQL)
Set rec = qry.OpenRecords et()


While Not rec.Fields!Proc ent = x
rec.MoveNext
Wend


While IsNull(rec.Fiel ds("mpa" & i))
rec.MovePreviou s
Wend


y1 = rec.Fields("mpa " & i)
x1 = rec.Fields!Proc ent


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.CreateQuery Def("", strSQL)
Set rec = qry.OpenRecords et()


rec.MoveFirst
While Not rec.Fields!Proc ent = x
rec.MoveNext
Wend


While IsNull(rec.Fiel ds("mpa" & i))
rec.MoveNext
Wend
x2 = rec.Fields!Proc ent
y2 = rec.Fields("mpa " & i)


rec.Close
Set qms = Nothing
End Function

[Anthony England]

Re: simple function record into array

"Geagleeye" <Uranium348@hot mail.com> wrote in message
news:1136973566 .788628.149700@ g47g2000cwa.goo glegroups.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.CreateQuery Def("", strSQL)
Set rec = qry.OpenRecords et()


For i = 2 To 8


rec.MoveFirst
Do Until Not IsNull(rec.Fiel ds("mpa" & i))
rec.MoveNext
Loop


Do Until rec.Fields("mpa " & i) = 0
If IsNull(rec.Fiel ds("mpa" & i)) Then
x = rec.Fields!Proc ent
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!Proc ent = x
rec.MoveNext
Wend
rec.Edit
rec.Fields("mpa " & i) = Format(res, "#0.000")
rec.Update
End If
rec.MoveNext
Loop
Next i
MsgBox "Interpoler ing 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.CreateQuery Def("", strSQL)
Set rec = qry.OpenRecords et()


While Not rec.Fields!Proc ent = x
rec.MoveNext
Wend


While IsNull(rec.Fiel ds("mpa" & i))
rec.MovePreviou s
Wend


y1 = rec.Fields("mpa " & i)
x1 = rec.Fields!Proc ent


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.CreateQuery Def("", strSQL)
Set rec = qry.OpenRecords et()


rec.MoveFirst
While Not rec.Fields!Proc ent = x
rec.MoveNext
Wend


While IsNull(rec.Fiel ds("mpa" & i))
rec.MoveNext
Wend
x2 = rec.Fields!Proc ent
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]

Re: simple function record into array

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