[AccessD] Access 2000 - Filtering SQL Server records

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