select into

[john24]

I've done a select ... into via

SqlCmd = "select LoanNumber, [Date], Status, MSI into #tempLoanStatus " & _
         "from MonthlyData " & _
         "where Shelf = '" & ShelfToPRocess & "'" & _
         "order by LoanNumber, [Date]"
 
Set cmdGetLoanData = New ADODB.Command
cmdGetLoanData.ActiveConnection = cnRFCData
cmdGetLoanData.CommandTimeout = 0
cmdGetLoanData.CommandText = SqlCmd
 
Set rsLoanTemp = cmdGetLoanData.Execute

and this would seem to work as it takes a few minutes to execute and no errors are returned (not necessarily a good assumption) but the question is: how do i then access the temp table #tempLoanStatus. i need to use the results in other sql selects. i tried to create a recordset via rsLoanTemp.Open etc but I keep getting a timeout error hence the command object

[PilgrimPete]

Hmmm. I would be tempted to say that you can't, but I'm not certain.
You might be able to do it with a global temporary table, but, again I doubt it.

You have a few alternatives (as I see it):
1. Create a permanent table temporarily (i.e. using CREATE TABLE...), use it for your queries, and then DROP it.
2. Return the records in a recordset and manipulate them client-side.
3. Do all the manipulation you need to do in a stored proc, server-side using either a temp table or a Table variable.

Personally I would go for #3, but it really depends on what you want to do with the data...

[szlamany]

What is the backend database - ACCESS or MS SQL?

[john24]

ms sql

[szlamany]

Using STORED PROCEDURES in MS SQL is a nice way to build temporary tables (or even TABLE VARIABLES - quicker and less burden to the SERVER) and then process multiple RECORDSETS from that TEMP TABLE.

Then using the ADO command object you can EXECUTE that SPROC and have it return all the recordsets in one call.

Are you looking to have the TEMPORARY table around for a very long time in the program?

[john24]

no, i just want to process the data in the recordset to produce various reports

[szlamany]

In query analyzer create a STORED PROCEDURE - something like:

I'm guessing at some of the datatypes and what not...

Create Procedure DoStuff @Shelf varchar(10)
As

Set NoCount On

Declare @TblVar Table (LN somedatatype, LoanDate datetime
   , Stat varchar(1), MSI varchar(10))

Insert Into @TblVar Select loanNumber, [Date], Status, MSI 
   From MonthlyData Where Shelf=@Shelf

Select * From @TblVar Order by LN,LoadDate

Select ...
Go

This creates a STORED PROCEDURE - you EXECUTE it with the .EXECUTE method of the COMMAND object in VB.

It's parameterized - so you pass in the SHELF to process.

It can do more than one SELECT - it's the way to go with MS SQL Server.

Hope this helps.

[john24]

helps greatly!! thank you