Excel to Access?

[MrZeal]

Hi I recently created an excel user form for a team of 7 people to use to input data into a single datasheet. they can update/edit/delete/add data using the form and update the main sheet directly from it. Unfortunately someone decided, at the last minute to advise that they would like to use the form at the same time so my excel userform doesnt work for them "read only etc" is there anything I can do to create some kind of connection from excel to access so that they can use the form at the same time and I dont have to re do the whole thing in access ( I have limited/no experience in access) I guess what I am thinking is something like a main form which is the master then several of the same sheet for each individual

 

[Micron]

Excel userforms and Access forms are different animals (so to speak). A major problem with Excel is that it is single user - same as Word. You wouldn't expect 2 users to be able to work on the same document at the same time either right? While an Access form could work with the Excel file, it will not be able to open the workbook any more than 2 users can do so at the same time. If you want a concurrent user situation, Excel is the wrong tool. You will either have to build a db and move everything into it, or wait until someone releases the workbook. If you go the Excel route, do not design your tables as spreadsheets or you will have nothing but issues as a result. Spreadsheet data is wide (columnar) while db's are tall. Tables should only store data for a single entity, and other related entities are contained in their own table. An example of this would be a PO (po attributes in one table) and the PO line items in another table. These tables are linked via a common field. If you are not familiar with normalization as it pertains to database tables, it is crucial that you understand this first. Access has a steep and long learning curve if you want to do it right. If you take shortcuts by using some of the features designed to make it easier for you, you will regret doing so. I speak of table level lookup fields, multi value fields, attachment fields, switchboard (built in) forms and navigation forms. Do your homework before jumping into Access.

 

[Micron]

cross posted

Excel to Access User form and Database

Hi I recently created an excel user form for a team of 7 people to use to input data into a single datasheet. They can update/edit/delete/add data using the form and update the main sheet directly from it. Unfortunately someone decided, at the last minute to advise that they would like to...

www.access-programmers.co.uk

MrZeal, please read Excelguru Help Site - A message to forum cross posters