SQL Vs Recordset

[Guest]

Hi there,

I read some articles that we can run SQL using VBA; however, at the same
time we can use OpenRecordset in DAO to accomplish the same task. Which one
should I be used and which one is better?

For instance I have created a form and report which source is a query using
qrydef at VBA to extract the data. It seems too awkward as I may need to
create many version for different qrydef. I believe there must be a better
way that we can create a recordset and use the same report/form to present
the data? Can you show me how? Thank you.

BL

 

[Tim Ferguson]

Hi there,

I read some articles that we can run SQL using VBA; however, at the
same time we can use OpenRecordset in DAO to accomplish the same task.
Which one should I be used and which one is better?

I don't think I really understand the question. The xx.OpenRecordset
method _is_ vba, using the DAO library, which results in the passing of
an SQL command to the db engine. What exactly are you wishing to compare
against what?

For instance I have created a form and report which source is a query
using qrydef at VBA to extract the data. It seems too awkward as I
may need to create many version for different qrydef. I believe there
must be a better way that we can create a recordset and use the same
report/form to present the data? Can you show me how? Thank you.

Again I don't have a clear idea of what you are trying to achieve. I
think it's something about parameterised queries -- you can point a
parameter at a control on a form, something like this:

WHERE BirthDate < DateValue(Forms!MyForm!txtBirthDateEnd)
AND etc...

but that does leave restrictions on how you can open it later. Newer
versions of Access seem to have a Recordset property for forms and
reports which seems to be read-write, but I haven't experimented much
with it. In the meantime, the normal method is still pretty robust:

DoCmd.OpenReport "rtpYoungPeople", _
Filter:="BirthDate < #1999-02-11#", _
OpenArgs:="Blue-Red"

and so on

Does that help?


Tim F

 

[Guest]

Thanks Tim,

May I clarify the question a bit,

a. I can run the SQL using DoCmd.OpenQuery "Qdf.SQL" method and at the same
time I can use xx.OpenRecordset method. Is OpenQuery also create a recordset?

b. How can I refer to a recordset in the form or report? As stated, I have
created forms and report based on a Query which can be changed by Qdf;
however, I do not know how to refer to a recordset yet instead.

Sorry for all the confusion and your help is much appreicated.

BL

 

[Tim Ferguson]

a. I can run the SQL using DoCmd.OpenQuery "Qdf.SQL" method and at the
same time I can use xx.OpenRecordset method. Is OpenQuery also create
a recordset?

Most of the DoCmd methods are simply vba handles to normal Access GUI
commands, rather than actual data manipulations. DoCmd.OpenQuery opens a
datasheet on the desktop; there is no easy programmatic access to the
rows.

If you want to manipulate the data, you need one of the OpenRecordset
methods:

DAO.Database.OpenRecordset() is the usual way of handling any SQL
SELECT statement.

DAO.QueryDef.OpenRecordset() will create a recordset from a pre-defined
qdf. It is said that precompiling makes a query faster, but if it does it
must by milli-seconds. An advantage is that once a query is debugged, you
don't have to mess about with vba string slicing any more. The
disadvantage is that you need to protect it from users rebugging it!

The database and querydef objects both have .Execute methods for
carrying out action queries such as delete, update, and insert. They are
generally preferred to DoCmd.RunSQL because you don't get warnings about
rows being changed, and you can get a trappable error if there's a
problem with the SQL parsing.

b. How can I refer to a recordset in the form or report? As stated, I
have created forms and report based on a Query which can be changed by
Qdf; however, I do not know how to refer to a recordset yet instead.

There are a couple of ways to change the recordsource of a form or
report.

At design time, you can use a parameterised query that points its
parameters at a UI control -- you just have to make sure that the form
containing it is available when the report is opening:

SELECT This, That, TheOther
FROM Somewhere
WHERE That >= Forms!MyForm!txtMinimumValue

An alternative method is to set the RecordSource property from within the
object itself, usually in the Load event:

Private Sub Form_Load()

' obviously, this need proper error trapping
strSQL = "SELECT This, That, TheOther " & _
"FROM Somewhere " & _
"WHERE That >= " & Me.OpenArgs

Me.Recordsource = strSQL
Me.Requery

End Sub

with this form, you can get info from the user, look up values on the
internet, whatever.

To orient yourself, you would do best to look round a working sample,
like the Northwind application; or get a good general Programming Access
book.

Hope that helps


Tim F