Is there a better way to insert data than Access?

Question

Post reply

Is there a better way to insert data than Access?

ben.brugman

SSChampion

Points: 13369
More actions
April 15, 2013 at 5:41 am

#276458

Is there a better tool to insert data into SQLserver than Access ?
For managers. Small number of people. Very limited amount of data. For small administrations. To replace spreadsheets used by the managers.
(Insert, read at and modify data).
Ben Brugman
Last week, I tried to ask the same question but bumbled.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Sim-473257 Mr or Mrs. 500 Points: 532 More actions · Answer 1

Ben,

The 'bumble' might help but I can't find it.

When you say 'better' .. what exactly do you mean ?

There are infinite ways to interact with SQL Server and each solution really need to be judged in it's own context.

What exactly are you trying to achieve ?

What are your struggles with your existing solution ?

What technical resources do you have at your disposal (programing languages/platforms skills etc )

Let's put some flesh onto the problem and see what happens ...

ben.brugman SSChampion Points: 13369 More actions · Answer 2

What exactly are you trying to achieve ?
What are your struggles with your existing solution ?

Problem:

At the moment managers use spreadsheets, which they build themselves. Problem with spreadsheet is copies, copies and more copies.

Large part of the data is often text based.

What technical resources do you have at your disposal (programing languages/platforms skills etc )

We have office (with excel and access).

I think that a SQL-server database is more suetable to hold data and information.

Mainrequirement is:

Insert and mutation capabilities on an implemented database, for a manager.

It does not have to be fancy. It should have only the basic functions for inserting and modifying data. If a solution has to be build or generated, this should only take limited time and or effort. Resourses we have all take to much time and effort to make and deploy a simple application.

Designing, building, maintaning a database with a few reports is not problem. This we do 'on the fly.

With access a solution can be build, but is often to complex, powerfull and has too many functions. The usage of SSMS is not safe and not simple enough for a manager.

Do you have any suggestions?

What simple and effective ways are there to insert and mutate data in a SQL-server database?

ben

Sim-473257 Mr or Mrs. 500 Points: 532 More actions · Answer 3

Still a little confused on your current situation,

but if I ignore your context, I can outline some options.

Moving up from simply 'spreadsheets', most people start with an Access Database to hold their data, and use the provided Access forms to display and interact with that data.

When your data is too large for Access, becomes otherwise too complex, or other restrictions kick in.. then you move to SQLServer.

If you have your data in SQL Server, there is no 'front end' for users to access data.

Unlike MSAccess, there are no in built forms or Reports.

Yes, you have SSMS, but this as you realize already, is intended for the technical development/administration of the database, not front end users.

It is up to YOU to build a front end out of your own skillset, and link it to your SQLServer for the data.

A front end for your managers can be built in an infinite number of ways, and the definition of 'easy' is really relative to your skills and experience.

If you are familiar with MSAccess, I would suggest using MSAccess Forms for your front end.

You can link the forms to the data in your SQL Tables, instead of the Access tables.

So, your data is coming from SQLServer, but you are using MSAccess as the 'display' that your managers would interact with.

Google should provide more than plenty of tutorials for setting this up.

If that is not good for you, then various other options exist, but again, 'easy' is relative to your skills.

If you know VBa, then you can use any of the Office products, such as Excel, to display and edit your data which is located centrally in SQLServer.... but I would only recommend this for 'simple' applications.

If you have web oriented skills like ASP/ASP.net .. then you can build a web front end to display the data from SQL server...

Again, C#/VB can be used for desktop applications, if you know good C#/VB.

.. and so on.

Again, I probably repeat, but the choice of front end for your managers, really does depend on (a) what you want to achieve in detail, but in your case I am assuming more emphasis on (b) the skill sets your team have. (MSAccess, VBa, ASP, .net... etc ..)

I just wanted to clarify your situation here :

With access a solution can be build, but is often to complex, powerfull and has too many functions.

Which part exactly is too complex/powerful ?

Are you talking about the data you are storing in Access ?

Or is building the forms to complex ?

Too complex for the developer ?

or to complex for the managers/users ?

The only complex side of an Access solution should be in developing it.

The developer should make forms in such a way that the users see only a simple and clear presentation of the select data they need to see.

Users themselves (your managers) should not see any buttons, data or other options other than those they need to see.

Everything else should be hidden by the developer..

