Excel Form to Access Table?

[Laney]

Hello All,

Wanted to see if this was possible or not.

I would like to be able to make a form in Excel that all employees can use on the G drive.

I would like to set it up to where any time they enter information on that Excel form ... it automatically updates to fields in an Access table. Is that possible?

Please let me know.

Thanks!

 

[Tiro Cupidus]

Probably, but I'm not sure. However, I know you can setup a linked table in Access and link it to an Excel file. This way, if you add new data using either Access or Excel, the spreadsheet is updated.

 

[Laney]

Thank you ...

Thanks for the info Tiro.

 

[Laney]

Well ...

Guess no-one else had any suggestions. haha Geeesh this sucks!

 

[Mile-O]

You'll need to set the adequate references in Excel. In this instance I have a form with one textbox and a command button.

This code is on the command button:

    Dim db As DAO.Database, rs As DAO.Recordset
    Set db = OpenDatabase("my full path to my database")
    
    Set rs = db.OpenRecordset("tblData")
    
    With rs
        .AddNew
        .Fields("Data") = Me.TextBox1
        .Update
        .Close
    End With
    
    Set rs = Nothing
    Set db = Nothing

In the database I have a table called tblData and a field called Data.

In Excel I set the references via Tools -> References for Microsoft Access Objects and Microsoft Data Access Objects. The priority is respective of the order I mentioned them in.

I click the button and whatever I type is put into tblData.


This will generate an error, however, if someone has the database open.

 

[Laney]

...

Thank you Mile.

Does that also work if the data is set up in columns and rows? Not just like a regular spreadsheet?

The Excel form will have the following information listed on it:

On the top it will have EmpName Emp# Week Ending

From left to right it will have
Mon Tue Wed Thu Fri Sat Sun Total

Then down on the left it will have
Client Project Task Memo Hours OT

 

[Mile-O]

I don't know enough about your table to know if it would be an easy transition for the data or if its going to be a big coding solution.

 

[Laney]

It'll look like this picture ...

Hopefully it'll be a simple form. I would like to do drop downs on client, project, task, week ending...

 

[Mile-O]

OKay, that's Excel but what about the database table you are wanting this to go into? What's the structure for that? It shouldn't be the same as that picture.

 

[Laney]

Ahhh ... Sorry about that ...

Sorry about that.

I am going to create a table with the same fields as the spreadsheet. It'll just be a normal table with the same fields and an auto number as an identifier.

So the table will have the fields

EmpName, Emp#, Week Ending, Mon, Tue, Wed, Thu, Fri, Sat
Sun, Total, Client, Project, Task, Memo, Hours, OT

Now, the tricky part is going to be that I have to some how get the Days to also show different dates one week at a time and each person may enter 1-6 different rows on each date.