turn - translate SQL into VBA?

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

I would like to turn 20 SQL queries that look like the two
below into one macro so that they can run one after the
other. I have already built a macro that runs the queries
one after the other, BUT that requires the queries to
exist. I figure that if I can turn SQL into VBA then I
will not need the actual queries. Can it be done?

INSERT INTO tbl_TEST_A ( ID, Location, Posture, Phase,
Rate )
SELECT [3Brates].ID, [3Brates].Location, Forms!
frmCLIIIBappend!lblBA.Caption AS Expr1, Forms!
frmCLIIIBappend!lblB.Caption AS Expr2, [3Brates].BUorAT AS
Rate
FROM 3Brates;

INSERT INTO tbl_TEST_A ( ID, Location, Posture, Phase,
Rate )
SELECT [3Brates].ID, [3Brates].Location, Forms!
frmCLIIIBappend!lblBDd.Caption AS Expr1, Forms!
frmCLIIIBappend!lblB.Caption AS Expr2, [3Brates].BUorAT AS
Rate
FROM 3Brates;
 
JR,

I'm a bit confused about your question, so hope I'm on track here with
the answer.

In a macro, you could simply use a series of RunSQL actions, one for
each query, and write your SQL straight into the SQL Statement argument
of the macro design.

Alternatively, you could save the queries in your database, and use a
series of OpenQuery actions, one for each query, to run them. This, to
me, is the preferable approach.

If, on the other hand, you want to do this in a VBA procedure instead of
a macro, you could use a DoCmd.RunSQL method, or some people prefer to
use CurrentDb.Execute. If this is being triggered from an event on the
frmCLIIIBappend form, as seems likely, the code might look something
like this...
Dim strSQL As String
strSQL = "INSERT INTO tbl_TEST_A ( ID, Location, Posture, Phase, Rate
)" & _
" SELECT ID, Location, '" & Me!lblBA.Caption & "', '" &
Me!lblB.Caption & "', BUorAT" & _
" FROM 3Brates"
CurrentDb.Execute strSQL, dbFailOnError
 
Thanks Steve,

What I don't want to have to do is store the queries in
the database as queries if I can avoid it.

The information you provided (I am new to Access Macros
which are (or appear) unlike the other VBAs I am used to --
mostly excell but also word) on how to use an Access
Macro to do it might work. I will give it a try.

Why do you prefer: said:
Alternatively, you could save the queries in your
Click to expand...
database, and use a series of OpenQuery actions, one for
each query, to run them. This, to me, is the preferable
approach.
<><><><><><><><><><><><><><> ?



I have a master database that I use to provide different
users the same data, but in different formats. The data
base is updated as newer data comes in.

In the particular instance I am working on requires twenty
append queries (five postures over four phases) to
translate my horizontal array into a vertical array.

JR
-----Original Message-----
JR,

I'm a bit confused about your question, so hope I'm on track here with
the answer.

In a macro, you could simply use a series of RunSQL actions, one for
each query, and write your SQL straight into the SQL Statement argument
of the macro design.

Alternatively, you could save the queries in your database, and use a
series of OpenQuery actions, one for each query, to run them. This, to
me, is the preferable approach.

If, on the other hand, you want to do this in a VBA procedure instead of
a macro, you could use a DoCmd.RunSQL method, or some people prefer to
use CurrentDb.Execute. If this is being triggered from an event on the
frmCLIIIBappend form, as seems likely, the code might look something
like this...
Dim strSQL As String
strSQL = "INSERT INTO tbl_TEST_A ( ID, Location, Posture, Phase, Rate
)" & _
" SELECT ID, Location, '" & Me!lblBA.Caption & "', '" &
Me!lblB.Caption & "', BUorAT" & _
" FROM 3Brates"
CurrentDb.Execute strSQL, dbFailOnError

--
Steve Schapel, Microsoft Access MVP
I would like to turn 20 SQL queries that look like the two
below into one macro so that they can run one after the
other. I have already built a macro that runs the queries
one after the other, BUT that requires the queries to
exist. I figure that if I can turn SQL into VBA then I
will not need the actual queries. Can it be done?

INSERT INTO tbl_TEST_A ( ID, Location, Posture, Phase,
Rate )
SELECT [3Brates].ID, [3Brates].Location, Forms!
frmCLIIIBappend!lblBA.Caption AS Expr1, Forms!
frmCLIIIBappend!lblB.Caption AS Expr2, [3Brates].BUorAT AS
Rate
FROM 3Brates;

INSERT INTO tbl_TEST_A ( ID, Location, Posture, Phase,
Rate )
SELECT [3Brates].ID, [3Brates].Location, Forms!
frmCLIIIBappend!lblBDd.Caption AS Expr1, Forms!
frmCLIIIBappend!lblB.Caption AS Expr2, [3Brates].BUorAT AS
Rate
FROM 3Brates;
Click to expand...
.
Click to expand...
 
Back
Top