ben.brugman SSChampion Points: 13369 More actions · Answer 4

Sim-473257 (4/15/2013)
Still a little confused on your current situation,
but if I ignore your context, I can outline some options.
Again, I probably repeat, but the choice of front end for your managers, really does depend on (a) what you want to achieve in detail, but in your case I am assuming more emphasis on (b) the skill sets your team have. (MSAccess, VBa, ASP, .net... etc ..)

Assume that we are capable off aquiring the needed skills.

Which part exactly is too complex/powerful ?

Access has a large menu system and is not geared towards input, but is geared towards a lot of other functions.

Are you talking about the data you are storing in Access ?

No, definitely not, storing data in Access leads to copy, copy and more copies. Same problem as with spreadsheets.

Or is building the forms to complex ?

See below

Too complex for the developer ?

See below.

or to complex for the managers/users ?

Yes in general access is to complex for managers, most managers are comfortable using Excel, but are not comfortable using Access. (Although Excel is rather complex, but that does not bother the managers). Access show's a lot of possibilities I do not want to be visible for managers.

And here is the see below,

Building a specific solution is yes complex, but more important is time consuming.

It is not easy to access rows with constraints in Access, example table A has a M:N relationship with table C, table B is used to resolve this relationship. When inserting a new row in table B (a new link between A and C) it is difficult to enter the fields which have the relationship with A and C. You should be able to select them from table A and C by browsing/selecting/filtering/sorting. For tables with a few rows a picklist can be build although this still takes some effort.

The only complex side of an Access solution should be in developing it.
The developer should make forms in such a way that the users see only a simple and clear presentation of the select data they need to see.
Users themselves (your managers) should not see any buttons, data or other options other than those they need to see.
Everything else should be hidden by the developer..

Access is complex when developing an application.

Building an application takes a fair amount of time for each application.

It is not a very suitable application for inserting and modifying data.

I am looking for a general application or for an application which can be build quickly or generated.

Those applications can use the table definitions and constraints which are allready available in the database.

The presentation can be fairly general and does not have to be fancy or adjusted specific tables.

Building a distinct application for every situation would take to much effort.

A specific solution must be ready or build in a few hours.

Tables can be presented in any grid form. No specific requirements for that. Adding a row should be easy even if that row has constraints with other tables. Changing a row should be simple as wel, also if the 'linking' fields are changed.

Thanks for your time and solutions.

And I am welcoming suggestions and/or solutions,

Ben

Andrew G SSChampion Points: 12809 More actions · Answer 5

I dont fully understand the requirements or problem from what you've said.

If there's multiple copies of spreadsheets floating about, why not use Sharepoint to store them which includes version history etc.

If the problem is that managers need access to a single point of truth for data, then you need a database, or a data mart. In which case, data is going to be recorded in a structured format through an application which sounds too restrictive for your requirements. Is it structured or unstructured data that you want to store?

ben.brugman SSChampion Points: 13369 More actions · Answer 6

foxxo (4/16/2013)
If the problem is that managers need access to a single point of truth for data, then you need a database, or a data mart.
Is it structured or unstructured data that you want to store?

The data is structured.

The technical staf uses SSMS for inserts and mutations. SSMS is general and uses info from SQL-server to insert and mutate, for example using the "Edit Top 200 Rows". We do not want to provide database access by SSMS for the managers. Also we want that the user gets some help when there are relational constraints, for example with a pick list for smal related tables or with a 'browsing' mode for the larger tables.

Ben

Sim-473257 Mr or Mrs. 500 Points: 532 More actions · Answer 7

I see where you are coming from now.

You just want to have an off-the-shelf front end, point it at your database, and hey presto !

All I can say is "Good Luck" !

There are 'generic' apps out ther that claim to do some of this, but Access is one of them !

My main concern here, is your comments about Access.

And I dont' want to offend here, but in order to help you, I must point out that it appears that your understanding of 'how things work' needs building on before going any further.

If you are using Access and still having multiple copies of data, then you are doing something terribly wrong !

If the users are being faced with complex screens, then you are not using Access correclty !

Instead of running away from this particular problem, I personaly feel that you need to face it and overcome.

If you can't do it in Access, purely for the reasons mentioned, than you will only struggle going forward, and you will not be in a position to make a wise choice of other software to use in place of Access.

storing data in Access leads to copy, copy and more copies

No, No, no !!

