Forum Discussion
Access conversion
Is it possible to import an excel sheet into a form in Access. I have a form that we use for bill of ladings and it is saved each time as a unique BOL. I would like to import this sheet and build a database around it.
- It is not possible to import a sheet into a form. There are options to import data from Excel into Access tables, but not with this sheet. Designing a database starts with the tables and relationships. Forms are based on tables. You should analyze the sheet and design your tables accordingly. Then build forms to input data.
- Tables first, then a report. Reports are the appropriate tool for displaying data as is done in this screenshot.
To supplement what XPS35 pointed out, here is a quick overview of the essential components of a relational database application, such as those created with Access.
- The Data layer, or data tier. In a relational database ALL data is stored in tables. The design of those tables is a critical element in creating a usable application. The first step in creating a new database application, therefore, is understanding the data required and setting up tables accordingly.
- The Interface layer, or interface tier. Applications require an interface through which users can interact with the data in the tables. There are four basic types of interaction.
- Creating new records as required in one or more tables.
- Reading data back from the tables into the interface for display to users.
- Updating existing records as required in one or more tables.
- Although less frequent than adding new records, a valid application requires the ability to change, or update, existing records as appropriate.
- Deleting existing records when required.
Together, these four tasks are often referred to by their initial letters, C, R, U, and D, or "CRUD operations".
- The Logic layer, or logic tier. This is the code which automates interactions with data and manipulates the interface, as required.
Your screenshot illustrates only the 2nd of these elements, an interface in which data is retrieved from underlying tables and displayed in a formatted presentation for the viewer.
You can build a report for the interface which displays data in a manner similar to that in your screenshot. However, the storage of the data is done only in tables. And the process of adding new records, updating existing records and reading them into the report is done via an interface designed to prepare the report for display.
Investing time in understanding how relational database applications work will pay off when it's time to use the application for tasks like this one.
- George,
thanks for taking the time to reply. I have broken down the BOL sheet into 3 parts and 3 separate tables, BOL Data, TDG data and Location data. I will build the form around these 3 tables. That way as you said it will be easy to create new records, edit/deleting existing records and retrieve records. It will take me some time to get into the swing of things but once I get the BOL solved the other forms we use will be easier to migrate over to access.- To create a proper database I think you need more/other tables than those. Like product, carriers, customers, transport (?), transport details (products).
Building a database is very different from creating a spreadsheet. With a spreadsheet you just start entering something. With a database you must first think carefully about the structure to avoid problems during use. The structure must also comply with certain (normalization) rules. Study those rules before you even think about staring to build.
- GetObject (, "Excel.Application")
automate extract data from excel?
