[AccessD] Thanks for the help

JWColby jwcolby at colbyconsulting.com
Mon May 7 11:46:24 CDT 2007


Arthur,

Thanks for the reply.

>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.

My FE is a regular MDB, running in Office 2003.  It is not an ADP.  I link
using the ODBC Databases() "files of type" when setting up the linked
tables.  Understand that I do not know if that is even required (linked
tables) since I am not directly updating the tables anyway.  Remember that
these are 100 million rec tables so I am not doing any of the table import /
export from inside of Access, but rather through the sprocs.

>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.

No ADPs here, nor am I attempting to view or update any of the information
through a form.

>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.

I think from what others are saying as well, that this is the route I will
take.

Thanks again for all assistance.  I will prevail!


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 Arthur Fuller
Sent: Monday, May 07, 2007 12:33 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list