February 15, 2011 at 1:51 am
I´m running VBA code in Access that, using ADODBcommand, executes a sp procedure in SQL. This SQL procedure returns a parameter, @pmID.
Questions are:
1. How can exactly the parameter be returned to VBA code, and with what scope (just afer calling the SP, in all the module, inside the ADODBcommand)?
So, if you declare a string var in Access (Dim str as string),how can you assign @pmID to that var?
2. Once that parameter is returned to the 'str' variable, I´d like to use it to insert/append a SQL table (tblData) through a recordset RS (its source being already an Access table). The 1st column of the RS will be the actual value of the returned parameter/Access variable (say, RT123).
It would look like this:
str (or @pmID) / RS (field1) / RS (field2) / RS (field3)
RT123 / John / Smith / 43 (being the name and age the values coming from the RS, and RT123 the parameter).
How do you insert this into SQL (using the same recordset, of DoCMD, other options...)?
Thanks in advance, a.
February 15, 2011 at 9:22 am
Hello,
On question 1 you should find some code examples by searching for 'adParamOutput'.
February 15, 2011 at 11:32 am
thanks, although that was the easybit, that might help!
February 16, 2011 at 10:08 am
Can you just use one procedure?
MyOneproc
AS
DECLARE @pmID int
SET @pmID int =
(Do the logic here that sets the value of @pmID)
--Do your insert
INSERT INTO myTable
SELECT @pmID AS ID,
etc...
FROM MyOtherTable
February 17, 2011 at 2:38 am
In a way, you could ideally solve the whole problem with just 1 SP in SQL.
This must be a piece of cake, but the issue (at least for a newbie like me) is the 'etc.' in your code, and the requirements (passing, calling the SPs....).
The problem in SQL can be schematised as:
• T1: Source table t1 (let's say, in SQL). Columns 1 to 20
• T2: Target table t2. Same columns 1 to 20 and structure (except that 1st column needs to be replaced by @pmID.
• SP1: procedure (generates @pmID). Its code shouldn't be changed (no chances of adding insert/update here). It's called at least once from VBA.
• SP2 (your procedure?): would need to be called either from VBA, or somewhere else, and perform 2 tasks:
1) inserting @pmID returned by SP1 (but how?) in the 1st column of T2, and 2) then inserting/updating columns 2-20 'just as they appear in T1'.
February 17, 2011 at 8:45 am
Is @pmID just a new id generated from the procedure?
CREATE PROCEDURE MyProc
AS
DECLARE @pmID int
--Here you would call your procedure SP1
--You should post your code here
--The end result is @pmID is populated
--I will duplicate this action using SET
SET @pmID = 100
INSERT INTO T2 (Column1, Column2, etc)
SELECT
@pmID,
Column2,
Cloumn3,
etc
FROM TI
WHERE...
--Don't you have a where statement or are you realling inserting the entire contens of T1 into T2?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply