473,727 Members | 2,046 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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


Jan 10 '06 #1
11 6105
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 GetResultSetFro mSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConne ction = "Provider=SQLOL EDB;" _
& "Data Source=yourServ er;" _
& "Initial Catalog=yourSql ServerDB;UID=sa ;PWD=;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStored Proc"
cmd.Parameters( "@bDate").V alue = sDate
cmd.Parameters( "@eDate").V alue = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenR ecordset("yourA ccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Co unt - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmd SetStatus, j)
Loop
RSado.Close
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jan 10 '06 #2
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 GetResultSetFro mSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConne ction = "Provider=SQLOL EDB;" _
& "Data Source=yourServ er;" _
& "Initial Catalog=yourSql ServerDB;UID=sa ;PWD=;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStored Proc"
cmd.Parameters( "@bDate").V alue = sDate
cmd.Parameters( "@eDate").V alue = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenR ecordset("yourA ccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Co unt - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmd SetStatus, j)
Loop
RSado.Close
End Sub
Rich

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


Jan 10 '06 #3
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 GetResultSetFro mSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConne ction = "Provider=SQLOL EDB;" _
& "Data Source=yourServ er;" _
& "Initial Catalog=yourSql ServerDB;UID=sa ;PWD=;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStored Proc"
cmd.Parameters( "@bDate").V alue = sDate
cmd.Parameters( "@eDate").V alue = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenR ecordset("yourA ccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Co unt - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmd SetStatus, j)
Loop
RSado.Close
End Sub
Rich

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

Jan 10 '06 #4
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.co m> wrote in message
news:8I******** *******@news.us west.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 GetResultSetFro mSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConne ction = "Provider=SQLOL EDB;" _
& "Data Source=yourServ er;" _
& "Initial Catalog=yourSql ServerDB;UID=sa ;PWD=;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStored Proc"
cmd.Parameters( "@bDate").V alue = sDate
cmd.Parameters( "@eDate").V alue = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenR ecordset("yourA ccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Co unt - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmd SetStatus, j)
Loop
RSado.Close
End Sub
Rich

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

Jan 10 '06 #5
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******** ************@co mcast.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 GetResultSetFro mSqlSvr()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, j As Integer, Retval As variant

cmd.ActiveConne ction = "Provider=SQLOL EDB;" _
& "Data Source=yourServ er;" _
& "Initial Catalog=yourSql ServerDB;UID=sa ;PWD=;"
cmd.CommandTime out = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_YourStored Proc"
cmd.Parameters( "@bDate").V alue = sDate
cmd.Parameters( "@eDate").V alue = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDb.OpenR ecordset("yourA ccessTable")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Co unt - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmd SetStatus, j)
Loop
RSado.Close
End Sub
Rich

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

Jan 10 '06 #6
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.
Jan 11 '06 #7
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 ***
Jan 11 '06 #8
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.co m> wrote in message
news:9i******** ********@news.u swest.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 ***

Jan 11 '06 #9
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.adelai de.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

Jan 11 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2594
by: manatlan | last post by:
In an intranet/lan, i'd like to put a "python server script" (which run forever) on a computer A... On another computer B, I'd like to send smtp-email to the computer A ... (send to "user@126.52.12.12", for example ...126.52.12.12 is the ip address of "B") in fact, i'd like to control "computer A" by sending smtp email from "computer B".
0
2245
by: Malcolm Cook | last post by:
I've discovered: Using "File > Print Preview" in a form's datasheet view with a server Filter crashes access after previewing... ....that is, unless ServerFilterByForm has been turned off after applying the filter. See the steps to recreate bug below for details.
6
3294
by: Willie wjb | last post by:
Hi, i have a client program that sends a filter expression to the server PC. On that server PC this filter is put over a datatable and the result is send back. the server can be located on a PC with let's say ddmmyyyy windows date format and the client has mmddyyyy. when the filter expression send is "Date < #31/01/2003#", it will fail on
5
2752
by: Paul de Goede | last post by:
I set the Response.Filter in my aspnet application but I have noticed that if you do a Server.Transfer that the filter doesn't get called. And in actual fact the response is mostly empty. It seems that only scripts get rendered. I have seen this mentioned on this newsgroup before but with no resolution. Can anyone give any insight into this problem. Is there a work around? Manually push the Reponse.OutputStream through your filter after...
2
2287
by: Kevin R | last post by:
I'm trying to get asp.net 1.1 running on my home PC. When I try creating a new ASP.NET Web Application in 'Visual Studio .NET 2003' I get the following error: "Visual Studio .NET has detected that the specified Web server is not running ASP.NET version 1.1. You will be unable to run ASP.NET Web applications or services" Thanks for any help in advance. Kevin ===============================
4
3513
by: John Boy | last post by:
Hi, Can anyone help. This is really doing my nut in. 3 years ASP exp. and now doing .DOT which is a step in the wrong direction. Basically I am left with the code of a guy who has left. When I click a button on a pop-up window the javascript for that button click does a 'button.form.submit'. On the Server side there is a Button click event for this button, but for some reason it no longer fires. It worked fine before and everything...
1
3843
by: ken | last post by:
Hi, Lets say we have a form that displays time card entries based on the calendar control date on the form. So the user clicks on a date and the form filters the table where the time card entries come from to get you proper data. In access I would just filter the query(rewrite its qrydef) and I'd get my answer. My question is, what is the proper way of doing this in ADP and SQL Server 2005. I could filter the data from the view on the...
6
3067
by: Chris Zoper | last post by:
Hello, I have a form that shows a lot of records based on a passthrough query to a SQL Server database. I noticed that the Filter and the Sort property of the form do not properly work, often the filter and sorting are not performed. When I base the form on a query on an Access-table everything works just fine, but when I use the passthrough query nothing happens. Does anyone know how to solve this problem?
5
3682
by: Jim Mandala | last post by:
Using Access 2003 front end; SQL Server 2005 Back end: I have a complex form that has lots of data fields including about thirty or so checkboxes storing Yes/No data that I would like my users to be able to use the Filter by Form functionality with to create a "Custom" query. This works perfectly fine when I filter on a text field. Also, this works perfectly fine for all data with an Access Back end. When I use boolean data and my SQL...
0
8752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9406
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9185
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8103
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6703
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.