[dba-SQLServer] Thanks for the help

John:

Here is a basic call to a stored procedure from VBA with two parameter:

Public Sub SetOrderStatus(ID as Long, Status as Long)
Dim conn As ADODB.Connection
Dim cmd As ADODB.command
Set conn = New ADODB.Connection
conn.ConnectionString = "Insert connection string here"
conn.Open
Set cmd = New ADODB.command
With cmd
    Set .ActiveConnection = conn
    .CommandType = adCmdStoredProc
    .CommandText = "sp_SetOrderStatus"
    .Parameters.Append cmd.CreateParameter("@ID", adInteger, adParamInput, ,
ID)
    .Parameters.Append cmd.CreateParameter("Status", adInteger,
adParamInput, , Status)
    .Execute
End With
Set cmd = Nothing
conn.Close
Set conn = Nothing 
End Sub

For your question on pulling records from SQL Server, if you are using SQL
Server 2005, you can use:

With OrdersA as (select [Order Number], ROW_NUMBER() over (Order By [Order
ID]) as 'ROWNUMBER' from Orders)
Select * from OrdersA where ROWNUMBER between 100 and 200 

Hope that helps.

James Barash

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, May 07, 2007 10:06 AM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Thanks for the help

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
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com