Macro not able to connect to MS Access DB

johnaubreymoore

New Member
Joined
Nov 13, 2018
Messages
1
Hello All,

I'm new to this forum so I hope I am posting this in the right local. I'm at my wits' end with a macro designed to pull from Access.

I'm receiving Run-Time Error '1004': Application defined or object-defined error

The debugger selects ".Refresh BackgroundQuery:=False" at the bottom of this chunk of code.


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=X:\GBS_FO_AR_ARS_ReceivablesReporting\2_Queries\Aging Da" _
, _
"tabase 2.0_Coree6.accdb;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _
, _
"Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Glo" _
, _
"bal Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=Fal" _
, _
"se;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Sup" _
, _
"port Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceFie" _
, "ld Validation=False"), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("qry_Unapplied")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"X:\GBS_FO_AR_ARS_ReceivablesReporting\2_Queries\Aging Database 2.0_Coree6.accdb"
.ListObject.DisplayName = "Table_Aging_Database_2.0_Coree6.accdb"
.Refresh BackgroundQuery:=False


I am relatively inexperienced when it comes to VB so any help would be greatly appreciated. Thank you!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Curious that the query def has

Code:
.CommandType = xlCmdTable

then
Code:
.CommandText = Array("qry_Unapplied")

Which doesn't look like a table. But that could be just how it rolls (I haven't used code like this in a little while).

I think you might want to just rebuild the query without writing it by hand (do it in the Excel UI). You shouldn't necessarily need a refresh macro since you can refresh from the menu (ribbon) in Excel (without any VBA).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top