Excel->Access ADODB Improvement Suggestions

[mojof1]

I use Excel 2003 SP3 and I currently have a macro in Excel that runs an access query and writes the records into Excel, which works perfectly fine.

However, as you can see in the codes below, the sql query in embedded into the sub as strings. I can't help but feel that this is a very amateurish way (correct me if I'm wrong), since I would have to write it in access first to make sure it works, and port it over to excel.

I would appreciate any suggestions for improvements in any parts of my code, as well as whether there is a way to run an access query directly from access without porting it over to excel.

thank you for your time.

Public Sub gef_query()

    Dim connectionString As String
    Dim connection As ADODB.connection
    Dim rst As ADODB.Recordset
    Dim introw As Integer
    
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Range("DBPath") & ";" & _
        "Persist Security Info=False;"
    Set connection = New ADODB.connection
    connection.Open connectionString
    Set rst = New ADODB.Recordset

    'switch to set cumulative data from selected date to current date
    If (Range("date_switch") = "True") Then
        'selected date to current date
        eq = ">="
    Else
        'selected date only
        eq = "="
    End If

    introw = 4
    With rst
        .ActiveConnection = connection
        
        'SQL
        connectionString = "SELECT GCX.run_date AS [Run Date], ISS.issuer_name AS [Issuer Name], ISS.country_res " & _
        "AS [Country of Domicile], PDT.product_type AS [Product Type], IIF(GEN.description Is Null,Null," & _
        "GEN.description) AS [Product Class], SUP.share_os AS [Shares OS], " & _
        "sum(IIF(GCX.transit In('" & Range("SBE") & "'),GCX.posn_unit,Null)) AS [SBE], " & _
        "sum(IIF(GCX.transit In('" & Range("SIL") & "'),GCX.posn_unit,Null)) AS [SIL], " & _
        "sum(IIF(GCX.transit In('" & Range("BNSL") & "'),GCX.posn_unit,Null)) AS [BNSL], " & _
        "sum(IIF(GCX.transit In(" & Range("SCEL") & "),GCX.posn_unit,Null)) AS [SCEL], " & _
        "sum(IIF(GCX.TRANSIT Not In('95075','38885','00375','01285','21485'),GCX.POSN_UNIT,Null)) AS [Elsewhere] " & _
        "FROM (((((Linked_Tickers AS TIC INNER JOIN sag_issuer AS ISS ON TIC.F1 = ISS.BLOOMBERG_TICKER_ID) " & _
        "INNER JOIN sag_master_security_desc AS MSD ON ISS.ISSUER_ID = MSD.ISSUER_ID) INNER JOIN " & _
        "sag_gc_extract_data AS GCX ON MSD.MSD_ID = GCX.MSD_ID) INNER JOIN sag_product AS PDT ON MSD.PRODUCT_ID = " & _
        "PDT.PRODUCT_ID) INNER JOIN sag_msd_supp AS SUP ON MSD.MSD_ID = SUP.MSD_ID) LEFT JOIN sag_generic_table " & _
        "AS GEN ON SUP.CLASS_ID = GEN.PRODUCT_ID " & _
        "WHERE ((GCX.RUN_DATE)" & eq & " #" & Range("RunDate") & "#) AND ((GEN.STATUS)='A') AND " & _
        "((GEN.TABLE_ID)='share_class') AND ((PDT.PRODUCT_ID) In (1) AND ((SUP.share_os) Is Not Null)) " & _
        "GROUP BY GCX.run_date, ISS.issuer_name, ISS.country_res, PDT.product_type, GEN.description, SUP.share_os;"
       
       rst.Source = connectionString
       
       'open record and write to excel sheet
       .Open
       Do Until .EOF
           introw = introw + 1
            Sheet3.Cells(introw, 1) = .Fields("Run Date")
            Sheet3.Cells(introw, 2) = .Fields("Issuer Name")
            Sheet3.Cells(introw, 3) = .Fields("Country of Domicile")
            Sheet3.Cells(introw, 4) = .Fields("Product Type")
            Sheet3.Cells(introw, 5) = .Fields("Product Class")
            Sheet3.Cells(introw, 6) = .Fields("Shares OS")
            Sheet3.Cells(introw, 7) = .Fields("SBE")
            Sheet3.Cells(introw, 8) = .Fields("SIL")
            Sheet3.Cells(introw, 9) = .Fields("BNSL")
            Sheet3.Cells(introw, 10) = .Fields("SCEL")
            Sheet3.Cells(introw, 11) = .Fields("Elsewhere")
           .MoveNext
       Loop
       .Close
   End With
   connection.Close
   
   'go to summary sheet
   Sheets("GEF Holdings Summary").Select

 

[Hermanito]

you could define the query in Access and change the SQL in Excel to just say something like "Select * From MyCustomQuery".

Personally, with Access, I prefer the method like you do, defining your queries in Excel itself, although I define all SQL strings as constants in a separate module to keep it all together in one place. That way you keep control over it, otherwise someone might go in your Access database and say: hey, that seems like a useful query to me, I just have to make a few changes.... and bam, your Excel application fails... if you can be really sure that will never happen, defining it in Access might be the way to go. Or, in another scenario, you yourself go into your Access database in six months from now... and you change a query because you forgot you need it in an Excel application...

Basically it comes down to preference, when using a 'real' database like SQL Server, I might be using stored procedures, because the chance of someone going in there and messing up a query is a lot less likely than with Access

 

[Norie]

There's nothing wrong with creating the SQL for the queries in the code.

In fact sometimes you have to, looking at your query I'm not sure but this might be one of those times.

There does seem to be a lot going on.

One thing you could do in your code is use CopyFromRecordset to copy the results to the worksheet in one go.

If anything is slowing the code down it could be Looping through the recordset for that.

If you do want to create a query in Access you could just make it one that returns the raw data.

Once you've got that in Excel you can do all the summing, counting, etc.

 

[stapuff]

I use the Access to Excel a lot and in many different ways, however, the one I prefer is actually keeping the sql statement completely seperated from the macro itself. I store the sql statement in a text file and call it to create the query and return the data dynamically then delete the query when completed.

I found this to be the fastest for me and since the query's actually do not exist it prevents user changing tables/query's which end up failing when you run the code as already stated. Another benefit is you can create the query in access and copy the code (that you know works they way you want it) then just paste it in the text file.

My 2 cents

stapuff