Link, drop link, relink

[dblacker333]

Hi,
I have a connection to an Access 2010 query that is linked to an Excel 2010table. I would like to use VBA to establish the link to the Access query, refresh the Excel table, then drop the link. This way Excel users have current data (updated monthly) and I don't have to be concerned that they mightbe connected to the database while the monthly updates to Access are in progress.

I could import but the Access database is in a remote location so it would be slower. Thanks for any ideas.

Doug

 

[Ben McClave]

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 OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;" & _
"Jet OLEDBatabase 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 OLEDBon'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

 

[dblacker333]

Hi,

I have a connection to an Access 2010 query that is linked to an Excel 2010 table. I would like to use VBA to establish the link to the Access query, refresh the Excel table, then drop the link. This way Excel users have current data (updated monthly) and I don't have to be concerned that they might be connected to the database while the monthly updates to Access are in progress.



I could import but the Access database is in a remote location so it would be slower. Thanks for any ideas.



Doug

Thanks, Ben. Just what I was looking for!