Confusion re Access' interplay with VBA & SQL

[Dennis]

Access 2003

I am new to Access VBA, and certainly, SQL. How many "languages" do I need to know to write simple
macros to copy and change properties of Queries & Reports?

Attempting to write a series of macros to modify:

15 Queries (with DAO.QueryDef) [How do I include it in a Macro?]

and

15 Reports (with CreateReportControl )

Specifically, all I want to do is:

REPORTS
1) design one report
2) copy that report to 14 other reports while
3) changing three items:
Report name (header)
Record Source (query)
Caption (same as report name above)

(Currently, every time I change the layout (display) of the report I have to do it 15 times)

QUERIES
1) design one query
2) copy that report to 14 other queries while
3) Assigning different criteria, sorts, and fields

Does anyone have examples I could modify that they would be willing to share?


TIA Dennis

 

[Jennifer H.]

To get the reports - if all you are changing are the
report title, record source, caption etc, it sounds like
the layout stays the same. So use just 1 report, and
change these options in the Report Open event using VBA.
You can invoke the report from various buttons which can
then set a global variable to a value corresponding to
the actual report you want displayed.

In the Report Open event, you can then check the setting
of that global variable and change the properties
accordingly.

With the queries, it would be faster to use the Query
window and change them yourself. Alternatively if all
the queries are based on the same table and you are just
changing criteria you might consider using parameters or
again having just 1 query that you modify using VBA when
you run it. j.

-----Original Message-----
Access 2003

I am new to Access VBA, and certainly, SQL. How

many "languages" do I need to know to write simple

macros to copy and change properties of Queries & Reports?

Attempting to write a series of macros to modify:

15 Queries (with DAO.QueryDef) [How do I include it in a Macro?]

and

15 Reports (with CreateReportControl )

Specifically, all I want to do is:

REPORTS
1) design one report
2) copy that report to 14 other reports while
3) changing three items:
Report name (header)
Record Source (query)
Caption (same as report name above)

(Currently, every time I change the layout (display) of

the report I have to do it 15 times)

 

[Dennis]

Thanks Jennifer for your knowledge & time!

Is it not possible to access the report structure directly via VBA or is Access such an MS orphan
that what someone can do in Excel (VBA) ?

I guess that I am struggling with why Access is so different than all other members of the Office
suite. [The Access Macro Design Form and Conversion to VBA etc.]

Why does one have to use SQL to get to Query properties?

What "references" do I need installed to get information like DAO.QueryDef, etc.

Thanks again Dennis

To get the reports - if all you are changing are the
report title, record source, caption etc, it sounds like
the layout stays the same. So use just 1 report, and
change these options in the Report Open event using VBA.
You can invoke the report from various buttons which can
then set a global variable to a value corresponding to
the actual report you want displayed.

In the Report Open event, you can then check the setting
of that global variable and change the properties
accordingly.

With the queries, it would be faster to use the Query
window and change them yourself. Alternatively if all
the queries are based on the same table and you are just
changing criteria you might consider using parameters or
again having just 1 query that you modify using VBA when
you run it. j.

-----Original Message-----
Access 2003

I am new to Access VBA, and certainly, SQL. How

many "languages" do I need to know to write simple

macros to copy and change properties of Queries & Reports?

Attempting to write a series of macros to modify:

15 Queries (with DAO.QueryDef) [How do I include it in a Macro?]

and

15 Reports (with CreateReportControl )

Specifically, all I want to do is:

REPORTS
1) design one report
2) copy that report to 14 other reports while
3) changing three items:
Report name (header)
Record Source (query)
Caption (same as report name above)

(Currently, every time I change the layout (display) of

the report I have to do it 15 times)

QUERIES
1) design one query
2) copy that report to 14 other queries while
3) Assigning different criteria, sorts, and fields

Does anyone have examples I could modify that they would be willing to share?


TIA Dennis

.

 

[Jennifer H.]

I don't understand what you mean by query properties. If
these are the properties that you set in the Query
Window, you do not use SQL for that. You don't even need
to use SQL unless you are creating the query in code -
then you won't be setting properties.

To reference the querydefs or reports etc, you can use
CurrentDB.QueryDef in the VBA code. You use DAO to get
to the data itself, not to reports or queries. Then you
just need to have a reference to Microsoft DAO 3.6 or
some such. Hope this helps. j.

-----Original Message-----
Thanks Jennifer for your knowledge & time!

