Accessing tables directly from VB

[Laurel]

This question is related to my "Best Strategy" posting. I've found some
code that suggests that you can manipulate records without having them bound
to a form.... e.g.,

Dim qdfDetail As QueryDef
Dim rstClassDetail As Recordset
Set qdfDetail = dbs.QueryDefs("qryScoresByClass")
Set rstClassDetail = qdfDetail.OpenRecordset

Do While Not rstClassDetail.EOF
... etc.


My question is this. Is there a way to access db tables directly from VB in
an analagous fashion? Or must one always develop a query, even if it is
only Select * from tblMyTable.

If someone could just direct me to the proper place in HELP, that would be
great. It's one of those situations where you can't find it unless you
already know what you're looking for.

 

[Douglas J. Steele]

Assuming dbs has been properly instantiated as a Database object, you can
use

Dim rstClassDetail As Recordset
Set rstClassDetail = dbs.OpenRecordset("MyTable", dbOpenTable)

where MyTable is the name of the table.

However, I think you're better off using a query, so that you can guarantee
you get only the fields you want, in the order you want:

Dim rstClassDetail As Recordset
Set rstClassDetail = dbs.OpenRecordset("SELECT Field1, Field2 FROM MyTable
ORDER BY Field2")

 

[Albert D. Kallal]

The ms-access environment does include a full VB development environment.

Actually, the programming language is called VBA (Visual Basic For
Applications), but for all purposes, it is the same programming language
used in VB.

So, can you manipulate records with code? Why of course you can, and likely
any application that really does anything of value will most certainly has
to "process" data.

The basic processing loop for a table names tblCustomers is as follows:


Dim rstRecords As DAO.Recordset

Set rstRecords = CurrentDb.OpenRecordset("tblCustomers")

Do While rstRecords.EOF = False
Debug.Print "last name is " & rstRecords!LastName
rstRecords.MoveNext
Loop
rstRecords.Close
Set rstRecords = Nothing


So, the above piece of code is the basic means to traverse a set of records
in a table. The above would display each last name in the debug window. Not
much use the above is, but it does show the basic processing loop in VB. Of
course, you can use sql in place of the table name to restrict record. So,
to change all the City Names from NY to New York, we could use:

Dim rstRecords As DAO.Recordset

Set rstRecords = CurrentDb.OpenRecordset("tblCustomers")

Do While rstRecords.EOF = False
if rstRecords!City = "NY" then
rstRecords.Edit
rstRecords!City = "New York"
rstRecords.Update
end if
rstRecords.MoveNext
Loop
rstRecords.Close
Set rstRecords = Nothing

Of course, since we can use sql to restrict what records will be in the
record set, then we can actually REMOVE the "if statement" and use the
following:

Dim rstRecords As DAO.Recordset

Set rstRecords = CurrentDb.OpenRecordset("select City from tblCustomers "
& _
" where City = 'NY'")

Do While rstRecords.EOF = False
rstRecords.Edit
rstRecords!City = "New York"
rstRecords.Update
rstRecords.MoveNext
Loop
rstRecords.Close
Set rstRecords = Nothing

Since all records will be city = 'NY', then we don't need to test in the
code if the city is = to 'NY'. Hence, using (and learning!) sql allows use
to save a lot of coding.

Of course, the above whole processing loop in VB is a real waste, and not
the best example since we have what is called sql (sql = structured query
language). Sql is really the key to un-locking the power of a database
system. We can replace all of the above code with ONE line of sql. It is
very power full. So, the VB code using sql could be:

currentdb.Execute "update tblCustomers set City = 'New York' Where City =
'NY'"

That is it!...one line of code!. So, when possible, you will most certainly
use code to process data, and actually make your application "do things".
However, when possible, you do want to use sql since is it less code,
generally runs faster, and is easer to maintain then complex processing VB
code.

You should get your self a book or two if you going to jump into this stuff.

Here is a good one to get your started, and it is free:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/bapp2000/ht
ml/acbatitle.asp

and

http://www.microsoft.com/accessdev/articles/bapp97/toc.htm

 

[Laurel]

THANK YOU SO MUCH!!!

This is just a great synopsis. You're right about the book... I finally
broke down and got one on Access - should get one on VB. But I do know
programming (PowerBuilder) and a fair amount of SQL, and with your great
list of "Essentials of Record Processing," including the Execute statement,
I'll probably be lulled into complacency for quite a while.

Thanks again!

 

[John Nurick]

Hi Laurel,

Yes, you just create a recordset that includes the records and fields
you're interested in. This is the way I usually do it:

Dim dbD as DA0.Database
Dim rsR as DAO.Recordset

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset(XXX)
'XXX can be the name of a table or query,
'or else a SQL SELECT statement.

'do stuff with rsR (and its .Fields and so on).


But in anwer to your previous posting:

1) Whenever possible, do it with a query, and bind your form to the
results of the query. The Access query designer lets you do a lot
without having to learn SQL, though the latter helps for some sorts of
query. The folk in microsoft.public.access.queries will help.
Iff the manipulation you need really can't all be done in a query (or a
series of queries each drawing data from the previous one), then it's
time to resort to recordset operations.

In that case, go ahead and create a query anyway, getting as close to
what you need as you can.

2) If there's a one-to-one correspondence between the records in this
query and the summarised records you want to present to the user,
a) use the query as the form's RecordSource
b) in the form's Current event, write code that grabs the contents
of the current record's fields, performs the calculations and displays
the results in controls on the form.

If you need to refer to other records (in the query or in other tables
or queries) in order to do the calculations, use DLookup(), DSum() and
similar functions in the current event. It's also possible to access
other records in the query via the form's .Recordsetclone property.

3) If there isn't a one-to-one correspondence between the records in the
query and the records you want to display to the user, things get more
complicatedP please post back with more information.

 

[Laurel]

Thanks for your thoughtful reply. I think that now that I see how
relatively easy it is to access a table in code, I'm going to read in the
details, and write them out to a temporary summary table which will be the
recordsource for my form. I'll do a Requery when I've loaded it with all its
data.

 

[Rolls]

I assume you meant that you want to use VBA from within Access, not VB, the
product, to work with an external MDB.

Code-behind bound forms is a good way to start. Use of the DAO (or ADO)
object model with recordsets will let you work with unbound forms. Using
dynamic arrays and these object models, it's possible to open multiple
instances of the same form at the same time, which isn't possible with bound
forms AFAIK.

In my experience I prefer to use chained SQL in preference to writing code
modules. SQL is easier and faster than sequential record processing! For
instance, summarizing dollars and allocating them across a variable range
can be done exclusively with SQL. If
you're doing multi-level summarizations and consulidations, SQL alone is
capable of doing the job. You don't have to write code unless you really
want to.

 

[Albert D. Kallal]

Using
dynamic arrays and these object models, it's possible to open multiple
instances of the same form at the same time, which isn't possible with bound
forms AFAIK.

Hum, not exactly sure what you mean, but you can most certainly open
multiple instances of the same form bound to the same table. Doing this
means your application can allow a user to "stop" working on a particular
customer, then minimize that particular customer, and have another copy of
the form opened to allow viewing/editing of other customers (for example
when the phone rings etc). There is nothing stopping one from doing this,
and you can certainly open multiple copies of the same from on bound forms.

 

[Laurel]

This is a response to a message from a different thread, but it seems to be
starting a whole new idea, so I'm starting a new thread.

Re: Accessing tables directly from VB

"Rolls" (e-mail address removed)

wrote in message news:<[email protected]>..

For instance, summarizing dollars and allocating them across a variable range
can be done exclusively with SQL. If
you're doing multi-level summarizations and consulidations, SQL alone is
capable of doing the job. You don't have to write code unless you really
want to.

Well, just out of curiosity here, I need to count the number of non-null
values in a field in a recordset (or result set) and then divide the sum of
those values by that count. There are a half dozen of these kinds of fields
in the same row/record, so I can't simply select for non-null values unless
I want to do the same process a half dozen different times. Is there a way
this can be done with just SQL?

 

[Laurel]

Well.. I thought I was starting a new thread.

Wanted to elaborate on my task here. Actually, I don't divide the total by
the count. Instead, I divide the total by the count times a column from a
second table (select Parameters.Value from Parameters where Parameters.Name
= "MaxScore"). Again for 6 different columns.

 

[Rolls]

Use a not-null filter in a query to get your count for each COL then calc
based on that result with another query.

In fact you can do separate pieces and reassemble them, producing a query
containing detail and summary totals.

 

[Rolls]

That's the situation. For example, a payment's misposted, so we want to see
two customer detail forms (same form, different query results), comparing
the postings before we make a correcting entry.

 

[Albert D. Kallal]

Sure, you can easily create such a system.

Assuming you have form with a bunch of buttons, and one of those buttons
launches the form, you can use the following:

Option Compare Database
Option Explicit

Dim colForms As New Collection

Private Sub Command0_Click()

Dim frmAdd As Form_CutMain

Set frmAdd = New Form_CutMain
frmAdd.Caption = frmAdd.Name & colForms.Count + 1

colForms.Add frmAdd
frmAdd.Visible = True

End Sub

I have pasted both the forms variable defs, and the code behind the button.
If you really want to see something cool, is open 5 copies of the same form,
and then close the form with the button that launched all the forms, and ALL
of those 5 open forms will close for you!

Note in the above, the form we launched was called CutMain, so, replace that
with the name of what you have...