Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Lost Wages, NV
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Random Read' (Access 2000/VBA)

    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

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Random Read' (Access 2000/VBA)

    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
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Random Read' (Access 2000/VBA)

    If you've created and opened a recordset, you already are reading that record. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> 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?
    Charlotte

  4. #4
    New Lounger
    Join Date
    Dec 2001
    Location
    Lost Wages, NV
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Random Read' (Access 2000/VBA)

    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

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: 'Random Read' (Access 2000/VBA)

    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.
    Wendell

  6. #6
    New Lounger
    Join Date
    Dec 2001
    Location
    Lost Wages, NV
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Random Read' (Access 2000/VBA)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Random Read' (Access 2000/VBA)

    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.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Dec 2001
    Location
    Lost Wages, NV
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Random Read' (Access 2000/VBA)

    Charlotte,
    Thanks for the explanation.

    ElDinosaurio

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •