Why should I use VBA instead of queries?

[Martin Lacoste]

There's likely not a simple answer for this, I know, but, I thought
I'd try anyways...

Background..
I've been racking my brain with some queries that I thought were
straightforward, but have been giving me errors, etc.. and am
wondering if I shouldn't be looking more towards using VBA. I have
used it in the past to do more complex stuff, but I would consider
myself just a little above a newbie in that department, since I seem
to have forgotten just about everything about it.

If I'm needing such things as...:
- comparing two tables without linking them
- doing the same operation between different sets of tables (e.g.
return results from Tables 1 and 2, then Tables 3 and 4, then Tables 1
and 4, etc..) - preferring the results to go together
- update/edit these results (which I can't with queries because of the
lack of primary keys)

.... would VBA allow me to accomplish these? Or what about macros?

I'm just trying to get a sense as to whether I should keep fighting
with queries, or move on, and I don't have a clear idea as to the
advantages of VBA or macros over queries.

I know that's vague, but based on that, any suggestions or
recommendations are welcome.

Thanks!
Martin Lacoste

[Mike MacSween]

"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...

This is a bit vague Martin. But most of what you describe below looks like
straight SQL stuff. SQL is the language which is designed to give you a
certain set of results from some tables. It's probably the obvious choice.
Whether you decide to save the SQL as a saved query, or issue it as part of
some VBA is up to your implmentation

If I'm needing such things as...:
- comparing two tables without linking them
- doing the same operation between different sets of tables (e.g.
return results from Tables 1 and 2, then Tables 3 and 4, then Tables 1
and 4, etc..) - preferring the results to go together
- update/edit these results (which I can't with queries because of the
lack of primary keys)
Give us a concrete example. 'Comparing two tables' isn't enough yet I'm
afraid
... would VBA allow me to accomplish these?
Well probably. I suppose you could open a recordset, then another, an loop
through them or somesuch. More likely you'd just have a SQL string in
amongst the VBA anyway
Or what about macros?
No.
I'm just trying to get a sense as to whether I should keep fighting
with queries,

You shouldn't have to 'fight' with queries (i.e. SQL). SQL is beautifully
clean and elegant.

Mike

[Lyle Fairfield]

ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:

There's likely not a simple answer for this, I know, but, I thought
I'd try anyways...

Background..
I've been racking my brain with some queries that I thought were
straightforward, but have been giving me errors, etc.. and am
wondering if I shouldn't be looking more towards using VBA. I have
used it in the past to do more complex stuff, but I would consider
myself just a little above a newbie in that department, since I seem
to have forgotten just about everything about it.

If I'm needing such things as...:
- comparing two tables without linking them
- doing the same operation between different sets of tables (e.g.
return results from Tables 1 and 2, then Tables 3 and 4, then Tables 1
and 4, etc..) - preferring the results to go together
- update/edit these results (which I can't with queries because of the
lack of primary keys)

... would VBA allow me to accomplish these? Or what about macros?

I'm just trying to get a sense as to whether I should keep fighting
with queries, or move on, and I don't have a clear idea as to the
advantages of VBA or macros over queries.

I know that's vague, but based on that, any suggestions or
recommendations are welcome.

Thanks!
Martin Lacoste

In the hands of a code jockey with an X-Base pre-query background, VBA may
be able to do some things that SQL cannot, and may be able to do some
things that SQL can do, faster. But, finding that person, and those things
may not happen very often.

So, I recommend that you post your query SQL strings here (perhaps one at a
time to ensure focus) here and see if you will get suggestions about how to
correct and optimize them.

Your statement about comparing tables implies that you have multiple tables
with the same structure. Your statement about the lack of primary keys is a
concern. If we can't identify the record, it can be difficult or impossible
to update it.Sometimes when db design is unsatisfactory, it's a good idea
to start all over.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Martin Lacoste]

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<41***********************@news.aaisp.net.uk> ...

"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...

This is a bit vague Martin. But most of what you describe below looks like
straight SQL stuff. SQL is the language which is designed to give you a
certain set of results from some tables. It's probably the obvious choice.
Whether you decide to save the SQL as a saved query, or issue it as part of
some VBA is up to your implmentation
As I've solved (with several persons' help!) a few problems I've been
running into with queries/SQL, I'll keep fiddling with it. My fear
about delving again into VBA is that I'll know even LESS about more,
which will probably not achieve the results I need, not to mention,
hurt my brain.

If I'm needing such things as...:
- comparing two tables without linking them
- doing the same operation between different sets of tables (e.g.
return results from Tables 1 and 2, then Tables 3 and 4, then Tables 1
and 4, etc..) - preferring the results to go together
- update/edit these results (which I can't with queries because of the
lack of primary keys)

Give us a concrete example. 'Comparing two tables' isn't enough yet I'm
afraid

Simple example (and probably not the best e.g.):

Table 1 has two fields: [first name] and [last name]
Table 2 has two fields: [full name] and [address]

