Unique Selections In Back End
in [Using MS SQL Server]
|
From: Neil on 14 Oct 2005 04:11 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
From: SriSamp on 14 Oct 2005 04:56 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: srisamp(a)gmail.com Blog: http://blogs.sqlxml.org/srinivassampath URL: http://www32.brinkster.com/srisamp "Neil" <nospam(a)nospam.net> wrote in message news:1LJ3f.1105$hY6.775(a)newsread1.news.pas.earthlink.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 >
From: Neil on 14 Oct 2005 05:37 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" <ssampath(a)sct.co.in> wrote in message news:eu6QXyJ0FHA.1040(a)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: srisamp(a)gmail.com > Blog: http://blogs.sqlxml.org/srinivassampath > URL: http://www32.brinkster.com/srisamp > > "Neil" <nospam(a)nospam.net> wrote in message > news:1LJ3f.1105$hY6.775(a)newsread1.news.pas.earthlink.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 >> > >
From: "Ol!v!é"<stevenlangenaken-at- on 14 Oct 2005 08:35 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" <ssampath(a)sct.co.in> wrote in message > news:eu6QXyJ0FHA.1040(a)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: srisamp(a)gmail.com >>Blog: http://blogs.sqlxml.org/srinivassampath >>URL: http://www32.brinkster.com/srisamp >> >>"Neil" <nospam(a)nospam.net> wrote in message >>news:1LJ3f.1105$hY6.775(a)newsread1.news.pas.earthlink.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 >>> >> >> > >
From: Steve Jorgensen on 14 Oct 2005 10:37 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" <nospam(a)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 >
|
Next
|
Last
Pages: 1 2 3 4 Prev: SCOPE_INDENTITY() Not Returning Next: OpenDataSource trouble with Advantage SQL |