i would like some help on putting some queries into vba. i have 4 of them and dont know how to start putting sql into vba. i have the sql from the queries but it surely cant be as easy as
Docmd.RunSQL (then copy and paste the sql from the query)
i have tried it before and it did not work and yes if i try it again it might happen.
should it work then?
PHP:
DoCmd.RunSQL (INSERT INTO tblStartTimeUpdate) OrdersItemsID, StartTime )SELECT RunningSumTotal.OrdersItemsID, [RunningTotal]-[TreatmentTime] AS StartTime FROM RunningSumTotal;)
this is the sql pasted straight in. i know the sql it built in sequence so what do i do with it?
should be: DoCmd.RunSQL ("INSERT INTO tblStartTimeUpdate (OrdersItemsID, StartTime )SELECT RunningSumTotal.OrdersItemsID, [RunningTotal]-[TreatmentTime] AS StartTime FROM RunningSumTotal")
it has come up with a runtime error
syntax error in INSERT INTO statement.
for me when this is cracked it will make my life so much easier. i have so many vba codes that update and append table records, if i can just generate the query and paste the sql into the vba(sort of) then it will be so much easier.
on another note. which is better.
running sql in vba
or
just the vba doing the work.
if you know what i mean
Why exactly? First, why are you updating and appending so much? Second, why are you doing this through VBA?
As to the runtime error, instead of directly running your SQL, assign it to a variable and then use that variable in the DoCmd.RunSQL call. That way, when something does go wrong you can spit out what the actual SQL is (i.e. MsgBox(StrSQL)) and you can see with your own eyes actually what its trying to run. If you can't immediately see any errors, paste that SQL into a query and see if you can use that to debug it.
plog. i dont know any other way. i cant really say anymore that i am new to this as i have been building this database since sept 2011. but i have had no formal teaching of programming so its the only way i know. there are many thing i am now doing differently now i know better. in time i may get it perfect but at present i can only do what dowrk then try to find a better answer.
i can only see me appending more. i dont really like having bound txt boxes on my forms so if i can append then i will. im not sure if this is the correct way of doing things but at the moment it is working.
Constructing SQL statements in VBA can become very tiresome and time-consuming. A better way would be using a "sql statements" table. That table would hold the ID number of the statement, the statement itself and the number of parameters (if any) to pass to the statement. After establishing the table, you can generate any query you want in Access' query grid, copy its SQL code, and paste into the table. Then you need to write a custom function that would fetch that sql statement and replace any placeholder parameters with the ones you want to pass (I can post my version of this function if you like). Using the function and the table, it becomes very easy to manage hundreds of ad-hoc queries.
Constructing SQL statements in VBA can become very tiresome and time-consuming. A better way would be using a "sql statements" table. That table would hold the ID number of the statement, the statement itself and the number of parameters (if any) to pass to the statement. After establishing the table, you can generate any query you want in Access' query grid, copy its SQL code, and paste into the table. Then you need to write a custom function that would fetch that sql statement and replace any placeholder parameters with the ones you want to pass (I can post my version of this function if you like). Using the function and the table, it becomes very easy to manage hundreds of ad-hoc queries.
Sure, I'd like to share my version. This technique practically eliminates the need to construct any SQL statement using VBA code forever, so I'm sure many people have developed their own version of it in one way or another.
For this technique to work, I use the following table:
zstblSQL StatementID (Autonumber) - The PK Statement (Memo) - The statement ParametreSayisi (Byte, Required, Default=0) - The number of parameters in the statement Notes (Text, Not required) - Optional notes for my future self, to remember where this statement is used and for what etc.
This table is populated by copy/paste operations. For any query, you design it in Access' query design grid, get it to work the way you want, switch to SQL view and just copy/paste the SQL code into zstblSQL.
The only trick in populating this table is about parameters. If there is a parameter in the query that you may want to change in future runs, it has to be replaced with the expression "paramX" where X is a positive number (byte) starting from 1 and never exceeding 254. The number X should also be increased by one for any other following parameters. Example:
Names of employees whose pay grade is 5
Code:
SELECT tblEmployees.EmployeeName FROM tblEmployees WHERE (((tblEmployees.PayGrade)=5));
If you want this query to return employee names from any pay grade, you should record it to the table as
Code:
SELECT tblEmployees.EmployeeName FROM tblEmployees WHERE (((tblEmployees.PayGrade)="param1"));
It does not matter what the parameter's data type is. Example:
Names of employees whose pay grade is 4 and whose names have the letter W in them
Code:
SELECT tblEmployees.EmployeeName, tblEmployees.PayGrade FROM tblEmployees WHERE (((tblEmployees.EmployeeName) Like "*w*") AND ((tblEmployees.PayGrade)=4));
Should be recorded to the table as
Code:
SELECT tblEmployees.EmployeeName, tblEmployees.PayGrade FROM tblEmployees WHERE (((tblEmployees.EmployeeName) Like "param1") AND ((tblEmployees.PayGrade)="param2"));
If we wanted to query the employees with the letter w in their name from any pay grade, we would modify the statement as follows:
Code:
SELECT tblEmployees.EmployeeName, tblEmployees.PayGrade FROM tblEmployees WHERE (((tblEmployees.EmployeeName) Like "*w*") AND ((tblEmployees.PayGrade)="param1"));
So the basic idea is, "replace any parameter that you may want to change with a placeholder in the form of paramX".
After you paste the statement into the table, there's one last step: record the number of parameters in your query in the ParametreSayisi field of your table. It is equal to the highest X index of the placeholders you just embedded.
After this point, we can use the function named GSQL (which stands for "Get SQL Statement") to retrieve this statement with any parameters we like. The function expects the StatementID of the query you want and returns the corresponding string which is the SQL statement itself. The function also accepts any number of arguments. The arguments passed to the function are sequentially replaced with the paramX placeholders you embedded in the SQL statement. The function replaces the placeholders (paramX'es) with the arguments you pass. I'll add my code for the function in the following post.
Example run:
Let's say you have a record in your zstblSQL table such as StatementID = 45 Statement = SELECT tblEmployees.EmployeeName, tblEmployees.PayGrade FROM tblEmployees WHERE (((tblEmployees.EmployeeName) Like "param1") AND ((tblEmployees.PayGrade)="param2")); ParametreSayisi = 2
Here are some sample runs utilizing this record:
Code:
Sub Test1()
Dim strSQL as String
strSQL = GSQL(45, "w*", 2)
Debug.Print strSQL
End Sub
Result:
SELECT tblEmployees.EmployeeName, tblEmployees.PayGrade FROM tblEmployees WHERE (((tblEmployees.EmployeeName) Like "w*") AND ((tblEmployees.PayGrade)=2));
Code:
Sub Test2()
Dim strSQL as String
strSQL = GSQL(45, 4, "x")
Debug.Print strSQL
End Sub
Result:
SELECT tblEmployees.EmployeeName, tblEmployees.PayGrade FROM tblEmployees WHERE (((tblEmployees.EmployeeName) Like "*4*") AND ((tblEmployees.PayGrade)="x"));
The second query will produce an error when you actually try to run it, because we failed to pass the arguments in the correct order. Even if the query successfully compiles, there won't be any employees whose name has the character "4" in it.
While producing recordsets, you can eliminate the step of declaring a string variable to hold the statement altogether:
Set rstSomeRecordset = CurrentDb.OpenRecordset(GSQL(45, "W*", 3), dbOpenDynaset)
The only downside of this method that I know of is the extra time it takes to retrieve the SQL statement from the table. However this step takes no more than a fraction of a second, certainly never in the noticable range, so I've never really considered it a problem myself.
And here's my code for the main and complementary functions. Error handling is left to the user. The main function is named GSQL, and the others are for ensuring that the data is SQL compatible.
Code:
Public Function GSQL(ByRef lngStatementID As Long, ParamArray varParams() As Variant) As String
Dim rstDeger As DAO.Recordset
Dim strSQL As String
Dim strReturnSQL As String 'holds the end product
Dim strArguman As String
Dim bytUbound As Byte
Dim bytLbound As Byte
Dim bytSayac As Byte 'a counter
Dim bytParametreSayisi As Byte 'holds the number of parameters
'Let's obtain the template SQL statement.
strSQL = "SELECT Statement, ParametreSayisi FROM zstblSQL WHERE StatementID=" & lngStatementID
Set rstDeger = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
If rstDeger.EOF Then
'The statement couldn't be found. This is an exception, so feel
'free to handle it any way you want
Else
strReturnSQL = rstDeger(0)
bytParametreSayisi = rstDeger(1)
'we are done with the recordset, so we let it go
Set rstDeger = Nothing
End If
'If the template SQL statement has placeholders embedded in it,
'we
If bytParametreSayisi > 0 Then 'parametreli bir queri
bytUbound = UBound(varParams)
bytLbound = LBound(varParams)
If bytParametreSayisi <> (bytUbound - bytLbound + 1) Then
'We've hit another exception. The number of parameters we passed with ParamArray
'is not consistent with the number of placeholders embedded in the SQL statement.
'handle this exception any way you want.
Else
'Since everything is in order, let's replace the placeholders with the actual data
'We do this by looping through the placeholders and the input array simultaneously.
'Therefore it is imperative that the order of the parameters we pass coincide with
'the order of the paramX placeholders.
'Also, the input data has to be converted into a format that is consumable by the SQL
'interpreter. We handle this problem with the complementary SQLValidateVariant function.
For bytSayac = bytLbound To bytUbound
strArguman = SQLValidateVariant(varParams(bytSayac))
strReturnSQL = Replace(strReturnSQL, Chr(34) & "param" & (CStr(bytSayac) + 1) & Chr(34), _
strArguman, , , vbBinaryCompare)
Next bytSayac
End If
End If
'all done. Return the end product
GSQL = strReturnSQL
End Function
Code:
Public Function SQLValidateVariant(ByVal InputVariant As Variant) As String
'Converts the input variant into a SQL-friendly string
'This step is especially important for dates/times if the local date/time format is different
'than the U.S. system.
Dim strRetVal As String 'temporary variable
Select Case VarType(InputVariant)
Case vbNull
strRetVal = "Null"
Case vbDouble, vbSingle
'The following line is required only if the local system uses the comma
'(or another character) as the decimal separator. If your local system uses the dot
'as the decimal separator then use -> strRetVal = CStr(InputVariant)
strRetVal = Replace(CStr(InputVariant), ",", ".")
Case vbDate
If DateValue(InputVariant) = InputVariant Then 'pure date
strRetVal = SQLTarih(InputVariant)
Else
strRetVal = SQLTarihSaat(InputVariant) 'non-pure date (has time data)
End If
Case vbString
If InputVariant = vbNullString Then
strRetVal = "Null"
Else
strRetVal = Chr(34) & CStr(InputVariant) & Chr(34)
End If
Case vbByte, vbLong, vbInteger
strRetVal = CStr(InputVariant)
Case vbBoolean
If InputVariant = True Then
strRetVal = "True"
Else
strRetVal = "False"
End If
End Select
SQLValidateVariant = strRetVal
End Function
Code:
Public Function SQLTarih(ByVal Tarih As Variant) As String
'returns a SQL friendly date string
If IsDate(Tarih) Then
SQLTarih = Format(Tarih, "\#mm\/dd\/yyyy\#", vbSunday, vbFirstJan1)
Else
SQLTarih = vbNullString
End If
End Function
Code:
Public Function SQLTarihSaat(ByVal TarihSaat As Variant) As String
'returns a SQL friendly date+time string
If IsDate(TarihSaat) Then
SQLTarihSaat = Format(TarihSaat, "\#mm\/dd\/yyyy hh\:nn\:ss\#", vbSunday, vbFirstJan1)
Else
SQLTarihSaat = vbNullString
End If
End Function
can i ask why this is better than copy/paste from query design view to vba behind the form. thats how i am doing it at the moment. also i could just have the query as a query rather than code. but the only reason i want it in code is that i want it there. if something happens when i click or open then i want to go straight to the event and see whats going on. i presume that it is pretty difficult to give everything a good enough description and that for me is time consuming. yes i will have to learn about syntax etc but is that not part of programming.
its each to their own but saying this i cant see me doing it in the future but i have said that too many times already.
Considering you only have a handful of queries, it may be more practical for you to keep up your current way of doing things rather than implement this solution. I have developed this solution around the time when
- I had nothing more to learn about concatenating sql in vba code
- I found my query object list to be too long and hard to manage, due to many ad-hoc queries, some of which obsolete
- I had grown tired of boilerplate code overhead of using querydef objects, especially to pass a parameter to parametrized queries
- The complex nature of multi-parameter queries whose parameters may or may not be passed as null
- Constructing long queries in code, having to declare variables to them, having to split them into lines, having to take special care with quotation marks, having to concatenate variables into them, the time sonsumption and the code footprint of the whole shebang...
Storing queries as SQL statements in a table and streamlining the functions that pass parameters to them solved all these problems for me.
- I can create complex parametrized queries and pass any parameter I want without querydef variables, or even strSQL variables. My vba procedures are much shorter and cleaner.
- I can see queries and where/for what they are used, just by looking at the table. I don't have to right click the query object and check its properties
- My query object list is much shorter now. I'm not sure if that necessarily translates into a smaller database size, although that would be a negligible and pleasant advantage.
- Am no longer discouraged by the prospect of using several queries in code to make complex decisions
On the other hand, it is a fact that resident query objects are faster relative to queries concatenated by code, since they have a compiled execution plan. It is also another performance hit to have to look up the SQL statement from a table, instead of having direct access to it as a resident object. However I have never witnessed these performance hits to be in the perceivable range. Both of these steps happen within milliseconds. It may be argued that a parametrized query may have to be run many times as part of a loop, however that can be remedied by creating temporary querydefs (though I never even had to even bother to do that).
It may be important for you to familiarize yourself with writing code and constructing SQL statements if you are in the learning stages of the process, but I believe in time, as you "level-up" and possibly have many more tens of queries, you will probably get tired and settle to a similar solution.
