Recordsets? 1

[sozzer]

I'd be really grateful if someone could please explain to me how to reference records in an access table without using a form (using recordsets?).

I have been working with access for a couple of years now (with a bit of vba) and am fed up having to use forms to manipulate data, but all the examples i have seen appear complicated. I cant believe it's that hard, i just dont know how...

Many thanks

Ad

 

[Eupher]

Hi, sozzer:

Not sure what you mean by "without using a form." You can do all kinds of things with recordsets, but where are you going to hang the code to do them if not on a form?

Ken S.

 

[sozzer]

Hi Eupher

What I mean is that I dont want to have to place controls on a form just to reference fields in a recordsource (access table or sql).

For example, I want to be able to update Field1 in all records depending on what the value of Field2 is at any given time, without looping through the records on a continuous form, but by say, clicking a button on a form which performs the operation in the 'background'.

cheers

 

[Eupher]

Okay, I'm going to give you an example of how to do precisely that. This example uses DAO, so you'll need to set a reference to DAO if you're using Access 2K or later (let me know if you don't know how to do that):

Private Sub btnMyButton_Click()
Dim Rs As DAO.Recordset
Set Rs = Me.Recordset
Rs.MoveLast      'populate the recordset
Rs.MoveFirst     'perhaps unnecessary, but needed if using RecordCount property
While Not Rs.EOF
    Rs.Edit
    Rs!Field1 = Me!Field2
    Rs.Update
    Rs.MoveNext
Wend
Rs.Close
End Sub

Post back if you have questions. Do a search on 'recordset' and you will find lots of examples. The VBA help files are also pretty useful.

HTH,

Ken S.

 

[sozzer]

Eupher

That is just what i was looking for - many thanks. My brain has finally gone 'click' on this one and i am very grateful!

 

[redapples]

I prefer ADO to DAO - except for some very specific functions that ADO does not do. The reasons for this are two fold.
1. It is the newer platform therefore likely to be supported longer.
2. It is useful out side of VBA in ways that DAO is not.

ADO is, once you've used it a bit, pretty easy. To demonstrate an example :- (air code)

Dim rs as New ADODB.Recordset
Dim cnn as ADODB.Connection

set cnn = CurrentProject.Connection ' this is to all intent and purpose CurrentDB from DAO
rs.open "MyTable",cnn,3,3 'read write =  adOpenStatic, adLockOptimistic
'read only would be rs.open "MyTable",cnn,3,1
with rs
     .addnew
     !Field1 = "Field"
     !Field2 = 2
     .Update
     .AddNew Array(rs.Fields(0).Name,rs.Fields(1).name),Array("Field1",3)
'note there is no update with this use of the method.
     .movefirst
     !Field2 = 4
     .Update
     .Find "Field2 = 4" 'a failure to find a field will result in the recordset becomeing EOF true.  So I often follow a search with IF Not rs.EOF then
End with
rs.close
set cnn = nothing
set rs = nothing

You can also do some neat stuff with SQL and ADO connections

dim cnn as ADODB.Connection
set cnn = CurrentProject.Connection 'this could also be a remote DB

cnn.Execute "CREATE TABLE tblMyTab (Pk Int IDENTITY,Field1 VARCAHR(25),Field2 Int);"

Look for these 2 chm files for good resources for ADO and Jet4 SQL

ADO210.CHM & JETSQL40.CHM

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[Tranman]

sozzer,
Just a hint. As a recordset novice, you need to study both Eupher's and redapples' examples, as they are doing very different things:

Eupher is instantiating a recordset object, and assigning it to the recordset (source) of an existing form.

redapples is instantiating a stand-alone recordset object, and populating it initially from a table (MyTable). He could have also populated his recordset by using a query name in place of MyTable, or he could have used a SQL statement: "Select * from MyTable".

So if you're working in a form, and that form's source contains a field that you need to manipulate, Eupher's code is the way to go. But if you need to use a recordset to manipulate data in a table that is not related to, or is not available on, your current form, or if you don't have any form open at all, redapples' code is the right example to follow.

Good luck with your learning. Recordsets are an incredibly powerful tool. You were right, though. They are neither as complicated, nor as difficult as they first seem.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[sozzer]

Thank you for the futher examples and tips - I'll give a ADO a go shortly as it makes sense that I might as well learn the newer platform.

The ability to reference data without it being related to a form is what spurned my initial question and so it's great to have an example of this in ADO.

I shall see how I get on when I get home (only have '97 and '95 (yuck) at work)

Ad

 

[Tranman]

sozzer,
Another hint:

A lot of people think they cannot work with ADO in Access '97, but the two actually work fine together.

The situation is that Access '97 came out before ADO, so '97 has no default reference to the ADO type library. 97's default data object library is the DAO(Data Access Objects).

If you open a code window in '97 and navigate to Tools | References, and locate "Microsoft ActiveX Data Objects n.n Library", and put a check in the box beside it, you'll be able to use the ADO in your Access '97 databases at work. (The current version (n.n) is 2.7)

Access 2000, and up default to the ADO type library (instead of DAO), so if you want to use DAO in later versions, you'll need to create a reference to that.

Should you wish to use both ADO and DAO in the same project, you will need to qualify any references to objects that have ambiguous names. For instance, both the DAO and the ADO libraries contain Recordset objects, but the two recordsets are not the same. So when you instantiate a recordset, you have to say whether you want to create a DAO or an ADO recordset:

Dim rst1 As DAO.Recordset
Dim rst2 As New ADODB.Recordset

Same with connection objects:
Dim con1 As DAO.Connection
Dim con2 As New ADODB.Connection
etc. etc.

Good luck,
Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[redapples]

He could have also populated his recordset by using a query name in place of MyTable, or he could have used a SQL statement: "Select * from MyTable"

So if you're working in a form, and that form's source contains a field that you need to manipulate, Eupher's code is the way to go. But if you need to use a recordset to manipulate data in a table that is not related to, or is not available on, your current form, or if you don't have any form open at all, redapples' code is the right example to follow.

Indeed - I very often use a form to determine what my recordset will hold. Something along the lines of

rs.open "SELECT * FROM MyTable WHERE PK = " & MyForm.Combo1 & ";",cnn,3,3

I confess one thing which ADO cannot do that DAO does so well is create a recordset based on the current content of a form. Similarly I've yet to find how a PivotQuery can be made using ADO. My guess is that where DAO is Access centric (to a degree) ADO is developed to exist in a wider environ.

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[sozzer]

Hi all

Sorry for the delay in replying - been busy.

Thanks for the further information, especially the ADO reference in '97 - that's perfect for me.

cheers