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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,885
Office Version
365
Platform
Windows
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,885
Office Version
365
Platform
Windows

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,550
Messages
5,512,021
Members
408,872
Latest member
Lorid24

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top