[AccessD] Thanks for the help

1. The first important thing for us to know is whether you're using an ADP
or an MDB+ODBC link. That makes a big difference in what you can do from
within Access, although it doesn't matter strictly from the point of view of
firing a sproc.

2. There are several ways to execute a sproc, the simplest being "EXEC
mysproc parm1 parm2 parm3".  If you're using an ADP and creating a form
whose data set comes from a sproc, then all you have to do is supply the
name of the sproc as the record source. This, incidentally, even works if
the sproc needs a parameter. You will find on the property sheet the place
to put your parameters.

You can also create a command object and then give it some parameter objects
and then execute the command object. I tried this a few times and it works,
but I got tired of typing all the lines required.

In your Access app, if you know the parameters you want to feed the sproc, I
would go with the first way. A simple EXEC (or EXECUTE if you're feeling
verbose).

Now, regarding the 2m row selection. This one is easy, unless I'm missing
something. Just SELECT TOP 2 Million to start with. Create a sproc that
expects a starting PK and defaults to zero or one. Make the sproc select the
top two million rows whose pk is greater than the parameter you feed in. You
could even declare a second parameter called @lastPK, defined as an OUTPUT
parameter, so you get back the last PK in the 2M rows selected. Then you can
use that value as the input next time you fire the sproc.

I hope this helped. I might have inadvertently left out a step. If so, let
me know.

Arthur


On 5/7/07, JWColby <jwcolby at colbyconsulting.com> wrote:
>
> I am making great progress on understanding how to create stored
> procedures
> and getting them functioning.  The piece I am still missing for my
> particular application is how to get them to run from Access.  As you
> probably know by now I am trying to do batch processing of data import /
> export.  Last week I was working on getting a 100 million record data
> import
> happening, where the data came in from 56 different files of various size,
> one or more files per state, depending on the population of the state.  I
> got a stored procedure built and, using a Bulk Insert SQL statement was
> able
> to up my import from a previous high less than 500 records / second to up
> above 12K records / second on average.  What an improvement that has been!
> Again a million thanks to all those who so patiently talked me through
> this
> stuff.
>
> In the end I simply opened a query window inside of SQL Server, and keyed
> in
> the name of the stored procedure and a file name, manually recorded the
> time
> it took SQL Server to perform the insert, modified the filename and did
> the
> next etc.  56 times and I was done.  Not efficient but with the import
> times
> so radically improved at least I could get it done.
>
> My next step has to be getting such a stored procedure functioning when
> run
> from Access.  ATM my application that does the data transformation from
> fixed width to csv is the driver for this entire process, and ATM it is
> written in Access / VBA.  Remember that these stored procedures simply do
> a
> BULK INSERT, passing in a file name.  therefore these stored procedures do
> not yet return a recordset (or even a value), but I really do need to get
> them to return a value eventually.  My strategy is to "baby step" this
> thing
> so that I can sort out where the inevitable problem lies and get it
> functioning one step at a time.  So my next step is simply to get the
> stored
> procedure executing when called from VBA.
>
> If anyone has code that they are willing to share that executes a stored
> procedure in SQL Server , passing in a parameter, executed from VBA out in
> Access I would be most appreciative.
>
> On another note entirely, does anyone know how to, in SQL, specify a
> specific quantity of records, from a specific place in a table, without
> depending on an autonumber PK to do it.
>
> IOW, I need to pull the first 2 million records, then the second 2 million
> records, then the third 2 million records etc.  I will be exporting these
> out to a CSV file.  The table has an autoincrement PK but some records
> have
> been deleted because their address was not deliverable.  Thus I could
> simply
> say "WHERE PKID >0 and <=2,000,000" and for the next set say "WHERE PKID >
> 2,000,000 and <=4,000,000"  and in fact I will use this approach if
> required.  The problem is that the result set will not be 2 million
> records,
> but rather 2 million minus the deleted records in that range.
>
> I suppose I could create another autoincrement field so that I would have
> a
> field where the numbers are consecutive and then use the approach above,
> using that field.  I am just trying to discover whether it is possible
> with
> SQL to do this without depending on an autoincrementing number field.
>
> Thanks,
>
> 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
>