How do I Pass VB Variables via SQL?? (1 Viewer)

[sphynx]

Hi need a little help with how to get an SQL insert string to recognise VB variable as the insert values.

Basically I have a Project Management Database I am working on.

Each project has 281 standard steps split into 8 gates.

The powers that be have a meeting every Monday to discuss and update project paths, after this meeting they would like to capture gate completion status data and generate a weekly KPI.

I already have a query that tracks the gate status of each project but needed a way to count these and add to a table to create a historic weekly record of progress.

I decided to go for a completion form at the end of the meeting to pass the command to generate and capture the appropriate data.

I have written the following code to calculate and pass the information to my table, the VB variables capture the appropriate data but my SQL values statement does not pass the info into the table, instead I get the enter parameters box.

Private Sub Command2_Click()

Dim strSQL As String

Dim TStatus1 As Double
Dim NDStatus1 As Double
Dim DStatus1 As Double
Dim ODStatus1 As Double
Dim CStatus1 As Double


TStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry")
NDStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'NOT DUE'")
DStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'DUE'")
ODStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'OVER DUE'")
CStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'COMPLETE'")

         
strSQL = "INSERT INTO GateKPI_Tbl (TSTATUS1, NDSTATUS1, DSTATUS1, ODSTATUS1, CSTATUS1)" & _
         "VALUES (TStatus1, NDStatus1, DStatus1, ODStatus1, CStatus1);"
           
DoCmd.RunSQL strSQL

Any help would be greatley recieved

 

[Kiwiman]

Howzit

Try

strSQL = "INSERT INTO GateKPI_Tbl (TSTATUS1, NDSTATUS1, DSTATUS1, ODSTATUS1, CSTATUS1)" & _
         " VALUES (" & TStatus1 & "," & NDStatus1 & "," & DStatus1 & "," & ODStatus1 & "," CStatus1 & ");"

 

[sphynx]

That worked a treat, Thanks a lot

 

[namliam]

If you are doing multiple DCounts on one table it is much better to do 1 sql statement instead... faster, more efficient... More managable..

 

[sphynx]

This is my completed Sub, it takes approx 30 seconds to run. If I understand you correctly are you suggesting I omit the Dcounts stored as variable and add the Dcount function to the SQL sequence?

Private Sub Command2_Click()

Dim strSQL As String
Dim TStatus1, NDStatus1, DStatus1, ODStatus1, CStatus1 As Double
Dim TStatus2, NDStatus2, DStatus2, ODStatus2, CStatus2 As Double
Dim TStatus3, NDStatus3, DStatus3, ODStatus3, CStatus3 As Double
Dim TStatus4, NDStatus4, DStatus4, ODStatus4, CStatus4 As Double
Dim TStatus5, NDStatus5, DStatus5, ODStatus5, CStatus5 As Double
Dim TStatus6, NDStatus6, DStatus6, ODStatus6, CStatus6 As Double
Dim TStatus7, NDStatus7, DStatus7, ODStatus7, CStatus7 As Double
Dim TStatus8, NDStatus8, DStatus8, ODStatus8, CStatus8 As Double

MsgBox ("YOUR REQUEST WILL TAKE APPROX 30 SECS" & vbCrLf & vbLf & "A SECOND MESSAGE WILL BE DISPLAYED WHEN COMPLETE")

'GATE 1
TStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry")
NDStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'NOT DUE'")
DStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'DUE'")
ODStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'OVER DUE'")
CStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'COMPLETE'")

'GATE 2
TStatus2 = DCount("[2 STATUS]", "CurrentLaunch_Qry")
NDStatus2 = DCount("[2 STATUS]", "CurrentLaunch_Qry", "[2 STATUS] = 'NOT DUE'")
DStatus2 = DCount("[2 STATUS]", "CurrentLaunch_Qry", "[2 STATUS] = 'DUE'")
ODStatus2 = DCount("[2 STATUS]", "CurrentLaunch_Qry", "[2 STATUS] = 'OVER DUE'")
CStatus2 = DCount("[2 STATUS]", "CurrentLaunch_Qry", "[2 STATUS] = 'COMPLETE'")

