Update table in Access from Spreadsheet

[thomasks]

I have a master spreadsheet that estimators use on a daily basis. I would like to be able to update a master database of all the quote activity.
On my spreadsheet now, I have a button that writes to another spreadsheet and updates to the last used row, the date, estimator name, project number, etc. from the worksheet.
How can I select certain cells from the excel spreadsheet and have an update query write the values to my access master database, instead of another spreadsheet?
I would like to have all the query ability of a database, and not just another spreadsheet.
Thanks for all the help!

 

[mrf1xa]

From your access database, use File, Get External Data, Link to add the Excel spreadsheet as a linked table to your database- you can now use queries or any other Access features on the data in the spreadsheet, or use an append query to add data to one of your tables if you prefer.

Hope this helps

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[thomasks]

Thanks for the suggestion.
I found this code on the net, and incorporated it into my spreadsheet with a vbyes no msgbox on the before close event, where I ask the user if they would like to update the master database. If they select yes then I run the code, if they select no then I exit the sub and close the worksheet.

Sub UpdateMasterDb()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim intResale As Integer
    Dim intHours As Integer
    intHours = Worksheets("Project pricing summary").Range("G60").Value + Worksheets("Project pricing summary").Range("G61").Value _
    + Worksheets("Project pricing summary").Range("G62").Value
    intResale = Worksheets("Project information").Range("D26").Value + Worksheets("Project information").Range("D28").Value
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=T:\Quot\CONTROLS\Formulas\Estworksheet\Master Quote List\Controls Estimating Database.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "tblMasterQuoteList", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    rs.AddNew
    rs.Fields("Project") = Worksheets("Project information").Range("L6").Value
    rs.Fields("Customer") = Worksheets("Project information").Range("D6").Value
    rs.Fields("Location") = Worksheets("Project information").Range("D7").Value
    rs.Fields("Quote_Type") = Worksheets("Project information").Range("L7").Value
    rs.Fields("Estimator") = Worksheets("Project information").Range("D8").Value
    rs.Fields("Date_Quoted") = Worksheets("Project information").Range("L5").Value
    rs.Fields("Comments_Scope") = Worksheets("Project pricing summary").Range("C15").Value
    rs.Fields("File_Path_Hyperlink") = Application.ActiveWorkbook.FullName
    rs.Fields("Resale") = intResale
    rs.Fields("Hardware") = Worksheets("Project information").Range("D27").Value
    rs.Fields("Eng_Hours") = intHours
    rs.Fields("Travel") = Worksheets("Project pricing summary").Range("L59").Value
    rs.Update
    MsgBox ("Database has been updated")
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub