Hello,
I have the below code where I have data in my 'Mastersheet' tab that is linked to the access database. I need the code to open this excel spreadsheet, close the database and update the data on the 'Mastersheet' tab that is linked to the access database that was closed. I have it all working, but the issue I am having is that once the database closes and data is being refreshed, I get a pop-up message to connect to the database "Do you want to connect to ...?". What am I missing in my code to go through the pop-up messages to connect to the database? IE. I want the code to automatically connect to the database by clicking Yes to the first message and then clicking OK to the Data Link Properties pop-up and then click OK again when the "Please enter Microsoft Access Database Engine OLE DB Initialization Information".
I have the below code where I have data in my 'Mastersheet' tab that is linked to the access database. I need the code to open this excel spreadsheet, close the database and update the data on the 'Mastersheet' tab that is linked to the access database that was closed. I have it all working, but the issue I am having is that once the database closes and data is being refreshed, I get a pop-up message to connect to the database "Do you want to connect to ...?". What am I missing in my code to go through the pop-up messages to connect to the database? IE. I want the code to automatically connect to the database by clicking Yes to the first message and then clicking OK to the Data Link Properties pop-up and then click OK again when the "Please enter Microsoft Access Database Engine OLE DB Initialization Information".
Code:
Sub RefreshAll()
Dim objAccess As Object
Dim objExcel As Object
Dim pt As PivotTable
Dim conn As New Connection
Dim rs As New Recordset
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=i:\CAP_Profile\Desktop\Projects\QA Database\QA_Database 1.0.accdb;" & _
"User Id=admin;Password="
conn.Open (strcon)
qry = "SELECT * FROM QAMaster"
rs.Open qry, conn, adOpenKeyset
rs.Close
conn.Close
On Error Resume Next
Set objAccess = GetObject(, "Access.Application")
Set objExcel = GetObject(, "Excel.Application")
Set pt = Worksheets("Sheet2").PivotTables("PivotTable1")
If Err.Number = 0 Then
'objAccess.CloseCurrentDatabase 'to close open database
objAccess.Quit 'to close access application. you dont need previous line if you use this
Else
Err.Clear
End If
objExcel.Visible = True
objExcel.Workbooks("QA Findings.xlsm").Sheets("Mastersheet").Range("Table_QA_Database_1.0.accdb").ListObject.QueryTable.Refresh BackgroundQuery:=False
pt.PivotCache.Refresh
End Sub