Connecting Excel 2013/16 to Access 2013/16 (SELECT, INSERT, and UPDATE)

Hello all,

I'm hoping that someone can assist with this endeavor of mine; I have found many different methods of attempting some aspect of this, but nothing has really worked thus far. Essentially, what I have created is a project management task/milestone tracker that is used by our team leads. What I want to do is use Access as a project high-level metrics summarization database (stored on a shared drive accessible by everyone on the team), and use Excel to push updates to it from my individual team project tracker spreadsheets. Then, I want another Excel spreadsheet to pull the data back from the database for executive review. I already created the tracker template and executive summary sheet, but was hoping for a "click once to update" button on each spreadsheet to make the necessary updates, rather than relying on a copy/paste exercise each week (so it's more to eliminate human error than anything).

That's the high level, here are the specifics:

1) Workbooks thus far, with explanations:
a)Project Tracker
--Tab: Tracker (used to track individual project metrics)
--Tab: Summary (used to summarize project health metrics from the Tracker tab; also used for database connection to retrieve a single record, and update said record)
b)Executive Summary - Project Health
--Tab: Project Health (pulls data from database and populates a summary tab)

2) In the Project Tracker workbook, I need a few things:

a) On open/when a button is pressed, attempt to retrieve the record from the database (this is based on the "ProjectCode" value, an Excel global named range...which is an indexed and unique field in the database).
--So...something like: "SELECT * FROM [summary table name] WHERE [summary table name].[Project Code] = """ & Range("ProjectCode") & """""
--and then place that in the named range "DatabaseRecord" (which has been formatted to accommodate the table record row with all fields)
----Also, if there isn't an existing record for that, then there should be one created with an INSERT query; probably like:
----"INSERT INTO [summary table name] ([Field1],[Field2],...) VALUES (""" & Range("DatabaseRecord").Item(1,1) & """,""" & Range("DatabaseRecord").Item(1,2) & ..."
b) When the weekly updates have been made by a team in the Tracker tab, they will hit a button in the Summary tab, which will then post updates to the database
--So...something like: "UPDATE [summary table name] SET [Field1] = """ & Range("DatabaseRecord").Item(1,1) & """, [Field2] = """ & Range("DatabaseRecord").Item(1,2) & ..."
c) (optional, but nice to have...more of a reminder to myself) Export a copy of the tracker spreadsheet to a destination folder on the shared drive when syncronizing with the database, so that the executive summary can create a link to it in case the executive team wants to dive into the details

3) In the Summary workbook, I need pretty much just one thing, database connection-wise:
On open/when a button is pressed, attempt to retrieve the table (entirely) from the database. So..pretty much the following:
--"SELECT * FROM [summary table name]"
--Update a named range to accommodate the table (based on the number of records in the table)
--Place the data in the named range for conditional formatting stuff to be called

4) (This is more of a reminder to myself, but if someone has suggestions on how to implement it, that's cool too) I'd also like to add in timestamps for the last update for both workbook views (so that it shows when the last entry in the database was made). However, I have folks in multiple timezones playing into this, so I'd need to make sure it included that data. This would also be useful to use as a quick reference point to ensure that the database connections are working correctly.

5) Overall, the database should just have connections made to it and never really be accessed otherwise. Perhaps consider using a duplicate database also stored on the shared drive as a backup.

I'm just not sure how to make the connections I need work and/or whether my thoughts on the code needed to make it work would...well, work. I have tried looking for guides on how to do this, and it seems like the basics are pretty simple; but...I just can't seem to make it work. Anyone out there have any thoughts/suggestions? Thanks much!

If you are comfortable with DAO recordsets, then make a DAO connection, loop through the tables and use DAO.FindFirst on your indices and .Update with the fields based on your cells. I will post an example in a moment.

This just adding new records. If you need more, I will post an example of updating existing. You need to make a reference to Microsoft Office 15.0 Access Database Library for this code to work

Please Login or Register  to view this content.

And to update existing records based on PK PRODUCT_NO

Please Login or Register  to view this content.

As to your question 3. Go to the data tab and get data from Access database. Follow the wizard. You can replace the table with SQL if you wish in the properties / definition. You can set the database table to refresh on open, and delete the data before closing to make the workbook smaller.

Question 4) add a log table in the database itself and rs.AddNew the time stamp using the same method.

A couple more examples. Had to add as text file as Forum Firewall really doesn't like SQL