Linking to multiple sources

[jlfoster2]

I am new here so if I have posted in the wrong place I apologize and will happily move the post if you let me know.

Alright so here is my situation. I work for a small manufacturing company and have been asked to set up an access database to track production. The end goal is to have a touch screen at every machine where operators will input data. This data will then dump to a different workbook for each machine. My question is: Is it possible to link one access tables with multiple xls documents so that, as data is added to any of the workbooks, it is then updated to a single access table? I also will need to find a quick way to link new files to the table as new parts are put into production.

Thanks for any help.

[help_me_with_access]

Is it possible to link one access tables with multiple xls documents so that, as data is added to any of the workbooks, it is then updated to a single access table?

not sure I understand this, but you can use the libs in visual basic to change link paths to tables and refresh them automatically. DAO, and also ADO can do things like this.

but lets see now...so we have multiple xls's that serve as the backend and 1 access db that serves as the front end?? i don't think you can do that, can you? doesn't make sense to me.

one link = one sheet in one table in one workbook. you can't create multiple links via the linking manager or any other resource because of definition ambiguities.

someone correct me if I'm wrong!

[June7]

Access tables don't link to Excel spreadsheets. An Access file can link to an Excel spreadsheet. The link can be worked with much like a table, but data is still in spreadsheet, not an Access table object.

You can query the link and use it as source for INSERT or UPDATE sql action to modify data in Access tables.

[RayMilhon]

I wouldn't recommend this setup.

Here's the setup I would use if it was me.

Create your access Database including tables, Forms, queries and reports
Split the Database into a frontend and backend place the backend on the server and the frontend on each workstation. You can install the Access Runtime on each workstation (free download from microsoft)

Test to make sure it all functions properly and you're done user's don't need excel for data entry.

You can include the user and workstation information so you can keep track of who did what and where.

[help_me_with_access]

Access tables don't link to Excel spreadsheets.

thanks for the correction June. I think I'm gonna stay off the forum for a few days.