Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Accessing SQL Server temp table for Access Project

    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 from MS Access (every thing works on Query Analyzer).
    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 like ##Test, but I can't use global temp tabels for my application)

    Here is my code, any idea what the problem might be?


    Dim Rs As ADODB.Recordset
    Set Rs = New ADODB.Recordset

    Dim SQL As String


    SQL = "exec sp_dropMListSource "
    SQL = SQL & "SELECT distinct dbo.tblContact.ContactID,ISNULL(dbo.tblContact.Fir stName, '') "
    SQL = SQL & "+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS [Contact Name]"
    SQL = SQL & "INTO #MListSource "
    SQL = SQL & " FROM dbo.tblContact INNER JOIN dbo.tblProperty ON "
    SQL = SQL & "dbo.tblContact.ContactID = dbo.tblProperty.ContactID "
    SQL = SQL & "WHERE dbo.tblProperty.Zip in (" & Me!zips & ")Order By [Contact Name]"

    Rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Me.MListSource.RowSource = "Select ContactID, [Contact Name] From #MListSource"

    Set Rs = Nothing
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> I know that local temp tables are deleted when the connection is lost
    Nope - it is dropped when the batch completes.
    In your case the batch is the create statement.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    nigelrivett Not exactly try the following, works just fine.

    Code:
    create table #Tmp(f1 int)
    go
    insert into #Tmp values(1)
    go
    select * from #Tmp
    go

    Sia Okay, this is just a guess but what happens if you run profiler as you step through your code? I am thinking that the temp table is alive until you execute the "Set Rs = Nothing". I think this implicitly (sp?) closes the connection and as a result your temp table goes away.

    Here is a blerb from BOL:

    Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


    All other local temporary tables are dropped automatically at the end of the current session.


    Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


    Can you run your code in debug and check this out?
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    I think that Nigel is right. Once the batch completes the local temp table is dropped. If you want to return the entries from your temp it would be better to put all the SQL statements in a SP that returns your recordset (use ADODB command object)

    Originally posted by Paul Young
    nigelrivett Not exactly try the following, works just fine.

    Code:
    create table #Tmp(f1 int)
    go
    insert into #Tmp values(1)
    go
    select * from #Tmp
    go

    Sia Okay, this is just a guess but what happens if you run profiler as you step through your code? I am thinking that the temp table is alive until you execute the "Set Rs = Nothing". I think this implicitly (sp?) closes the connection and as a result your temp table goes away.

    Here is a blerb from BOL:

    Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

    A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.


    All other local temporary tables are dropped automatically at the end of the current session.


    Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


    Can you run your code in debug and check this out?
    Steve

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    The problem is that the local temp table is accessible only through the SP and not from outside, however the global temp table is accissble from outside.
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Unless a temporary table is created within a stored procedure it persists until it is explicitly dropped or it's connection ends.

    The problem is that your code is using a second connection to populate the list box than the one that created the table. If you have an adp project, you shouldn't have to make a new connection or even create a temporary table. Just set your listbox's source directly, and remember to requery it to show the results:

    SQL = SQL & "SELECT distinct dbo.tblContact.ContactID,ISNULL(dbo.tblContact.Fir stName, '') "
    SQL = SQL & "+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS [Contact Name]"
    SQL = SQL & " FROM dbo.tblContact INNER JOIN dbo.tblProperty ON "
    SQL = SQL & "dbo.tblContact.ContactID = dbo.tblProperty.ContactID "
    SQL = SQL & "WHERE dbo.tblProperty.Zip in (" & Me!zips & ")Order By [Contact Name]"

    Me.MListSource.RowSource = SQL
    Me.MListSource.Requery

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •