SQL code in VB with "Access"

[GenesisVF]

Hello all,

I would like to use some complex SQL statements inside my access
project which uses VB and I am trying to find out a way of adding SQL
code into VBA in such way that VBA understands the syntax.

My aim is to work SQL commands in Access and be able to set the SQL
variables from a form.

Currently I know how to "wrap" SQL statements in VBA strings and then
concatenate the strings and send them to be processed.

For example at the moment if I have an SQL statement that looks like
this:

select * from employee_table where name = 'GEORGE' and employee_number
= '1234'

In VBA I would translate the above command as:

SQL_string_1 = "select * from employee_table where name = ' "

name = (Get_variable_from_form)

SQL_String_2 = " ' and employee_number = ' "

employee_number = (Get_the_other_variable_from_the_form)

SQL_String_3 = " ' "

and then, I would run the equivalent VB command to process it:

process: SQL_String_1 & cell_id & SQL_String_2 & bsc_id &
SQL_String_3

In essence, I have to HARD CODE the SQL commands into VBA strings and
this is such a pain and bad programming!

Do you know any other way ?

Your help would be most grateful.


Thanks

Genxx

 

[Douglas J. Steele]

You can create a query that refers to controls on a form:

SELECT *
FROM employee_table
WHERE [name] = Forms![NameOfForm]![NameOfControl]
AND employee_number = Forms![NameOfForm]![NameOfControl]


Note that "name" isn't a good choice for a field name: it's a reserved word,
and using reserved words for your own purposes can lead to problems. If you
cannot (or will not) change the name, at least enclose it in square
brackets, as I did above. For a good discussion about what names to avoid,
see what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[GenesisVF]

Thank's for your reply Doug,
The code above was a simple example. So, from what you are saying I
can include plain SQL code in VB, right?
More speciffically, in a module I'll write SQL commands and VB can
handle them?

