Hello Everyone
My question is I have 2 Queries
1. Select Qry (InsGrades)
SQL:
SELECT SessionYearT.Session_ID, SessionYearT.SessionYear, SessionYearTypeT.SessionYearType_ID, SessionYearTypeT.SessionTypeT, SessionTypeT.SessionType, SessionYearTypeT.StartDate, SessionYearTypeT.EndDate, GradesT.Grade_ID, GradesT.GradeName
FROM (SessionTypeT INNER JOIN GradesT ON SessionTypeT.SessionType_ID = GradesT.SessionType_ID) INNER JOIN (SessionYearT INNER JOIN SessionYearTypeT ON SessionYearT.Session_ID = SessionYearTypeT.SessionYearT) ON SessionTypeT.SessionType_ID = SessionYearTypeT.SessionTypeT
WHERE (((SessionYearTypeT.IsCurrent)=1));
2. Append Qry (InsGrades1)
SQL:
PARAMETERS [Forms]![frmSessionYear]![frmSessionTypesSubform]![txtSessionYearType_ID] Long;
INSERT INTO tblSessionGrades ( SessionYearType_ID, GradeID )
SELECT [Forms]![frmSessionYear]![frmSessionTypesSubform]![txtSessionYearType_ID] AS QSessionYearType_ID, InsGrades.Grade_ID
FROM InsGrades
WHERE (((InsGrades.Session_ID)=1) AND ((InsGrades.SessionTypeT)=1));
ok what i want is to write these sql statements in Vba just for learning purpose How a select statement is declared and after that insert query
regards
A name of a query indicates that the query is a stored object. In order to run the second query, you don't have to do anything else with that first query. Execution of a query starts with the evaluation of the FROM part. If there is a query instead of a table, this query is automatically executed and evaluated before the upper level continues.
Alternatively, you can use the SQL statement of one query directly in that of the second, without the trailing semicolon. There are reasons to do this and reasons not to.
SQL:
...
FROM (SELECT SessionYearT.Session_ID, ...) AS InsGrades
WHERE ...
Another alternative: If you put the line with INSERT INTO in front of the selection query, it automatically becomes an append query. Adjusting the field lists of append and select as well as entering the parameter: A query about everything is done.
Hello Everyone
My question is I have 2 Queries
1. Select Qry (InsGrades)
SQL:
SELECT SessionYearT.Session_ID, SessionYearT.SessionYear, SessionYearTypeT.SessionYearType_ID, SessionYearTypeT.SessionTypeT, SessionTypeT.SessionType, SessionYearTypeT.StartDate, SessionYearTypeT.EndDate, GradesT.Grade_ID, GradesT.GradeName
FROM (SessionTypeT INNER JOIN GradesT ON SessionTypeT.SessionType_ID = GradesT.SessionType_ID) INNER JOIN (SessionYearT INNER JOIN SessionYearTypeT ON SessionYearT.Session_ID = SessionYearTypeT.SessionYearT) ON SessionTypeT.SessionType_ID = SessionYearTypeT.SessionTypeT
WHERE (((SessionYearTypeT.IsCurrent)=1));
2. Append Qry (InsGrades1)
SQL:
PARAMETERS [Forms]![frmSessionYear]![frmSessionTypesSubform]![txtSessionYearType_ID] Long;
INSERT INTO tblSessionGrades ( SessionYearType_ID, GradeID )
SELECT [Forms]![frmSessionYear]![frmSessionTypesSubform]![txtSessionYearType_ID] AS QSessionYearType_ID, InsGrades.Grade_ID
FROM InsGrades
WHERE (((InsGrades.Session_ID)=1) AND ((InsGrades.SessionTypeT)=1));
ok what i want is to write these sql statements in Vba just for learning purpose How a select statement is declared and after that insert query
regards
I think I need to ask your intent here. You want to write the statements in VBA. But what does that mean or what did you want it to mean?
If you mean you wanted to create an SQL string, that is one thing. All you need is a string variable to hold what you build and use the concatenation operator ( & ) to put the parts together in the right order, including any literal values, variables, and form control contents as need for your goal.
If you wanted to have VBA evaluate the SQL, that is entirely different because VBA doesn't do SQL. It issues commands to the Access API to execute SQL in a given context. I.e. the Access programming interface does not execute SQL - it passes it to the data engine, probably ACE unless you have a REALLY old copy of Access, in which case it uses JET.
Opening an SQL-based SELECT statement works in recordset context. If the SELECT statement is inherently updateable and you open the recordset in the appropriate mode, you can do INSERT or UPDATE or DELETE through the recordset interface or can simply copy selected fields exposed by the recordset as needed.
You would use either a DoCmd.RunSQL or a CurrentDB.Execute to run an action query such as INSERT INTO, UPDATE, or DELETE. The recordset method doesn't work for action queries because action queries don't return recordsets.
Either of those action query methods COULD (at least in theory) open a SELECT query but would do nothing with it except instantly close it with no visible effect. Don't know that I've ever actually tried to execute a SELECT query but then it doesn't matter. Even if it opens, it will do nothing at all to your data. At most, if you were running the Windows Task Manager when you tried, you would see a quick blip in disk usage and CPU usage. Maybe a very little blip in memory usage. If the database is split and the back-end is on another system, maybe a blip in network usage. But you would see nothing from the Access interface itself.
Yes, I understand you want an INSERT action to append record(s). But why do you need to do this programmatically? Why not data entry with a form? Is this to 'batch' create a number of records at one time?
As Doc described, there is more than one method for programmatically adding records: DoCmd.RunSQL and CurrentDb.Execute
And then can open a recordset and loop through records - really least efficient when SQL is available.
PARAMETERS
pID Long
;
INSERT INTO
tblSessionGrades(
SessionYearType_ID,
GradeID
)
SELECT
pID AS QSessionYearType_ID,
GradesT.Grade_ID
FROM
(SessionTypeT
INNER JOIN GradesT
ON SessionTypeT.SessionType_ID = GradesT.SessionType_ID
)
INNER JOIN
(SessionYearT
INNER JOIN SessionYearTypeT
ON SessionYearT.Session_ID = SessionYearTypeT.SessionYearT
)
ON SessionTypeT.SessionType_ID = SessionYearTypeT.SessionTypeT
WHERE
SessionYearTypeT.IsCurrent = 1
AND
SessionYearT.Session_ID = 1
AND
SessionYearTypeT.SessionTypeT = 1
Code:
' Call
Dim lRet As Long
lRet = ExecuteParamQuery(CurrentDb, "qinsData", _
"pID", Forms.frmSessionYear.frmSessionTypesSubform.Form.txtSessionYearType_ID
MsgBox lRet & " records are affected"
The used function (store in a standard module):
Code:
Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
ParamArray QueryParams() As Variant) As Long
Dim qd As DAO.QueryDef
Dim i As Long
If UBound(QueryParams) Mod 2 = 1 Then ' Paare Parameterfeld-Wert vorhanden
If QueryExists(MyDB, AnyQuery) Then
' gespeicherte Abfrage
Set qd = MyDB.QueryDefs(AnyQuery)
Else
' SQL-Anweisung
Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
End If
For i = 0 To UBound(QueryParams) Step 2
qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
Next
qd.Execute dbFailOnError
ExecuteParamQuery = qd.RecordsAffected
qd.Close
Set qd = Nothing
End If
End Function
Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
Dim qd As DAO.QueryDef
For Each qd In MyDB.QueryDefs
If qd.Name = QueryName Then
QueryExists = True
Exit For
End If
Next
End Function
The function ExecuteParamQuery can also process a passed sql statement. But storing a constant sql statement in vba code makes less sense.
Hello Everyone
My question is I have 2 Queries
1. Select Qry (InsGrades)
SQL:
SELECT SessionYearT.Session_ID, SessionYearT.SessionYear, SessionYearTypeT.SessionYearType_ID, SessionYearTypeT.SessionTypeT, SessionTypeT.SessionType, SessionYearTypeT.StartDate, SessionYearTypeT.EndDate, GradesT.Grade_ID, GradesT.GradeName
FROM (SessionTypeT INNER JOIN GradesT ON SessionTypeT.SessionType_ID = GradesT.SessionType_ID) INNER JOIN (SessionYearT INNER JOIN SessionYearTypeT ON SessionYearT.Session_ID = SessionYearTypeT.SessionYearT) ON SessionTypeT.SessionType_ID = SessionYearTypeT.SessionTypeT
WHERE (((SessionYearTypeT.IsCurrent)=1));
2. Append Qry (InsGrades1)
SQL:
PARAMETERS [Forms]![frmSessionYear]![frmSessionTypesSubform]![txtSessionYearType_ID] Long;
INSERT INTO tblSessionGrades ( SessionYearType_ID, GradeID )
SELECT [Forms]![frmSessionYear]![frmSessionTypesSubform]![txtSessionYearType_ID] AS QSessionYearType_ID, InsGrades.Grade_ID
FROM InsGrades
WHERE (((InsGrades.Session_ID)=1) AND ((InsGrades.SessionTypeT)=1));
ok what i want is to write these sql statements in Vba just for learning purpose How a select statement is declared and after that insert query
regards
PARAMETERS
pID Long
;
INSERT INTO
tblSessionGrades(
SessionYearType_ID,
GradeID
)
SELECT
pID AS QSessionYearType_ID,
GradesT.Grade_ID
FROM
(SessionTypeT
INNER JOIN GradesT
ON SessionTypeT.SessionType_ID = GradesT.SessionType_ID
)
INNER JOIN
(SessionYearT
INNER JOIN SessionYearTypeT
ON SessionYearT.Session_ID = SessionYearTypeT.SessionYearT
)
ON SessionTypeT.SessionType_ID = SessionYearTypeT.SessionTypeT
WHERE
SessionYearTypeT.IsCurrent = 1
AND
SessionYearT.Session_ID = 1
AND
SessionYearTypeT.SessionTypeT = 1
Code:
' Call
Dim lRet As Long
lRet = ExecuteParamQuery(CurrentDb, "qinsData", _
"pID", Forms.frmSessionYear.frmSessionTypesSubform.Form.txtSessionYearType_ID
MsgBox lRet & " records are affected"
The used function (store in a standard module):
Code:
Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
ParamArray QueryParams() As Variant) As Long
Dim qd As DAO.QueryDef
Dim i As Long
If UBound(QueryParams) Mod 2 = 1 Then ' Paare Parameterfeld-Wert vorhanden
If QueryExists(MyDB, AnyQuery) Then
' gespeicherte Abfrage
Set qd = MyDB.QueryDefs(AnyQuery)
Else
' SQL-Anweisung
Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
End If
For i = 0 To UBound(QueryParams) Step 2
qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
Next
qd.Execute dbFailOnError
ExecuteParamQuery = qd.RecordsAffected
qd.Close
Set qd = Nothing
End If
End Function
Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
Dim qd As DAO.QueryDef
For Each qd In MyDB.QueryDefs
If qd.Name = QueryName Then
QueryExists = True
Exit For
End If
Next
End Function
The function ExecuteParamQuery can also process a passed sql statement. But storing a constant sql statement in vba code makes less sense.
I would attribute errors to your individual implementation of the whole thing.
My parameter as the interface of the query to the outside has a name (pID), and as part of the call, the associated value is assigned to the parameter named by name, in the example shown from a text field of a subform.
However, the same query can also receive values from completely different sources, i.e. it can be used more universally.
Perhaps I understand, since in your exhibit in post #10 you are referencing a stored query as the source for your INSERT in the subroutine. Is it that you would really prefer to directly recover the same data that the stored query would recover and just use that directly in the INSERT query?
If so, there is nothing stopping you from just copying the relevant parts of that query into the SELECT statement portion of the INSERT, directly feeding from the table that feeds InsGrades1.
We've seen the code. I don't see anything obviously wrong with the idea, so can you tell us in plain language what your problem is? What is stopping you?
If you are looking for other direction, please try to use language first. Code, believe it or not, is a TERRIBLE way to show us anything if the problem was that you weren't sure what you were doing anyway.
