Edit Access table while it is linked to an open Excel file - Is this possible?

[Harry Flashman]

I have an Excel file with a table that contains data imported from a query in Access. I have two questions.

1. I would like to be able to edit the data in Access while the Excel file is open. Presently if I open the Access DB while the Excel file is also open I get the following message: "this database has been opened read-only". But sometimes I would like to edit the data in Access, and then simply refresh the Excel data, without having to first close the Excel file and then open Access and do my editing. Is this possible?

2. Sometime Excel will not allow me to refresh the data. I occasionally get the following message: "The query did not run, or the database table could not be opened." This problem comes and goes. Any suggestions as to what might be causing it?

My first question is the import though, and the other problem only occurs occasionally.

If it helps the Access query, partly involves some tables in Access and some linked tables to SQL Server (I am not sure if that is part of the problem). The first problem occurs even with queries that are not related to SQL Server.

I would greatly appreciate and advice/suggestions. Cheers.

 

[SydneyGeek]

Harry,

If you use MS Query to pull the data from Access you have the benefit of simplicity but the downside is that Excel and Access tend to lock each other up.
Whichever one opens first, it prevents the other one being edited.

Instead, try pulling the data into Excel using code. No permanent connection between the two, so they don't lock each other up any more. I wrote a tutorial several years ago which shows how to go about it -- see this link.

Denis

 

[Harry Flashman]

Harry,

If you use MS Query to pull the data from Access you have the benefit of simplicity but the downside is that Excel and Access tend to lock each other up.
Whichever one opens first, it prevents the other one being edited.

Instead, try pulling the data into Excel using code. No permanent connection between the two, so they don't lock each other up any more. I wrote a tutorial several years ago which shows how to go about it -- see this link.

Denis

Thanks SydneyGeek. You have been a big help. I haven't had a chance to do your full tutorial yet but I took a look at some of your code with a slight modification. This is what I managed to cobble together.

Sub TestGet()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SelectStatement As String

MyConn = "C:\Users\Harrry\Harry Local\TVI_Database.accdb"
MyConn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & MyConn

' open the connection

Set cnn = New ADODB.Connection
With cnn
    .Open MyConn
End With

' my query is called ExtractsDB
 Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="ExtractsDB", ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
             Options:=adCmdTable
    
    'clear existing data on the sheet
     Set ShDest = Sheets("Table download")
    ShDest.Activate
    Range("A1").CurrentRegion.Offset(1, 0).Clear
    
    'create field headers
    i = 0
    With Range("A1")
        For Each fld In rst.Fields
            .Offset(0, i).Value = fld.Name
            i = i + 1
        Next fld
    End With
     
    'transfer data to Excel
    Range("A2").CopyFromRecordset rst
    
    ' Close the connection
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

End Sub

For some reason this driver did not work for me:

MyConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Harrry\Harry Local\TVI_Database.accdb".

So I used the one listed in my code above. Quite a while ago I had done some experiments sending data from Excel to Access with VBA and I recall I had to specially download a particular driver for Excel 2010 64 bit (and modify the code I had found in Excel text books accordingly).

Anyway your solution works for me, and I look forward to studying your tutorial closely. Cheers.

 

[Harry Flashman]

Building from the above, now that I can successfully write from Access to Excel, I would like to be able to do the opposite, and write from Excel back to Access, specifically write back to the Access table that I originally got the data from.

Thus I would like to be able get the data from Access, make modifications in Excel, and then write back to Access. I was thinking that I would just completely write over the existing table (something like that)


I have previously experimented with something similar following the advice given in Bill Jelen's book "VBA and Macros for Excel 2010 Chapter 21" and added records to a table, but I have not edited existing values.
The trouble with the code in Mr Excel's book is that because I am using Excel 2010 64 bit I can't use this driver: "Microsoft.Jet.OLEDB.4.0"

Instead I had to install something called the Microsoft Access Database Engine 2010, and modify the code slightly (as Illustrated in the code in the post above this one)

Thus instead of this:

MyConn = "C:\Users\Harry\Harry Local\Access to Excel To Access.accdb"
Set cnn = New ADODB.Connection
With cnn
    '.Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open MyConn
   End With

I have this:

MyConn = "C:\Users\Harry\Harry Local\Access to Excel To Access.accdb"
MyConn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & MyConn

Set cnn = New ADODB.Connection
With cnn
    .Open MyConn
End With

I am now experimenting with SydneyGeek's code from his tutorial but I am not having any luck:

I tried several variations on the code below (including different drivers but they all fail)

The code below results in the following error:

Run-time error '-214727900 (80040e14)':

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause

Sub AlterOneRecord()
Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset
   Dim fld As ADODB.Field
   Dim MyConn
   Dim lngRow As Long
   Dim lngID As Long
   Dim j As Long
   Dim sSQL As String
   
   'determine the ID of the current record and define the SQL statement
   
   lngRow = ActiveCell.Row
   lngID = Cells(lngRow, 1).Value
   
   Set cnn = New ADODB.Connection
   MyConn = "C:\Users\daviesst\Precis Local\Access to Excel To Access.accdb"
   MyConn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & MyConn
       
   sSQL = "SELECT * FROM MyTable Where ID =" & lngID

   With cnn
    '.Provider = "Microsoft.ACE.OLEDB.12.0"
     .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenDynamic, _
            LockType:=adLockOptimistic, Options:=adCmdTable

   'Load contents of modified record from Excel to Access.
   'do not load the ID again.
   For j = 2 To 7
      rst(Cells(1, j).Value) = Cells(lngRow, j).Value
   Next j
   rst.Update

   ' Close the connection
   rst.Close
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing

End Sub

I tried changing the driver to this: "Microsoft.ACE.OLEDB.12.0"

But that also results in a "Syntax error FROM clause"

Where am I going wrong? Does anyone know what I mean about drivers for 64 bit (it means the examples I have found in various books don't work as written - and I can't really do SydneyGeek's tutorial as written because Jet doesn't seem to work).
Any suggestions appreciated.

 

[Harry Flashman]

Well I have made some progress, after much experimenting I have found this works:

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn
   End With

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseServer
   rst.Open Source:=MyString, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenKeyset, _
            LockType:=adLockOptimistic

I removed this bit: Options:=adCmdTable

And it now works! Thanks to SydneyGeek's code (with just a little modification).