[AccessD] Calling a stored procedure from Access

Eric,

Thanks so much for your patience.  

Infutor is the database name, as well as the table name.  I just changed the
table to be called tblInfutor.

I corrected the syntax error to get the '\n'. 
As soon as I put the Use statement in there I get a syntax error:

A Use database statement is not allowed in a procedure, function or trigger.

What I have now is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[BulkInsertInfutor]
	-- Add the parameters for the stored procedure here
	@FilName	varchar(255)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	DECLARE @FileSpec varchar(1000)
	set @filespec = 'D:\PSMData\INFUTOR\CSV\'	
	set @filespec = @filespec + @FilName

	DECLARE @SQL varchar(1000)
	set @SQL = 'BULK INSERT dbo.tblinfutor	from ' + char(39) +
@filespec + char(39)
	set @SQL = @SQL +' WITH	(FieldTerminator = '
	set @SQL = @SQL + char(39) + '|' + char(39) + ', '
	set @SQL = @SQL + 'ROWTERMINATOR = '
	set @SQL = @SQL + char(39) + '\n' + char(39) + ')'
	exec @SQL
END


I now get the error:

Could not locate entry in SysDatabases for 'Bulk INSERT dbo.' ...

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 11:01 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Calling a stored procedure from Access

So basically this is the command you end up with based on your sproc...

BULK INSERT Infutor.dbo.infutor from 'D:\PSMData\INFUTOR\CSV\NAR_MT1.csv'
WITH (FieldTerminator = '|', ROWTERMINATOR = '\'n)

First off you have a SYNTAX ERROR in that statement

'\'n) should be '\n') as I believe you meant to specify the newline
character (\n) as the ROWTERMINATOR

I'm guessing that's where your previous syntax error in '\' message was
coming from as well...

Just need to verify...

Infutor is your database name?

Infutor is also the table name?

I recommend adding this line after the BEGIN statement in your sproc...

USE databasename

...and make sure that what follows BULK INSERT is the tablename