[AccessD] Calling a stored procedure from Access

Here's a template to create the sproc.

CREATE PROC sp_readTextFile @filename sysname
as


     BEGIN 
     SET nocount ON 
     CREATE TABLE #tempfile (line varchar(8000))
     EXEC ('bulk INSERT #tempfile FROM "' + @filename + '"')
     SELECT * FROM #tempfile
     DROP TABLE #tempfile
 END 

If the sproc already exists change CREATE to ALTER

ALTER PROC sp_readTextFile @filename sysname
as


     BEGIN 
     SET nocount ON 
     CREATE TABLE #tempfile (line varchar(8000))
     EXEC ('bulk INSERT #tempfile FROM "' + @filename + '"')
     SELECT * FROM #tempfile
     DROP TABLE #tempfile
 END

-- Assumes:Usage : (if using Query Analyzer in SQL server)
 EXEC sp_readTextFile  'D:\PSMData\infutor\CSV\NAR_ND1.CSV'

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

I am on the final leg of the data import for the 100 million record data
import.  I have about 30 remaining "csv files" in a directory which have to
be imported to SQL Server.  ATM I am manually editing a query which looks
like:
 
BULK INSERT Infutor.dbo.infutor
    FROM 'D:\PSMData\infutor\CSV\NAR_ND1.CSV'
    with (
        yadayada
    )
 
Obviously  'D:\PSMData\infutor\CSV\ is the path to the files and
NAR_ND1.CSV' is the file currently being processed.
 
I have a table of all the files from a preprocessor I wrote that generated
the csv files.  If I could turn query into a stored procedure with a
parameter for the file name, I could then call the stored procedure from
Access and pass in the filenames.  
 
I have never done this before so I need to know how this is done.  I have
created a couple of stored procedures but none with a parameter that has to
be appended to a string etc.  All of my books make a rather huge assumption
that any parameters passed in will be used a filters in where clauses and so
forth, and completely ignore things like constants and variables inside of
the stored procedure.  
 
My ASSUMPTION is that I can create a constant to hold the path, dim a
variable to hold the finished file name with path, then append the path
constant and the passed in parameter and store the results in the string
variable.  Then I can use that to replace the string in the FROM clause.  Or
just do the append directly "in place" in the FROM clause.  At any rate I
end up with something like:
 
    .
    .
    .
    FROM 'D:\PSMData\infutor\CSV\' & @FileName
 
QUESTIONS:
 
1) Having created this stored procedure, how do I get it to save?  Whenever
I try to save it SQL Server tries to save it out as a text file on disk.
2) Having saved it, how do I execute it from Access passing in the file
names.
 
If I can get this concept down I will be a huge step further along in my
automation task.
 
Any help is much appreciated.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.467 / Virus Database: 269.6.2/785 - Release Date: 5/2/2007 2:16
PM