Updating Exel From Access (1 Viewer)

[kirkm]

Private Sub Command202_Click()
    On Error GoTo Err_Command202_Click
    
    Dim ExcelPath As String, sConnect As String, sql As String
    Dim Conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
     'browse to xls
    ExcelPath = BrowseFile("Excel Workbook to Populate from This Disk", "xls")
    
    'open connection to xls file
    sConnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ExcelPath & ";HDR=Yes';"
    Conn.Open sConnect
    
    sql = "Select [File Name], Title, Album, Year, Comment, [Track #], Composer from [Sheet1$]"
    rs.Open sql, Conn, adOpenStatic, adLockOptimistic
    Debug.Print rs.RecordCount
    rs.MoveFirst
    Do
        Debug.Print rs(0)
        rs(3) = "test"
        rs.Update
        
        rs.MoveNext
    Loop Until rs.EOF
    rs.Close
    Conn.Close

Exit_Command202_Click:
        Exit Sub
    
Err_Command202_Click:
        Debug.Print Err.Description
        Resume Exit_Command202_Click
    
End Sub

The error I cannot understand, it is

[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '(File Name=Pa_RaM001 AND Title=Pa_RaM002 AND Album=Pa_RaM003 AND Year=Pa_RaM004 AND Comment IS NULL AND Track #=Pa_RaM005 AND Composer IS NULL )'.

This isn't my sql and it does work ok for reading, but errors trying to update. Any help appreciated.

 

[arnelgp]

You cant update using that method. The resulting recordset is not updatable. You must open it sing excel automation and set the cell value there.

 

[kirkm]

Ah, thank you. Do you have a link to an example please?
This is a new concept for me.

 

[isladogs]

For info, it used to be possible to update linked Excel files direct from Access but the functionality was removed almost 20 years ago as it seems someone took out a patent preventing its use.

If you do a site search for excel automation you will find examples to fit your purposes

 

[arnelgp]

firstly, you must add a Reference to
Microsoft Excel XX.X Object Library on
VBE (Tools->Reference). XX.X is the version
of Excel installed on your system.
next instanciate Excel Objects.

...
...
ExcelPath = BrowseFile("Excel Workboot to Populate from this disk")
If ExcelPath <> "" Then
Dim xlApp As Excel.Application
Dim xlWB As Excel.WorkBook
Dim xlSh As Excel.WorkSheet
Dim iRow As Integer, iColumn As Integer
Dim iLastRow As Integer
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(ExcelPath)
Set xlSh = xlWB.Sheets(1)
iLastRow = xlSh.UsedRange.Rows.Count
iRow = 2
iColumn = 4
While While iRow <= iLastRow
xlSh.Cells(iRow, iColumn).Value = "Testing
iRow = iRow + 1
Wend
xlWB.Close True
xlApp.Quit
Set xlApp = Nothing
End If

 

[kirkm]

That's weird Colin, a patent to prevent it's use?
Anyway thanks Arnelgp, tooke me a while but I got there. I stepped through the code and found it opened another Excel somehow but no Close button, but then it closed itself (good). It wouldn't over write as "Opened for Read only" Any way around that ? Otherwise I'll manually delete and rename etc.

Very nifty little routine
Thanks again.

 

[isladogs]

There was some sort of legal dispute which resulted in the functionality being removed. For more info on this and workarounds, see
https://support.microsoft.com/en-gb/help/904953/you-cannot-change--add--or-delete-data-in-tables-that-are-linked-to-an

Does the code behave itself if Excel isn't already open?

 

[kirkm]

It doesn't seem to matter whether Excel is open, or not. Either way works.

I've been playing around with the code in your link e.g.

Dim strSQL, SQL As String
    Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection
    Db.CursorLocation = adUseClient
    Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=<AccessPath>"
    'Note: In Office Access 2007, use the following line of code:
    'Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=<AccessPath>"

But not getting very far.. changing <AccessPath> in line Db.Open... to application.currentproject.path (and I can't see what other path it would want) results in

The Microsoft Office Access database engine cannot open or write to the file 'E:\ExCelTest'. It is already opened exclusively by another user, or you need permission to view and write its data.

Any idea how to get around this ?

 

[kirkm]

I got around the error by using a copy of the mdb, instead of the one with the code (how crazy is that ?!) But it was no better than anelgp's code and still wanted to create a new xls and was not able to overwrite the existing one.
It's a bit of a mess compared to .Update if that only worked. Suppose it does in Office > 2007 ?