February 4, 2009 at 10:34 am
I'm a newbie to Access but familiar with SQL. I have an Access database that is linked to a SQL Server. My goal is to run a left outer join with a SQL temp table (#date_list) and a SQL database table to build data for an Access form. I have created a stored procedure that creates a SQL temp table. My problem is how to reference the temp table given the limited SQL commands available in Access (that is why I had to use a stored procedure to create the temp table). My question basically is how to code the select within Access to be able to execute the left outer join.
TIA for any help/advice.
February 4, 2009 at 10:38 am
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
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply