Learning MS Access

Question

i am new to ms-access, and i have started to look at code that works. unfortunately it is completely uncommented.

    Private Sub OKbut_Click()
Dim dt As Date
dt = Now()
Set rstOrder = New ADODB.Recordset
rstOrder.Open "tblUsers", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
    With rstOrder
        .AddNew
        .Fields("title") = title
        .Fields("first") = first
        .Fields("last") = last
        .Fields("gender") = gender
        .Fields("date_submitted") = dt
        .Update
    End With
End If
rstOrder.Close
Set rstOrder = Nothing
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT id FROM tblUsers WHERE date_submitted=#" & dt & "#")
duser = rs.Fields("id")
rs.Close
Set rs = Nothing
    Do While Not user_defined(duser)
        DoCmd.OpenForm "define_user_frm", , , , , acDialog
    Loop
'Forms(0).user_lst.RowSource = "select * from users where id=" & duser
Me.SetFocus
DoCmd.Close
End Sub

1. what does the function Now() do?
2. ADODB.Recordset is just a way to connect to a table?
3. adOpenStatic, adLockOptimistic what are these?
4. why are we checking this: if rstOrder.Supports(adAddNew) ?
5. why do we need to do this: Set rstOrder = Nothing?
6. what does this do:? Do While Not user_defined(duser) DoCmd.OpenForm "define_user_frm", , , , , acDialog Loop

Answer 1

I'm very rusty with VB/Access but:

1) Now returns the current date and time

2) Recordset is a set of records - from memory doesn't have to be a table, could be a query.

3) Those are flags to indicate how the table (as it is a table in this case) should be accessed can't remember static but the Lock says that we're going to use optimistic locking i.e. we will basically assume that the record won't be changed by anyone else.

4) Because you can open recordsets in a number of different ways - so it may well be the case that you have a read-only set of data. In the context of the example its notionally redundant but in terms of good practice its a reasonable bit of defensive programming.

5) Probably don't but again its sensible defensive programming and it ensures that all resources associated with the recordset are released. rstOrder is coming from somewhere external to the sub so not leaving it in an open state is sensible.

6) The loop repeats until the function user_defined(duser) returns true. duser being the - erk, that looks horrid - ID returned by the query but that doesn't look safe. DoCmd.Open form is popping up the "define_user_form" as a dialog so... if the user doesn't exist then fire up the dialog to define the user then check again that the user has been defined. That's also a bit questionable (although of course without a bit more context its hard to tell).

Basically VBA in Access is mostly VB6 but with sufficient differences to drive one slightly mad... there's a whole pile of stuff that you can assume to exist in access that you have to be rather more explicit about in VB.OLD

Answer 2

what does the function Now() do?

Place the cursor on the word Now in your subroutine, then press the F1 key. Is Access' help not helpful for answering your question?

I'm trying to suggest it should be quicker for you to use the built-in Help feature for a question like that, instead of posting to Stack Overflow and waiting for responses. Then you only need post the questions that Help doesn't answer adequately for you.

Answer 3

1. what does the function Now() do?
   • Returns a Variant (Date) specifying the current date and time according your computer's system date and time.
2. ADODB.Recordset is just a way to connect to a table?
   • The ADO Recordset object is used to hold a set of records from a database table. A Recordset object consist of records and columns (fields). It can be used to open tables, queries, custom SQL statements, etc.
3. adOpenStatic, adLockOptimistic what are these?
   • adOpenStatic: Provides a static copy of the records (you can't see additions, changes or deletions by other users), but all types of movement are enabled (e.g. .moveNext, .MoveFirst, .moveLast, etc).
   • adLockOptimistic: Record locking is performed only when you call the Update method.
4. why are we checking this: if rstOrder.Supports(adAddNew) ?
   • Checking whether the recordset allows new records to be added.
5. why do we need to do this: Set rstOrder = Nothing?
   • When you're completely finished with rstOrder, setting it equal to Nothing removes it from memory. You can think of it as a simple version of garbage collection.
6. what does this do:? Do While Not user_defined(duser) DoCmd.OpenForm "define_user_frm", , , , , acDialog Loop
   • Assuming user_defined() is a custom function, it loops through the record just added and opens a form that allows you to define the user.

Reference:

• http://www.w3schools.com/ado/ado_ref_recordset.asp

Answer 4

1. what does the function Now() do?

Now() returns the current system date and time

2. ADODB.Recordset is just a way to connect to a table?

Yes - more importantly, it is a good way to iterate through records of a dataset individually. Like a cursor in SQL Server. You could do something like

While not rstOrder.EOF
'a.k.a. while there are still records left to iterate through
   'Do something
End While

3. adOpenStatic, adLockOptimistic what are these?

From http://www.dotnetspider.com/forum/16958-In-VB-What-difference-between-adopendynamic.aspx

adOpenForwardOnly - this is the default cursor if no other is specified. This cursor allows only forward movement through a recordset

adOpenKeyset - this cursor supports forwards as well as backwards navigation. It also allows you to update a recordset and all changes will be reflected in other users recordsets. The cursor also supports bookmarking

adOpenDynamic - this cursor supports forward and backward navigation but bookmarks may not be supported (ie Access). Any changes made to data are immediately visible with no need to resynchronise the cursor with the database

adOpenStatic - this cursor uses a static copy of data from the database and therefore no changes to the data are visible and supports forward and backward navigation

4. why are we checking this: if rstOrder.Supports(adAddNew) ?

It's a way of writing more robust code - i.e. before attempting to add a new record, first check whether the recordset supports the addition of new records.

5. why do we need to do this: Set rstOrder = Nothing?

Frees the recordset from memory. Not absolutely necessary but again, makes for more robust code.

6. what does this do:? Do While Not user_defined(duser) DoCmd.OpenForm "define_user_frm", , , , , acDialog Loop

Checks for existence of a user and if it doesn't exist, it opens a form called "define_user_frm" which I assume allows for creating a new user.

Answer 5

1. Now() just returns the current date and time

2. ADODB.Recordset is in this instance being used as a way to connect to a table. It is generally used to connect to an external (read SQL) dataset. And can be populated from a query or stored procedure. Similar to the access builtin Recordset

3. These are switches used to set certain options in the connection. I would recommend reading over the MSDN documentation.

4. Not every recordset supports adding records (such as that returned from a query) this simply checks that before you attempt to add a record. It is part of writing code that won't throw errors.

5. This is simply a way to clean up the memory. As far as I know VBA is garbage collected but if you have a large recordset this can free up some memory sooner.

6. I'm not sure about this here. but it looks like it's calling a user defined function and it returns false it opens a form.

Answer 6

Approaching existing code like this isn't the best way to learn to program. There are plenty of online tutorials that will help you get started and progress in a linear manner. The other advice I'd give is to try things out for yourself. Write a small method that contains "MsgBox Now()" and run it to see what happens. Then run it a couple of seconds later.

I can't over-recommend finding a beginners tutorial.

However that said . . .

1. Now - returns the computers system date and time, at the moment it's called
2. Yes - Among other things.
3. Hints to the query engine. Open Static says you don't want to see rows added after the record set is retrieved, optimistic locking tells the engine which record locking option to use (the other is pessemistic)
4. Checking this because you can't add records to all resordsets (e.g. complex queries joining several tables)
5. Not stricly necessary, but better for memory management, otherwise the recordset object (and all associated resources - memory, handles etc) wouldn't be disposed until the end of the method.
6. Opens an ms access form until the condition "user_defined(duser)" returns true

Hope this helps.

Answer 7

1. what does the function Now() do? - Creates a DateTime for the current date and time the function is being run.

2. ADODB.Recordset is just a way to connect to a table? This is an object or saying that rstOrder is not a ABDODM.Recordset with a method to open the connection to the DB.

3. why do we need to do this: Set rstOrder = Nothing? - This is cleaning up the connection so that the computer does not keep the memory and for rstOrder. Other wise after running your computer might slow down or have less memory.

4. what does this do:? Do While Not user_defined(duser) DoCmd.OpenForm "define_user_frm", , , , , acDialog Loop - This is a loop that will be repeated and will Open an Access form as long as user_defined is not duser.