SQL Server - Filter

[Bob]

I am in the process of upgrading an Access database to SQL Server (and
climbing that learning curve!). The wizard happily upgraded all the tables
and I can link to them OK using ODBC.

The application controls allocation of revisions to aircraft maintenance
manuals for an airline type operation.

In the application there is a form loaded at start-up allowing the user/s to
select the records that they are currently interested in from 4 different
pull down lists which are based on 4 lookup tables (Aircraft Type, Aircraft
Registration, Base and Person Responsible). The user can make a selection
from none or some or all of the lists. In the current system all the forms
are based on queries that include records by where clauses based on the
form, eg, 'Where (TableX.BaseId = Form!Selection!BaseId or
Form!Selection!BaseId is Null)'. When the Selection Form is updated (the
user changes their selection) the form forces a requery on any currently
open form and any combo/List boxes on that form so that the forms don't have
to be specifically coded to even know about it. This works well using jet
and provides excellent flexibility to the user and was relatively easy to
program (some years ago when my brain worked better I think).

In SQL Server using queries like the above slows things down (more than a
little!) because the query has to be executed in Access to allow the
inclusion of the Forms! parameters stuff. I want to be able to create
views in SQL which will pass back the data required but without using
parameter queries if possible.

I have had several ideas about how to do this but all of seem to have
problems of some sort. What would seem to be the simplest (to my mind
anyway) would be to have a 'Selection' table on the Server with one row for
each currently logged on user with the filter selections in the table and
then base all the views on the data tables joined via this selection table.
This would mean one update of the selection table each time the user varied
their selection and not much extra work to be done (by me in designing the
views etc). My problem with this approach is that I cannot work out how to
have one row in the Selection table for each current user, noting that one
user could be logged on to the same database on more than one computer or
(heaven help us) logged on more than once on one computer (I know it isn't
good but that is the way it is). I looked into using USER_ID but that would
appear to end up with duplicates in the 'one user 2 computers' scene, and
looked into SID (I think it is) but that seems to be per connection and my
reading of the On-Line book implies that there can be multiple connections
for one Access session. What I need is some sort of unique identifier for
each Access 'session' running that SQL Server knows about and which I can
find and then include in the Selection table.

What I am looking for is a simple way of making the views work without
having to code the SQL in each form (and combo box and list box) etc.

Other people must have had the same sort of problem. Is there any elegant
(ie minimal work) solution?

Thanks

I do love this news group - it has saved me soooo much time (and hair
pulling out) over the last few years. Thanks especially to the regular
contributors - you, and everyone else, know who you are.

Regards

Bob Collinson

bob leave this out at colsoft dot com dot au

[Rich P]

Hi Bob,

If you are connecting to sql server via ODBC and running queries agains
the sql server tables from access you are pretty much defeating the idea
of using a sql server. The idea is for the sql server to do the work.
You run the queries (stored procedures) in sql server and pull down the
result set to Access. The catch is that you don't use ODBC. Use ADO. I
have seen people at my place run queries against the sql server tables
from Access - 45 minutes later they are still waiting for a result set.
Eventually, I wrote them a stored procedure for the same query in sql
server. 45 minutes turned into 450 miliseconds. No kidding!

Here is a sample how you run a stored procedure in sql server from
access and pull down the result set - this example assumes you are
pulling data for a specified date range, so you have date parameters for
start date and end date -- Note: you need to make a reference in
Tools/Reference/Microsoft ActiveX Data Objects 2.6 Library or higher.
You get this from MDAC2.6. Note also, you need to have MDAC2.5 loaded
because MDAC2.5 was the last MDAC to include Jet4.0. Get this from MDSN
or google it.

