Re: SQL Server - Filter

Hi Rich,

Thanks for further comments. I appreciate that other readers of the
newsgroup will gain form your comments, as I believe I have.

What I am trying to do is minimise the amount of work that I will have to do
to convert the existing database. Given that practically all forms and
queries in the existing Access DB refer to the selection criteria that the
user specifies so I really am looking at a way of telling SQL Server that
all Views should only pass back records that fit the criteria and I really
don't want to rewrite all the forms and associated combo boxes etc to take
this into account. What I am looking for is some way of specifiying to SQL
Server that for 'this user' on 'this Access session' only include rows that
match their selection criteria. I am happy that I can create a row in a
Selection Table and then base all views on joins to that table BUT what I am
looking for is a way to relate the row in the selection table back to the
'this user' on 'this Access session'.

I reckon that what I am trying to do would probably have lots of use in real
life database coding, if nothing else it could remove lots of hard coding of
'where' statements into forms whether they are based on DAO, ADO or
whatever! To put it simply, what I am trying to do is say to SQL ... just
send back rows that meet these criteria until I tell you otherwise ... and
of course this only applies to to ME ie the person, session or whatever that
sends the command.

Thanks for your input.

Bob Collinson
"Rich P" <rpng123@xxxxxxx> wrote in message
news:9i1xf.71$gp1.12248@xxxxxxxxxxxxxxxxxx
> Quick disclaimer here, I am just sharing my experience(s) with Access
> and Sql Server and what worked for me. I should have ended my post with
> an "HTH".
>
> Anyway, here is one other suggestion for working with Sql Server and
> Access, you can use the Access ADP. From this interface you can write
> stored procedures against sql server directly from Access, sql server
> functions, and so on. The only catch is that the ADP requires you to
> already be a Sql Server developer to really benefit from it - implying
> your are familiar with Tsql. I find that I have way more control
> writing procedures in Sql Server and pulling the data that I want. The
> downside is that you can't write adhock queries against the Sql Server
> tables if they are not linked. Also, if your sql server tables don't
> have millions of records, then yes, you can write regular Access queries
> against these smaller tables (a few hundred thousand records). My
> earlier post was based on real large tables - too large for Access to
> handle. Anyway, the thing with Access and Sql server is that there are
> several ways to accomplish various tasks. I was just sharing something
> that has worked for me.
>
> The purposes of my posts are to provide assistance to other developers
> and aspiring developers in exchange for staying proficient in my craft
> of programming by putting forth the effort to answer the question(s) or
> my perception of a question.
>
> As far as selecting specific records from a view, if you have properly
> indexed your sql server table(s) then Access queries should run
> efficiently. You can use the Sql Server tuning wizard (from the Tools
> menu in Enterprise Manager) to have it check if you need any indexes are
> respective tables. My preference, however, in addition to using the
> tuning wizard, is to write my tSql statements through com ADO. Use the
> ADO command object as I have and write your queries using tSql. Thus,
> you can write your tSql directly in an ADP or you can write tSql using
> com ADO (as opposed to ADO.Net which is not supported in Access at this
> time). Or you can use Access queries against properly indexed sql
> server tables.
>
> HTH.
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • Re: User Name
    ... dude screw your MDB crap ... SQL Server is bigger, better, cheaper, easier administration, easier ... Now, since Access queries can be this simple, e.g., the query ...
    (microsoft.public.excel)
  • Re: Query stops working with ODBC to SQL Server. Help!
    ... The SQL "flavor" only becomes T-SQL in a passthrough query to SQL Server. ... floats to the top and the True floats to the bottom. ... Does this effect the function of "IIf" functions in Access queries? ...
    (comp.databases.ms-access)
  • Multiple queries require many logins to same database
    ... I have several Excel workbooks that use Access as their ... data source (Access is linked to SQL server). ... workbooks uses a number of the Access queries to get its ...
    (microsoft.public.excel.misc)
  • Query To Stored Procedure Conversion Problem
    ... I am having some problem in conversion of access queries to SQL server 2000 ... I have exported Access tables to SQL server 200 easily but ...
    (microsoft.public.access.queries)
  • Re: SQL Server - Filter
    ... I am just sharing my experiencewith Access ... and Sql Server and what worked for me. ... have millions of records, then yes, you can write regular Access queries ... is to write my tSql statements through com ADO. ...
    (comp.databases.ms-access)