Posted by Ol!v!� on 10/14/05 15: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@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]