Excel to Access?

MrZeal

New Member
Joined
Jun 1, 2020
Messages
14
Office Version
2016
Platform
Windows
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,846
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,846

Watch MrExcel Video

Forum statistics

Threads
1,101,936
Messages
5,483,783
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top