[AccessD] was SQL Server queries - appending strings

On 31 Jan 2004 at 21:27, John W. Colby wrote:

> I have a main tabbed form which uses a multi-table join to get live data
> from a client / claim .  The client wanted it that way so that the user
> could edit fields in either table.
> 
> Views appear to be similar to tables, i.e. I can link to them and they show
> up in the table window.  Is there a way to pass parameters to them?  "Where
> LastName like col*" etc?
> 

No, but you can modify views using "ALTER VIEW" to change the 
parameters. Not a good option with multiple users though :-(

> ATM, I pull the whole recordset and then filter down to a single record.
> Not fast, but after the initial pull the filter proceeds at a reasonable
> rate.  I can then "filter" to a specific claim reasonably fast.  I would
> like to change this to actually ask SQL Server for a view of exactly one
> record each time they want to see a claim.  Thus avoiding the "pull the
> entire recordset across the net, now pull the index, etc.
> 
> This must be editable so AFAIK I cannot use stored procedures which would
> allow passing parameters.  Because this is A2K AFAIK I cannot assign a
> recordset to the form's recordsource property.
> 
 What are my options here?  Do I have any?
> 

I hate to say this, but you could go unbound with a SProc and update 
statements :-)

One off the top of my head:

Create a control table containing LoginName and ClaimID fields.

Create the view of  your data which includes a join on the control 
table and a constraint of LoginName = SYSTEM_USER.

Before you pull the view, update the control table with the users 
LoginName and the relevant ClaimID.







 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.