Data Input without Access (i.e., e.g., in Excel) on a network? 1

[TomYC]

I spend too much time doing data input from sloppy sheets of paper. If all users had Access, I could design a form to enforce data integrity (e.g., no missing date fields, etc.). But most users in my environment do not have Access. They do have Excel, however. So I could create a form in Excel, put it out on the network, and ask multiple users to connect to it and do their data input, giving me much of the same data integrity options. Then I'd have to import data into a central database, say with linked tables, etc.

Is this common practice when Access is unavailable, and does anyone have suggestions, pointers, links, etc.?
Many thanks,
Tom

 

[BradCollins]

You can still use Access, if you have a full version, you can design your database complete with all forms and then install the runtime version of Access onto the users machine.
The runtime version can be downloaded for free from the Microsoft website.

 

[ZiggyS1]

the free runtime is only available for 2007

Using Excel for data entry would work and linking to the file(s) and then appending to a final table ( from the excel ones) in Access....Importing is also an option...you just have to think the process through, the timing and whether there is potential for duplication.

So in Access make sure you have a way of identifying a Unique key for the imported records this way you can set the index to not allow duplicates so it only accepts new ones.

* sometimes you have to combine fields in your append query to make it unique.

 

[TomYC]

Thanks to both of you; I did start reading up on InfoPath and xml, per Brad's tip. But in my environment (2003) I'm probably better off creating a data integrity form in Excel and having users input and then email to me. The unique key tip is essential!

 

[ZiggyS1]

no problem, just to clarify, for 2007 "Free" is the operative word, for previous versions it is also available in developer tools which is part of...

Visual Studio Tools for the Microsoft Office System

I've never used it, just know that they made it Free in 2007, I plan on looking into it though as we upgraded to 2007.

 

[jrbarnett]

Why not just have Excel submit the data straight into the database? write your code in VBA and use an ADO connection to post it (you probably want to use SQL Server rather than Access for import like this).
Your submit function has to write back into the source form an ID to allow updates to the form's data (into a read only field).

John

 

[TomYC]

thanks, jrbarnett--
I'll be studying up on Excel as a front end, as much of what you suggest is beyond me, at present. but I am determined to get people in my organization (healthcare) moving away from the paper and pencils modality!