Best data link provider to import and link access table into excel

[Sketchin]

I have an access 2010 database that is split into FE and BE. In Excel 2010, I would like to link to a query that resides in the BE of my database.

What is the "best" OLE DB provider?

12.0 Access Database Engine OLE DB Provider?
15.0 Access Database Engine OLE DB Provider?

Does it really matter? What are the key differences?

All I am doing with the linked data is viewing it. There will be no data entry on the Excel side.

Thanks

 

[Rx_]

Are you planning to:
import it with a wizard?
Write VBA Code to find the excel file at a some location and import it over and over
Use a Linked Table to permanently link to one Excel Worksheet - the work book / worksheet can be updated or replaced so long as the location and names remain the same.

Answer this and you will have many options narrowed down to a few

 

[Sketchin]

I am importing it with a wizard.

The excel worksheet will be permanently linked as a table and will reside in the same location always. The back end database is always in the same place as well.

No VBA code involved (unless that would be the best way)

 

[Rx_]

Thanks!
Use the Linked Table Manager and choose Excel as the Data Source.
http://www.informit.com/articles/article.aspx?p=102281&seqNum=3
There are plenty of step-by-step instructions, this one is not better or worse, I just googled it as an example.

Be aware that an Excel worksheet can be updateable too. It is up to you to choose the formatting to work with updated data. Computers are stupid. Of course the day they are not stupid anymore, they will read what I said here and probably get "show me"?
It is just that a badly designed Excel worksheet linked to Access won't solve your problems.
It is recommended that in Excel, each column data type be set (e.g Date, Number, ...)
It will work better that way.

 

[Sketchin]

Sorry, I might have confused you. The data source is the database. I will be viewing the data in Excel.

 

[Rx_]

LOL, yes you did confuse me!
Well then, we have many other options to discuss.
You can create a query directly in Access and save it there. If the database is set as an ODBC connection In Excel you can choose DATA> Import External Data> New Database Query> select the database from the list and then the query you saved.

OR, I typically create a VBA module that will read a query, it will then create a new instance of Excel, then copy the data from Access tables (or view) to populate the Excel Workbook, then use code to Save As the workbook and close the Excel application.
After this works, set Excel visible to False so it just looks automatic.
Don't be afraid of the Code - we have several examples at this site.

OR
It is possible to set an Excel Worksheet Range to be linked to an Access Query. This can only be used internally on the company network (or workstation). If the Excel is emailed - it will just display broken links.

There are other choices too! What options were you thinking about?

 

[Sketchin]

So I have created query's in Access already.

The spreadsheet will only be viewed when on our network, or through a VPN.

In the past I have used OLEDB connections, but only because I don't have a solid background in this sort of thing. An example connection string I was typically using is as follows:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=S:\Programs\WD Geomatics Lab\Database Developement\Programs Database\Database Release 7.5 Back End.accdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;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;Jet OLEDB:Bypass UserInfo Validation=False


So I guess the clarifications that I need are as follows:

1. Is it better to use Microsoft.ACE.OLEDB.12.0 or Microsoft.ACE.OLEDB.15.0? Does it really matter?

2. Should I use Mode=Read or Mode= Share Deny None? The spreadsheet will not be used for data entry. Multiple users could be viewing the database and spreadsheet at the same time.

3. Is ODBC "Better" than "OLEDB"?

4. Am I worrying too much about all of this???

 

[Rx_]

If you haven't had a good drink lately, then you are worrying too much!
It probably doesn't make any difference.
As a purely personal preference, ODBC comes in many great forms. Eg. ODBC, SQL Server Native Client, ...
There is probably more code for ODBC (generally speaking).
In this case, a Read-Only will probably do.

I have a few post related to this. Here is one of them:
http://www.access-programmers.co.uk/forums/showthread.php?t=233117&highlight=harvest

Noticed that one of the msaccess file upload example codes was opened almost 350 times - ... and not a single Thanks. Evidently that is not as good rating on my part as desired! LOL

In this example, a huge amount of Excel files were searched, verified, opened, data harvested, then stick an X into a cell (put a feather in its hat) to show it was harvested (e.g. Serviced).

 

[Sketchin]

Thank you so much for helping me with this. I was always curious if I was doing this the right way, but you have put my mind at ease.

Cheers