Is there a better way to insert data than Access?

In order:

1) One column in the spreadsheet could indicate add, update, or delete.

2) Those extra spreadhseets would have to disappear. For those who require extra information, they should find some way to get the data into SQL Server, and create their own reports.

3) The package could run hourly.

But as you point out, this solution is not perfect. If the effort is so important, then management should allocate resources to get a front end program written for data entry. Of course that doesn't mean that it is going to happen, it didn't for me either :angry:

Regarding letting the user add new values look at the AllowValueEditLists property (see help below). I haven't personally used it but it appears it might answer your problem. I believe this is fairly new, like Access 2007. The other way of handling this takes some coding in the event that occurs when a value that isn't in the list is entered occurrs (don't remember the specific event name now).

Access Developer Reference

ComboBox.AllowValueListEdits Property

Show All

Hide All

Gets or sets whether the Edit List Items command is available when the user right-clicks a combo box. Read/write Boolean.

Version Information

Version Added: Access 2007

Syntax

expression.AllowValueListEdits

expression A variable that represents a ComboBox object.

Remarks

The AllowValueEditLists property determines whether the the Edit List Items command is available when the user right-clicks a combo box that's bound to a Lookup field .

If the Lookup field is bound to a list of values, then the Edit List Items dialog box is displayed when the user clicks Edit List Items. The user can then add, delete, or edit the items to be displayed in the combo box.

If the Lookup field is bound to a table or query, then the form specified by the ListItemsEditForm property is diplayed when the user clicks Edit List Items. The user can use the form to add, delete, or edit the items to be displayed in the combo box.

The AllowValueEditLists property is not available for combo boxes on a report.

© 2007 Microsoft Corporation. All rights reserved.

This (emphasis mine) alone is enough to set you up for a world of pain. If managers can add colors on the spot when entering new product data, you'll get all kinds of messes. For example, someone will add a new product with a new color and when prompted to enter the new color, type 'magneta'. Then, when he can't find his new product by searching on the color he *thinks* he entered ('magenta'), he'll (1) assume that the data entry "didn't go through" and repeat the process, resulting in two rows for what should be the same product, (2) come screaming to you about how the "system" is broken, or (3) both. In the meantime, two or three other users will add new products and choose 'magneta' as the color, either overlooking the misspelling or assuming that they should use this choice when they mean 'magenta', while another will add 'magenta' to the list of colors and enter four or five products with this color.

The point of a lookup table that serves as the source of a drop-down selection window is to ensure that users enter data in a consistent format and use only valid values. If you allow them to edit the lookup table on the spot, it's really not much better than allowing them to enter free-form values.

I hope you will not take offense that others have offered you so much unsolicited advice in response to what seemed to you like a simple question. So many of us have had to clean up the messes left by predecessors who failed to appreciate and meet the challenges of creating a robust, functional database application.

Thanks for your participation. And I do totaly agree with you dat when users get the opportunity to muck up a table they probably will. And they probably would use terms as 'to be determined', 'general', 'not known', for anything they can not decide or they do not know yet. But being able to enter the data can be done in combination with a picklist.

We have considered this, but somebody has to fill the lookup table with the correct values and that is the same group of users. Maybe the filling of the lookup tables should be managed, (by the same group of users).

Depending on the usage, the input to lookup tables should be restricted. Because I do not know how this should be achieved, I have left this outside the scope of this project. There are certainly tables which are referenced by other tables where the enduser is allowed to add rows. (For example when entering an order, there should be a client, but the user is allowed to add the client while entering the order).

One advantage: the users who enter the data are also the users who use the data after it is entered. Have they entered data which is not usable then they notice the consequences of that themselves.

(Sorry, but all examples I have used are generalised use cases of actual use cases we have here and I can not show the actual cases).

Thanks for your participation, and thanks for your valid point.

Ben

ben.brugman (4/16/2013)

-- ben ben:

How does it get there that is the actual question. Users come up with this information and they want it stored somewhere. The (IT) specialists come up with a databasedesign and implementation. The users (managers) have to enter the data themselves. Or if they have the data in a spreadsheet or comma delimited file or something this can initially be uploaded by the technical people.

The data is information the users want to have stored. This can be all kinds of data.

-- neb neb.

-- ben ben:

Could be for example if you want to enter some (not a lot) of test data in a database which allready exist but where the application is not finished.

But also to store data which has not yet an application.

-- neb neb.

-- ben ben:

Yes it could also be used to store the information for a personel trip, where the different persons have different 'requirements'.

For example which equipment is used, which insurances do the persons need. How are the rooms used. How is the transport done.

Now this type of data is done in a single spreadsheet. (And for this use the spreadsheet is sufficient enough).

But some data in the spreadsheet is duplicated and there are inconsistencies in the sheet because of that. (Some information get's changed but not in all the locations where the data is duplicated.)

For this usage a human controler for the data is sufficient enough, but if there would be a generalised application which the users a familiar with the spreadsheet might get replaced by storage in SQL-server.

-- neb neb.

-- ben ben:

Yes the managers understand completely how the data hangs together. It is their data. Most managers are very familiar with handling their own data.

-- neb neb.

-- ben ben:

They are all for it if the application can be found. They do understand that SSMS is not for everybody, but they also understand that a 'striped' down SSMS which can only be used for CRUD would be an exelent idea.

And yes there is enthosiasm to build it ourselves, but we will only revert to that if we can not find a suetable application.

(We would like to build it ourselves 🙂 )

-- neb neb.

-- ben ben:

we are in total control of the database.

In general with read only the database would stay empty.

In general the control of the data is build in in SQL-server, so SQL-server does limit what can be entered in the database.

Further it is the responsibility for the user to enter the data correctly.

-- neb neb.

-- ben ben:

See the example of the personal trip.

But it could also be a Query and Answer database which are made by a team.

Even a footbalpool would be an example. (If the personal is allready one of the tables in the database).

-- neb neb.

____________________________________________________________

Mongodb?