Is it not possible to access the report structure

directly via VBA or is Access such an MS orphan

that what someone can do in Excel (VBA) ?

I guess that I am struggling with why Access is so

different than all other members of the Office

suite. [The Access Macro Design Form and Conversion to VBA etc.]

Why does one have to use SQL to get to Query properties?

What "references" do I need installed to get information like DAO.QueryDef, etc.

Thanks again Dennis

To get the reports - if all you are changing are the
report title, record source, caption etc, it sounds like
the layout stays the same. So use just 1 report, and
change these options in the Report Open event using VBA.
You can invoke the report from various buttons which can
then set a global variable to a value corresponding to
the actual report you want displayed.

In the Report Open event, you can then check the setting
of that global variable and change the properties
accordingly.

With the queries, it would be faster to use the Query
window and change them yourself. Alternatively if all
the queries are based on the same table and you are just
changing criteria you might consider using parameters or
again having just 1 query that you modify using VBA when
you run it. j.

-----Original Message-----
Access 2003

I am new to Access VBA, and certainly, SQL. How

many "languages" do I need to know to write simple

macros to copy and change properties of Queries & Reports?

Attempting to write a series of macros to modify:

15 Queries (with DAO.QueryDef) [How do I include it

in
a Macro?]

and

15 Reports (with CreateReportControl )

Specifically, all I want to do is:

REPORTS
1) design one report
2) copy that report to 14 other reports while
3) changing three items:
Report name (header)
Record Source (query)
Caption (same as report name above)

(Currently, every time I change the layout (display)

of
the report I have to do it 15 times)

QUERIES
1) design one query
2) copy that report to 14 other queries while
3) Assigning different criteria, sorts, and fields

Does anyone have examples I could modify that they

would
be willing to share?

TIA Dennis

.

.

 

[Dirk Goldgar]

PMFJI. Comments inline.

Thanks Jennifer for your knowledge & time!

Is it not possible to access the report structure directly via VBA or
is Access such an MS orphan that what someone can do in Excel (VBA) ?

Of course you can. But Jennifer is trying to tell you a better way to
achieve what seems to be your goal. For minor cosmetic changes like
those you describe, it's much more efficient to have a single report and
modify a few of its properties at run time, rather than clutter up your
database with 15 almost-identical reports, all of which now must be
maintained separately and kept in sync.

If you insist on "cloning" a report, you can open the report in design
view, change various properties, and then save it with a new name -- all
using VBA code like this:

DoCmd.OpenReport "Report1",acViewDesign
With Reports("Report1")
.Caption = "Report1A"
.RecordSource = "qryReport1A"
End With
DoCmd.Save , "Report1A"
DoCmd.Close acReport, "Report1A"

I guess that I am struggling with why Access is so different than all
other members of the Office suite. [The Access Macro Design Form and
Conversion to VBA etc.]

I'm not sure in what areas you think Access is so different from the
other members of the Office suite. Like each of the other Office
products, Access has its own object model and special objects and
methods appropriate to the job it's intended to do. Access also
supports a number of different ways of doing things, for example in
supporting both macros and VBA. I don't follow what you're saying about
the "Macro Design Form" and conversion to VBA.

Why does one have to use SQL to get to Query properties?

It's not clear what you mean. A stored query is essentially nothing but
stored SQL and a few other properties used to control its execution. In
query design view, a few select "interpreted" properties are extracted f
rom the SQL and presented on the property sheet as a convenience, but
that's all the work of the Access user interface -- the properties I'm
talking about are not actually stored except as keywords in the SQL. I
have no idea what you mean by your question. You can certainly use the
DAO QueryDef object to get at a stored query and manipulate its SQL
directly, but you must then do your own interpretation of what that SQL
means.

What "references" do I need installed to get information like
DAO.QueryDef, etc.

To work with DAO objects, you need to a reference to the Microsoft DAO
3.6 Object Library (if you are using Access 2000 or later).

 

[Dennis]

Dirk,

Thank you for all the time that you took!

We all must remember that it is the ultimate user's capabilities and desires that have a major
impact on how the designer lays out the interface.

I agree, if it were just my call, that I would do exactly what you suggest.

All this started when I suggested, to a friend, that "oh just see if your accounting s/w can export
to Excel and I'll re-export the data into Access for capture and reporting."

I have never had even one hour of programming instruction nor any training in Access.
So please forgive me for not asking questions as if I knew the approach to the answer. Typical when
one does not know the lingo nor where to even begin a question - a position that a learned
professional forgets (when they were "new.")

My point about "conversion." As you well know, in Excel if one wants to "code" in VBA one can, at
least, record a macro and build from that. In Access, no recording is available to provide at least
the objects, methods and properties. That said, the Macro Design View does force one to think in
steps. The problem is, to the "new," it is more than a bit overwhelming.

In addition, in Access, one must use the Macro Design View to "code" a macro. Then when one is
completed, there is the option to "Convert that Macro to VBA" I had a problem understanding the
"why" of that, because now I has two processes to accomplish the same goal 1) Access Macro and 2)
VBA code. I mean, which one is better, faster? (I realize that the VBA method is more adaptable.)

It is interesting that one can get gobs of VBA samples, tools and help for Excel but not for Access.
Me thinks that there are far few users of Access and/or it is harder for users to part with
knowledge.

That is why I am so impressed that you took the time to code a simple process. I have asked this
the essence of this issue three times (but in different forms - not forum) in the last few days.
What I got was a few globals and methods to ask even more stupid (to the learned) questions.

Previous "helpers" strongly stated that it was far easier to manually change 15 queries and reports
that to develop a macro. I want to know how the do it smartly. Thank you for that!

Dennis

PMFJI. Comments inline.

Thanks Jennifer for your knowledge & time!

Is it not possible to access the report structure directly via VBA or
is Access such an MS orphan that what someone can do in Excel (VBA) ?

Of course you can. But Jennifer is trying to tell you a better way to
achieve what seems to be your goal. For minor cosmetic changes like
those you describe, it's much more efficient to have a single report and
modify a few of its properties at run time, rather than clutter up your
database with 15 almost-identical reports, all of which now must be
maintained separately and kept in sync.

If you insist on "cloning" a report, you can open the report in design
view, change various properties, and then save it with a new name -- all
using VBA code like this:

DoCmd.OpenReport "Report1",acViewDesign
With Reports("Report1")
.Caption = "Report1A"
.RecordSource = "qryReport1A"
End With
DoCmd.Save , "Report1A"
DoCmd.Close acReport, "Report1A"

I guess that I am struggling with why Access is so different than all
other members of the Office suite. [The Access Macro Design Form and
Conversion to VBA etc.]

I'm not sure in what areas you think Access is so different from the
other members of the Office suite. Like each of the other Office
products, Access has its own object model and special objects and
methods appropriate to the job it's intended to do. Access also
supports a number of different ways of doing things, for example in
supporting both macros and VBA. I don't follow what you're saying about
the "Macro Design Form" and conversion to VBA.

Why does one have to use SQL to get to Query properties?

It's not clear what you mean. A stored query is essentially nothing but
stored SQL and a few other properties used to control its execution. In
query design view, a few select "interpreted" properties are extracted f
rom the SQL and presented on the property sheet as a convenience, but
that's all the work of the Access user interface -- the properties I'm
talking about are not actually stored except as keywords in the SQL. I
have no idea what you mean by your question. You can certainly use the
DAO QueryDef object to get at a stored query and manipulate its SQL
directly, but you must then do your own interpretation of what that SQL
means.

What "references" do I need installed to get information like
DAO.QueryDef, etc.

To work with DAO objects, you need to a reference to the Microsoft DAO
3.6 Object Library (if you are using Access 2000 or later).

 

[Dirk Goldgar]

Thank you for all the time that you took!

You're welcome.

All this started when I suggested, to a friend, that "oh just see if
your accounting s/w can export to Excel and I'll re-export the data
into Access for capture and reporting."

I have never had even one hour of programming instruction nor any
training in Access.
So please forgive me for not asking questions as if I knew the
approach to the answer. Typical when one does not know the lingo nor
where to even begin a question - a position that a learned
professional forgets (when they were "new.")

Of course. Those of who hang out in these newsgroups do try to bear
that in mind, but sometimes we can't tell what the questioner's
background and level of experience are. Sometimes it's harder to come
up with a satisfactory answer if the question is specific and technical,
than it is if the OP just describes the general situation and ultimate
goal.

My point about "conversion." As you well know, in Excel if one wants
to "code" in VBA one can, at least, record a macro and build from
that. In Access, no recording is available to provide at least the
objects, methods and properties.

True. I have occasionally missed the ability to record a series of
steps. On the other hand, when I've recorded Excel macros, I've usually
found that the VBA generated is far indeed from what I would have
written if I'd studied the object model and written the procedure from
scratch.

That said, the Macro Design View
does force one to think in steps. The problem is, to the "new," it
is more than a bit overwhelming.

Now see, I find the Excel object model perplexing and hard to work with,
because I'm just a tyro at Excel. The Access object model, to me, is a
breeze. Working with macros, in Access, is very limited because the
macros only support the actions you can perform via the user interface.

In addition, in Access, one must use the Macro Design View to "code"
a macro. Then when one is completed, there is the option to "Convert
that Macro to VBA" I had a problem understanding the "why" of that,
because now I has two processes to accomplish the same goal 1) Access
Macro and 2) VBA code. I mean, which one is better, faster? (I
realize that the VBA method is more adaptable.)

There's no doubt in my mind that coding in VBA is preferable, because
one can work directly with both the data and the objects. It's more
complicated, but more comprehensive and more efficient. Plus, as a
developer, I need to be able to handle errors that may arise, and
there's no error-handling support in macros.

It is interesting that one can get gobs of VBA samples, tools and
help for Excel but not for Access. Me thinks that there are far few
users of Access and/or it is harder for users to part with knowledge.

I have to dispute this. There are enormous quantities of VBA samples
and tools out there. Have you looked at the Northwind and Solutions
sample databases that are distributed with Access? I'll grant you that
there are far fewer templates for Access on the MS Office site than
there are for Word and Excel, and that has more to do with the relative
number of users. And that's probably because unlocking the power of a
relational database system requires more thought about how such systems
work than it takes to create a pretty Word document or a usable
spreadsheet.

But aside from the samples provided by Microsoft, there many, many web
sites with snippets of "how-to" code. You may want to start your search
at The Access Web:

http://www.mvps.org/access/

And there are links on that site to lots of other good sites. I'd also
recommend using Google Groups to search the microsoft.public.access.*
hierarchy for newsgroup postings on topics you're interested in. And
don't forget the non-MS Usenet group, <comp.databases.ms-access> (though
I must warn you, that group has taken a nasty turn recently, as certain
individuals are conducting private vendettas. I don't go there any
more.)

I have asked this the essence of this issue three times
(but in different forms - not forum) in the last few days. What I got
was a few globals and methods to ask even more stupid (to the
learned) questions.

Previous "helpers" strongly stated that it was far easier to manually
change 15 queries and reports that to develop a macro.

And I agree with them, just as I agree with Jennifer that you'd be
better off in the long run *not* creating these 15 separate reports.
However ...

I want to know how the do it smartly.

This is a sentiment I understand completely, so I'm glad I was able to
help.

 

[John Viescas]

And another "PMFJI" ... <s>

Microsoft made a HUGE mistake assigning the name "macro" to the macro
facility in Access. (Internally, the object that Access stores is called a
Script!) To end the confusion, let's call this "programming for beginners"
facility Gorp. There, that makes me feel better.

As you have discovered, there is no "macro record" facility in Access as
there is in Word and Excel. In modern versions of Word and Excel, you
actually get Visual Basic code when you "record" a macro. In Access, you
can write a Gorp, save it, and then choose Tools / Macro / Convert Macros to
Visual Basic to get the roughly equivalent code translated into Visual
Basic. But I don't recommend using that facility because, although the
resulting code will usually work just like the macro, it creates a very
simplistic version of the equivalent VB code.

There are *tons* of examples in books and on the web for coding Visual Basic
in Microsoft Access. Some of my favorites (other than my own website <s>)
are:

http://www.mvps.org/access/

http://members.iinet.net.au/~allenbrowne/tips.html

http://www.developershandbook.com/

http://www.lebans.com/

http://www.granite.ab.ca/accsmstr.htm

http://www.rogersaccesslibrary.com/index.htm

... and the list you can find here:

http://www.mvps.org/links.html#Access

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Dirk,

Thank you for all the time that you took!

We all must remember that it is the ultimate user's capabilities and desires that have a major
impact on how the designer lays out the interface.

I agree, if it were just my call, that I would do exactly what you suggest.

All this started when I suggested, to a friend, that "oh just see if your accounting s/w can export
to Excel and I'll re-export the data into Access for capture and reporting."

