Post reply

How to read a SQL temp table in Access

  • February 4, 2009 at 10:38 am

    #938182

    You can't,it has to be done at the stored procedure execution time. The table is destroyed after that.

    You could go to ##temp which makes it global, but also limits the useofthe procedure to only 1 user at a time.

  • February 13, 2009 at 2:32 am

    #943403

    If you create an ado connection object, and call the stored procedure to create the temp table through this object, you can use the connection object while it is still open as the connection for your select statement

    An example is shown here, creating a temp table, then selecting from it. mcnn is an open ADO connection:

    'Get the import description from the brands in the file.

    'Check temp table doesn't already exist

    strSQL = "IF OBJECT_ID('tempdb..#tblImpDesc') > 1 DROP TABLE #tblImpDesc"

    mcnn.Execute strSQL

    strSQL = "SELECT DISTINCT brand_name " _

    & " INTO #tblImpDesc" _

    & " FROM #tblRegionImport" _

    & " WHERE brand_name IS NOT NULL"

    mcnn.Execute strSQL

    Set rstDesc = New ADODB.Recordset

    With rstDesc

    .Open "SELECT * FROM #tblImpDesc ORDER BY brand_name", mcnn, adOpenStatic, adLockOptimistic, adCmdText

    mstrDesc = "Brand Level Import."

    Do Until .EOF

    mstrDesc = mstrDesc & " " & !Brand_Name & "."

    .MoveNext

    DoEvents

    Loop

    .Close

    End With

    Set rstDesc = Nothing

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply