468,765 Members | 1,128 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,765 developers. It's quick & easy.

Unique Selections In Back End

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the MDB
file, the result is a heterogeneous join in the underlying form query, which
degrades performance. I would like to have the entire query be based on back
end SQL data. However, each user needs to be able to make a unique set of
selections, without other users' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined to
the selections table; link the view to the front end; and base the form on
the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil
Oct 14 '05 #1
18 1774
Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a comma-separated
list of IDs that is then sent to a backend procedure. In this procedure, you
can conver the comma-separated values into a temp table (using the
techniques in: http://www.algonet.se/~sommar/arrays-in-sql.html) and then do
the join with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.earthli nk.net...
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form query,
which degrades performance. I would like to have the entire query be based
on back end SQL data. However, each user needs to be able to make a unique
set of selections, without other users' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the form
on the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil

Oct 14 '05 #2
The machine name changing isn't an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They're not
permanent entities.

Also, if I use a temporary table, I'm not sure how I would bring that into
the front end except through a pass-through query. In that case, it would be
read-only.

Thus, I think it's best that I work with a view that joins the two table or
some other method that allows me to link it via ODBC. I'm a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large. Granted,
it would only return the records for the current machine. Still, it seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...
Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.earthli nk.net...
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form
query, which degrades performance. I would like to have the entire query
be based on back end SQL data. However, each user needs to be able to
make a unique set of selections, without other users' selections
affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the
form on the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil


Oct 14 '05 #3
You can create a temporary table in Access only by using Docmd.Runsql
"CREATE TABLE #...", and then using the table as a recordsource for a
form (just be sure to assign the recordsource after the creation of the
table). If you create a primary key in the table, you will be able to
edit it in your forms.
The temporary table will be dropped using a Docmd.Runsql "DROP TABLE
#.."-statement or when the connection from the front-end is closed.

In the procedure, you can then use the temporary table in the queries.

Neil wrote:
The machine name changing isn't an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They're not
permanent entities.

Also, if I use a temporary table, I'm not sure how I would bring that into
the front end except through a pass-through query. In that case, it would be
read-only.

Thus, I think it's best that I work with a view that joins the two table or
some other method that allows me to link it via ODBC. I'm a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large. Granted,
it would only return the records for the current machine. Still, it seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...
Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.earth link.net...
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form
query, which degrades performance. I would like to have the entire query
be based on back end SQL data. However, each user needs to be able to
make a unique set of selections, without other users' selections
affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the
form on the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil



Oct 14 '05 #4
The strategy I've arrived at is to have a table of working sets, and have the
table of selections include the working set key. When the user starts the
task, the program first makes a new working set record, and thus gets a new
unique ID for the set of selection records.

With this design, you can keep treating working sets as dynamic by deleting
them after use (and have a garbage collection process to empty out old ones
that failed to get deleted) or allow the user to name them, keep them around,
and refer to them again later.

On Fri, 14 Oct 2005 08:11:41 GMT, "Neil" <no****@nospam.net> wrote:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the MDB
file, the result is a heterogeneous join in the underlying form query, which
degrades performance. I would like to have the entire query be based on back
end SQL data. However, each user needs to be able to make a unique set of
selections, without other users' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined to
the selections table; link the view to the front end; and base the form on
the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil


Oct 14 '05 #5
I never had much luck with that. Access uses more than one connection to do
its work, and doesn't hold connections open indefinitely, so it's not clear
whether, after creating a temp table, Access will be able to find it later.
You also can't bind a for to a query involving a temp table and have it be
editable because you have to use a pass-though query.

On Fri, 14 Oct 2005 14:35:25 +0200, "Ol!v!"
<stevenlangenaken-at-@gmail-dot-.com> wrote:
You can create a temporary table in Access only by using Docmd.Runsql
"CREATE TABLE #...", and then using the table as a recordsource for a
form (just be sure to assign the recordsource after the creation of the
table). If you create a primary key in the table, you will be able to
edit it in your forms.
The temporary table will be dropped using a Docmd.Runsql "DROP TABLE
#.."-statement or when the connection from the front-end is closed.

In the procedure, you can then use the temporary table in the queries.

Neil wrote:
The machine name changing isn't an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They're not
permanent entities.

Also, if I use a temporary table, I'm not sure how I would bring that into
the front end except through a pass-through query. In that case, it would be
read-only.

Thus, I think it's best that I work with a view that joins the two table or
some other method that allows me to link it via ODBC. I'm a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large. Granted,
it would only return the records for the current machine. Still, it seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...
Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.eart hlink.net...

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form
query, which degrades performance. I would like to have the entire query
be based on back end SQL data. However, each user needs to be able to
make a unique set of selections, without other users' selections
affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to the selections table; link the view to the front end; and base the
form on the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil



Oct 14 '05 #6
On Fri, 14 Oct 2005 08:11:41 GMT, "Neil" <no****@nospam.net> wrote:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a boolean
field used for the check box.


By the way - if you can ditch Access 2000 for 2002 or 2003 - do it now.
Access 2000 has far more bugs and quirks with ODBC usage that can waste your
time and get you into trouble.
Oct 14 '05 #7
The problem is that I won't be able to edit the data if I access it through
a pass-through query. The table needs to be linked to the MDB in order for
it to be edited.

What would be best would be if it were possible to have a view that is
linked to the front end, but which can be customized to return the records
only for the particular user.

Thanks,

Neil
""Ol!v!"" <stevenlangenaken-at-@gmail-dot-.com> wrote in message
news:11***************@seven.kulnet.kuleuven.ac.be ...
You can create a temporary table in Access only by using Docmd.Runsql
"CREATE TABLE #...", and then using the table as a recordsource for a form
(just be sure to assign the recordsource after the creation of the table).
If you create a primary key in the table, you will be able to edit it in
your forms.
The temporary table will be dropped using a Docmd.Runsql "DROP TABLE
#.."-statement or when the connection from the front-end is closed.

In the procedure, you can then use the temporary table in the queries.

Neil wrote:
The machine name changing isn't an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They're not
permanent entities.

Also, if I use a temporary table, I'm not sure how I would bring that
into the front end except through a pass-through query. In that case, it
would be read-only.

Thus, I think it's best that I work with a view that joins the two table
or some other method that allows me to link it via ODBC. I'm a little
leary about the approach I outlined in my message since it means that the
view will have X records x Y machines, which would make it very large.
Granted, it would only return the records for the current machine. Still,
it seems that there would be a large number of records initially dealt
with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...
Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.eart hlink.net...

I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check
a box to select one or more records. This is accomplished with a local
table containing two fields: the primary key value of the SQL table and
a boolean field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB file, the result is a heterogeneous join in the underlying form
query, which degrades performance. I would like to have the entire query
be based on back end SQL data. However, each user needs to be able to
make a unique set of selections, without other users' selections
affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table
joined to the selections table; link the view to the front end; and base
the form on the SQL: "Select * From MyView Where
MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil


Oct 14 '05 #8
Right. So it seems that my original idea of just having a view that returns
all records and then use the machine name as a criterion in the recordsource
query is the only way to go. Unless you have another idea? See any problems
with that approach?

Thanks,

Neil

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:6n********************************@4ax.com...
I never had much luck with that. Access uses more than one connection to
do
its work, and doesn't hold connections open indefinitely, so it's not
clear
whether, after creating a temp table, Access will be able to find it
later.
You also can't bind a for to a query involving a temp table and have it be
editable because you have to use a pass-though query.

On Fri, 14 Oct 2005 14:35:25 +0200, "Ol!v!"
<stevenlangenaken-at-@gmail-dot-.com> wrote:
You can create a temporary table in Access only by using Docmd.Runsql
"CREATE TABLE #...", and then using the table as a recordsource for a
form (just be sure to assign the recordsource after the creation of the
table). If you create a primary key in the table, you will be able to
edit it in your forms.
The temporary table will be dropped using a Docmd.Runsql "DROP TABLE
#.."-statement or when the connection from the front-end is closed.

In the procedure, you can then use the temporary table in the queries.

Neil wrote:
The machine name changing isn't an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They're not
permanent entities.

Also, if I use a temporary table, I'm not sure how I would bring that
into
the front end except through a pass-through query. In that case, it
would be
read-only.

Thus, I think it's best that I work with a view that joins the two table
or
some other method that allows me to link it via ODBC. I'm a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large.
Granted,
it would only return the records for the current machine. Still, it
seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...

Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.ear thlink.net...

>I am using SQL 7 with an MS Access 2000 MDB front end, using bound
>forms
>with ODBC linked tables. In one form, the user needs to be able to
>check a
>box to select one or more records. This is accomplished with a local
>table
>containing two fields: the primary key value of the SQL table and a
>boolean field used for the check box.
>
>Since the local table used to contain the boolean field is local to the
>MDB file, the result is a heterogeneous join in the underlying form
>query, which degrades performance. I would like to have the entire
>query
>be based on back end SQL data. However, each user needs to be able to
>make a unique set of selections, without other users' selections
>affecting theirs.
>
>An idea I have is to port the selections table to the back end with an
>additional field for machine name; create a view of the main table
>joined
>to the selections table; link the view to the front end; and base the
>form on the SQL: "Select * From MyView Where MachineName='MyMachine'".
>
>However, I wonder if there's a better approach. Any ideas would be
>appreciated.
>
>Thanks,
>
>Neil
>

Oct 14 '05 #9
Never mind my other message in reply to this. Didn't see that you had posted
other messages.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:6n********************************@4ax.com...
I never had much luck with that. Access uses more than one connection to
do
its work, and doesn't hold connections open indefinitely, so it's not
clear
whether, after creating a temp table, Access will be able to find it
later.
You also can't bind a for to a query involving a temp table and have it be
editable because you have to use a pass-though query.

On Fri, 14 Oct 2005 14:35:25 +0200, "Ol!v!"
<stevenlangenaken-at-@gmail-dot-.com> wrote:
You can create a temporary table in Access only by using Docmd.Runsql
"CREATE TABLE #...", and then using the table as a recordsource for a
form (just be sure to assign the recordsource after the creation of the
table). If you create a primary key in the table, you will be able to
edit it in your forms.
The temporary table will be dropped using a Docmd.Runsql "DROP TABLE
#.."-statement or when the connection from the front-end is closed.

In the procedure, you can then use the temporary table in the queries.

Neil wrote:
The machine name changing isn't an issue, since these selections are
temporary -- maybe a few hours or overnight at the most. They're not
permanent entities.

Also, if I use a temporary table, I'm not sure how I would bring that
into
the front end except through a pass-through query. In that case, it
would be
read-only.

Thus, I think it's best that I work with a view that joins the two table
or
some other method that allows me to link it via ODBC. I'm a little leary
about the approach I outlined in my message since it means that the view
will have X records x Y machines, which would make it very large.
Granted,
it would only return the records for the current machine. Still, it
seems
that there would be a large number of records initially dealt with.

Thanks,

Neil
"SriSamp" <ss******@sct.co.in> wrote in message
news:eu**************@TK2MSFTNGP14.phx.gbl...

Relying on machine names may be difficult, since if the machine name
changes, your code will not work (not that it is very frequent, but it
happens). One technique that I've seen people use is to have a local MDB
table itself for making the selections. You can then form a
comma-separated list of IDs that is then sent to a backend procedure. In
this procedure, you can conver the comma-separated values into a temp
table (using the techniques in:
http://www.algonet.se/~sommar/arrays-in-sql.html) and then do the join
with the actual table to show the results back.
--
HTH,
SriSamp
Email: sr*****@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Neil" <no****@nospam.net> wrote in message
news:1L****************@newsread1.news.pas.ear thlink.net...

>I am using SQL 7 with an MS Access 2000 MDB front end, using bound
>forms
>with ODBC linked tables. In one form, the user needs to be able to
>check a
>box to select one or more records. This is accomplished with a local
>table
>containing two fields: the primary key value of the SQL table and a
>boolean field used for the check box.
>
>Since the local table used to contain the boolean field is local to the
>MDB file, the result is a heterogeneous join in the underlying form
>query, which degrades performance. I would like to have the entire
>query
>be based on back end SQL data. However, each user needs to be able to
>make a unique set of selections, without other users' selections
>affecting theirs.
>
>An idea I have is to port the selections table to the back end with an
>additional field for machine name; create a view of the main table
>joined
>to the selections table; link the view to the front end; and base the
>form on the SQL: "Select * From MyView Where MachineName='MyMachine'".
>
>However, I wonder if there's a better approach. Any ideas would be
>appreciated.
>
>Thanks,
>
>Neil
>

Oct 14 '05 #10
That's interesting, since I've heard so much about 2002 and 2003 not being
that much different than 2000 and not worth the upgrade. Thanks for the
note.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:hs********************************@4ax.com...
On Fri, 14 Oct 2005 08:11:41 GMT, "Neil" <no****@nospam.net> wrote:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean
field used for the check box.


By the way - if you can ditch Access 2000 for 2002 or 2003 - do it now.
Access 2000 has far more bugs and quirks with ODBC usage that can waste
your
time and get you into trouble.

Oct 14 '05 #11
That sounds fine, and is similar to what I mentioned (working set key would
be machine name). When you used these, were they linked to an MDB or were
they just back end processes? I need to be able to use this in a bound form
approach, where the selection check box on the form is bound to the
selection table. Thus, the recordsource for the form needs to just return
the records pertaining to a particular working set key. How did you
accomplish that?

Thanks,

Neil
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ac********************************@4ax.com...
The strategy I've arrived at is to have a table of working sets, and have
the
table of selections include the working set key. When the user starts the
task, the program first makes a new working set record, and thus gets a
new
unique ID for the set of selection records.

With this design, you can keep treating working sets as dynamic by
deleting
them after use (and have a garbage collection process to empty out old
ones
that failed to get deleted) or allow the user to name them, keep them
around,
and refer to them again later.

On Fri, 14 Oct 2005 08:11:41 GMT, "Neil" <no****@nospam.net> wrote:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB
file, the result is a heterogeneous join in the underlying form query,
which
degrades performance. I would like to have the entire query be based on
back
end SQL data. However, each user needs to be able to make a unique set of
selections, without other users' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to
the selections table; link the view to the front end; and base the form on
the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil

Oct 14 '05 #12
SriSamp (ss******@sct.co.in) writes:
(using the
techniques in: http://www.algonet.se/~sommar/arrays-in-sql.html)


That link is dead. My stuff is now on www.sommarskog.se, so the link is
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 14 '05 #13
Thank you very much for this Erland....

a truly well-thought-out tutorial!!!

gary

That link is dead. My stuff is now on www.sommarskog.se, so the link is
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 15 '05 #14
Check out my example of multi-select here:

http://www.members.shaw.ca/AlbertKal.../msaccess.html

In the above, I don't use a column, nor do I use a temp field. So,
presumably, you could pass this "result" to a proc on sql server.....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Oct 16 '05 #15
Hi, Steve. Elsewhere in this thread I said to "ignore my other post," so I'm
thinking that my post here got ignored, when I actually meant the other one.
Thus, I'm reposting it below, in case that happened. Thanks!

Neil

Original post:

That sounds fine, and is similar to what I mentioned (working set key would
be machine name). When you used these, were they linked to an MDB or were
they just back end processes? I need to be able to use this in a bound form
approach, where the selection check box on the form is bound to the
selection table. Thus, the recordsource for the form needs to just return
the records pertaining to a particular working set key. How did you
accomplish that?

Thanks,

Neil

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ac********************************@4ax.com...
The strategy I've arrived at is to have a table of working sets, and have
the
table of selections include the working set key. When the user starts the
task, the program first makes a new working set record, and thus gets a
new
unique ID for the set of selection records.

With this design, you can keep treating working sets as dynamic by
deleting
them after use (and have a garbage collection process to empty out old
ones
that failed to get deleted) or allow the user to name them, keep them
around,
and refer to them again later.

On Fri, 14 Oct 2005 08:11:41 GMT, "Neil" <no****@nospam.net> wrote:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms
with ODBC linked tables. In one form, the user needs to be able to check a
box to select one or more records. This is accomplished with a local table
containing two fields: the primary key value of the SQL table and a
boolean
field used for the check box.

Since the local table used to contain the boolean field is local to the
MDB
file, the result is a heterogeneous join in the underlying form query,
which
degrades performance. I would like to have the entire query be based on
back
end SQL data. However, each user needs to be able to make a unique set of
selections, without other users' selections affecting theirs.

An idea I have is to port the selections table to the back end with an
additional field for machine name; create a view of the main table joined
to
the selections table; link the view to the front end; and base the form on
the SQL: "Select * From MyView Where MachineName='MyMachine'".

However, I wonder if there's a better approach. Any ideas would be
appreciated.

Thanks,

Neil

Oct 17 '05 #16
"Neil" <no****@nospam.net> wrote
The machine name changing isn't an issue,
since these selections are temporary -- maybe
a few hours or overnight at the most. They're not
permanent entities.


How about constructing the T-SQL update Query in code and using it as a
Passthrough Query, or creating a Stored Procedure to which you pass
parameters, or, indeed, just creating a Jet query based on information from
the local table, but that does not Join to the local table -- one of these
approaches might avoid the delays, which are, as you pointed out, due to
using a heterogeneous join.
Oct 19 '05 #17
Hi, Larry! The problem is that the customer really likes the way it's set
up. They have a bound check box control in the form, which they use in form
view or datasheet view, and it's an integral part of how they work with
records. To get them to give up the check box would be hard. Thus, somehow
the field needs to appear in the form with its current selection status.

A new wrinkle, though: I just found out that the selections do not need to
persist between instances of the form. (Previously I thought that that's how
they were using them; but I found out that they actually do all their
selecting and working with selections in a single session.) So that opens up
other possibilities.

Neil
"Larry Linson" <bo*****@localhost.not> wrote in message
news:eP**************@TK2MSFTNGP09.phx.gbl...
"Neil" <no****@nospam.net> wrote
The machine name changing isn't an issue,
since these selections are temporary -- maybe
a few hours or overnight at the most. They're not
permanent entities.


How about constructing the T-SQL update Query in code and using it as a
Passthrough Query, or creating a Stored Procedure to which you pass
parameters, or, indeed, just creating a Jet query based on information
from the local table, but that does not Join to the local table -- one of
these approaches might avoid the delays, which are, as you pointed out,
due to using a heterogeneous join.

Oct 19 '05 #18
On Mon, 17 Oct 2005 00:02:35 GMT, "Neil" <no****@nospam.net> wrote:
Hi, Steve. Elsewhere in this thread I said to "ignore my other post," so I'm
thinking that my post here got ignored, when I actually meant the other one.
Thus, I'm reposting it below, in case that happened. Thanks!

Neil

Original post:

That sounds fine, and is similar to what I mentioned (working set key would
be machine name). When you used these, were they linked to an MDB or were
they just back end processes? I need to be able to use this in a bound form
approach, where the selection check box on the form is bound to the
selection table. Thus, the recordsource for the form needs to just return
the records pertaining to a particular working set key. How did you
accomplish that?


I didn't use check boxes. I used a continuous form with combo boxes for
selecting records to include. To add one to the list, add a record to the
batch detail, and select the target record (filling in its key). To remove
one from the list, delete its batch detail record.
Oct 20 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by John Hoge | last post: by
8 posts views Thread by nescio | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.