Creating new form to add data to an already existant linked table

[JohnnyBGood]

I am trying to create a form for a table that is linked to an Excel spreadsheet.

The table already has legacy data in it, and my boss wants me to design this form so that rows can be added to this data in Excel. It's in Excel because there are various manipulations they want to do to the data and they are more comfortable in Excel... but Excel doesn't allow multiple users to enter new data at the same time (workbook share doesn't work for this).

Anyway, I used the form wizard to start the form, but I can't get it to allow new data. If I set the Date Entry property to Yes, then the form comes up with no fields to enter data into... what do I need to do?

Also, I need to use comboboxes to allow users to select names from another linked table called Lists... can I do this when I am using linked tables?

 

[boblarson]

Sorry, but you can't update Excel worksheets like that using them linked. Microsoft lost a lawsuit many years ago around the technology to do that and they had to remove that functionality from Access 2000 and subsequent versions.

 

[Patfreem]

It'd be nice if you could: Start by importing the Excel sheet (lots of front-end work doing this, but it only needs done once) and use a Form linked to a Table to enter new data. Then, periodically Import the Excel spreadsheet, Run an Update query to add the new entries, then Export the data back out to Excel. Maybe there's a MacroMonster out there. [nothing's impossible to an ignoramus, apologies if I recommend impossible tasks].

 

[Pat Hartman]

You could use automation to add rows to the spreadsheet but that will also prevent shared access.

Perhaps its time to replace the spreadsheet. You should be able to create forms that make updating simpler than the spreadsheet interface and you can create reports that replicate the spreadsheet look. You could also export the data back to excel for viewing only. They'd still have to update in your Access app.

 

[JohnnyBGood]

Thanks guys.

All points well taken and will be pondered. I think I like Pat's recommendation and will try to convince the powers that be.