SELECT
source,
o_date,
MAX(int_ext_all) int_ext_all,
SUM(hand_out_atts) hand_out_atts
....
...
..
FROM
beh_bsc1_hourly
WHERE
------------------------------------------------------------------------------------------------------------------------
becc.sampled >= TO_DATE('Forms![NameOfForm]![NameOfControl]','DD-MON-
RR')
------------------------------------------------------------------------------------------------------------------------
....
...
..
Is this valid to use ?

Thanks again for your reply

George

You can create a query that refers to controls on a form:

SELECT *
FROM employee_table
WHERE [name] = Forms![NameOfForm]![NameOfControl]
AND employee_number = Forms![NameOfForm]![NameOfControl]

Note that "name" isn't a good choice for a field name: it's a reserved word,
and using reserved words for your own purposes can lead to problems. If you
cannot (or will not) change the name, at least enclose it in square
brackets, as I did above. For a good discussion about what names to avoid,
see what Allen Browne has athttp://www.allenbrowne.com/AppIssueBadWord.html

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

Hello all,

I would like to use some complex SQL statements inside my access
project which uses VB and I am trying to find out a way of adding SQL
code into VBA in such way that VBA understands the syntax.

My aim is to work SQL commands in Access and be able to set the SQL
variables from a form.

Currently I know how to "wrap" SQL statements in VBA strings and then
concatenate the strings and send them to be processed.

For example at the moment if I have an SQL statement that looks like
this:

select * from employee_table where name = 'GEORGE' and employee_number
= '1234'

In VBA I would translate the above command as:

SQL_string_1 = "select * from employee_table where name = ' "

name = (Get_variable_from_form)

SQL_String_2 = " ' and employee_number = ' "

employee_number = (Get_the_other_variable_from_the_form)

SQL_String_3 = " ' "

and then, I would run the equivalent VB command to process it:

process: SQL_String_1 & cell_id & SQL_String_2 & bsc_id &
SQL_String_3

In essence, I have to HARD CODE the SQL commands into VBA strings and
this is such a pain and bad programming!

Do you know any other way ?

Your help would be most grateful.

Genxx

 

[Douglas J. Steele]

Valid to use for what? Creating SQL in VB doesn't really do anything: you
have to assign the SQL as, say, the RecordSource property for a form or the
RowSource property for a combo or list box, or else you have to use the SQL
to open a recordset.

I think you may have misinterpretted things. If you're going to use SQL in
VB, you have to assign it to a string. In other words, your original SQL
statement would still have to be:

strSQL = "SELECT * " & _
"FROM employee_table " & _
"WHERE [name] = """ & Forms![NameOfForm]![NameOfControl] & """, " & _
"AND employee_number = " & Forms![NameOfForm]![NameOfControl]

(I don't understand your comment that it's "bad programming" to do this.)

(Note that I'm assuming name is a text field, so it's necessary to include
quotes around the value being passed, and that employee_number is a numeric
field, not a number stored in a text field)

Note, too, that the SQL in your second example isn't syntactically valid.

I'm assuming you're trying to alias the computed fields below:

MAX(int_ext_all) int_ext_all,
SUM(hand_out_atts) hand_out_atts

First, you're missing the AS keyword between the field and its alias and
second, you cannot reuse the field name as the alias. That would have to be
something liks

MAX(int_ext_all) AS BiggestValue,
SUM(hand_out_atts) AS TotalValue

Also, I'm not sure what your TO_DATE function is supposed to be: there's no
such function built into Access.

If I were writing that in-line, I'd likely use:

" becc.sampled >= " & Format(CDate(Forms![NameOfForm]![NameOfControl]),
"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank's for your reply Doug,
The code above was a simple example. So, from what you are saying I
can include plain SQL code in VB, right?
More speciffically, in a module I'll write SQL commands and VB can
handle them?

SELECT
source,
o_date,
MAX(int_ext_all) int_ext_all,
SUM(hand_out_atts) hand_out_atts
...
..
.
FROM
beh_bsc1_hourly
WHERE
------------------------------------------------------------------------------------------------------------------------
becc.sampled >= TO_DATE('Forms![NameOfForm]![NameOfControl]','DD-MON-
RR')
------------------------------------------------------------------------------------------------------------------------
...
..
.
Is this valid to use ?

Thanks again for your reply

George

You can create a query that refers to controls on a form:

SELECT *
FROM employee_table
WHERE [name] = Forms![NameOfForm]![NameOfControl]
AND employee_number = Forms![NameOfForm]![NameOfControl]

Note that "name" isn't a good choice for a field name: it's a reserved
word,
and using reserved words for your own purposes can lead to problems. If
you
cannot (or will not) change the name, at least enclose it in square
brackets, as I did above. For a good discussion about what names to
avoid,
see what Allen Browne has
athttp://www.allenbrowne.com/AppIssueBadWord.html

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

Hello all,

I would like to use some complex SQL statements inside my access
project which uses VB and I am trying to find out a way of adding SQL
code into VBA in such way that VBA understands the syntax.

My aim is to work SQL commands in Access and be able to set the SQL
variables from a form.

Currently I know how to "wrap" SQL statements in VBA strings and then
concatenate the strings and send them to be processed.

For example at the moment if I have an SQL statement that looks like
this:

select * from employee_table where name = 'GEORGE' and employee_number
= '1234'

In VBA I would translate the above command as:

SQL_string_1 = "select * from employee_table where name = ' "

name = (Get_variable_from_form)

SQL_String_2 = " ' and employee_number = ' "

employee_number = (Get_the_other_variable_from_the_form)

SQL_String_3 = " ' "

and then, I would run the equivalent VB command to process it:

process: SQL_String_1 & cell_id & SQL_String_2 & bsc_id &
SQL_String_3

In essence, I have to HARD CODE the SQL commands into VBA strings and
this is such a pain and bad programming!

Do you know any other way ?

Your help would be most grateful.

Genxx

 

[David W. Fenton]

you cannot reuse the field name as the alias.

Eh? Yes, you can always re-use the fieldname as the alias if you're
replacing a straight field with a calculated expression. However,
you have to be careful in case the field whose name you're using
happens to be used in other expressions -- the results might be
unexpected.