Access to Excel data connections

Question

• All my tables in Excel use a OLEDB connection to an MS Access database
• Queries used are simple not complex
• MS Access database and Excel files are stored in a server
• Multiple users use the Excel files and will need to refresh tables

My worry is that all users may need to have MS Access installed on their PCs in order to refresh the tables. Do any of the connections (OLEDB, ODBC, etc.) resolve this issue?

Answer 1

The JET/ACE engine that underlies Access databases is a Windows component and comes installed on every PC. So you could create applications that use accdb and mdb files as data sources and use the corresponding installed OLEDB Providers or ODBC DSNs.

With that said, Excel is not a multiple user program. Hence, other users will get read-only status, unable to modify the spreadsheet.

One option you can explore is to have users download the free Access Runtime available in 2007/2010/2013 versions. Then, build an Access accdb file with user navigated forms that replicate the Excel table layout which really is just opening queries to screen. So really no need for Excel unless you run formulas, graphs, charts, and other analytics.

Answer 2

If you create your db, then publish it as an ACCDE file it saves as a compiled executable file, so no one will need Access to open it.

Split your database before making the ACCDE. that way you will have all your tables in one spot and you can adjust them as needed.