0
  • 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?

TigerhawkT3
  • 48,464
  • 6
  • 60
  • 97
Elixir
  • 303
  • 3
  • 9
  • 26

2 Answers2

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.

Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Interesting so non of my users need MS Access installed since the JET/ACE engine is already in place. I'm not concern about the read/write issue. Which connection would be better to use OLEDB or ODBC , my users are only refreshing the tables in excel which are linked to Access? My current database source is a accdb file – Elixir Jun 23 '15 at 18:04
  • 1
    Either one works. OLEDB is a more generalized connection (connects to many data types) than ODBC (only connects to relational databases). See this [SO post](http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources). – Parfait Jun 23 '15 at 21:01
  • I update the database twice a week and I'm the only person who maintains the database All my users just refresh tables (Based on MS Access quires) in excel, which they use for formulas, graphs etc Should I split my database? I heard that splitting can bring benefits? – Elixir Jun 24 '15 at 21:36
  • What would you split? Splitting refers to separating objects within the Access object structre: data (only tables) into a backend and application items (queries, forms, reports, macros, modules) into a frontend file. Since Excel serves as a connecting client, it already lies outside Access. As I mentioned in my answer, consider using Excel as a document that runs graphs/formulas from time-stamped dumped data out of Access (no OLEDB/ODBC). Then each user will have their own local spreadsheet. Of course a pre-designed Excel template of graphs/formulas will be needed. – Parfait Jun 24 '15 at 23:19
0

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.

msiudut
  • 251
  • 1
  • 20