Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Is there a better way to insert data than Access? Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 5:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:18 AM
Points: 246, Visits: 1,167
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.


Post #1442268
Posted Monday, April 15, 2013 6:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 7:35 AM
Points: 20, Visits: 92
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 ...

Post #1442278
Posted Monday, April 15, 2013 7:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:18 AM
Points: 246, Visits: 1,167

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
Post #1442296
Posted Monday, April 15, 2013 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 7:35 AM
Points: 20, Visits: 92
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 :
[color=#0000ff]With access a solution can be build, but is often to complex, powerfull and has too many functions. [/color]

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




Post #1442322
Posted Monday, April 15, 2013 6:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:18 AM
Points: 246, Visits: 1,167
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
Post #1442549
Posted Tuesday, April 16, 2013 1:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 1,153, Visits: 1,595
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?
Post #1442616
Posted Tuesday, April 16, 2013 2:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:18 AM
Points: 246, Visits: 1,167
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

Post #1442646
Posted Tuesday, April 16, 2013 4:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 7:35 AM
Points: 20, Visits: 92
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.

Post #1442680
Posted Tuesday, April 16, 2013 8:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:18 AM
Points: 246, Visits: 1,167
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.



[quote]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

Post #1442776
Posted Tuesday, April 16, 2013 11:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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 =)
Post #1442871
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse