Question External form that interfaces with access

[Deb-rkc]

I'm looking for a form system that will interface with Access.

At present, we collect information from clients and store it in an Access 2003 database. I have designed my form as an Excel spreadsheet. I use Excel as we are missing the reference files that will enable Access to work with Word and some other programs. The Excel form is emailed to the client, who fills it in & emails it back to us.

To restrict entry of certain fields to a list of values, I use lists in the Excel Workbook, which emulate the lookup tables in Access. I am expecting to add more values to the lookup tables, which would mean that I have to constantly update the lists on the Excel spreadsheet, with the lists eventually becoming quite large.

I have written an import program in Access which imports the Excel worksheet/s into the relevant Access tables. This all works, except the entire process is very manual.

What I would like is to use an external form program that would do the following:
1. Allow me to design something that looks nicer than a spreadsheet
2. Can be updated with my lookup tables so I don't have to manually edit the lists (as in Excel)
3. Can be imported into Access (export to an xls or csv file would be ok if not into Access)
4. Is portable, self-contained & can be emailed to the client so doesn't require them to log into any of our systems, including our web site. The user can fill it out with the data being validated at the same time & then return it to us.
5. Is inexpensive (or even free!)

Does anyone know if something like this already exists? Any help is much appreciated.

 

[JHB]

I use Excel as we are missing the reference files that will enable Access to work with Word and some other programs.

Isn't it because you only need to set the reference in MS-Access to Microsoft Word Object library? (Else I'm sure you can find it on the Net.)
But why are you not using MS-Access to it all, why Excel/Word/other programs it is even make it more complicated?

 

[RichP]

Isn't it because you only need to set the reference in MS-Access to Microsoft Word Object library?

That. You can then also set the Word doc to be a form so there are only certain fields where than can enter the data. It should then be easy to read back the forms

If you wanted to upgrade to Access 2010 then you could use the collect by email feature
office.microsoft.com/en-us/access-help/add-the-data-collected-through-e-mails-to-your-access-database-HA010341539.aspx

but I'm not sure that 2013 has it

 

[Deb-rkc]

Thanks for your replies JHB & RichP.

When I say I'm missing the reference file, in fact I do have the file but it doesn't allow me to use the Word app.

The reference file for Microsoft Word 11.0 Object Library is MSWORD.OLD which I have. When I select this in Tools, References, then try to use it in a program as follows:

Dim wApp As Word.Application
Set wApp = CreateObject("Word.Application")

On the 'set wApp...' line I get the following Runtime error:

'Automation error
Library not registered.'

So, the file is corrupt or there's something else wrong. I've searched on the net for another copy of the file without success. I have also registered the Microsoft Office Object Library.

I can't rely on using any of the features of a later version of Access as I don't know when we might be upgrading, although I hope it's soon. I'm sure it would make my life easier.

Can you explain how I can use an Access form without our clients having to log into our system?

Thanks, Deb

 

[JHB]

Can you explain how I can use an Access form without our clients having to log into our system?

You could make a runtime version for them, if they don't have MS-Access installed. Do you have many clients?
But more or less it depend how much time/money you want to spend on that and how much data your clients have to fill in!
You could also create a kind of WEB-based form, sent it per E-mail to the clients, where they could fill it out and return it to you without need for using Excel, Words etc.

So, the file is corrupt or there's something else wrong. I've searched on the net for another copy of the file without success. I have also registered the Microsoft Office Object Library.

Can't you copy it from another computer, (in the office)?

 

[Deb-rkc]

I did think of creating a runtime version, but this would entail them installing something on their system which could be an issue for a number of our clients.

I did some searching and I found a product called Zoho Creator. You can create forms, reports, etc, but the best thing about it is it links to Access. I can upload my tables & it creates the forms from the tables. Then I can add validation, etc using their interface or attach script programming to do more advanced stuff.

The link to the form can be emailed (perfect for us) or embedded.

I'm learning how to use it at present. So far, I've uploaded some Access tables & created a form which I'm testing. It does what I want, which is use the data in Access tables as lookup values in a drop-down list. This would be impractical in Excel with many values.

From what I can see, the forms are pretty basic, ie you can't do much to make them look attractive, but maybe I haven't found that bit yet.

It's free for up to 3 users (1 concurrent), which is fine for my needs at present as we won't have more than 1 client using it at once.