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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Micron

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

Watch MrExcel Video

Forum statistics

Threads
1,102,692
Messages
5,488,301
Members
407,634
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top