'GATE 3
TStatus3 = DCount("[3 STATUS]", "CurrentLaunch_Qry")
NDStatus3 = DCount("[3 STATUS]", "CurrentLaunch_Qry", "[3 STATUS] = 'NOT DUE'")
DStatus3 = DCount("[3 STATUS]", "CurrentLaunch_Qry", "[3 STATUS] = 'DUE'")
ODStatus3 = DCount("[3 STATUS]", "CurrentLaunch_Qry", "[3 STATUS] = 'OVER DUE'")
CStatus3 = DCount("[3 STATUS]", "CurrentLaunch_Qry", "[3 STATUS] = 'COMPLETE'")

'GATE 4
TStatus4 = DCount("[4 STATUS]", "CurrentLaunch_Qry")
NDStatus4 = DCount("[4 STATUS]", "CurrentLaunch_Qry", "[4 STATUS] = 'NOT DUE'")
DStatus4 = DCount("[4 STATUS]", "CurrentLaunch_Qry", "[4 STATUS] = 'DUE'")
ODStatus4 = DCount("[4 STATUS]", "CurrentLaunch_Qry", "[4 STATUS] = 'OVER DUE'")
CStatus4 = DCount("[4 STATUS]", "CurrentLaunch_Qry", "[4 STATUS] = 'COMPLETE'")

'GATE 5
TStatus5 = DCount("[5 STATUS]", "CurrentLaunch_Qry")
NDStatus5 = DCount("[5 STATUS]", "CurrentLaunch_Qry", "[5 STATUS] = 'NOT DUE'")
DStatus5 = DCount("[5 STATUS]", "CurrentLaunch_Qry", "[5 STATUS] = 'DUE'")
ODStatus5 = DCount("[5 STATUS]", "CurrentLaunch_Qry", "[5 STATUS] = 'OVER DUE'")
CStatus5 = DCount("[5 STATUS]", "CurrentLaunch_Qry", "[5 STATUS] = 'COMPLETE'")

'GATE 6
TStatus6 = DCount("[6 STATUS]", "CurrentLaunch_Qry")
NDStatus6 = DCount("[6 STATUS]", "CurrentLaunch_Qry", "[6 STATUS] = 'NOT DUE'")
DStatus6 = DCount("[6 STATUS]", "CurrentLaunch_Qry", "[6 STATUS] = 'DUE'")
ODStatus6 = DCount("[6 STATUS]", "CurrentLaunch_Qry", "[6 STATUS] = 'OVER DUE'")
CStatus6 = DCount("[6 STATUS]", "CurrentLaunch_Qry", "[6 STATUS] = 'COMPLETE'")

'GATE 7
TStatus7 = DCount("[7 STATUS]", "CurrentLaunch_Qry")
NDStatus7 = DCount("[7 STATUS]", "CurrentLaunch_Qry", "[7 STATUS] = 'NOT DUE'")
DStatus7 = DCount("[7 STATUS]", "CurrentLaunch_Qry", "[7 STATUS] = 'DUE'")
ODStatus7 = DCount("[7 STATUS]", "CurrentLaunch_Qry", "[7 STATUS] = 'OVER DUE'")
CStatus7 = DCount("[7 STATUS]", "CurrentLaunch_Qry", "[7 STATUS] = 'COMPLETE'")

'GATE 8
TStatus8 = DCount("[8 STATUS]", "CurrentLaunch_Qry")
NDStatus8 = DCount("[8 STATUS]", "CurrentLaunch_Qry", "[8 STATUS] = 'NOT DUE'")
DStatus8 = DCount("[8 STATUS]", "CurrentLaunch_Qry", "[8 STATUS] = 'DUE'")
ODStatus8 = DCount("[8 STATUS]", "CurrentLaunch_Qry", "[8 STATUS] = 'OVER DUE'")
CStatus8 = DCount("[8 STATUS]", "CurrentLaunch_Qry", "[8 STATUS] = 'COMPLETE'")
               
