How to use SQL variables in VBA

  • Thread starter Thread starter schoultzy
  • Start date Start date
S

schoultzy

Hello, I have the following SQL code:

DECLARE draw_num2 int

SELECT draw_num2 = max (draw_num)+ 1 --THIS LINE IS MY PROBLEM
FROM hdx_housing_assign_master

I want to execute this code in Access VBA, however, I am having a
problem understanding how to accomplish this same task in VBA. My
problem is that the variable is manipulated within the SQL statement
and I do not know how to re-produce that functionality in VBA.

Thanks for Your Help,
Matt
 
schoultzy said:
Hello, I have the following SQL code:

DECLARE draw_num2 int

SELECT draw_num2 = max (draw_num)+ 1 --THIS LINE IS MY PROBLEM
FROM hdx_housing_assign_master

I want to execute this code in Access VBA, however, I am having a
problem understanding how to accomplish this same task in VBA. My
problem is that the variable is manipulated within the SQL statement
and I do not know how to re-produce that functionality in VBA.
Click to expand...


I don't know what variety of SQL you are using, but the
"normal" way of doing that is:

SELECT max(draw_num)+ 1 AS draw_num2
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your example looks like a SQL Server type DB engine (but, not SQL
Server). This means you can put that code in a Stored Procedure (SP)
and return the variable draw_num2. Something like this (using SQL
Server T-SQL syntax):

CREATE PROCEDURE GetDrawNum()
AS

DECLARE @draw_num2 int

SELECT @draw_num2 = max (draw_num)+ 1
FROM hdx_housing_assign_master

RETURN @draw_num2


Or, like this:

CREATE PROCEDURE GetDrawNum(
@draw_num2 int OUT
)
AS

SELECT @draw_num2 = max (draw_num)+ 1
FROM hdx_housing_assign_master

RETURN

You can use DAO or ADO to retrieve the results of the SP.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRCxlr4echKqOuFEgEQJM+QCgxxHrQjjz/tmX3STGSSeG4dA1TGQAn38U
c7H4MKzehBp+0sCWDAN3DJqz
=GTYm
-----END PGP SIGNATURE-----
 
Back
Top