Temp table and access project

[Zirak]

Hi,
I am trying to use a temp table as the record source for a listbox on a access project form.
I have no problem creating the temp table and inserting data to it, but I can't access it.
I know that local temp tables are deleted when the connection is lost, but I'm on the same form that create the temp table, why can't I access the data??
(It works when I use global temp tables ##Test, but I can't use global temp tabels for my application)

 

[cmmrfrds]

Can you show your control flow on how the temp table is created and then the source for the listbox?

 

[Zirak]

Neber mind I fixed it

 

[shiggyshag]

hi

How did you fix it please

Cheers

 

[Zirak]

Hi,
I used global temp tables. My problem was that what happens if more than one user try to use the same functionality and the same global temp table? So I used global temp tables but named them with the current user, for example ##Interaction_MyUser1, and progrmatically set the record source of the list box to "##Interaction_" & UserName.

 

[shiggyshag]

Ok Thats a good idea

how did you specify the name of the table to create in the SP?

Cheers

 

[cmmrfrds]

You don't need to use a Global temp table. As long as the select from the temp table is done on the same connection as the temp table was created the temp table will be available. The temp table will remain until the connection is dropped, so it is best to create a connection for the duration of the process such as creating the temp table and then selecting from the temp table and then dropping the connection, or explicitly drop the temp table when you are done.

Your DBA may start to question the unnecessary temp tables in the temp table area.

 

[shiggyshag]

HI

I create my temp Table using an SP when the sp is finished the table is dropped how can I keep the temp table alive until i manually drop it?

Cheers

 

[Zirak]

My experience with local temp tables is that (when used with access projects) they drop without lossing the connection, thats why i suggested the global temp tables.
But anyway, the temp tables is not droped unless the connection is lost. In your application you might be doing somthing that drops your connection

 

[cmmrfrds]

I usually create a separate connection to group processes. Something like the following.

Dim cn as new adodb.connection, rs as new adodb.recordset
set cn = currentproject.connection

cn.execute "the sp"
rs.open "the sql", cn, 3,3
cn.execute "drop ##temp table"

set rs = nothing
set cn = nothing