SQL Statements based on a Recordset

[mannabaron]

I want to generate a sql statemante, which is based on an existing ADODB Recordset. This existing Recordset itself is also a sql statement.
Can someone tell me if this is possible an give me a little sample code?
Thank you very much for help.
Greetings.

Carpe diem.

 

[VBslammer]

Are you generating the recordset using a Command object? If you have access to the Command object it contains the SQL if it's a text type command. Since there can be several different types of commands, you need to check which type is being used:

  Select Case rst.ActiveCommand.CommandType
    Case ADODB.adCmdUnknown
    
      'probably a saved querydef.
      strSQL = CurrentDb().QueryDefs(rst.ActiveCommand.CommandText).SQL
      
    Case ADODB.adCmdText, ADODB.adCmdTable, ADODB.adCmdTableDirect
      
      'SQL statement or tabledef.
      strSQL = rst.ActiveCommand.CommandText
      
    Case ADODB.adCmdStoredProc
      
      'stored procedure.
      'N/A
  End Select

If you don't have access to a Command object, you can at least get information on the fields:

  Dim strFields As String
  Dim fld As ADODB.Field
  
  For Each fld In rst.Fields
    strFields = strFields & fld.Properties("BASETABLENAME").Value & "." & fld.Name & vbCrLf
  Next fld
  
  Debug.Print strFields

VBSlammer

Unemployed in Houston, Texas

 

[mannabaron]

Hi VbSlammer, thank you for your answer, but maybe my question wasn´t precisse. I try it again:

Sometimes it is too slow to use access querys bases on another access querys. Therefore I have to use temporary tables. With the SQL Server it is no problem, because you dont have to manage to delete these tables. In Access I am looking for a solution. I am thinking about using recordsets, because these are working in the memory and I don´t have to save these. So I can build the first recordset. This is no problem. But now I cant find a possibility to build a second recordset which is based on the first recordset. Maybe this is no possible. I dont know. May be you know a workaround?

Thank you very much.
Greetings.

Carpe diem.

 

[cmmrfrds]

Is the second recordset a subset of the first recordset or is it different data?

 

[VBslammer]

I use temp tables in Access all the time, and it only takes one line of code to "manage" to delete one:

  DoCmd.RunSQL "DROP TABLE #tblTemp"

However, instead of creating and dropping temp tables dynamically, I prefer to create my temp tables in the designer, then clear/populate them at runtime as needed. You normally know which fields are needed in the temp table ahead of time anyway, right?

An empty table doesn't take up much space and if you prefix each temp table with "#" they all stay together in the Access database window when sorted by name.

Perhaps you can post some of the SQL Server scripts you're trying to migrate to Access, and we could get a better idea of how to do it in a way that is efficient enough for your needs.

VBSlammer

Unemployed in Houston, Texas

 

[mannabaron]

Hi cmmrfrds!
Yes, I want to build a subset.

Hi vbslammer,
thank you very much for your answer.
I was looking in the net until now, but I cant find a solution to build subsets. So I give up and I use temp Tables in the front end.

Greetings.

Carpe diem.

 

[RoyVidar]

In my little experience, running queries are faster than recordset approaches. I would try experimenting with such approaches, if possible.

To assign one recordset to another, you should be able to do that using the .clone method of the recordset:

[tt]set newrs = rs.clone[/tt]

and depending on the properties of it, use the .Filter property to limit the records of it.

I think you could also populate a disconnected recordset, perhaps something along these lines (you'd probably need some testing/errortraps on this thrown together sample...).

[tt]' rs is your current recordset, where you've issued a .filter

dim rsDis as adodb.recordset
dim lngCount as long

set rsDis = new adodb.recordset
for lngCount = 0 to rs.fields.count-1
' loop through "old" recordset, fetch the relevant (hopefully)
' properties, and create a disconnected recordset
select case rs.fields(lngCount).type
case advarwchar,adlongvarwchar
rsdis.fields.append rs.fields(lngCount).name, rs.fields(lngCount).Type, _
rs.fields(lngCount).definedsize, adfieldisnullable
case else
rsdis.fields.append rs.fields(lngCount).name, rs.fields(lngCount).Type
end select
next lngCount
rsDis.Open
' open and populate the disconnected recordset with
' values from the (filtered) "old" recordset
do while not rs.eof
for lngCount = 0 to rs.fields.count-1
rsdis.fields(lngCount).value = rs.fields(lngCount).value
next lngCount
rs.movenext
loop[/tt]

Roy-Vidar

 

[cmmrfrds]

Since it is an ADO recordset it is easy to make a subset by just using the Filter Method. For example.

rs.Filter = "myid = 3"
rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend
'-- turn off the filter, which will make the whole recordset available again.
rs.Filter = adFilterNone

'--You could even save the filtered recordset if you needed to use again. Before turning off the filter. Save
Dim rs1 as new adodb.recordset
Set rs1 = rs

 

[RoyVidar]

Ah, uhm, yeah, should at least be a rsdis.addnew in my last loop..., there's probably more typos in there too

Roy-Vidar