referencing a cell of an Access table with VB

Posted by David Galvin on September 26, 2001 1:32 PM

Any help would be greatly appreciated.

1) Suppose I have a table "myTable" with columns "Column1" and "Column2," and I want to access the value stored in the third row of column two - how do I do it? (in visual basic).

2) Suppose I want to copy the whole of "myTable" into an excel spreadsheet, with with the TopLeft cell of myTable going into sheets("sheet2").cells(B:3). Again, How?

3) how can I use VB to edit the contents of cells of an Access table? (eg I want to change the value of row3 in column1 of mytable to read "17"

Thanks again!!

David

Posted by Dank on September 27, 2001 12:49 AM

There are several ways you could do this. Here's one suggestion.

Open the VB Editor (Alt+F11)
Click Tools, References and choose Microsoft DAO 3.x Object Library. This will be 3.5 for Access 97 and 3.6 for Access 2000.

Now use a procedure like this:-

Sub PlayWithAccess()
Dim DAOdb As DAO.Database
Dim DAOrs As DAO.Recordset

'Open the database, change this to suit your needs
Set DAOdb = DAO.OpenDatabase("C:\temp\test.mdb")

'Now create a recordset of all records in table myTable
Set DAOrs = DAOdb.OpenRecordset("myTable", dbOpenTable)

'Retrieve the third record, field 2. This means you have to move forward 2
'records and then extract field(1) because it starts at Field(0).
DAOrs.Move 2
MsgBox "The value of record 3, field 2 is " & DAOrs.Fields(1).Value

'Now copy the whole table to sheet2, B3
DAOrs.MoveFirst
Sheets("sheet2").Range("B3").CopyFromRecordset DAOrs

'Now update record 3, field 1 to value 17
DAOrs.MoveFirst
DAOrs.Move 2
DAOrs.Edit
DAOrs.Fields(0).Value = 17
DAOrs.Update

'Close and clean
DAOrs.Close
DAOdb.Close
Set DAOrs = Nothing
Set DAOdb = Nothing
End Sub

The main point to understand is that when you create a recordset (in this case all of the records in your table) is that at any time you're only looking at one record. This is why you see the various Move methods being used as you need to move to various places depending on what you want to do.

This code works fine on my machine (Access and Excel 2000) but may not on earlier versions. Let me know if you have any problems.

Regards,
Daniel.