SQL from a returned recordset

[osx99]

I have an excel app I'm developing which fetches some data from a database over a network using SQL. This works fine and returns a recordset which I've passed into an array for calcs.

The intention is to also use this returned recordset to populate a set of comboboxes based on other combobox inputs. Since several comboboxes will be selected I only want the first combobox to return a recordset from the database based on code below then further comboboxes to use the already returned data. I only want to connect over the network once only

The code so far is

Function Access_Data(wSQL As String) As Variant()

     'Requires reference to Microsoft ActiveX Data Objects xx Library  [msado15.dll is v2.8]
    'Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll"
    
    Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
    Dim MyConn, sSQL As String
    Dim vaData As Variant
    Dim vaData_transposed(), test As Variant
    Dim vaData_rowcount, vaData_columncount As Long
     'Set source
    MyConn = "\\sr2iw03\ConnectDocuments\xxxxxxxx\Documents\Delivery\xxx\TheProjectManagementFramework\Templates\Editable\CAF 1.2 Data.mdb"
    
    
     'Create query
    sSQL = "SELECT ......... "
    
     fullSQL = sSQL & wSQL
     'Debug.Print (fullSQL)
     'Create RecordSet
    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        Set Rs = .Execute(fullSQL)
    End With
    
    If Not Rs.BOF And Not Rs.EOF Then
    
        'write recordset to array VaData
        vaData = Rs.GetRows()
        vaData_columncount = UBound(vaData)
        vaData_rowcount = UBound(vaData, 2)
        
        test = TransposeArray(vaData, vaData_transposed)
        Access_Data = vaData_transposed
        If test = True Then
        vaData_transposed_columncount = UBound(vaData)
        vaData_transposed_rowcount = UBound(vaData, 2)
        
        End If
         
    Else
    MsgBox ("No Records to Export")
    End
    End If
    Set Cn = Nothing
    
End Function

If I made "Rs As ADODB.Recordset" a public recordset what is the correct VBA syntax for then using this as a master table to run further SQL statements to filter and sort data into smaller arrays?

Is there a better way perhaps WITHOUT connecting over the network every time?

 

[SkipVought]

Hi,

Have a control or event that opens the connection and one closes the connection.

Between those two events, open/close as many recordsets as you like. In other words, take this out of your function...

    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        Set Rs = .Execute(fullSQL)
    End With
'....
'....
'....
    Cn.close
    Set Cn = Nothing

Either pass the cn object to the function or declare as a globel variable.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Here's something to keep in mind... you say you only want to connect over the network once. Well, you'd have to open the connection and close the connection in another procedure then, for sure...

This line:
Set Cn = Nothing

Closes your connection to the data source. So each time you change the combo boxes, and re-run the query, you're running a new connection.

So maybe you could have one Function built to connect to the data source, one to run your query, and then a third to close it... or actually, a Sub Procedure to open it, Function to run it, and Sub Procedure to close it....

Public MyConn As String
Public sSQL As String
Public [green]'whatever else[/green]
...

Sub ConnectMe()
  [green]'Move connect code here[/green]

End Sub

Sub ConnectClose()
  [green]'Move connection closing/cleanup code here[/green]

End Sub

Function Access_Data(wSQL As String) As Variant()
  [green]'Rest of code here...
  'And in this code, you'll need to reset the connection, I imagine, or refresh.. so you'll need to set the source for the connection to an udpdated SQL string based on the value changes of the combo boxes... then refresh the connection... which, not sure, but I'd wonder how closely related that will be to basically recreating the connection - connecting over the network more than once..[/green]
End Function

 

[kjv1611]

Hey, looks like I wasn't too far off.... just shoulda refreshed the page before posting!

 

[osx99]

Thanks both for the advice which I may end up having to use.

Perhaps I didn't explain properly or perhaps you are proposing the optimal solution anyway.....?

I have 14 comboboxes. The above code runs only when the first combobox is selected to grab a set of data based on an SQL query of an access database.

All further combobox selections will always be a subset of the first recordset fetched on selection of the first combobox.

Since I already have all of the data required for further selections either in the first recordset (Rs) or tranferred into an array (Access_Data), is there a way to then run further SQL queries on this recordset/array instead of connecting each time?

I thought this would improve performance as further queries would be quicker run from memory rather than over the network?

 

[kjv1611]

Here's the way you need to build your SQL for whatever purpose you have, using multiple controls:

1. Put the SQL building code in a Standard Module
2. In your Form Module, in each Combo Box's after update event, or on a button click, or however you want to pull it, put the code to CALL the SQL building code.

So, it'd be something like this:

[green]'************************************************
'Code in a standard module[/green]
Sub BuildSQL()
[green]'In here, you put your SQL building code[/green]
End Sub
[green]'OR You could build it as a Function, and then return the Function as a SQL string...[/green]
Function SQLBuild() As String
[green]'In here, you put your SQL building code[/green]
[green]'At end of code...[/green]At end of code...
   SQLBuild = strSQL

[green]'************************************************
'In the Form Module:[/green]
ComboBox1_AfterUpdate()
  BuildSQL [green]'if used Sub Procedure method[/green]
  Call SQLBuild [green]'if used function method... or more accurate:[/green]
  Form.Recordset = SQLBuild [green]'or something like that[/green]

ComboBox2_AfterUpdate()
  BuildSQL [green]'if used Sub Procedure method[/green]
  Call SQLBuild [green]'if used function method... or more accurate:[/green]
  Form.Recordset = SQLBuild [green]'or something like that[/green]

ComboBox3_AfterUpdate()
  BuildSQL [green]'if used Sub Procedure method[/green]
  Call SQLBuild [green]'if used function method... or more accurate:[/green]
  Form.Recordset = SQLBuild [green]'or something like that[/green]

ComboBox4_AfterUpdate()
  BuildSQL [green]'if used Sub Procedure method[/green]
  Call SQLBuild [green]'if used function method... or more accurate:[/green]
  Form.Recordset = SQLBuild [green]'or something like that[/green]

[green]'And so on...[/green]