Say I want to search for occurrences of [first name] in [full name], I
can't use a join, since there is no common field (assume that
splitting [full name] is not an option - not a great example here..).
And if there are 1000s of records in each, running a query can take
some time, there being no join. I was wondering if, somehow,
something can be done within vba to make this more efficient. My
guess is not.

... would VBA allow me to accomplish these?

Well probably. I suppose you could open a recordset, then another, an loop
through them or somesuch. More likely you'd just have a SQL string in
amongst the VBA anyway

Sounds like it's almost 6 of one, half-dozen of the other. Better to
stick with what I know (sort of know).

Or what about macros?

No.

I'm just trying to get a sense as to whether I should keep fighting
with queries,

You shouldn't have to 'fight' with queries (i.e. SQL). SQL is beautifully
clean and elegant.

Perhaps what I'm needing more than anything is a good resource (with
clear troubleshooting documentation..!) that I can draw on. I have
not been very successful finding solutions either within the help file
or online (microsoft support, etc..). I keep having to bug you guys
in this newsgroup - thankfully, there are folks like yourself who are
very generous with your time and expertise - thanks very much!!!!

Take care!
Martin
Mike

[Martin Lacoste]

Lyle Fairfield <Lo******@FFDBA.Com> wrote in message news:<Xn*******************@130.133.1.4>...

ma*******@rogers.com (Martin Lacoste) wrote in
news:d4**************************@posting.google.c om:

There's likely not a simple answer for this, I know, but, I thought
I'd try anyways...

Background..
I've been racking my brain with some queries that I thought were
straightforward, but have been giving me errors, etc.. and am
wondering if I shouldn't be looking more towards using VBA. I have
used it in the past to do more complex stuff, but I would consider
myself just a little above a newbie in that department, since I seem
to have forgotten just about everything about it.

If I'm needing such things as...:
- comparing two tables without linking them
- doing the same operation between different sets of tables (e.g.
return results from Tables 1 and 2, then Tables 3 and 4, then Tables 1
and 4, etc..) - preferring the results to go together
- update/edit these results (which I can't with queries because of the
lack of primary keys)

... would VBA allow me to accomplish these? Or what about macros?

I'm just trying to get a sense as to whether I should keep fighting
with queries, or move on, and I don't have a clear idea as to the
advantages of VBA or macros over queries.

I know that's vague, but based on that, any suggestions or
recommendations are welcome.

Thanks!
Martin Lacoste

In the hands of a code jockey with an X-Base pre-query background, VBA may
be able to do some things that SQL cannot, and may be able to do some
things that SQL can do, faster. But, finding that person, and those things
may not happen very often.

So, I recommend that you post your query SQL strings here (perhaps one at a
time to ensure focus) here and see if you will get suggestions about how to
correct and optimize them.

Your statement about comparing tables implies that you have multiple tables
with the same structure. Your statement about the lack of primary keys is a
concern. If we can't identify the record, it can be difficult or impossible
to update it.Sometimes when db design is unsatisfactory, it's a good idea
to start all over.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date

Thanks for your reply, Lyle. It does seem as though I should stick
with SQL - I'm slowly finding solutions to the roadblocks I keep
running into. I wish there were better help/support options - I get
the feeling that the errors I run into are obvious, and yet, I can't
seem to find such 'obvious' items either in the help file or at
various online support sites (or perhaps I just can't locate
them..?!). For example, it wasn't until recently that I realized that
I needed primary keys to make a query updatable. This is not
indicated in the "When can I update data from a query?" file in Access
help, and I spent several hours looking all over. Wasn't till I
posted here that someone casually mentioned something about it (it
seemed like it was either not evident even to the experts here, or,
it's one of those things that everyone should know, so was overlooked
because of its sheer obviousness - BUT... if it's so obvious, why
isn't it in the help file!!!???).

Anyways, I'm ranting... - thankfully there are many like yourself who
are very helpful, and it is very much appreciated.

BTW - the database that I'm working with is undoubtedly not set up as
good as it could be, though part of it is inherent in the type of data
present. I'm always reevaluating the design, and if you have any
recommendations as to resources that can help me in general
(intermediate-level) database design, that would be great.

Thanks again for your help, Lyle!
Martin

[Douglas J. Steele]

"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...

Simple example (and probably not the best e.g.):

Table 1 has two fields: [first name] and [last name]
Table 2 has two fields: [full name] and [address]

Say I want to search for occurrences of [first name] in [full name], I
can't use a join, since there is no common field (assume that
splitting [full name] is not an option - not a great example here..).
And if there are 1000s of records in each, running a query can take
some time, there being no join. I was wondering if, somehow,
something can be done within vba to make this more efficient. My
guess is not.

Actually, you can do a join in that particular case: you just can't do it
through the GUI.

Create your query, and pretend that you want to join first name to full
name. Then, switch to the SQL view of your query.

Somewhere in the query, you'll see the phrase "ON [Table 1].[first name] =
[Table 2].[full name]"

Change that to "ON InStr([Table 2].[full name], [Table 1].[first name]) > 0"

Now, you won't be able to switch back to the graphical interface once you do
that, but the query will work.

In almost all cases, SQL is going to be more efficient than using VBA to do
the same thing.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)