How to get data from Access database without closing accdb ?

[hvsummer]

Ok, this is my next step to learn, I want to ask some information about how to get all data fastest-medthod from Access to excel without having to close it ?

currently I use built-in function "Import from Access" with connection string (given automatically by excel)

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=D:\data WS\WS admin.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

but this method force me to close access when I have to press "Refresh all" to refresh all data on all table connections.

are there anyother method that let's me pull all data (refresh or whatever) without closing access :-?

edit: I found out I can change "Mode=Share Deny None" to fullfill my need.
sothat I want to ask 1 more question
if we use VBA code to pull data from Access, will it be faster than built-in connections ?

[zmbd]

As you have discovered
>> Mode=Share Deny None (Related thread)
is the solution to having to close the Access file.

Unfortunately, answering the question as to which is faster... "both/and/or"
That is to say, it all depends on what your dataset looks like and it really doesn't matter if you link to the Access backend via VBA or Linked table.

[zmbd]

Just a thought, MS Query.
This might be a slightly better method....

https://bytes.com/topic/excel/answers/964602-linking-tables-make-pivot-table-excel-2010-a

The connection string would be a tad different... something along these lines (caution air code):

ODBC;DSN=MS Access Database;DBQ=(path to file);
DefaultDir=(Path to paremt);
DriverId=25;
FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

and, of course, there would be a different SQL for the recordset.

This shouldn't hold the access file hostage and should give the same ability to pull the data...

[hvsummer]

@zmbd: ok I got it,

I've tho that ADO or DAO get recordset then push everything into an 2 dimension array next loop thro them and decorate the result in worksheet
<--- can be faster. But i think that could be bad idea :D

[zmbd]

Unless you are working with a small record set memory may be a limitation.
Within Access you have the Recordset.GetRo ws Method (DAO) that will read the records to a 2D-Array. I've not personally found a good use for this; however, there is an example in the link

The downside is returning the data to the recordset if needed... Bytes>how to transfer a 2d array into a table however, as Stewart Ross points out in his post, Excel has a way to do move the data from an array to a the ranges which may be applicable.

If you will search Bytes, there are a number of threads dealing with moving a recordset to an excel workbook and then formatting the results.

I have worked on one or two that inserted a worksheet, copied the data to the worksheet, updated the range-names to point at the new worksheet, deleted the placeholder, and hid the worksheet with the data. I'll have to dig it out, if I still have it and if the file will open in Acc2013!

-z

[hvsummer]

in fact, If we have enough time, we can code everything for each cells (value, formula, formating) in workbook.

transfer array into table, I think we can use

.resize = application.transpose(array)

that will be the fastest way

[hvsummer]

Aw, I tho that Mode=Share Deny None could help me out, but when run vba code in access, it appear the dialog "the database has been placed in state by "user" on abc that prevent it from open or lock" ==

Trying to find the solution 3hrs ago, but nowhere could help ==

[zmbd]

Ok, most likely cause is that you are instancing the Excel from the same database that you are pulling the data from, the database has been opened in exclusive mode.
Ribbon>File>Opt ions>>Client Settings>>Advan ced>> Default Open Mode

See what that is set to. I have mine set to Shared by default as I'm always in and out of things.

There is some old code (( ACC: How to Determine If a Database Is Open Exclusively )) that one can use to determine if the file is opened as exclusive.

The method I use is:
<ctrl><g>

?(currentdb.Containers!forms.allpermissions and dbsecfullaccess) = dbsecfullaccess

If returns "True" then it should be in a shared state.

IN the production databases, I split the front from the back and then make my calls/links to the backend as needed; thus, sharing the data portion of the database. This may also be a solution for you.

[hvsummer]

@zmbd:
I alway set the open mode to shared altho only me using database ==
but I findout what happen.

first thing I've open the attachment dialog in attachment field when I debug another code inside vba to test why the ADO.recordset2 return "object does not support this method".

I tho that db was placed in state. but I wrong, I've tricked by microsoft funny system ==

I rewrote the code for ADO.recordset2 and everything work fine.

next time I'll remember not to open any dialog while debug another error == it'll lead to big missurender...