Posted by Neil on 10/14/05 21:41

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:1129293323.871920@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" <ssampath@sct.co.in> wrote in message
>> news:eu6QXyJ0FHA.1040@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@gmail.com
>>>Blog: http://blogs.sqlxml.org/srinivassampath
>>>URL: http://www32.brinkster.com/srisamp
>>>
>>>"Neil" <nospam@nospam.net> wrote in message
>>>news:1LJ3f.1105$hY6.775@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
>>>>
>>>
>>>
>>

[Back to original message]