Learn how to a build a cloud-first strategyRegister Now


Convert/ transfer an Access form into an Excel form that will be used for data input

Posted on 2006-03-30
Medium Priority
Last Modified: 2012-05-05
I have an Access database that works fine with nice forms. We use the forms to input data.
Now, I'd like others to do the form data entry: my idea is to send them an Excel file which displays an Excel form. When all the data is entered in the Excel file, this file is sent back to me, I do some automated checks and then update the Access file from the data in the Excel file.

My question is: the Access form is highly complete, and I would prefer to use a similar-looking form for data entry. Is there an easy way to do that?
Question by:fibo
LVL 15

Expert Comment

ID: 16332014
I don't really know about Excel forms...  But why not send a copy of your access mdb, they input data in it and returne it, then you can run an insert query from the copy mdb to the main mdb...

Expert Comment

ID: 16332126
You could also create a second database with just the forms. The Based tables are linked in, so the new database file is not large, and they are entering directly into your main tables.
LVL 85
ID: 16332688
The answer is no - Excel isn't a data-entry program but is instead an analytical tool. You don't have the same GUI tools available in Excel as you do in Access, so you'd be quite hard-pressed to simulate a "nice-looking" Access form in Excel.

I second the idea of sending them a database (.mde format, by the way) to do this. The concerns there would be:

If you've used any ActiveX controls in your design you'd have to distribute and install them
The enduser would have to have Access installed on their machine (either full or runtime)
You'll have to support it


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 19

Expert Comment

by:Richard Daneke
ID: 16333041
Excel can provide a form for data entry, but the data cannot be validated using the Data, Form option.  
If you enter the data in rows in Excel, you can use Data, Validation to limit or test the data and you can use formulas for VLOOKUP against table data  to fill in some cells.   The data can then be imported into Access with a combination of Append, Delete, and Update queries to add back into access tables.  
A form can be designed with VBA and forms controls for data entry similar to the Access forms and storing the data in a table in Excel - either in another worksheet or in a linked workbook.

If you are wanting others to share in data entry that do not have Access installed, you should look into saving your form as a Data Access Page, then the others on the LAN can use their Internet browser to assist with data entry.
LVL 29

Author Comment

ID: 16333365
How do I create a Data Access Page (I presume this is buried in some strange place in some strange menu...)
Coudl they be updated off-line?
That would certainly help me for the capture/transfer of information.

Is there a "good way" to avoid redesigning the Excel form while the Access form is here readily available?
LVL 19

Expert Comment

by:Richard Daneke
ID: 16333703
The Data Access Page is one of the Access objects - after Forms, Reports, then Pages.  
The quickest way is to open a form and then choose File, Save As... and select Data Access Page.
Access will create a shortcut to the html based page in the Pages section.  The data access page and the access database must be stored in a shared folder available to everyone that needs to use it.  Typically, to a network drive.
It is okay to move your Access database to the shared drive then create the data access page.
The database will be shared - many people can add/edit at the same time, but off-line data entry is not possible.  What are the issues for working off-line?
LVL 29

Author Comment

ID: 16334153
1 - Use scenario:
a = an (Excel) file is put on line or sent by mail. It is a form for a credit authorization request.
b= the form in the Excel file is used to enter all the data: name, address, background info, amounts, salary, etc.
c = once the data entered, the XL file is sent to the company, where it is used to add a record into the right table.

2 - More precisions on the initial question
- I think I wil be able to solve most or all the problems in b/ andc/
- I know I can build the Excel form that will be used to transfer the info. This process is going to be a long pain, because there are 100 + fields to place correctly on the form. I was hoping to fime some better way to solve this tedious formmaking process.
LVL 19

Accepted Solution

Richard Daneke earned 2000 total points
ID: 16334337
I think I would create a worksheet with the cells needed and arrange in a pleasant format similar to the paper document.  Use lookups (vlookup or hlookup) to pull from tables to complete data and use Data, Validation to qualify the data, to prompt for user input, and to customize error messages as much as possible.  

Hide the gridlines to make the worksheet more form like in appearance.

On a second, hidden worksheet I would transfer the data into a row like format for data import to access.  The simple use of the basic formula =Sheet1!A4 will do that.

You may be able to structure this hidden sheet to be able to copy/paste the Excel row directly into an Access record.  

Use Tools, Proection, Protect Workbook to  keep your hidden worksheet secure.  Use Tools, Protection, Protect worksheet to keep your 'form' secure.

This use of a front worksheet to complete a backend worksheet can be reviewed in the Invoice template that ships with Excel.  (File, New...  From Templates)
LVL 19

Expert Comment

by:Richard Daneke
ID: 16334393
BTW, the data access page can be used over the big WWW internet, but the tricks lay in publishing the data access page to the web, connecting to your Access database (via an ASP connection) and the setting security for your remote access.   Then, you could connect the office Access to the web Access and update records on a scheudled basis.  
LVL 29

Author Comment

ID: 16337043
Thx, I've a clear process now.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question