Access generated sql vs. VB sql

[Stephanie]

Hi. sql skills not great. Sometimes I'd like to let
Access generate the sql for me (for example, in the query
builder) and then use it in vb. But of course it isn't
the same. Can vb sql handle inner joins? Can it handle
union queries? What differences in the syntax do I need
to look out for and change to make it work in vb?

Thanks for the help,
Stephanie

 

[GraemeR]

Hi Stephanie
MS Access SQL is Jet SQL. Jet SQL is different to SQLServer SQL is different
to Oracle SQL, etc. [Though most of it's the same]

If, by VB [which doesn't understand SQL], you are referring to code that is
running queries against a jet database then you can copy and paste the SQL
into the module (as a string).
SQL from an ms access query may need minor changes to run against an SQL
Server database or Oracle database.

e.g.
Query
SELECT * FROM myTable;
VB
Dim rst As DAO.Recordset
' Will work on any database (note no ";" at end of string)
Set rst = Currentdb.OpenRecordset("SELECT * FROM myTable")

HTH, Graeme.

 

[Albert D. Kallal]

Hi. sql skills not great. Sometimes I'd like to let
Access generate the sql for me (for example, in the query
builder) and then use it in vb. But of course it isn't
the same. Can vb sql handle inner joins?

Yes, virtually all of the sql you make in the query builder will work in the
VB code editor.

Can it handle
union queries?
Yes...

What differences in the syntax do I need
to look out for and change to make it work in vb?

There is not much different in syntax, but you do have to be aware of what
the sql does. For example, the follow code in VB uses sql to update a city
field.

dim strSql as string

strSql = "update tblCustomers set City = 'New York' where City = 'N.Y.'"

currentdb.Execute strSql

We can also type the above sql into the query builder, and hit the ! (run)
button.

However, what about the following:

strSql = "select City from tblCustomers"

currentdb.Execute strSql

Now, the above will NOT work. The reason here is that the sql will not know
where the results should go!! Should the results go the screen, or maybe a
report, or where? So, you need to be aware of quires that don't return any
records, and modify data. These types of queries are called action queries,
and are most often used in VB code (they modify data..but do NOT return
records).

So, it not so much that things are different in VB, but you do have to be
aware of what the sql is going to do. You can pull sql records into what is
called a reocrdset, and then use code to work on that data. We can go:

dim strSql as string
dim rstRecs as dao.RecordSet

strSql = "select City from tblCustomers"

set rstRecs = currentdb.OpenRecordSet(strSql)
do while rstRecs.EOF = false
debug.print rstRecs!City
rstRecs.MoveNext
loop
rstRecs.Close
set rstRecs = nothing

The above program loop would diplay all cities in the debug window. So, you
can pull data as a select, or union query in VB, and process it as above.

However, most of the time, you can just build the sql in the query builder,
and then have the VB code run that query. There is no need to "paste" the
sql into the code if you already got a query built. You can simply build the
query, and then run it in VB code.

Currentdb.Execute "yourQueryNameGoesHere"

So, just leave the sql in the query builder...

 

[david epsom dot com dot au]

The 'Access SQL' is Access/DAO SQL. If you are using
ADO, there are minor differences. If you are using ADO
against an Access/Jet database, you should consider using
DAO instead.

(david)

 

[Stephanie]

Albert-
Fabulous explanations! I appreciate your time and am
very excited to run code with an existing query.
Question though. The union query I want to use returns a
value (such as "5"). I want to compare that value (which
is a running total) to the current value in a field. So
if I use Currentdb.Execute "yourQueryNameGoesHere" how
can I compare what's returned to what's new?

Thanks,
Stephanie

 

[Stephanie]

Thanks all for your great explanations- I appreciate your
time. Stephanie

 

[Stephanie]

I guess I'm still not getting it... I received this
error: Cannot execute a select query. (Error 3065)
You tried to use the Execute method with a select query.
The Execute method is valid only with action queries.
Select queries contain a SELECT statement and can return
records; action queries do not.

How can I run my query to get the value (example: "5")
and then compare that value to another value? Thanks,
Stephanie

 

[Albert D. Kallal]

I guess I'm still not getting it... I received this
error: Cannot execute a select query. (Error 3065)
You tried to use the Execute method with a select query.
The Execute method is valid only with action queries.

The message should now make sense. As I explained,
you can run a action query using execute, but not a select
query. For a select query, where will the results go to?

How can I run my query to get the value (example: "5")
and then compare that value to another value? Thanks,

You have several choices. Dlookup is often handy in
code since it takes less work.

You don't mention what, when, and how you want to
compare a value. You can grab the results of your
query like:

dim strSomeValue as string

strSomeValue = dlookup("SomeFieldName","SomeUnionQueryName")

The above will thus put the results of your query into a variable
strSomeValue. In fact, it put the FIRST reocrd of the query into the
varibale strSomeValue.

if strSomeValue = "5" then
msgbox "the value returned by the query is 5"
end if

At this point, I suppose you can compare this result to something, but it is
not 100% at what point you want the compare to happen. Also, the above
dlookup of course only returns the first value from the query
SomeUnionQueryName. If the query has more then one value, then you could
use:

dim strSql as string
dim rstRecs as dao.RecordSet

set rstRecs = currentdb.OpenRecordSet("YourUnionQueryName")
do while rstRecs.EOF = false
debug.print rstRecs!SomeField
rstRecs.MoveNext
loop
rstRecs.Close
set rstRecs = nothing

The above would read all values from the query, and display them in the
debug window. I don't know if your Union Query always returns just ONE
value, or a bunch of values? And, if it returns a bunch of values, how many
of the values from the query do you want to compare to somthing?

 

[Stephanie]

Thanks for the help. My union query does just return one
value (once it knows ContactID). So I'll play around a
bit and see if I can figure it out based on your
information. Thanks again- I appreciate the lessons!
Stephanei

 

[Stephanie]

OK, I get it. I can't work it that way. What I really
want to do is learned "How to create your own input box
form (which I have) and pause code for user input"- steps
curtesy of ATTAC Consulting Group's Access Tips. That
way I could use my union query for the specific ContactID-
allowing the user to enter that info for me, loop
through the records for that ContactID, add those values
together and then compare the summed value against the
current record. I have the steps for how to do it (from
ATTAC), and the code I want to loop through, but not the
coding capabilities to add the bells and whistles that
will make it work.

Basically, I'm storing 2 like fields in two different
tables. From one, it's easy to get the value I want
since ContactID is in the table. For the other,
ContactID is 3 tables away. That's why I wanted to use
the union query. But you are correct- the union query
brings back many values, and several lines for the same
ContactID. Maybe I'm just over my head. Can it be
done? Can it be done more easily? Thanks for your
patience. Stephanie