Access table data

[carbon_13]

I'm kinda new to programming and have an easy enough question that I can't seem to figure out. I have a table (tblData) in Access with lets say 4 or more rows and 3 or more columns,

ColumnA ColumnB ColumnC
---23------- 12------- 23
---33------- 13------- 22
---43------- 14------- 33
---53------- 15------- 44

Now in visual basic I am ultimately taking the data from the table and looping through each row so I can manipulate the data. I've seen Recordset used to create tables but I kinda wanna use the reverse of this, it seems. I need to essentially get the first row from the table, assign Variable1 = # from ColumnA ; Variable2 = # from ColumnB; Variable3 = # from ColumnC. I would loop through each row and do this. I would then take Variable1-3 and manipulate them at my own discretion. So my loop would be something like this, (And I'm not saying it's right by any means, I would rather think that I at least gave it a shot.)

Dim MyData As Recordset

Set MyData = CurrentDb.OpenRecordset("tblData")

MyData.MoveFirst
  Do Until MyData.EOF
     Variable1 = MyData!ColumnA
     Variable2 = MyData!ColumnB
     Variable3 = MyData!ColumnC

    'Somewhere in here I would use Variable1-3 and manipulate them how I choose
    'and then put them in a different table or something else
   Loop

Mydata.Close

Is this the right way to call individual rows from the table? Also, is there a way to call, lets say row i, from the table so that I can loop through it that way, almost like and index system. For example

For i = 1 to count(the rows in the table) 'I know this isn't right, need help : )
     Variable1 = MyData!ColumnA.Index(i)
     Variable2 = MyData!ColumnB.Index(i)
     Variable3 = MyData!ColumnC.Index(i)

Thanks a bunch

 

[NickHa]

Your first example is nearly right – you just need to add MyData.MoveNext just in front of the Loop statement.
In your second example, you would have to do MyData.MoveLast then MyData.RecordCount to get the number of records before MyData.MoveFirst to return to the start of the recordset. This approach is clumsy for what you need to do, but can be useful in more complex logic. BTW, I suggest using rstData as your recordset name, so it is clearly associated with tblData in the code.

 

[vbaInet]

I suspect your table is not properly normalised. What do ColumnA, ColumnB and ColumnC represent?

And what kind of manipulation do you want to do? I would imagine you will be able to do it more effeciently in a query.

 

[carbon_13]

Thanks for the advice NickHa. I don't quite understand why i have to MyData.MoveLast then MyData.RecordCount to get the number of records. Does it matter what record i'm at for MyData.RecordCount to work correctly, or maybe I misunderstood the direction you were trying to lead me to

An vbaInet, I am not sure what you mean by properly normalized. The columns will be names and then numbers in the others. Well I am working from an existing code and so I don't have much choice to do a query, etc. Like I said I am fairly new to Access/VB anyways. I'm quite enjoying to learn VB at the moment anyways. Thanks for your input.

 

[NickHa]

When you use RecordCount, it gives the number of records encountered so far. You have to use MoveLast in order to get the RecordCount set to the highest record number in the recordset (this is not recommended for large recordsets, as it can affect performance). Having positioned the recordset at its end and retrieved the RecordCount, you need to reposition it at the start if you want to processsequentially from the first record.

 

[vbaInet]

So...

what kind of manipulation do you want to do?

?? Tell us exactly how you want to manipulate it.