Accessing records in VBA

[dfwelch]

I had posted this question to the wrong forum, but received one response that left me with more questions:

My Question
I've used Visual Basic with Excel to look through my spreadsheet and make changes to "records" based on certain criteria. The syntax I used was:
ActiveSheet.Cells(3, 14) = something
where this would refer to the 3rd row, 14th column in the active spreadsheet.

Is there a similar way for me to look at for testing purposes and then make changes to a certain field for certain records in Access using VB?

The Response
As far as I know there is not a way to go directly to a field in a table. You can however cycle through records and change the value.

An approach like this could work:


Dim rs As Recordset
Dim db As database
Set db = CurrentDb()
strSQL = "SELECT YourTable.YourField FROM YourTable;"
Set rs = db.OpenRecordset(strSQL)
For x=1 to 20
if x=20 then
rs.edit
rs!YourField= 100
rs.Update
end if
rs.MoveNext
next

This code will update the 20th record in the field "YourField" to 100.

So, I tried this, but the Dim db As database statement is not working. VB does not recognize database as a valid variable type. I've also tried DAO.database, to no avail.

 

[dyarwood]

Check the libraries you are using. Might need to turn on the DAO one. Had a similar thing myself

dyarwood