'INSERT INFO INTO GateKPI_Tbl
strSQL = "INSERT INTO GateKPI_Tbl (TSTATUS1, NDSTATUS1, DSTATUS1, ODSTATUS1, CSTATUS1, " & _
                                  "TSTATUS2, NDSTATUS2, DSTATUS2, ODSTATUS2, CSTATUS2, " & _
                                  "TSTATUS3, NDSTATUS3, DSTATUS3, ODSTATUS3, CSTATUS3, " & _
                                  "TSTATUS4, NDSTATUS4, DSTATUS4, ODSTATUS4, CSTATUS4, " & _
                                  "TSTATUS5, NDSTATUS5, DSTATUS5, ODSTATUS5, CSTATUS5, " & _
                                  "TSTATUS6, NDSTATUS6, DSTATUS6, ODSTATUS6, CSTATUS6, " & _
                                  "TSTATUS7, NDSTATUS7, DSTATUS7, ODSTATUS7, CSTATUS7, " & _
                                  "TSTATUS8, NDSTATUS8, DSTATUS8, ODSTATUS8, CSTATUS8)" & _
          " VALUES (" & TStatus1 & "," & NDStatus1 & "," & DStatus1 & "," & ODStatus1 & "," & CStatus1 & "," & _
                   "" & TStatus2 & "," & NDStatus2 & "," & DStatus2 & "," & ODStatus2 & "," & CStatus2 & "," & _
                   "" & TStatus3 & "," & NDStatus3 & "," & DStatus3 & "," & ODStatus3 & "," & CStatus3 & "," & _
                   "" & TStatus4 & "," & NDStatus4 & "," & DStatus4 & "," & ODStatus4 & "," & CStatus4 & "," & _
                   "" & TStatus5 & "," & NDStatus5 & "," & DStatus5 & "," & ODStatus5 & "," & CStatus5 & "," & _
                   "" & TStatus6 & "," & NDStatus6 & "," & DStatus6 & "," & ODStatus6 & "," & CStatus6 & "," & _
                   "" & TStatus7 & "," & NDStatus7 & "," & DStatus7 & "," & ODStatus7 & "," & CStatus7 & "," & _
                   "" & TStatus8 & "," & NDStatus8 & "," & DStatus8 & "," & ODStatus8 & "," & CStatus8 & ");"
      
DoCmd.RunSQL strSQL

MsgBox ("KPI DATA HAD BEEN RECORDED")

Call Command108_Click ' call for separate Auto mail function 

End Sub

 

[sphynx]

I have had a play around, is the SQL below how you suggested or is there a way to use the Dcount function just once?

