[AccessD] Access 2000 - Filtering SQL Server records

Thanks Martin.

John W. Colby
www.ColbyConsulting.com


Martin Reid wrote:
> John
> 
> example of the web
> 
> Public Function CreateProc()
>    Dim strProc As String
>    strProc = "Create Procedure qryCustByCity " & _
>              "(prmCity varchar) as " & _
>              "select * from Customers where City = prmCity"
>    CurrentProject.Connection.Execute strProc
> End Function
>     
> 
> Public Function RSFromParameterQuery(strCity As String)
>    Dim prm As ADODB.Parameter
>    Dim cmd As ADODB.Command
>    Dim rst As ADODB.Recordset
>    Set cmd = New ADODB.Command
>    Set cmd.ActiveConnection = CurrentProject.Connection
>    cmd.CommandText = "qryCustByCity"
>    cmd.CommandType = adCmdStoredProc
>    Set prm = cmd.CreateParameter("prmCity", adVarChar, _
>              adParamInput, Len(strCity))
>    prm.Value = strCity
>    cmd.Parameters.Append prm
>    Set rst = New ADODB.Recordset
>    rst.Open cmd
>    Do Until rst.EOF
>       Debug.Print rst(0), rst(1), rst(2)
>       rst.MoveNext
>    Loop
> End Function
> 
> 
> 
> Martin WP Reid
> Information Services
> The Library at Queen's
> Tel : 02890976174
> Email : mwp.reid at qub.ac.uk
> ________________________________________
> From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com]
> Sent: 16 September 2009 15:05
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Access 2000 - Filtering SQL Server records
> 
> I have an application that goes to SQL Server for three different tables.  These tables are from a
> third party payroll system.  The tables are about check information.  One of the fields holds a text
> representation of the autonumber (long int) PK from one of my tables (the Claimant table).  ATM we
> have a single view in SQL Server that pulls each and every check record.  Back in Access I then
> convert the string to long and then filter down to just the records for the claim(s) I am interested in.
> 
> This is sloooooowwwwwww.......
> 
> In this case I am using one single view for everything I do with this check data anywhere in this or
> any other of my applications.  Thus it returns every check (over 100K) any time I do ANYTHING.
> 
> I don't really use SQL Server very much as a BE to Access applications so I don't have experience in
> other ways to do this.  So I need help.
> 
> I know that the first thing I could do is rewrite the view to do the data type conversion out in SQL
> Server and hand me back records with that PKID already converted to a long integer.
> 
> Aside from that though, I really want to pass in a specific PKID and have SQL Server give me back
> just the check records for that PKID.
> 
> Remember that this is 2K.  The data is NOT editable so I do not need, and in fact cannot even get
> write access to the data.
> 
> So how do I get data back from SQL Server specific to a PK?  I assume that I have to go with a
> stored procedure?
> 
> Any help (as detailed as possible) is much appreciated.
> 
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com