access thread: Action Query in ADP
Message #1 by "George Oro" <george@c...> on Tue, 19 Nov 2002 12:29:41 +0400
|
|
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it. Below is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #2 by "Carnley, Dave" <dcarnley@a...> on Tue, 19 Nov 2002 09:43:15 -0600
|
|
Sure I can help, I am writing an ADP project even now. Here is how I handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the _sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag, PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc. I
have a class module for every table that includes private functions to go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it. Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #3 by "George Oro" <george@c...> on Wed, 20 Nov 2002 12:50:30 +0400
|
|
Thank you very much Dave!, but its quite complicated at the moment, I guess its better if we can start
with this simple task, if it
is OK to you?
Append Query:
Assuming this is my frm123:
ContactsID: 123 (Bound Field)
FullName: George Oro (Bound Field)
----------------
Favorites: Basketball (Unbound Dropdown)
Add (cmdButton)
Once I click the cmdAdd the Basketball & ContactsID value should add to m02TblFavorites (m02ContactsID, m02Favorites)
But how the SP recognize that the value I need is the value from frm123?
Sorry Dave if I made you confused! please help....
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:43 PM
To: Access
Subject: [access] RE: Action Query in ADP
Sure I can help, I am writing an ADP project even now. Here is how I handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the _sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag, PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc. I
have a class module for every table that includes private functions to go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it. Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #4 by "Carnley, Dave" <dcarnley@a...> on Wed, 20 Nov 2002 09:30:44 -0600
|
|
you tell it what you want to use...
in cmdAdd.click, build a string like this
str = "sp_ins_m02TblFavorites "
str = str & cstr(txtContactsID) & ", '" & cstr(txtFavorites) & "'"
db.execute str
(assuming db is a connection to your database...)
(you need single-quotes around strings)
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:51 AM
To: Access
Subject: [access] RE: Action Query in ADP
Thank you very much Dave!, but its quite complicated at the moment, I guess
its better if we can start with this simple task, if it
is OK to you?
Append Query:
Assuming this is my frm123:
ContactsID: 123 (Bound Field)
FullName: George Oro (Bound Field)
----------------
Favorites: Basketball (Unbound Dropdown)
Add (cmdButton)
Once I click the cmdAdd the Basketball & ContactsID value should add to
m02TblFavorites (m02ContactsID, m02Favorites)
But how the SP recognize that the value I need is the value from frm123?
Sorry Dave if I made you confused! please help....
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:43 PM
To: Access
Subject: [access] RE: Action Query in ADP
Sure I can help, I am writing an ADP project even now. Here is how I handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the _sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag, PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc. I
have a class module for every table that includes private functions to go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it. Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #5 by "George Oro" <george@c...> on Sat, 23 Nov 2002 14:55:05 +0400
|
|
Hi Dave,
How to convert the below code to SP? I copied this below code from my append query (q30QryAddToList)... the query runs on click
events:
Private Sub cmdAdd_Click
DoCmd.SetWarnings False
DoCmd.OpenQuery "q30QryAddToList", acViewNormal, acEdit
DoCmd.SetWarnings True
End Sub
INSERT INTO q31TblCriteriaDetails ( q31FieldName, q31CriteriaLine2, q31CriteriaLine1, q31CriteriaID )
SELECT [Forms]![q30FrmCriteria]![txtField] AS CurrentField, [Forms]![q30FrmCriteria]![txtView2] AS View2,
[Forms]![q30FrmCriteria]![txtView1] AS View1, q30TblCriteria.q30CriteriaID
FROM q30TblCriteria
WHERE (((q30TblCriteria.q30CriteriaID)=[Forms]![q30FrmCriteria]![q30CriteriaID]));
Sorry about this Dave, but its VERY helpful to me if you can provide the solution/steps from A-Z which is very easy to you to do.
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, November 20, 2002 7:31 PM
To: Access
Subject: [access] RE: Action Query in ADP
you tell it what you want to use...
in cmdAdd.click, build a string like this
str = "sp_ins_m02TblFavorites "
str = str & cstr(txtContactsID) & ", '" & cstr(txtFavorites) & "'"
db.execute str
(assuming db is a connection to your database...)
(you need single-quotes around strings)
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:51 AM
To: Access
Subject: [access] RE: Action Query in ADP
Thank you very much Dave!, but its quite complicated at the moment, I guess
its better if we can start with this simple task, if it
is OK to you?
Append Query:
Assuming this is my frm123:
ContactsID: 123 (Bound Field)
FullName: George Oro (Bound Field)
----------------
Favorites: Basketball (Unbound Dropdown)
Add (cmdButton)
Once I click the cmdAdd the Basketball & ContactsID value should add to
m02TblFavorites (m02ContactsID, m02Favorites)
But how the SP recognize that the value I need is the value from frm123?
Sorry Dave if I made you confused! please help....
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:43 PM
To: Access
Subject: [access] RE: Action Query in ADP
Sure I can help, I am writing an ADP project even now. Here is how I handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the _sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag, PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc. I
have a class module for every table that includes private functions to go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it. Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #6 by "Brian Skelton" <brian.skelton@b...> on Sat, 23 Nov 2002 17:22:29 -0000
|
|
George
I'll assume that q31FieldName is text field with a max size of 30 and
that all others are integers. Here's the stored procedure:
Create Procedure q30QryAddToList
@vcharField varchar(30),
@intView2 int,
@intView2 int,
@intID int
AS
Set nocount on
Declare
@intRowCount int
BEGIN TRAN
INSERT INTO q31TblCriteriaDetails
(q31FieldName, q31CriteriaLine1, q31CriteriaLine2, q31CriteriaID )
VALUES
(@vcharField,@intView2,@intView2,@intID)
SET @intRowCount=@@ROWCOUNT
END TRAN
Return @intRowCount
I use functions to run all my stored procedures. Here's a nice general
one that allows you to capture a return value:
Public Function RunSP(strSPName As String, ParamArray varParameters() As
Variant) As Long
' Code Header inserted by the Procedure Header Add-In
'=============================================================
' mdlADPRecord.RunSP
'-------------------------------------------------------------
' Purpose
' Author : Brian Skelton, 13-01-2002
' Notes : Runs a stored procedure and returns the value of RETURN
statement. ' Example call:
lngReturn=RunSP("spSample","@charSurname",adString,"Skelton",30)
' See the help file under CreateParameter for data type constants
'-------------------------------------------------------------
' Parameters
'-----------
' The stored procedure name:strSPName (String)
' n(0 to infinity) sets of four parameter descriptions in the following
' order parameter name,parameter data type,parameter value,
' parameter data size:varParameters() (Variant)
'-------------------------------------------------------------
' Returns: The value of the RETURN statement from the Stored Procedure
(long)
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 13-01-2002 BDS:
'=============================================================
' End Code Header block
'-----------------------------------------------------------------------
-------
Dim prmUID As ADODB.Parameter
Dim prmArray As ADODB.Parameter
Dim cmdSP As ADODB.Command
Dim intCount As Integer
On Error GoTo ErrRunSP
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = CurrentProject.Connection
cmdSP.CommandType = adCmdStoredProc
cmdSP.CommandText = strSPName
'Prepare a parameter to capture the value of the RETURN statement
Set prmArray = cmdSP.CreateParameter("RETURN", adInteger,
adParamReturnValue)
cmdSP.Parameters.Append prmArray
'Prepare the parameters from the supplied array
If Not IsMissing(varParameters()) Then
For intCount = 0 To UBound(varParameters()) Step 4
Set prmArray
cmdSP.CreateParameter(varParameters(intCount), varParameters(intCount
1), adParamInput, varParameters(intCount 3))
cmdSP.Parameters.Append prmArray
prmArray.Value = varParameters(intCount 2)
Next
End If
cmdSP.Execute
'Capture and return the value of RETURN Statement
RunSP = cmdSP("RETURN")
exitRunSP:
On Error Resume Next
Exit Function
ErrRunSP:
CaptureError "mdlADPRecord.RunSP", Err.Number, Err.Description
RunSP = -1
MsgBox Err.Description
Resume exitRunSP
End Function
And this is the call I would make to run your stored procedure:
Dim lngReturn as long
lngReturn=RunSP("q30QryAddToList", _
"@vcharField",adVarChar, [Forms]![q30FrmCriteria]![txtField], 30
_
"@intView2",adInteger, [Forms]![q30FrmCriteria]![txtView2],4 _
"@intView1",adInteger, [Forms]![q30FrmCriteria]![txtView1],4 _
"@intID",adInteger, [Forms]![q30FrmCriteria]![q30CriteriaID],4)
Phew!! Hope there's not too many mistakes in the above and that all
makes sense.
Brian
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: 23 November 2002 10:55
To: Access
Subject: [access] RE: Action Query in ADP
Hi Dave,
How to convert the below code to SP? I copied this below code from my
append query (q30QryAddToList)... the query runs on click
events:
Private Sub cmdAdd_Click
DoCmd.SetWarnings False
DoCmd.OpenQuery "q30QryAddToList", acViewNormal, acEdit
DoCmd.SetWarnings True
End Sub
INSERT INTO q31TblCriteriaDetails ( q31FieldName, q31CriteriaLine2,
q31CriteriaLine1, q31CriteriaID )
SELECT [Forms]![q30FrmCriteria]![txtField] AS CurrentField,
[Forms]![q30FrmCriteria]![txtView2] AS View2,
[Forms]![q30FrmCriteria]![txtView1] AS View1,
q30TblCriteria.q30CriteriaID
FROM q30TblCriteria
WHERE
(((q30TblCriteria.q30CriteriaID)=[Forms]![q30FrmCriteria]![q30CriteriaID
]));
Sorry about this Dave, but its VERY helpful to me if you can provide the
solution/steps from A-Z which is very easy to you to do.
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, November 20, 2002 7:31 PM
To: Access
Subject: [access] RE: Action Query in ADP
you tell it what you want to use...
in cmdAdd.click, build a string like this
str = "sp_ins_m02TblFavorites "
str = str & cstr(txtContactsID) & ", '" & cstr(txtFavorites) & "'"
db.execute str
(assuming db is a connection to your database...)
(you need single-quotes around strings)
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:51 AM
To: Access
Subject: [access] RE: Action Query in ADP
Thank you very much Dave!, but its quite complicated at the moment, I
guess
its better if we can start with this simple task, if it
is OK to you?
Append Query:
Assuming this is my frm123:
ContactsID: 123 (Bound Field)
FullName: George Oro (Bound Field)
----------------
Favorites: Basketball (Unbound Dropdown)
Add (cmdButton)
Once I click the cmdAdd the Basketball & ContactsID value should add to
m02TblFavorites (m02ContactsID, m02Favorites)
But how the SP recognize that the value I need is the value from frm123?
Sorry Dave if I made you confused! please help....
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:43 PM
To: Access
Subject: [access] RE: Action Query in ADP
Sure I can help, I am writing an ADP project even now. Here is how I
handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the
_sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag,
PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for
every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the
insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause
the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app
executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc.
I
have a class module for every table that includes private functions to
go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the
properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with
the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the
object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it.
Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #7 by "Carnley, Dave" <dcarnley@a...> on Mon, 25 Nov 2002 10:11:28 -0600
|
|
Looks Good to me!
-----Original Message-----
From: Brian Skelton [mailto:brian.skelton@b...]
Sent: Saturday, November 23, 2002 11:22 AM
To: Access
Subject: [access] RE: Action Query in ADP
George
I'll assume that q31FieldName is text field with a max size of 30 and
that all others are integers. Here's the stored procedure:
Create Procedure q30QryAddToList
@vcharField varchar(30),
@intView2 int,
@intView2 int,
@intID int
AS
Set nocount on
Declare
@intRowCount int
BEGIN TRAN
INSERT INTO q31TblCriteriaDetails
(q31FieldName, q31CriteriaLine1, q31CriteriaLine2, q31CriteriaID )
VALUES
(@vcharField,@intView2,@intView2,@intID)
SET @intRowCount=@@ROWCOUNT
END TRAN
Return @intRowCount
I use functions to run all my stored procedures. Here's a nice general
one that allows you to capture a return value:
Public Function RunSP(strSPName As String, ParamArray varParameters() As
Variant) As Long
' Code Header inserted by the Procedure Header Add-In
'=============================================================
' mdlADPRecord.RunSP
'-------------------------------------------------------------
' Purpose
' Author : Brian Skelton, 13-01-2002
' Notes : Runs a stored procedure and returns the value of RETURN
statement. ' Example call:
lngReturn=RunSP("spSample","@charSurname",adString,"Skelton",30)
' See the help file under CreateParameter for data type constants
'-------------------------------------------------------------
' Parameters
'-----------
' The stored procedure name:strSPName (String)
' n(0 to infinity) sets of four parameter descriptions in the following
' order parameter name,parameter data type,parameter value,
' parameter data size:varParameters() (Variant)
'-------------------------------------------------------------
' Returns: The value of the RETURN statement from the Stored Procedure
(long)
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 13-01-2002 BDS:
'=============================================================
' End Code Header block
'-----------------------------------------------------------------------
-------
Dim prmUID As ADODB.Parameter
Dim prmArray As ADODB.Parameter
Dim cmdSP As ADODB.Command
Dim intCount As Integer
On Error GoTo ErrRunSP
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = CurrentProject.Connection
cmdSP.CommandType = adCmdStoredProc
cmdSP.CommandText = strSPName
'Prepare a parameter to capture the value of the RETURN statement
Set prmArray = cmdSP.CreateParameter("RETURN", adInteger,
adParamReturnValue)
cmdSP.Parameters.Append prmArray
'Prepare the parameters from the supplied array
If Not IsMissing(varParameters()) Then
For intCount = 0 To UBound(varParameters()) Step 4
Set prmArray
cmdSP.CreateParameter(varParameters(intCount), varParameters(intCount
1), adParamInput, varParameters(intCount 3))
cmdSP.Parameters.Append prmArray
prmArray.Value = varParameters(intCount 2)
Next
End If
cmdSP.Execute
'Capture and return the value of RETURN Statement
RunSP = cmdSP("RETURN")
exitRunSP:
On Error Resume Next
Exit Function
ErrRunSP:
CaptureError "mdlADPRecord.RunSP", Err.Number, Err.Description
RunSP = -1
MsgBox Err.Description
Resume exitRunSP
End Function
And this is the call I would make to run your stored procedure:
Dim lngReturn as long
lngReturn=RunSP("q30QryAddToList", _
"@vcharField",adVarChar, [Forms]![q30FrmCriteria]![txtField], 30
_
"@intView2",adInteger, [Forms]![q30FrmCriteria]![txtView2],4 _
"@intView1",adInteger, [Forms]![q30FrmCriteria]![txtView1],4 _
"@intID",adInteger, [Forms]![q30FrmCriteria]![q30CriteriaID],4)
Phew!! Hope there's not too many mistakes in the above and that all
makes sense.
Brian
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: 23 November 2002 10:55
To: Access
Subject: [access] RE: Action Query in ADP
Hi Dave,
How to convert the below code to SP? I copied this below code from my
append query (q30QryAddToList)... the query runs on click
events:
Private Sub cmdAdd_Click
DoCmd.SetWarnings False
DoCmd.OpenQuery "q30QryAddToList", acViewNormal, acEdit
DoCmd.SetWarnings True
End Sub
INSERT INTO q31TblCriteriaDetails ( q31FieldName, q31CriteriaLine2,
q31CriteriaLine1, q31CriteriaID )
SELECT [Forms]![q30FrmCriteria]![txtField] AS CurrentField,
[Forms]![q30FrmCriteria]![txtView2] AS View2,
[Forms]![q30FrmCriteria]![txtView1] AS View1,
q30TblCriteria.q30CriteriaID
FROM q30TblCriteria
WHERE
(((q30TblCriteria.q30CriteriaID)=[Forms]![q30FrmCriteria]![q30CriteriaID
]));
Sorry about this Dave, but its VERY helpful to me if you can provide the
solution/steps from A-Z which is very easy to you to do.
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, November 20, 2002 7:31 PM
To: Access
Subject: [access] RE: Action Query in ADP
you tell it what you want to use...
in cmdAdd.click, build a string like this
str = "sp_ins_m02TblFavorites "
str = str & cstr(txtContactsID) & ", '" & cstr(txtFavorites) & "'"
db.execute str
(assuming db is a connection to your database...)
(you need single-quotes around strings)
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:51 AM
To: Access
Subject: [access] RE: Action Query in ADP
Thank you very much Dave!, but its quite complicated at the moment, I
guess
its better if we can start with this simple task, if it
is OK to you?
Append Query:
Assuming this is my frm123:
ContactsID: 123 (Bound Field)
FullName: George Oro (Bound Field)
----------------
Favorites: Basketball (Unbound Dropdown)
Add (cmdButton)
Once I click the cmdAdd the Basketball & ContactsID value should add to
m02TblFavorites (m02ContactsID, m02Favorites)
But how the SP recognize that the value I need is the value from frm123?
Sorry Dave if I made you confused! please help....
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:43 PM
To: Access
Subject: [access] RE: Action Query in ADP
Sure I can help, I am writing an ADP project even now. Here is how I
handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the
_sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag,
PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for
every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the
insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause
the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app
executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc.
I
have a class module for every table that includes private functions to
go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the
properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with
the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the
object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it.
Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
Message #8 by "George Oro" <george@c...> on Tue, 26 Nov 2002 09:26:58 +0400
|
|
Thanks Brian, but didn't try yet, something came up.
I will let you know... thanks again.
Cheers,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, November 25, 2002 8:11 PM
To: Access
Subject: [access] RE: Action Query in ADP
Looks Good to me!
-----Original Message-----
From: Brian Skelton [mailto:brian.skelton@b...]
Sent: Saturday, November 23, 2002 11:22 AM
To: Access
Subject: [access] RE: Action Query in ADP
George
I'll assume that q31FieldName is text field with a max size of 30 and
that all others are integers. Here's the stored procedure:
Create Procedure q30QryAddToList
@vcharField varchar(30),
@intView2 int,
@intView2 int,
@intID int
AS
Set nocount on
Declare
@intRowCount int
BEGIN TRAN
INSERT INTO q31TblCriteriaDetails
(q31FieldName, q31CriteriaLine1, q31CriteriaLine2, q31CriteriaID )
VALUES
(@vcharField,@intView2,@intView2,@intID)
SET @intRowCount=@@ROWCOUNT
END TRAN
Return @intRowCount
I use functions to run all my stored procedures. Here's a nice general
one that allows you to capture a return value:
Public Function RunSP(strSPName As String, ParamArray varParameters() As
Variant) As Long
' Code Header inserted by the Procedure Header Add-In
'=============================================================
' mdlADPRecord.RunSP
'-------------------------------------------------------------
' Purpose
' Author : Brian Skelton, 13-01-2002
' Notes : Runs a stored procedure and returns the value of RETURN
statement. ' Example call:
lngReturn=RunSP("spSample","@charSurname",adString,"Skelton",30)
' See the help file under CreateParameter for data type constants
'-------------------------------------------------------------
' Parameters
'-----------
' The stored procedure name:strSPName (String)
' n(0 to infinity) sets of four parameter descriptions in the following
' order parameter name,parameter data type,parameter value,
' parameter data size:varParameters() (Variant)
'-------------------------------------------------------------
' Returns: The value of the RETURN statement from the Stored Procedure
(long)
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' 13-01-2002 BDS:
'=============================================================
' End Code Header block
'-----------------------------------------------------------------------
-------
Dim prmUID As ADODB.Parameter
Dim prmArray As ADODB.Parameter
Dim cmdSP As ADODB.Command
Dim intCount As Integer
On Error GoTo ErrRunSP
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = CurrentProject.Connection
cmdSP.CommandType = adCmdStoredProc
cmdSP.CommandText = strSPName
'Prepare a parameter to capture the value of the RETURN statement
Set prmArray = cmdSP.CreateParameter("RETURN", adInteger,
adParamReturnValue)
cmdSP.Parameters.Append prmArray
'Prepare the parameters from the supplied array
If Not IsMissing(varParameters()) Then
For intCount = 0 To UBound(varParameters()) Step 4
Set prmArray
cmdSP.CreateParameter(varParameters(intCount), varParameters(intCount
1), adParamInput, varParameters(intCount 3))
cmdSP.Parameters.Append prmArray
prmArray.Value = varParameters(intCount 2)
Next
End If
cmdSP.Execute
'Capture and return the value of RETURN Statement
RunSP = cmdSP("RETURN")
exitRunSP:
On Error Resume Next
Exit Function
ErrRunSP:
CaptureError "mdlADPRecord.RunSP", Err.Number, Err.Description
RunSP = -1
MsgBox Err.Description
Resume exitRunSP
End Function
And this is the call I would make to run your stored procedure:
Dim lngReturn as long
lngReturn=RunSP("q30QryAddToList", _
"@vcharField",adVarChar, [Forms]![q30FrmCriteria]![txtField], 30
_
"@intView2",adInteger, [Forms]![q30FrmCriteria]![txtView2],4 _
"@intView1",adInteger, [Forms]![q30FrmCriteria]![txtView1],4 _
"@intID",adInteger, [Forms]![q30FrmCriteria]![q30CriteriaID],4)
Phew!! Hope there's not too many mistakes in the above and that all
makes sense.
Brian
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: 23 November 2002 10:55
To: Access
Subject: [access] RE: Action Query in ADP
Hi Dave,
How to convert the below code to SP? I copied this below code from my
append query (q30QryAddToList)... the query runs on click
events:
Private Sub cmdAdd_Click
DoCmd.SetWarnings False
DoCmd.OpenQuery "q30QryAddToList", acViewNormal, acEdit
DoCmd.SetWarnings True
End Sub
INSERT INTO q31TblCriteriaDetails ( q31FieldName, q31CriteriaLine2,
q31CriteriaLine1, q31CriteriaID )
SELECT [Forms]![q30FrmCriteria]![txtField] AS CurrentField,
[Forms]![q30FrmCriteria]![txtView2] AS View2,
[Forms]![q30FrmCriteria]![txtView1] AS View1,
q30TblCriteria.q30CriteriaID
FROM q30TblCriteria
WHERE
(((q30TblCriteria.q30CriteriaID)=[Forms]![q30FrmCriteria]![q30CriteriaID
]));
Sorry about this Dave, but its VERY helpful to me if you can provide the
solution/steps from A-Z which is very easy to you to do.
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, November 20, 2002 7:31 PM
To: Access
Subject: [access] RE: Action Query in ADP
you tell it what you want to use...
in cmdAdd.click, build a string like this
str = "sp_ins_m02TblFavorites "
str = str & cstr(txtContactsID) & ", '" & cstr(txtFavorites) & "'"
db.execute str
(assuming db is a connection to your database...)
(you need single-quotes around strings)
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:51 AM
To: Access
Subject: [access] RE: Action Query in ADP
Thank you very much Dave!, but its quite complicated at the moment, I
guess
its better if we can start with this simple task, if it
is OK to you?
Append Query:
Assuming this is my frm123:
ContactsID: 123 (Bound Field)
FullName: George Oro (Bound Field)
----------------
Favorites: Basketball (Unbound Dropdown)
Add (cmdButton)
Once I click the cmdAdd the Basketball & ContactsID value should add to
m02TblFavorites (m02ContactsID, m02Favorites)
But how the SP recognize that the value I need is the value from frm123?
Sorry Dave if I made you confused! please help....
TIA,
George
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:43 PM
To: Access
Subject: [access] RE: Action Query in ADP
Sure I can help, I am writing an ADP project even now. Here is how I
handle
this issue:
For each table, I create a minimum of 4 stored procedures,
"sp_ins_tablename", "sp_upd_tablename", "sp_del_tablename", and
"sp_sel_tablename". there may be a need for multiple version of the
_sel_
procedure, selecitng on different fields, like "sp_sel_CustByName",
"sp_sel_CustByID", etc...
Here is an example
CREATE PROCEDURE [sp_ins_Request]
(
@BOSFundFlag bit, @WaiveFeeFlag bit, @BillToAcctFlag
bit,
@BillExternalFlag bit, @PremierFlag bit, @DateAdded
datetime,
@DateModified datetime, @DelStateID int, @AcctQueriedID
int,
@AcctBillToID int, @DelCountryID int, @DeliveryMethodID
int,
@BillExternalID int, @DelCity varchar(40), @DelZip
varchar(15),
@XrefAccount varchar(20), @EmailAddrTo varchar(80), @DelAddr1
varchar(40),
@DelAddr2 varchar(40), @AddedByID varchar(10), @ModifiedByID
varchar(10),
@FaxToNum varchar(80), @RequestType varchar(40), @WaiveUserID
varchar(10),
@WaiveMgrID varchar(10), @BatchID int)
AS
SET NOCOUNT ON -- Turn off messages
DECLARE @ErrorCode int
DECLARE @ReturnID as int
/* do any validation edits here, return error codes if necessary */
BEGIN TRANSACTION
-- Does the record exist?
INSERT INTO tbl_Request (
BOSFundFlag, WaiveFeeFlag, BillToAcctFlag, BillExternalFlag,
PremierFlag,
DateAdded, DateModified, DelStateID, AcctQueriedID, AcctBillToID,
DelCountryID, DeliveryMethodID, BillExternalID, DelCity, DelZip,
XrefAccount, EmailAddrTo, DelAddr1, DelAddr2, AddedByID,
ModifiedByID, FaxToNum, RequestType, WaiveUserID, WaiveMgrID,
BatchID)
VALUES (
@BOSFundFlag, @WaiveFeeFlag, @BillToAcctFlag, @BillExternalFlag,
@PremierFlag,
@DateAdded, @DateModified, @DelStateID, @AcctQueriedID, @AcctBillToID,
@DelCountryID, @DeliveryMethodID, @BillExternalID, @DelCity, @DelZip,
@XrefAccount, @EmailAddrTo, @DelAddr1, @DelAddr2, @AddedByID,
@ModifiedByID, @FaxToNum, @RequestType, @WaiveUserID, @WaiveMgrID,
@BatchID)
SELECT @ErrorCode = @@ERROR
IF @ErrorCode = 0
BEGIN
SELECT @ReturnID = @@IDENTITY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
RAISERROR (55001, 16, 1, 'tbl_Request', 'sp_Upd_Request')
ROLLBACK TRANSACTION
RETURN (1)
END
So for an insert (and for the update) procs there is a parameter for
every
field in the table. I also include some very basic error/transaction
handling. In some procs I do a lot of validation up front before the
insert
but in this example there is none.
Notice the statement "SELECT @ReturnID = @@IDENTITY". This will cause
the
procedure to return a recordset to the calling app with one field, the
autonumber key field generated by SQL Server. So when my VBA app
executes
"set rs = adoConn.execute ("sp_ins_Request...")" my recordset rs has
rs.fields(1) holding the new ID.
I generate the SQL strings that are executed based on form values etc.
I
have a class module for every table that includes private functions to
go
both ways between object and data :
PopulateObjectFromRecordset(rs as adodb.recordset) [populates the
properties
of the object based on the fields in rs ie "me.strname = rs!Name" ]
and
BuildParameterString(mode as string) as string [returns a string with
the
properties of the object formatted as SQL parameters ie "name:='Joe
Blow',StateID:=1", to be used in UPD and INS procedure calls.] [the mode
param indicates INS or UPD. On UPD the returned string includes the
object
unique ID, on INS it does not]
I hope this helps...
David
-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 2:30 AM
To: Access
Subject: [access] Action Query in ADP
Just new in ADP...
Hi Guys,
I'm upgrading my Access2000 to AccessProject2000 and I want all the
functionality will be the same. Now I'm on the stage of changing
my action queries to Stored Procedure which I don't know how to do it.
Below
is my common approach to run my action queries in mdb
format:
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyDeleteQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyAppendQuery", acViewNormal, acEdit
DoCmd.OpenQuery "MyUpdateQuery", acViewNormal, acEdit
DoCmd.SetWarnings True
Can any one can give some assistance or example how to achieve action
queries in adp?
Any help & tips is highly appreciated...
TIA,
George
|
View Cart