INSERT INTO GateKPI_Tbl ( TSTATUS1, NDSTATUS1, DSTATUS1, ODSTATUS1, CSTATUS1, TSTATUS2, NDSTATUS2, DSTATUS2, ODSTATUS2, CSTATUS2, TSTATUS3, NDSTATUS3, DSTATUS3, ODSTATUS3, CSTATUS3, TSTATUS4, NDSTATUS4, DSTATUS4, ODSTATUS4, CSTATUS4, TSTATUS5, NDSTATUS5, DSTATUS5, ODSTATUS5, CSTATUS5, TSTATUS6, NDSTATUS6, DSTATUS6, ODSTATUS6, CSTATUS6, TSTATUS7, NDSTATUS7, DSTATUS7, ODSTATUS7, CSTATUS7, TSTATUS8, NDSTATUS8, DSTATUS8, ODSTATUS8, CSTATUS8 )
SELECT DCount("[1 STATUS]","CurrentLaunch_Qry") AS TS1, DCount("[1 STATUS]","CurrentLaunch_Qry","[1 STATUS] = 'NOT DUE'") AS NDS1, DCount("[1 STATUS]","CurrentLaunch_Qry","[1 STATUS] = 'DUE'") AS DS1, DCount("[1 STATUS]","CurrentLaunch_Qry","[1 STATUS] = 'OVER DUE'") AS ODS1, DCount("[1 STATUS]","CurrentLaunch_Qry","[1 STATUS] = 'COMPLETE'") AS CS1, DCount("[2 STATUS]","CurrentLaunch_Qry") AS TS2, DCount("[2 STATUS]","CurrentLaunch_Qry","[2 STATUS] = 'NOT DUE'") AS NDS2, DCount("[2 STATUS]","CurrentLaunch_Qry","[2 STATUS] = 'DUE'") AS DS2, DCount("[2 STATUS]","CurrentLaunch_Qry","[2 STATUS] = 'OVER DUE'") AS ODS2, DCount("[2 STATUS]","CurrentLaunch_Qry","[2 STATUS] = 'COMPLETE'") AS CS2, DCount("[3 STATUS]","CurrentLaunch_Qry") AS TS3, DCount("[3 STATUS]","CurrentLaunch_Qry","[3 STATUS] = 'NOT DUE'") AS NDS3, DCount("[3 STATUS]","CurrentLaunch_Qry","[3 STATUS] = 'DUE'") AS DS3, DCount("[3 STATUS]","CurrentLaunch_Qry","[3 STATUS] = 'OVER DUE'") AS ODS3, DCount("[3 STATUS]","CurrentLaunch_Qry","[3 STATUS] = 'COMPLETE'") AS CS3, DCount("[4 STATUS]","CurrentLaunch_Qry") AS TS4, DCount("[4 STATUS]","CurrentLaunch_Qry","[4 STATUS] = 'NOT DUE'") AS NDS4, DCount("[4 STATUS]","CurrentLaunch_Qry","[4 STATUS] = 'DUE'") AS DS4, DCount("[4 STATUS]","CurrentLaunch_Qry","[4 STATUS] = 'OVER DUE'") AS ODS4, DCount("[4 STATUS]","CurrentLaunch_Qry","[4 STATUS] = 'COMPLETE'") AS CS4, DCount("[5 STATUS]","CurrentLaunch_Qry") AS TS5, DCount("[5 STATUS]","CurrentLaunch_Qry","[5 STATUS] = 'NOT DUE'") AS NDS5, DCount("[5 STATUS]","CurrentLaunch_Qry","[5 STATUS] = 'DUE'") AS DS5, DCount("[5 STATUS]","CurrentLaunch_Qry","[5 STATUS] = 'OVER DUE'") AS ODS5, DCount("[5 STATUS]","CurrentLaunch_Qry","[5 STATUS] = 'COMPLETE'") AS CS5, DCount("[6 STATUS]","CurrentLaunch_Qry") AS TS6, DCount("[6 STATUS]","CurrentLaunch_Qry","[6 STATUS] = 'NOT DUE'") AS NDS6, DCount("[6 STATUS]","CurrentLaunch_Qry","[6 STATUS] = 'DUE'") AS DS6, DCount("[6 STATUS]","CurrentLaunch_Qry","[6 STATUS] = 'OVER DUE'") AS ODS6, DCount("[6 STATUS]","CurrentLaunch_Qry","[6 STATUS] = 'COMPLETE'") AS CS6, DCount("[7 STATUS]","CurrentLaunch_Qry") AS TS7, DCount("[7 STATUS]","CurrentLaunch_Qry","[7 STATUS] = 'NOT DUE'") AS NDS7, DCount("[7 STATUS]","CurrentLaunch_Qry","[7 STATUS] = 'DUE'") AS DS7, DCount("[7 STATUS]","CurrentLaunch_Qry","[7 STATUS] = 'OVER DUE'") AS ODS7, DCount("[7 STATUS]","CurrentLaunch_Qry","[7 STATUS] = 'COMPLETE'") AS CS7, DCount("[8 STATUS]","CurrentLaunch_Qry") AS TS8, DCount("[8 STATUS]","CurrentLaunch_Qry","[8 STATUS] = 'NOT DUE'") AS NDS8, DCount("[8 STATUS]","CurrentLaunch_Qry","[8 STATUS] = 'DUE'") AS DS8, DCount("[8 STATUS]","CurrentLaunch_Qry","[8 STATUS] = 'OVER DUE'") AS ODS8, DCount("[8 STATUS]","CurrentLaunch_Qry","[8 STATUS] = 'COMPLETE'") AS CS8;

 

[DCrake]

Hell of a lot of DCounts there

Have a play with this idea.

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("CurrentLaunch_qry")

If Not Rs.EOF And Not Rs.BOF Then

   Do Until Rs.EOF
      [B]'GATE 1 [/B]
      TStatus1 = TStatus1 + 1
      Select Case Rs("1 Status")
          Case "NOT DUE"
                 NDStatus1 = NDStatus1 + 1
          Case "DUE"
                 DStatus1 = DStatus1 + 1
          Case "OVERDUE"
                 ODStatus1 = ODStatus1 + 1
          Case "COMPLETE"
                 CStatus1 = CStatus1 + 1
      End Select
       [B]'GATE 2 [/B]
      TStatus2 = TStatus2 + 1
      Select Case Rs("2 Status")
          Case "NOT DUE"
                 NDStatus2 = NDStatus2 + 1
          Case "DUE"
                 DStatus2 = DStatus2 + 1
          Case "OVERDUE"
                 ODStatus2 = ODStatus2 + 1
          Case "COMPLETE"
                 CStatus2 = CStatus2 + 1
      End Select
      'REPEAT FOR EACH GATE
      Rs.MoveNext
   Loop
   Rs.Close
