Using SQL in VBA to Join Data from two Excel Workbooks (Read-Only error)

Question

I am seeking to join specific data from two very large excel workbooks, then save this to another workbook. In order to do this, I was using ADODB Objects to use SQL commands, which make performing the join much easier than doing hundreds of thousands of VLookups. My setup prompts the name of a state, and the data source is formatted to depend this state. Let's say you entered "Nevada". It would connect to a file named "Nevada - biological metadata multiple cont" and join this to another file named "Nevada - Site data only multiple cont" and select certain fields from this.

When I am debugging, I can see that the connection is working just fine, and where I change the string variable 'sql' to equal "SELECT * FROM [biologicalresult$]", it will work correctly. The problem seems to be tied to the fact that I am joining it to another workbook through ADODB queries, and I get the error: "Cannot update. Database or object is read-only"

Option Explicit

Sub RunSELECT()

    On Error GoTo ErrorHandling

    Dim cn As Object, rs As Object, output As String, sql As String, state As String
    state = InputBox("Please insert the state name with the first letter being capital")

    Set cn = CreateObject("ADODB.Connection")

    '---Connecting to the Data Source---
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .connectionstring = "Data Source=T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\State Data\" & state & _
                            " - biological metadata multiple cont;Extended Properties=""Excel 12.0 Xml;HDR=YES;Readonly = false;IMEX = 0"";"
        .Open
    End With

    '---Run the SQL SELECT Query---
    sql = "SELECT b.OrganizationIdentifier, b.OrganizationFormalName, b.MonitoringLocationIdentifier, s.LatitudeMeasure, s.LongitudeMeasure, " & _
        "b.ActivityIdentifier , b.ActivityTypeCode, b.ActivityMediaName, b.ActivityMediaSubdivisionName, " & _
        "b.ActivityStartDate, b.ProjectIdentifier, b.CharacteristicName, b.ResultSampleFractionText, b.ResultMeasureValue, b.[ResultMeasure/MeasureUnitCode] " & _
        "FROM [biologicalresult$] b INNER JOIN (SELECT * FROM [Excel 12.0 Xml; IMEX = 0; HDR = Yes; Database = T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\" & _
        "State Data\" & state & " - Site data only multiple cont;Readonly = False].[station$]) s " & _
        "ON b.MonitoringLocationIdentifier = s.MonitoringLocationIdentifier;"

    Set rs = cn.Execute(sql)

    '---Clean up---
    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing

Exit Sub

ErrorHandling:
    MsgBox ("Source: " & Err.Source & vbNewLine & "Number: " & Err.Number & vbNewLine & "Description: " & Err.Description & vbNewLine & "Help Context: " & Err.HelpContext)
done:
    End Sub

Thank you for any help!

Parfait · Accepted Answer · 2019-08-22 16:20:54Z

Admittedly, the error is not too informative as issue may be due to file path issue. However, this simple typographic fix may be helpful for future readers who make same subtle mistake. By the way, for long names, consider separate variables and concatenate into connection and SQL strings.

Include full path including extension, .xlsx or .xlsm in source file.

meta_file = "T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\State Data\" _
             & state & " - biological metadata multiple cont.xlsx"

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .connectionstring = "Data Source=" & meta_file & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;Readonly=false;IMEX=0"";"
    .Open
End With

Remove space before and after the Database argument inside the bracket namespace: ...Database=T:\Marketing\.... Otherwise, engine interprets space as part of name. Even consider removing spaces between all arguments IMEX, HDR, Readonly, etc.

site_file = "T:\Marketing\Data Analytics\GIS Data\Water Quality Portal Data\State Data\" _
             & state & " - Site data only multiple cont.xlsx"

'---Run the SQL SELECT Query---
sql = "SELECT b.OrganizationIdentifier, b.OrganizationFormalName, " & _
      "       b.MonitoringLocationIdentifier, s.LatitudeMeasure, " & _ 
      "       s.LongitudeMeasure, b.ActivityIdentifier, b.ActivityTypeCode, " & _ 
      "       b.ActivityMediaName, b.ActivityMediaSubdivisionName, " & _
      "       b.ActivityStartDate, b.ProjectIdentifier, b.CharacteristicName, " &  _
      "       b.ResultSampleFractionText, b.ResultMeasureValue, " & _
      "       b.[ResultMeasure/MeasureUnitCode] " & _
      "FROM [biologicalresult$] b " & _
      "INNER JOIN (SELECT * FROM [Excel 12.0 Xml;IMEX=0;HDR=Yes;" & _
      "                           Database=" & site_file & ";Readonly=False].[station$]) s " & _
      "ON b.MonitoringLocationIdentifier = s.MonitoringLocationIdentifier;"

Collectives™ on Stack Overflow

Using SQL in VBA to Join Data from two Excel Workbooks (Read-Only error)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related