Hi all,
Hoping you could help me with the following.
I have an Excel spreadsheet that draws in queries from an Access database using OLEDB.
There are many queries, so rather than refresh them manually I built a macro to do so.
I recorded myself manually refreshing one query and came up with the following code which I copy/pasted 60 or so times, changing the cell reference so it would update each different query:
Range("A4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
However when it gets towards the 55th query or so it gives me the error message "2147217848 (80040e4e)" and calls it an "unspecified error".
Some explanation and help would be hugely appreciated. I can't understand why it would work when I refresh it manually but not via a macro. And sometimes a query that refreshes fine in one run of the macro won't refresh properly the next time.
The connection string is as follows:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Toshiba\Desktop\Wholesale inputs database - Scenario 1.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database 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:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False.
Thanks!
Paul
Hoping you could help me with the following.
I have an Excel spreadsheet that draws in queries from an Access database using OLEDB.
There are many queries, so rather than refresh them manually I built a macro to do so.
I recorded myself manually refreshing one query and came up with the following code which I copy/pasted 60 or so times, changing the cell reference so it would update each different query:
Range("A4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
However when it gets towards the 55th query or so it gives me the error message "2147217848 (80040e4e)" and calls it an "unspecified error".
Some explanation and help would be hugely appreciated. I can't understand why it would work when I refresh it manually but not via a macro. And sometimes a query that refreshes fine in one run of the macro won't refresh properly the next time.
The connection string is as follows:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Toshiba\Desktop\Wholesale inputs database - Scenario 1.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database 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:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False.
Thanks!
Paul