Whatever system you use, be it Access or other, you should have ONE instance that contains only data objects. All your data lives here in ONE place, ONE copy. This the 'Back end'

You then create another instance with your forms and reports - no data!. This is the 'Front End'

You then create data links between the Font & back Ends.

When you open a form on the FE, data is fetched accross the network from the BE and displayed in the form.

Your user interacts with the data, and any changes/additions get sent back to BE and stored there.

No data remains in the FE.

The only 'copies' that should exist, are that each user will have a copy of the FE, which is a copy of the forms only, not a copy of data.

Access has a large menu system and is not geared towards input, but is geared towards a lot of other functions.

Again "No" !

When you create forms, you design the forms to show ONLY the fields, buttons and menus that you explicity want the user to see and use.

All other menus, buttons, superfluous data, functions - are hidden by the person making the form.

You just have to use it right !

Seriously, hit Google, buy a book, take a course and learn how to use it properly.

I now create my Front Ends in ASP, C#, Excel, webservices ... BUT, it is only by first getting to grips with the basics on an 'easier' platform like Access that made all that possible.

If you try to go for another option before getting the basic conecpts worked out, you will be just hiding the problen under the rug.

ben.brugman SSChampion Points: 13369 More actions · Answer 8

Sim-473257 (4/16/2013)
I see where you are coming from now.
You just want to have an off-the-shelf front end, point it at your database, and hey presto !
All I can say is "Good Luck" !

Thank you,

There are 'generic' apps out ther that claim to do some of this, but Access is one of them !

I do not consider Access a rapid application development (RAD) tool.

storing data in Access leads to copy, copy and more copies
No, No, no !!

Some years ago storing data in SQL-server was far superiour to Access and I think it still is.

It is possible that this has changed over the years, the engine in Access is (almost) the same as the engine in SQL-server. But Access storage is still file based (I think) and making a backup of a Access database I think still consists of making a file copy. (Or am I wrong about this?).

So I still hugely prefere SQL-server for storage. We have SQL-server available so there is no problem.

Access has a large menu system and is not geared towards input, but is geared towards a lot of other functions.
Again "No" !

For using Access as a frontend, I have to re-evalute this. I still do not consider Access to be a rapid application development tool. But maybe I am not using Access correctly.

Last time I used Access for something similar, it took far longer than I actually want to spend now on each application.

But here is a direct question (this is my main problem with Access):

Use case:

A user is inserting a row in Table A, table A has a relation with table B. Field X references an identity field in table B. How is the user going to select the correct row from table B, and if the row does not exist how is he going to enter the row in table B? And after that get the identity in field X of Table A.

I do not see how this Use case can be solved in Access.

But I might be missing something, Access has improved over the years.

At the moment I am also looking into:

Microsoft LightSwitch

QueryExpress

Mini SQLQuery

SimpQL

But maybe there is no of the shelf solution. But that's why I am asking the question, it is better to ask this before starting a project than discovering after a project that the project is redundant.

Thanks for your effort, I will have a new look into Access,

Ben

haiao2000 Hall of Fame Points: 3734 More actions · Answer 9

ben.brugman (4/16/2013)
But here is a direct question (this is my main problem with Access):
Use case:
A user is inserting a row in Table A, table A has a relation with table B. Field X references an identity field in table B. How is the user going to select the correct row from table B, and if the row does not exist how is he going to enter the row in table B? And after that get the identity in field X of Table A.
I do not see how this Use case can be solved in Access.
But I might be missing something, Access has improved over the years.

I would like to reiterate someone's opinion up there mentioning that you must have done something terribly wrong. The problem you mentioned up here isn't Microsoft Access problem, it is the problem with your application architecture, another word, whoever designed those front-end forms using MS Access didn't design them correctly.

If user try to enter a row in table A, then he should be forced to select a corresponding item (dropdown list) from table B. if the corresponding value does not exist in table B, your application should have some interface that allows user to enter that value in table B first. Once a row has been inserted in table A successfully then user can get the Identity of inserted row...

It seems what you really need is a real VBA programmer and not another form of software =). Hire me and I make sure it is done correctly for you =)

x SSC-Insane Points: 23660 More actions · Answer 10

this is a fairly interesting topic!

However, what you're looking for might not exist in a perfect form. What you seem to be asking is for a tool that allows management types to create nice normalized applications, but in thinking and experiencing situations like that, management will probably go with what they do in spreadsheets, enter data in ways that look obvious to them but are in reality not great candidates for sturdy databases.