End If

Set Rs = Nothing

'Rest of the code

This will cut out all of the dcounts. also all the TStatus1,2,3,4,etc will all be the same value as they are simply counting the number of records returned by the query. The fact that they are counting different fields makes no difference to the number returned.

David

 

[namliam]

*bbbbbrrrrr* 1 status, 2 status?? sounds like you have some design issues....

TRANSFORM Count(CurrentLaunch_Qry.[1 Status]) AS [CountOf1 Status]
SELECT Count(CurrentLaunch_Qry.[1 Status]) AS Total, "x" AS Dummy
FROM CurrentLaunch_Qry
GROUP BY "x"
PIVOT CurrentLaunch_Qry.[1 Status]
In ('NOT DUE', 'DUE', 'OVER DUE', 'COMPLETE') ;

This will make your Totals as well as your different statusus...
The "in" part will ensure the different columns will always be there.

Make 8 queries like this then make a new one to join them all together on the Dummy field and make an append query.

If your DCounts are done in 10 secs, this should be done inside 1 sec.

@David
Why do something in code you can do in a query faster and more efficient ??

 

[DCrake]

Mailman

Force of habit, I have never liked having to many queries as it comes to a time when they become unmanageable. Also you are actually creating 9 objects to get 1 answer. in my solution there are no queries involved appart form the append query which is done in vba. Consistancy and LAE.

David

 

[namliam]

The 9 objects can be replace by 1 or even NONE and all done in code... Yet still all doing queries which will be 100% faster than your code.

The reason I show to make 9 objects is to make it 'more managable' for -probably- less advanced user(s)/developers. Evenso with a good naming convention there is no problem in having 1.000.000 query objects... AND have it be completely managable!

LAE??

Beyond that 1 status, 2 status, etc... *brrrrr*

 

[namliam]

Also, just the idea of doing 8 identical Case statements *brrrr*

 

[DCrake]

LAE - Least administrative effort. How to perofrm a function or exercise with the minimum amount of fuss and effort. Admittedly the 8 case statements could have been recorded inside a loop with variable parsing to the variable name.

Essentially I am a VB man and I find it easier coding from vb than having to open up a back end to create queries. When using Access as your front end then this is more efficient, however every time access encounters a query the first thing it does is to check it syntactically, then perform the action. If you code it vba and deploy an mde the mde has already qualified the code and compiled it, therefore it does not need to check it each time it encounters it. Also queries can be amended by unwitting users which may stuff up the app. Its a case of protection I suppose.

David

 

[sphynx]

Thanks for the feedback.

I am relatively new to all this and have only been working with access for 2 years, being self taught I also have a few gaps in my knowledge!

I would like to say that this forum has been an invaluable recourse, thanks again

 

[namliam]

Essentially I am a VB man and I find it easier coding from vb than having to open up a back end to create queries. When using Access as your front end then this is more efficient, however every time access encounters a query the first thing it does is to check it syntactically, then perform the action. If you code it vba and deploy an mde the mde has already qualified the code and compiled it, therefore it does not need to check it each time it encounters it. Also queries can be amended by unwitting users which may stuff up the app. Its a case of protection I suppose.

Still what if a code is added? Then again that is a problem with this whole setup...

When running thru recordsets and getting counts/sums/averages etc.... queries pre-compiled or otherwize will (nearly) always beat out code by miles and miles.

In particular on bigger recordsets the differences are shocking...

@sphynx
I am curious to know how you solved your problem at hand... or did you stick with the DCount(s)? I guess 10 secs is nothing to really worry about...

 

[sphynx]

Eventually I decided to run with the SQL statment that I posted as an append query within access which is run from my button and VB.

The function now takes approx 5 seconds to complete. The DB is only a test system with 5 records in it so I may have to alter this at a later point in time

I did however look at the SQL statment you posted and ran this as a query which worked fine.

What I couldn't work out is how it worked!, I am unfamiliar with crosstab queries and some of the statement that you have used so if you have the time to explain how it works and what the staments do I would appreciate it.

I will also look at DCrake code though as I generally prefer to work in VB

Thanks again

 

[namliam]

What part dont you understand?

 

[sphynx]

Sorry haven't replied sooner, a family emergency has kept me from work.

As soon as get my head back into things I will re post

Thanks