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