Ms Access query results to Excel via button in Excel

[DonVman]

Hello guys,

First off this is my first post and I hope that I did it correctly. I know very little about vba and I need some assistance with my problem. I have an existing Excel Workbook and on Sheet1 I am trying to track employees time per job number. Btw, we use MS Access for employees timesheets and I have a query in MS Access called "Get Employee Time" (see Access Query Results.jpg). Based on running this query I would like to take the hours and
place that time by the employee into my Excel spreadsheet (see Excel Before.jpg & Excel After.jpg) I am providing my Excel workbook "Example.xlsm" for clarity. I have a button in my Excel spreadsheet on Sheet1 call "Update Time". The idea would be that while in this Excel file I would like to hit the button "Update Time" and it will go run the MS Access query "Get Employee Time" and take the results from that (see Access Query Results.jpg) and put the corresponding time by the employee into the Excel spreadsheet based on where the Job number matches in the Excel file. My MS Access database is called "Timesheet".


This really is a simple concept but very difficult for me. Is this possible? I really need some direction.

 

[bobhc]

You can set all this up via Access with just a few clicks of the mouse, but your database tables must be set up right and be able to hold ALL the data you need on all the tables needed, the worse thing you can do is add tables long afterwards, the relations get totally messed up, best to design your tables and what they will hold with good old pencil and paper it will save a lot of heart ache later.

 

[Chihiro]

Why VBA? Just set up MS Query connection or PowerQuery/Get & Transform query to Access. Then transform the data brought in as needed, using pivot table or other methods.

Or do it from Access side as bobhc suggested.

It's more stable than VBA and much easier to maintain (especially when you are not familiar with details of ADO coding).

 

[DonVman]

Excellent points. I really appreciate the information. I will have to evaluate which approach I will take.

 

[DonVman]

Hi, guys... I did some digging and I found this solution on the Microsoft Community. This works just fine for dumping the query results to my existing MS Excel file. I just thought I would share what I came up with.

Sub GetQuery()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Long
    Dim wsh As Worksheet
    Set dbs = DBEngine.OpenDatabase("path+filename here")
    Set rst = dbs.OpenRecordset("query name here")
    Set wsh = Worksheets("Sheet1")
    For i = 0 To rst.Fields.Count - 1
        wsh.Cells(1, i + 1).Value = rst.Fields(i).Name
    Next
    wsh.Range("A1").Resize(ColumnSize:=rst.Fields.Count).Font.Bold = True
    wsh.Range("A2").CopyFromRecordset rst
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Sub

I do have one more question though... Is it possible for my Access query to ask the user a question and the user types in response the answer then the query will run and insert the results into my Excel file? For instance, one of my queries asks for user input a question "Like [Type in the job #]

I had to hard code the answer in my query to get the above to work but I really need it to ask for user input the job number.


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​