‘Random Read’ (Access 2000/VBA)

Topic

New Reply

Hi List,
I have another novice question for the board. I have gone through declaring a record set and all the other overhead to retrieve one record (row) from a table. Is it possible to just “read a record by the unique key of a select”?
TIA
ElDinosaurio

Reply | Quote

Replies

If I understand your question ……. the “Random Read” in the post title makes me wonder if I do.

You can set criteria in a Query to select a specific record. You can also use the “Where” parameter of the DoCmd.OpenForm and DoCmd.OpenReport methods to specify criteria.

HTH
RDH

Reply | Quote

Ricky,

I guess I was not clear in what I wanted to do. I want to just retrieve one record using known values in the record without making record sets with a lot of overhead. Something like a VSAM read on a mainframe.

TIA
ElDinosaurio

Reply | Quote

Things like VSAM reads are generally hidden from the user in Access – for one thing Access isn’t a sequential file, it’s relational. So things are stored in random places. Record access is generally done using SQL if you want to restrict the recordset or retrieve it in a specific order. If you just want to get some records, you can open a recordset at the table level and move through it. Since you are using 2000, you have to decide whether to use DAO or ADO. ADO is new technology in Access 2000, but some things you can do in DAO you can’t do in ADO, so it gets a little complicated if you want to do more than the mundane things. All this assumes that you want to work with records in code – but 90% of Access database is done using either Jet or the MSDE interface, so code is generally associated more with event handling than with scrolling through records or editing records.

As an example, to display a single record on a form that is bound to a table simply involves setting a filter on the appropriate field to select the record or records that you want, and Access/Jet does the rest. It’s a whole different paradigm than the mainframe world, and is a much faster development process. Hope this helps.

Reply | Quote

Wendell,
Thanks for answering. The object of my question is to put my 30+ years of IT to work in this new environment. I am working in an unbound form and trying to get data from the data base on a per record basis as opposed to a per field basis which i can do with Dlookup. I thought that setting up a record set was a lot of overhead for the machine when it is busy and was wondering if there was a simple , “Open Input, Read by criteria, Close” scenario that would not use as many resources as I believe that setting up a record set uses.

I am not sure what Access/Jet or MSDE is. I certainly do understand that this environment is much different from the mainframe world. BUT . . . The more the software does for you, the more it does to you. I remember RPG.

Thanks again.

ElDinosaurio

Reply | Quote

Actually, you can do something like that using ADO and disconnected recordsets, but I wouldn’t suggest you start out trying to work with them before you become more familiar with the way Access works. Otherwise, you’ll confuse yourself with the similarities and go nuts over the differences.

Unbound forms are great, and I use them frequently. But I’d suggest you start off with bound forms and get a feel for what the application can do for you without a lot of programming before you make any decisions about how you want to make it work. Unbound forms require programming, and you still need a recordset to get the data for the unbound form, since you definitely don’t want to do it using something like DLookup.

Jet is the database engine that Access and VB use by default. MSDE (Microsoft Database Engine) is the personal version of SQL Server that comes with Access/Office 2000. If you create an ADP, you connect either to an MSDE database or a regular SQL Server database. If you build an MDB, all the local tables in the MDB are Jet tables, and you have to talk to SQL Server/MSDE either through ODBC links or using ADO and the MSSQL OLE DB provider.

Some of us have posted samples and demos in the Lounge in the past. You might want to do a Lounge Search on sample database and take a look at some of those to see various ways things can be done.

Reply | Quote

Charlotte,
Thanks for the explanation.

ElDinosaurio

Reply | Quote

If you’ve created and opened a recordset, you already are reading that record. Could you explain what you’re trying to do? SQL doesn’t know about keys, it just uses them in joins. Are you talking about a unique key returned in the recordset? Or are you looking for an alternate method of reading the information in the record?

Reply | Quote