Posted by Steve Jorgensen on 10/14/05 17:41

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" <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]