I have never had even one hour of programming instruction nor any training in Access.
So please forgive me for not asking questions as if I knew the approach to the answer. Typical when
one does not know the lingo nor where to even begin a question - a position that a learned
professional forgets (when they were "new.")

My point about "conversion." As you well know, in Excel if one wants to "code" in VBA one can, at
least, record a macro and build from that. In Access, no recording is available to provide at least
the objects, methods and properties. That said, the Macro Design View does force one to think in
steps. The problem is, to the "new," it is more than a bit overwhelming.

In addition, in Access, one must use the Macro Design View to "code" a macro. Then when one is
completed, there is the option to "Convert that Macro to VBA" I had a problem understanding the
"why" of that, because now I has two processes to accomplish the same goal 1) Access Macro and 2)
VBA code. I mean, which one is better, faster? (I realize that the VBA method is more adaptable.)

It is interesting that one can get gobs of VBA samples, tools and help for Excel but not for Access.
Me thinks that there are far few users of Access and/or it is harder for users to part with
knowledge.

That is why I am so impressed that you took the time to code a simple process. I have asked this
the essence of this issue three times (but in different forms - not forum) in the last few days.
What I got was a few globals and methods to ask even more stupid (to the learned) questions.

Previous "helpers" strongly stated that it was far easier to manually change 15 queries and reports
that to develop a macro. I want to know how the do it smartly. Thank you for that!

Dennis

PMFJI. Comments inline.

Thanks Jennifer for your knowledge & time!

Is it not possible to access the report structure directly via VBA or
is Access such an MS orphan that what someone can do in Excel (VBA) ?

Of course you can. But Jennifer is trying to tell you a better way to
achieve what seems to be your goal. For minor cosmetic changes like
those you describe, it's much more efficient to have a single report and
modify a few of its properties at run time, rather than clutter up your
database with 15 almost-identical reports, all of which now must be
maintained separately and kept in sync.

If you insist on "cloning" a report, you can open the report in design
view, change various properties, and then save it with a new name -- all
using VBA code like this:

DoCmd.OpenReport "Report1",acViewDesign
With Reports("Report1")
.Caption = "Report1A"
.RecordSource = "qryReport1A"
End With
DoCmd.Save , "Report1A"
DoCmd.Close acReport, "Report1A"

I guess that I am struggling with why Access is so different than all
other members of the Office suite. [The Access Macro Design Form and
Conversion to VBA etc.]

I'm not sure in what areas you think Access is so different from the
other members of the Office suite. Like each of the other Office
products, Access has its own object model and special objects and
methods appropriate to the job it's intended to do. Access also
supports a number of different ways of doing things, for example in
supporting both macros and VBA. I don't follow what you're saying about
the "Macro Design Form" and conversion to VBA.

Why does one have to use SQL to get to Query properties?

It's not clear what you mean. A stored query is essentially nothing but
stored SQL and a few other properties used to control its execution. In
query design view, a few select "interpreted" properties are extracted f
rom the SQL and presented on the property sheet as a convenience, but
that's all the work of the Access user interface -- the properties I'm
talking about are not actually stored except as keywords in the SQL. I
have no idea what you mean by your question. You can certainly use the
DAO QueryDef object to get at a stored query and manipulate its SQL
directly, but you must then do your own interpretation of what that SQL
means.

What "references" do I need installed to get information like
DAO.QueryDef, etc.

To work with DAO objects, you need to a reference to the Microsoft DAO
3.6 Object Library (if you are using Access 2000 or later).

 

[Dennis]

Dirk

I am finally getting to appreciate Access' approach and this NG much much better.

Dennis

 

[Dennis]

Hello John,

OK I'll be "new," What does PMFJI mean? I absolutely will be embarrassed when I realize how
obvious it PMFJI is!

Beginning to learn a process is (no brainer) the most difficult; as even asking understandable
questions is quite difficult.

Jennifer, Dirk, Brendan and yourself have provided to me the positive nudge to transcend my mental
block re: Access. I just wish there was a way that I could return now, or in time, benefits to all
of you and the many others how help out in these forums.

As a result of all of your knowledge(s), I now have a number of links to explore and learn.

Dennis

 

[John Viescas]

Pardon Me For Jumping In. (A thread on which someone else is already
responding.)

Also sometimes PMJI - Pardon My Jumping In. <s>

Don't worry. If you stick with it, you'll be "jumping in" in no time, too,
and helping out.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)