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

Never mind my other message in reply to this. Didn't see that you had posted
other messages.

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:6ngvk1tcaigdsb4g2d0j1d7s285v7st96h@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" <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]