[AccessD] Calling a stored procedure from Access

John,

I can't find the code in my code VBA/VBScript LIBRARY (I do know I have
it...just have to dig through my CD archives)  but the general approach
would go something like this...

Option 1

1. Add this line at the end of your sproc - RETURN @@ROWCOUNT
2. Use the ADO command object
3. Add a command parameter and define that parameter as output in scope
(must be the first parameter)
4. After executing the sproc grab the value of that parameter

Option 2

1. Add an output parameter to your sproc (i.e. @retVal	int=0, OUTPUT)
2. Add this line at the bottom of the sproc (SET @retVal = @@ROWCOUNT)
3. At the end of the sproc return that value (i.e. RETURN @retVal)
4. Use the ADO command object
5. After executing the sproc grab the value of that parameter

--Eric

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Thursday, May 03, 2007 9:45 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Calling a stored procedure from Access

Eric,

>http://www.tek-tips.com/viewthread.cfm?qid=1362565&page=1

This code does not work for my purposes.  Probably because I am not
returning a recordset.  I cut and pasted it in and fixed up the pieces but
ended up with a "multistep process not completed.  No work done" error
message on the last step:

GetADoRSP.Open...

If I can get this part working I am golden!  

Thanks for your assistance in all this.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
Sent: Thursday, May 03, 2007 6:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Calling a stored procedure from Access

John,

Here's how I created the bulk insert statement for my application...

ALTER PROCEDURE dbo.ImportEmployeesFromPeopleSoft
	@DataFileName	varchar(255),
	@ImportFormatFileName	varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql	varchar(1000)

	-- Create a table to hold the data
	CREATE TABLE #tmpEmployees
	(
		suffix	varchar(5),
		last_name	varchar(30),
		first_name	varchar(20),
		
		...the rest of your fields go in here
		
	)
	-- Read the text file into the temp table
	SET @sql ='BULK INSERT #tmpEmployees FROM ' + CHAR(39) +
@DataFileName + CHAR(39) 
	SET @sql = @sql + ' WITH (FORMATFILE = ' + CHAR(39) +
@ImportFormatFileName + CHAR(39) + ')'

	EXEC (@sql)

	-- Remove the records from the Employees table first
	--NOTE: truncate bypasses transaction log but it is the fastest way
to clear a table and reset identity fields; use DELETE FROM if you want to
recover using t-log
	TRUNCATE TABLE Employees
	-- insert the records into the Employees table
	INSERT INTO Employees
	SELECT
		RTRIM(suffix) as suffix,
		RTRIM(last_name) as last_name,
		RTRIM(first_name) as first_name,

		...the rest of your fields go in here
		
	FROM #tmpEmployees
	-- And then clean up
	DROP TABLE #tmpEmployees
END 

Here's a link that shows how to call the sproc using ADO via Access.

http://www.tek-tips.com/viewthread.cfm?qid=1362565&page=1


--Eric

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com