I remember during my education oriented coding days, a user had created an "app" in msaccess that had students and courses, the first column was the student's ID, maybe another or two columns for the students demographics, and remaining columns for course entry. Obviously a normalized study of this would result in two (OR MORE) tables, but this doesn't initially seem obvious for whatever reasons.

I just think a "wizard" approach to letting users create nicely normalized table structures would be nice, but I also doubt it exists in a form suitable for what you're asking. Microsoft Lightswitch however still seems to be a decent candidate for the job of getting "anywhere close" to this. MS access is nice but its use might not result in the sort of setup you as an dba type fellow might be interested in.

edit: I meant to type two (OR MORE) tables

Chrissy321 SSCoach Points: 15763 More actions · Answer 11

Access has had master/detail or parent/child forms since at least Access 97.

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

Hi...at the moment I am not at all clear what the "data" is ...it lives on SQL...but how did it get there and what is it being used for?

Is this a a third party application you are trying to build additional user interfaces for....or build a set of structured reports off?

or is it something you are already doing yourselves in your own "app"

I would be very wary of building any user interface where you allow managers to edit/create/delete any data at all....unless you have a lot of previous experience in the product you are using and have a full understanding of how the underlying data "hangs together"..

you mention you have a "technical team" that use SSMS...what are their views on your proposal?

fyi.....i have built add ons to exg 3rd party vendors based on SQL...with an Access front end...but nearly always as "read only". why "read only" ...cos I had no idea of what triggers / prequisite info etc was required or what actaully ran under the hood...

I have also built Access front end to our own SQL dbs...where we were in total control of the database....and allowed insert/amend/delete......these type of front end apps do take longer to build becasue you need to build in much much more error handling / data validation / transactional roll backs etc.

perhaps you could share some idea of the "data" you are talking about..and some sample SQL tables that explains some more.

Kind regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 13

Ben, in my opinion there are no Rapid Application Development tools. Just ones that people are the most familiar with so they're quick using it, and pre-built components that automate pieces for you that other people developed. Those pre-built components almost always require tweaking.

A mistake a lot of people make when dealing with MS Access is forgetting that it's two applications in a single box. It is weaker than SQL Server in a number of ways, one of the primary ones being concurrency concerns. It is also a crapton cheaper for TCO.

The JET engine (and whatever else they've added to it, like MSDB) is the database engine. There's also the forms/reports tier, which is a front end design component. You can develop in Access, move the tables to SQL Server as an Access Project, and still keep the Access Front End. There is little difference to the forms/Reports as to how you do that, just to the views (usually passthrough queries) that they're using and you have to manipulate data differently when sending it through the ADP to the SQL Server instead of using direct DAO calls.

However, none of this means anything if your managers are creating tables. MS Access needs to be treated like any other significant project. Design specs laid out, expectations spelled out, data modeling performed, access points (not MSAccess) for the data described, and then you start laying out your data tier. Once that's done, you start laying out the front end tier in forms/reports for your users. You build search screens. You build dropdowns and the rest.

You develop. Not managers stuffing crap in willy nilly, you need forms. You develop. Managers don't access the data tier, you create ETL components for them to load data in bulk. You develop. I'm repeating myself, but I'm hoping it's helping or I just come off looking like a shmuck.

External to IT noone should CARE how you designed and built the Access DB. I used to lock off everything but the front end screens by deploying a front end to their system with a shared file on another machine. If you go to MS Access and it's not for personal organization but for doing a shared piece between people, you need to develop an application, not a database.

Otherwise, you need to look for a vendor application that has developed it for you already and can tweak it to your exact needs. That takes time and beta testing as well.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

ben.brugman SSChampion Points: 13369 More actions · Answer 14

Hello all,

Thanks all for your enthousiasm with answering. Thanks.

Where to store the data is not an issue, we have choosen for SQL-server because that is an exelent place to store data.

The model (logical, physical) will be prepared by specialists, offcourse instructed by the users needs.

Depending on the needs queries can be build for specific purposes, reports can be made using ssrs.

Views can also be used to show the data in a more logical form.

We are only missing a CRUD (Create Read Update Detele) program for the users.

Users can only do this to the data (they have only read and write permissions to the database).

Offcourse users are forced to respect the constraints of the database. (relational or other constraints).

But we do not want to force the users in which order they are going to aproach their task.

If they are going to enter new row (say a new product) and want to give the product a color which is not available yet in the color table they must be able to add the color to that table after they have started inserting the new product. Yes the users are aware that there are relational constraints, but they do not have to plan their tasks so that it suits the datamodel, the application has to do that. Offcourse they can not finish entering the new product if they the dependencies are not resolved.

And yes I am aware that "Access has had master/detail or parent/child forms since at least Access 97.". But that is not the issue, the issue is that it does not help the user entering them. It only forces the user to respect the relation.

We want that the user can use his own workflow, for example, the user decides that a new product is needed. He is going to enter the product, but during the entering of the product het discovers that he has to select a color that does not exist yet. So the application has to help the user to add (or select) the color and then continue what he was doing. The user does not want to make a 'shoppinglist' before he starts but should be able to make decisions when he reaches the the point where the info is needed.

The technologie should not dictate the order the user is working in. But yes the user should supply the extra information if this is needed.

If a user wants to enter the color first, this is offcourse not a problem he is allowed to do that, but he is not forced to.

For master detail relations where the number of masters is limited, a picklist (dropdownlist) can be used, but if the number of rows in the master is large there should be some way to do the browsing more efficient.

The interface has to be functional, not fancy, so if all tables are shown in a similar way, the user wil get used to that.

I think that a generic program for this is possible, and that is the reason that I am searching the internet for this, because if it is allready there somewhere that would be a lot better than invent the wheel myself.

Sorry that I have repeated myself a few times above, and I tried to individualy explain and anwser the other questions and remarks below.

Everybody many thanks for your input, it helps me to formulate the problem.

And if what I seek is not available yet, at least I have searched for it.

And on the subject of using Access as Front-end, I am missing some possibilities in Access. But as a number of you remarked Access might be the best solution within the time, budget and quality needed. (And I haven't worked with the most recent versions of Access). The question of how to help the user to respect the relation with a large master table is still not anwsered. (And the user needs definity some help with this).

Thanks

ben brugman

-- ben ben:
-- neb neb.

haiao2000 (4/16/2013)
ben.brugman (4/16/2013)
But here is a direct question (this is my main problem with Access):
Use case:
A user is inserting a row in Table A, table A has a relation with table B. Field X references an identity field in table B. How is the user going to select the correct row from table B, and if the row does not exist how is he going to enter the row in table B? And after that get the identity in field X of Table A.
I do not see how this Use case can be solved in Access.
But I might be missing something, Access has improved over the years.
I would like to reiterate someone's opinion up there mentioning that you must have done something terribly wrong. The problem you mentioned up here isn't Microsoft Access problem, it is the problem with your application architecture, another word, whoever designed those front-end forms using MS Access didn't design them correctly.

-- ben ben:

The front end forms have not been designed yet, I have a use-case. The working order can be determined by the user and is not dictated by the application. So there is not front-end forms for this at this moment. Using MS Access in previous project had the shortcoming that they dictated the working order.

-- neb neb.

If user try to enter a row in table A, then he should be forced to select a corresponding item (dropdown list) from table B. if the corresponding value does not exist in table B, your application should have some interface that allows user to enter that value in table B first. Once a row has been inserted in table A successfully then user can get the Identity of inserted row...

-- ben ben:

If the user wants to inset the value in B first he is allowed to, but for a user experience it would be a lot nicer if he is not forced to do this first.

-- neb neb.

It seems what you really need is a real VBA programmer and not another form of software =). Hire me and I make sure it is done correctly for you =)

[/quote]

-- ben ben:

Thank you for the offer, but telling a user; that the user that he has to enter the value in table B first, where the requirement of the user is that he wishes he can do this on the fly means that you misunderstood the requirements of the user.

So I am passing on your offer.

-- neb neb.

------------------------------------------------------

patrickmcginnis59 10839 (4/16/2013)
this is a fairly interesting topic!
However, what you're looking for might not exist in a perfect form. What you seem to be asking is for a tool that allows management types to create nice normalized applications, but in thinking and experiencing situations like that, management will probably go with what they do in spreadsheets, enter data in ways that look obvious to them but are in reality not great candidates for sturdy databases.

-- ben ben:

The management can not alter the model, and yes they will find ways to enter data in a form where the database is not designed for.

Yes they will enter a color 2.5 liters in the color table, I do realise that that will happen, but in general is something is called a color most users will use those fields where it is designed for.

So the structure for the data is not designed or implemented by the users. The can not alter that.

-- neb neb.

------------------------------------------------------

Chrissy321 (4/16/2013)
Access has had master/detail or parent/child forms since at least Access 97.
http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx%5B/quote%5D

-- ben ben:

As said data is stored in SQL-server so that is not the issue.

The issue lies with using Access as a front end, see te introduction.

-- neb neb.

------------------------------------------------------

J Livingston SQL (4/16/2013)
Hi...at the moment I am not at all clear what the "data" is ...it lives on SQL...but how did it get there and what is it being used for?

-- 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.

Is this a a third party application you are trying to build additional user interfaces for....or build a set of structured reports off?

-- 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.

or is it something you are already doing yourselves in your own "app"

-- 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.

I would be very wary of building any user interface where you allow managers to edit/create/delete any data at all....unless you have a lot of previous experience in the product you are using and have a full understanding of how the underlying data "hangs together"..

-- 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.

you mention you have a "technical team" that use SSMS...what are their views on your proposal?

-- 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.

fyi.....i have built add ons to exg 3rd party vendors based on SQL...with an Access front end...but nearly always as "read only". why "read only" ...cos I had no idea of what triggers / prequisite info etc was required or what actaully ran under the hood...
I have also built Access front end to our own SQL dbs...where we were in total control of the database....and allowed insert/amend/delete......these type of front end apps do take longer to build becasue you need to build in much much more error handling / data validation / transactional roll backs etc.

-- 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.

perhaps you could share some idea of the "data" you are talking about..and some sample SQL tables that explains some more.

-- 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.

Kind regards

------------------------------------------------------

Evil Kraig F (4/16/2013)
Ben, in my opinion there are no Rapid Application Development tools. Just ones that people are the most familiar with so they're quick using it, and pre-built components that automate pieces for you that other people developed. Those pre-built components almost always require tweaking.
A mistake a lot of people make when dealing with MS Access is forgetting that it's two applications in a single box. It is weaker than SQL Server in a number of ways, one of the primary ones being concurrency concerns. It is also a crapton cheaper for TCO.
The JET engine (and whatever else they've added to it, like MSDB) is the database engine. There's also the forms/reports tier, which is a front end design component. You can develop in Access, move the tables to SQL Server as an Access Project, and still keep the Access Front End. There is little difference to the forms/Reports as to how you do that, just to the views (usually passthrough queries) that they're using and you have to manipulate data differently when sending it through the ADP to the SQL Server instead of using direct DAO calls.
However, none of this means anything if your managers are creating tables. MS Access needs to be treated like any other significant project. Design specs laid out, expectations spelled out, data modeling performed, access points (not MSAccess) for the data described, and then you start laying out your data tier. Once that's done, you start laying out the front end tier in forms/reports for your users. You build search screens. You build dropdowns and the rest.

-- ben ben:

As said we are using SQL-server as the back-end and do no use the storage engine of Access.

And described above, the managers can only CRUD data, not the tables.

So the application would be a CRUD only capable application, something similar but far simpler than SSMS.

-- neb neb.

You develop. Not managers stuffing crap in willy nilly, you need forms. You develop. Managers don't access the data tier, you create ETL components for them to load data in bulk. You develop. I'm repeating myself, but I'm hoping it's helping or I just come off looking like a shmuck.

-- ben ben:

Yes I am also repeating myself, it's the users data, we can not request the users to first create correct tables or CSV files and then use an ETL tool that would be working backward. If the user want to enter a new product or an anwser to a question or a question the user must be able to enter this data him or herself.

-- neb neb.

External to IT noone should CARE how you designed and built the Access DB. I used to lock off everything but the front end screens by deploying a front end to their system with a shared file on another machine. If you go to MS Access and it's not for personal organization but for doing a shared piece between people, you need to develop an application, not a database.

-- ben ben:

design and implementation is done by IT, users have only access to the data (and to the IT crowd).

-- neb neb.

Otherwise, you need to look for a vendor application that has developed it for you already and can tweak it to your exact needs. That takes time and beta testing as well.

-- ben ben:

I have never needed to tweak or test SSMS and that exists as wel.

But it is very well possible that I am seeking something that does not exist yet.

-- neb neb.