Doug,
The first thing you might try is to change the Mode that Excel uses to openthe connection. I think that it defaults to "Mode=Share Deny Write", which will prevent users from writing to the Access file while Excel is open.If you go to the Connection properties and change this setting to "Mode=Share Deny None" it should allow changes while Excel is open.
If you would still prefer to drop the link, then your code will need to recreate the connection each time you run it, since unlinking will remove the query settings from the table. The code below is what I use to do something similar. Note that you may have to record a macro where you add the connection in order to find the appropriate settings for items such as the connection string or command text.
Hope this helps,
Ben
Sub GetAccessData()
Application.ScreenUpdating = False
Dim sDBName As String 'Database file location
Dim wsDest As Worksheet 'Destination Worksheet
Dim rDest As Range 'Destination Range
Dim sCommand As String 'Table/Query Name
Dim sDisplay As String 'Display name for table
'Assign variables
sDBName = "C:\Desktop\My Database.accdb"
Set wsDest = ActiveSheet
Set rDest = wsDest.Range("$A$6")
sCommand = "My Table"
sDisplay = "My_Table"
'Get data from Access table
Application.DisplayAlerts = False
On Error Resume Next
wsDest.ListObjects(sDisplay).Delete 'Clears existing table
On Error GoTo 0
With wsDest.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & _
sDBName & ";Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";Jet OLEDB
atabase Password="""";Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB
atabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" & _
"Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" & _
"Jet OLEDB
on't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False", Destination:=rDest).QueryTable
.CommandType = xlCmdTable
.CommandText = Array(sCommand)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = sDBName
.ListObject.DisplayName = sDisplay
.Refresh BackgroundQuery:=False
End With
'Remove link to Access and apply an autofilter to the range
Set rDest = wsDest.Range(sDisplay & "[#All]")
wsDest.ListObjects(sDisplay).Unlink
rDest.AutoFilter
Set rDest = Nothing
Set wsDest = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
