• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6140
  • Last Modified:

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

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?
Bernard S.
Bernard S.
1 Solution
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...
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Richard DanekeTrainerCommented:
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.
Bernard S.CTOAuthor Commented:
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?
Richard DanekeTrainerCommented:
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?
Bernard S.CTOAuthor Commented:
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.
Richard DanekeTrainerCommented:
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)
Richard DanekeTrainerCommented:
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.  
Bernard S.CTOAuthor Commented:
Thx, I've a clear process now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now