Sub GetResultSetFromSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConnection = "Provider=SQLOLEDB;" _
& "Data Source=yourServer;" _
& "Initial Catalog=yourSqlServerDB;UID=sa;PWD=;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStoredProc"
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("yourAccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmdSetStatus, j)
Loop
RSado.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[lesperancer]

Rich,

this looks great, it copies the data from sql server into an access
table

is it possible to do something similar, but use ado recordset as a form
/ report recordsource instead of updating an access table ?
Rich P wrote:

Hi Bob,

If you are connecting to sql server via ODBC and running queries agains
the sql server tables from access you are pretty much defeating the idea
of using a sql server. The idea is for the sql server to do the work.
You run the queries (stored procedures) in sql server and pull down the
result set to Access. The catch is that you don't use ODBC. Use ADO. I
have seen people at my place run queries against the sql server tables
from Access - 45 minutes later they are still waiting for a result set.
Eventually, I wrote them a stored procedure for the same query in sql
server. 45 minutes turned into 450 miliseconds. No kidding!

Here is a sample how you run a stored procedure in sql server from
access and pull down the result set - this example assumes you are
pulling data for a specified date range, so you have date parameters for
start date and end date -- Note: you need to make a reference in
Tools/Reference/Microsoft ActiveX Data Objects 2.6 Library or higher.
You get this from MDAC2.6. Note also, you need to have MDAC2.5 loaded
because MDAC2.5 was the last MDAC to include Jet4.0. Get this from MDSN
or google it.

Sub GetResultSetFromSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConnection = "Provider=SQLOLEDB;" _
& "Data Source=yourServer;" _
& "Initial Catalog=yourSqlServerDB;UID=sa;PWD=;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStoredProc"
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("yourAccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmdSetStatus, j)
Loop
RSado.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Steve Jorgensen]

Rich P wrote:

Hi Bob,

If you are connecting to sql server via ODBC and running queries agains
the sql server tables from access you are pretty much defeating the idea
of using a sql server. The idea is for the sql server to do the work.
Now, your code example below looks like a very nice implementation for
certain kinds of application, but statements like the one above get me
really pissed off. I've have had to support the worst kinds of
unnecessarily complex tangled messes of code that users have created
because they read some false statement like this one on some forum, when
their app would have been vastly easier to implement and maintain and
had many fewer bugs if they'd just used Access queries for the most part.

The truth is that in most cases, Access works very well querying data
using Access queries against links to server tables, and many of the
features that make Access such a convenient platform for developing
database applications are thwarted by shemes to use only stored
procedures to query data from the back-end.

Specifically - it is NOT true that Access must read entire back-end
tables to the client to perform Access queries. Access generally
forwards query logic to the back-end for processing, even when joining 2
or more tables, so long as they are linked to the same ODBC source.

Of the cases where Access cannot run efficient queries against server
tables using an Access query, 90% of those are due to problems that can
be fixed by adjusting the query to eliminate problems like including a
VBA function in a GROUP BY field.
You run the queries (stored procedures) in sql server and pull down the
result set to Access. The catch is that you don't use ODBC. Use ADO. I
have seen people at my place run queries against the sql server tables
from Access - 45 minutes later they are still waiting for a result set.
Eventually, I wrote them a stored procedure for the same query in sql
server. 45 minutes turned into 450 miliseconds. No kidding!
Sure, but that doesn't mean you have to turn the whole app inside out.
If adjusting the Access query doesn't give the same benefits you're
describing, then perhaps a stored procedure should be used in that
specific case.
Here is a sample how you run a stored procedure in sql server from
access and pull down the result set - this example assumes you are
pulling data for a specified date range, so you have date parameters for
start date and end date -- Note: you need to make a reference in
Tools/Reference/Microsoft ActiveX Data Objects 2.6 Library or higher.
You get this from MDAC2.6. Note also, you need to have MDAC2.5 loaded
because MDAC2.5 was the last MDAC to include Jet4.0. Get this from MDSN
or google it.

Sub GetResultSetFromSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConnection = "Provider=SQLOLEDB;" _
& "Data Source=yourServer;" _
& "Initial Catalog=yourSqlServerDB;UID=sa;PWD=;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStoredProc"
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("yourAccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmdSetStatus, j)
Loop
RSado.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob]

Hi Rich,

Thanks for your input but it sort of misses my point. I certainly want to
put all he work on the server. I want to run Views on the server which will
be linked to from Access (so that Access sees them as a table). My problem
is how to make the view only select the particular records that are required
by a particular Access 'session'.

Thanks anyway.

--
Bob Collinson
"Rich P" <rp*****@aol.com> wrote in message
news:8I***************@news.uswest.net...

Hi Bob,

If you are connecting to sql server via ODBC and running queries agains
the sql server tables from access you are pretty much defeating the idea
of using a sql server. The idea is for the sql server to do the work.
You run the queries (stored procedures) in sql server and pull down the
result set to Access. The catch is that you don't use ODBC. Use ADO. I
have seen people at my place run queries against the sql server tables
from Access - 45 minutes later they are still waiting for a result set.
Eventually, I wrote them a stored procedure for the same query in sql
server. 45 minutes turned into 450 miliseconds. No kidding!

Here is a sample how you run a stored procedure in sql server from
access and pull down the result set - this example assumes you are
pulling data for a specified date range, so you have date parameters for
start date and end date -- Note: you need to make a reference in
Tools/Reference/Microsoft ActiveX Data Objects 2.6 Library or higher.
You get this from MDAC2.6. Note also, you need to have MDAC2.5 loaded
because MDAC2.5 was the last MDAC to include Jet4.0. Get this from MDSN
or google it.

Sub GetResultSetFromSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConnection = "Provider=SQLOLEDB;" _
& "Data Source=yourServer;" _
& "Initial Catalog=yourSqlServerDB;UID=sa;PWD=;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStoredProc"
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("yourAccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmdSetStatus, j)
Loop
RSado.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob]

Steve,

Yes Rich missd my point actually!

Sure, but that doesn't mean you have to turn the whole app inside out. If
adjusting the Access query doesn't give the same benefits you're
describing, then perhaps a stored procedure should be used in that
specific case.
Happy to use a stored procedure but prefer to use a View that can be linked
to so that it looks like a table to Access - at least that is what I think I
want to do - still climbing that learning curve!
Bob Collinson

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:V9********************@comcast.com... Rich P wrote:

Hi Bob,

If you are connecting to sql server via ODBC and running queries agains
the sql server tables from access you are pretty much defeating the idea
of using a sql server. The idea is for the sql server to do the work.

Now, your code example below looks like a very nice implementation for
certain kinds of application, but statements like the one above get me
really pissed off. I've have had to support the worst kinds of
unnecessarily complex tangled messes of code that users have created
because they read some false statement like this one on some forum, when
their app would have been vastly easier to implement and maintain and had
many fewer bugs if they'd just used Access queries for the most part.

The truth is that in most cases, Access works very well querying data
using Access queries against links to server tables, and many of the
features that make Access such a convenient platform for developing
database applications are thwarted by shemes to use only stored procedures
to query data from the back-end.

Specifically - it is NOT true that Access must read entire back-end tables
to the client to perform Access queries. Access generally forwards query
logic to the back-end for processing, even when joining 2 or more tables,
so long as they are linked to the same ODBC source.

Of the cases where Access cannot run efficient queries against server
tables using an Access query, 90% of those are due to problems that can be
fixed by adjusting the query to eliminate problems like including a VBA
function in a GROUP BY field.

You run the queries (stored procedures) in sql server and pull down the
result set to Access. The catch is that you don't use ODBC. Use ADO. I
have seen people at my place run queries against the sql server tables
from Access - 45 minutes later they are still waiting for a result set.
Eventually, I wrote them a stored procedure for the same query in sql
server. 45 minutes turned into 450 miliseconds. No kidding!

Sure, but that doesn't mean you have to turn the whole app inside out. If
adjusting the Access query doesn't give the same benefits you're
describing, then perhaps a stored procedure should be used in that
specific case.

Here is a sample how you run a stored procedure in sql server from
access and pull down the result set - this example assumes you are
pulling data for a specified date range, so you have date parameters for
start date and end date -- Note: you need to make a reference in
Tools/Reference/Microsoft ActiveX Data Objects 2.6 Library or higher.
You get this from MDAC2.6. Note also, you need to have MDAC2.5 loaded
because MDAC2.5 was the last MDAC to include Jet4.0. Get this from MDSN
or google it.

Sub GetResultSetFromSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConnection = "Provider=SQLOLEDB;" _
& "Data Source=yourServer;" _
& "Initial Catalog=yourSqlServerDB;UID=sa;PWD=;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStoredProc"
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenRecordset("yourAccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmdSetStatus, j)
Loop
RSado.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Steve Jorgensen]

Bob wrote:

Steve,

Yes Rich missd my point actually!

Sure, but that doesn't mean you have to turn the whole app inside out. If
adjusting the Access query doesn't give the same benefits you're
describing, then perhaps a stored procedure should be used in that
specific case.

Happy to use a stored procedure but prefer to use a View that can be linked
to so that it looks like a table to Access - at least that is what I think I
want to do - still climbing that learning curve!

Linking to a view can work well, but be sure to specify a virtual
primary key for the view, or performace may suffer, and recordsets will
not be updateable. Also, I'm not saying that it would never make sense
to run a stored procedure and cache results in a local table if that's a
good fit for the problem at hand.

[Rich P]

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 ***

[Bob]

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" <rp*****@aol.com> wrote in message
news:9i****************@news.uswest.net...

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 ***

[Zlatko Matiæ]

Hello.

I have a similar situation that you explained. I use PostrgeSQL server as
back-end. There are two tables (departments and plants) joined one-to-many,
that should be filtered by a user to select which departments and plants are
going to be included in reports. The selection of rows is performed by check
box, not by list.
Well, I solved it by creating two additional tables: departments1 and
plants1, which are actually copies of original tables, but have some
additional fields:one for entering current user and one for autoincrement id
by which records in two tables are joined. There is a pass-through query,
similar to stored procedure that populate these two tables by copying from
orginal tables, on start of every session. Of course there is also a
procedure that delets these session records on application closing. So any
user gets its own rows in these two tables during start-up of Access
front-end.
All select queries use departments1 and plants1 instead departments and
plants and filter records by user field and by bool value of check box
(true, false). If I use pass-through select queries, I use PostgreSQL
function current_user() for filtering current user of the session. If I use
JET select query, I use text box in the main form that preserves data about
current session (there is a passs-through query for the main form). In any
case, every user has its own portion of data available and don't interfere
with other users. If you want to preserve the same user to be logged from
two different computers, you could add another field, some autoincrement id
that would be copied from some auxiliary table, as unique designation of a
session...

Regards,

ZLatko
"Bob" <bob at colsoft dot com dot .au> je napisao u poruci interesnoj
grupi:43********@duster.adelaide.on.net...

I am in the process of upgrading an Access database to SQL Server (and
climbing that learning curve!). The wizard happily upgraded all the tables
and I can link to them OK using ODBC.

The application controls allocation of revisions to aircraft maintenance
manuals for an airline type operation.

In the application there is a form loaded at start-up allowing the user/s
to select the records that they are currently interested in from 4
different pull down lists which are based on 4 lookup tables (Aircraft
Type, Aircraft Registration, Base and Person Responsible). The user can
make a selection from none or some or all of the lists. In the current
system all the forms are based on queries that include records by where
clauses based on the form, eg, 'Where (TableX.BaseId =
Form!Selection!BaseId or Form!Selection!BaseId is Null)'. When the
Selection Form is updated (the user changes their selection) the form
forces a requery on any currently open form and any combo/List boxes on
that form so that the forms don't have to be specifically coded to even
know about it. This works well using jet and provides excellent
flexibility to the user and was relatively easy to program (some years ago
when my brain worked better I think).

In SQL Server using queries like the above slows things down (more than a
little!) because the query has to be executed in Access to allow the
inclusion of the Forms! parameters stuff. I want to be able to create
views in SQL which will pass back the data required but without using
parameter queries if possible.

I have had several ideas about how to do this but all of seem to have
problems of some sort. What would seem to be the simplest (to my mind
anyway) would be to have a 'Selection' table on the Server with one row
for each currently logged on user with the filter selections in the table
and then base all the views on the data tables joined via this selection
table. This would mean one update of the selection table each time the
user varied their selection and not much extra work to be done (by me in
designing the views etc). My problem with this approach is that I cannot
work out how to have one row in the Selection table for each current user,
noting that one user could be logged on to the same database on more than
one computer or (heaven help us) logged on more than once on one computer
(I know it isn't good but that is the way it is). I looked into using
USER_ID but that would appear to end up with duplicates in the 'one user 2
computers' scene, and looked into SID (I think it is) but that seems to be
per connection and my reading of the On-Line book implies that there can
be multiple connections for one Access session. What I need is some sort
of unique identifier for each Access 'session' running that SQL Server
knows about and which I can find and then include in the Selection table.

What I am looking for is a simple way of making the views work without
having to code the SQL in each form (and combo box and list box) etc.

Other people must have had the same sort of problem. Is there any elegant
(ie minimal work) solution?

Thanks

I do love this news group - it has saved me soooo much time (and hair
pulling out) over the last few years. Thanks especially to the regular
contributors - you, and everyone else, know who you are.

Regards

Bob Collinson

bob leave this out at colsoft dot com dot au

[Rick Brandt]

Bob wrote:

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.

If you use SQL Server's tracing tools you would see that running an Access
SELECT query against your link that did the filtering will send a statement to
the server that will process that criteria. Frankly I doubt that even the use
of your view is doing much for you.

My rule with SQL Server is link the tables and then use standard Access query
techniques until and unless you see performance problems. Most of the time the
server WILL do the lion's share of the work.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rich P]

Hi Bob,

I appreciate your position/situation. When I was a little more junior
in what I do I posted lots of questions in this newsgroup. Sometimes
people would give me the exact answer that I needed but I just wasn't
connecting, mostly due to lack of experience with the given solution.
After many tries, someone would use some buzzword or maybe I just
started getting it, but the light eventually came on. The trick is to
just keep at it. Anyway, here is what I understand about your problem:

You want to be able to pass criteria to a querie(s) and you want to
improve the performance of the query because it is a little bit slow -
at least this is what I thought I read. For a scenario like this, you
can write stored procedures in sql server and use/pass parameters to the
SP. Now you get the performance of the sql server and the flexibility
of Access. Here is a sample stored procedure with parameters - should
match the Access Subroutine I posted earlier for invoking stored
procedures with parameters. Create this procedure in Sql Server or an
Access ADP that is linked to a sql server:

CREATE PROCEDURE [stp_TestProc]
@bDate datetime,
@eDate datetime

AS

SELECT
z.[ID],
z.[DateofTransport],
z.[MemName],
z.[MemMR]
s.[Zip]
FROM tblz z Left Join tblS s On z.ID = s.ID
Where (z.DateOfTransport Between @bDate And @eDate)
GO

You can call this sql Server procedure from the Access subroutine in my
earlier post. Just change the name in
cmd.CommandText = "stp_TestProc". Actually, you might want to test the
procedure first in Query analyzer to make sure it works correctly.
Note: in tSql dates are delimited with single apostrophes ' (as opposed
to # in Access)

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***