none
Help, slow VBA OLED connection string query... RRS feed

  • Question

  • Hi all,

    Purpose: To determine if a city and state combination exists in an existing excel workbook.

    Using: VBA with an OLED connection so I don't have to open the workbook. 

    Code:

    Dim cn As New ADODB.Connection
    Dim srcData, cmd As String
    Dim cityADO As Boolean
    
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & srcData _
         & "Extended Properties=""Excel 12.0;"""
        .Open
    End With
    
    cmd = "SELECT COUNT (*) FROM [" & sSrc & "$A:B] WHERE [City]='" & cCty & _
              "' AND [State]='" & cSt & "';"
                
        Set rst = cn.Execute(cmd)
        
        cityADO = CInt(rst.GetString)
    I'm self teaching myself and this is part of a self imposed project. (It's basically checking addresses from a list in an excel sheet. Using Bing or google maps API would be cheating in this case) . So my issue is this method is slow and I was wondering if there was a faster way meeting my objective or if there is something wrong in my code that could be causing a bottleneck.

    Monday, February 9, 2015 9:40 PM

Answers

  • Hi DaBirdGUY,

    I made a simple demo with your code, and I did not see the obvious performance issue from the query. Based on my understanding, Excel is not a file format designed for high performance query, so it could be slow when the row count is large. A common way to improve the performance is load the data to memory and query in memory.

    In addition, if the row count is very large, I will recommend you use SQL Server to query rather than Excel spreadsheet.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, February 11, 2015 5:31 AM