Query by Example in Visual Studio vs Access

We currently use MS Access as a front end to Oracle and are thinking on moving on to .Net as a way of creating either a web-based or desktop application.  Right now it's pretty easy to view our tables as well as queries in Access whenever we are troubleshooting a problem.  How would someone using Visual Studio accomplish the same thing?  Would you still keep using Access so that you can easily do so?  In addition, how would you go about reusing queries if not using Access.  Everything I've seen so far has to do with using SQL strings in the code for queries; so is there no simple way to save a query and reuse it in code by referring to its name?
LVL 29
IrogSintaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You would have to use the Oracle tools, whatever they are.  I think they have a free toolset similar to what you get with SQL Server Express.  For SQL Server you would use SSMS - SQL Server Management Studio.  It has a really poor QBE but a really good SQL text window and a passable diagramming tool.

I haven't used Oracle or DB2 in some years so I can't say what their current tool set is but the tool set for SQL Server could do with some "Access-like" features.

I can understand giving up Access as the FE if you need a web app but I'd think twice about replacing Access with a .net client/server app.  How much are you prepared to spend?  What extra value will you get?
rspahitzCommented:
There are a lot of tools now in VS.Net to assist with data management.  Many of the features of Access are also there for managing queries.
However, I've found that Access is generally easier to use since it's a dedicated DB tool.
To start, make a new .NET form and look next to the tools for the Data tab and see if that helps get you going.
Nick67Commented:
I am on the Access/SQL Server end of things, too.
The Access QBE is one of the finer pieces of UI in existence.
With Visual Studio, when you go to flange up a dataset, there's a GUI very reminiscent of the QBE to help you generate CRUD statements
editor.
You can generate sprocs, too.
http://weblogs.asp.net/stevewellens/automatically-generate-stored-procedures-with-visual-studio

Everything I've seen so far has to do with using SQL strings in the code for queries; so is there no simple way to save a query and reuse it in code by referring to its name?
Kindly -- but still -- ROFLMAO.

It is a very different environment, especially the web end.
The data stuff, if you're coming from Access, will be the least of your grief.
SQL is SQL is SQL.
Chances are you aren't doing anything radical yet in Access, so the VS tools will do the job.

Reuse of stuff in VS comes from creating, declaring and using classes.
That takes a bit to grasp.
You create a class that may take some inputs
In the class, a datareader grabs data from a sqldatasource, and then the class can return lots of stuff about itself to whatever instantiated it.

Very different way of thinking.
Access is procedural.  We write code to execute procedures based on UI events
VS is object-oriented.  You code objects to consume and be consumed by other objects based on the users interaction with objects you've presented them with.

The learning curve is quite steep.
I have come to despise .ToString
Dim MyTextbox as TextBox = Me.SomeTextBoxControl
Dim myString as String = String.Empty
MyString = MyTextbox.Value.ToString

Really?!?

I don't care much for
Select case Page.IsPostBack
    Case TRUE
'stuff
    Case FALSE
'stuff
End Select
either.

But if you are moving beyond Access, well, there it is.
Probably 15% of my VBA skills are applicable to ASP.Net with VB.Net syntax.

YMMV

Nick67
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jacques Bourgeois (James Burger)PresidentCommented:
You first need to download and install the Oracle Data Provider for .NET. Make sure to pickup the version that is compatible with both your version of Oracle and Visual Studio. If you use a recent version of Oracle, you might have to upgrade to a newer version of Visual Studio.

Once you have that installed, you will find tools to work with your Oracle database from Visual Studio by activating the Server Explore through the View menu. This displays along with the Toolbox. Depending on your version of Visual Studio, you will find there a button, menu or context menu that will enable you to Connect to Database. From there, you can navigate directly to your Oracle database or to your Access file.

The tools provided either with Oracle or Access are not as complete as those provided for SQL Server, but they give you the basics and can complete each other, so I suggest that you connect to both and see what is available. Simply right click on any element to see what is offered.

how would you go about reusing queries if not using Access. Simply move them to Oracle, either as views (for simple SELECT statements) or stored procedures (for more involved SELECT statements as well as for UPDATE and INSERT queries). This means that you know a bit about SQL, because you do not have something as simple to use as the Query Designer in Oracle.

You will have to learn about views and stored procedures. You can copy the SQL from Access as a starting point. For simple things it sometimes works as is. But even if SQL is a standard, all databases have their own version, and Access might well have the less standard of them all. As soon as queries become a little more complex, such as queries that require parameters, you will have to modify the SQL to adapt it to Oracle. But you can learn SQL bit by bit during that process.

As for using them in your code, you can call stored procedures with code similar to the code used to run SQL strings. You simply have to change the CommandType property of the Command object that you use to StoredProcedure. There is something similar if you want to still use your Access queries, but through the OleDBCommand object instead of OracleCommand.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaAuthor Commented:
@Pat, the extra value we are hoping to get is the ability to have a front end on mobile and remote devices without having to RDP in.  However, I'm still uncertain of the ROI because of how much more time would be needed compared to the ease developing in Access.

@rspahitz, thanks for the input but unfortunately the Data tab isn't what I was looking for.

@Nick, "Chances are you aren't doing anything radical yet in Access"    Actually, I do quite a bit of radical things in Access since I've been using it since 1997 :-)  Thanks for your thoughts on the differences between Access and VS and also for the sproc link.  I'm just wondering now, it looks like you can use the wizard to bring up a Query Builder to create the sproc, but will you be able to go back and modify the created sproc in the Query Builder?

@James, Thanks for your detailed answer.  Let me just ask... if you need to modify a complex query or stored procedure, and you want to do it using a query designer, is the normal process for a VS developer to just open up Access (or Oracle) and use the Query Designer there?

Ron
Jacques Bourgeois (James Burger)PresidentCommented:
For Access yes.

For Oracle (that I do not use) I do think so, but do not expect a Query Designer as powerful as the one in Access. One is expected to know SQL and write SQL code directly.

If you were using SQL Server, you would be able to do it either in Visual Studio or Entreprise Manager, the default tool to work with SQL Server. They have simple designers, but you cannot do more than simple queries in it. As for Oracle, it is expected that you know SQL when you get at that level.
PatHartmanCommented:
ability to have a front end on mobile and remote devices without having to RDP in
Have you considered Citrix?  It is the best way to share Access apps across the internet.  I have one app that is hosted in Farmington, CT that is used by users from San Francisco to Paris via Citrix.

You can also gain a lot of traction by upsizing from Jet/ACE to SQL Server and just duplicating a limited feature set for web access.  That leaves the bulk of the app intact for local users or rdp access and provides some access for remote users via web pages.
Nick67Commented:
@Ron
Actually, I do quite a bit of radical things in Access since I've been using it since 1997 :-)  
I know it's not your first rodeo, but the big db engines (SQL Server, Oracle, Postgres) are capable of things that you just cannot do with Access SQL, and sometimes are even hard-pressed to do with VBA and recordsets.  There's just way, way more power available within the engines themselves.
For SQL Server--all these are available, and generally, Oracle and SQL Server as fierce competitors usually have the same feature set.  PL/SQL and T-SQL have many similarities as well as subtle differences.
But then, if you are an Oracle shop already, you probably know many of these things.

Table-valued variables https://msdn.microsoft.com/en-us/library/bb510489.aspx
Over and Partition http://www.midnightdba.com/Jen/2010/10/tip-over-and-partition-by/
Row_Number https://msdn.microsoft.com/en-us/library/ms186734.aspx
Returning multiple result sets in a single query
And flow control within a query itself using CASE and IF THEN
And that's the stuff I can understand coming from Access.
Then there's the stuff that's completely over my head.

Oracle has tools similar to SQL Server Management Studio, and like SSMS, they are free.
http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

If you haven't already, I would ask Questions with Oracle as the primary Zone and .Net as a secondary.  Leave Access out altogether.  You'll get an entirely different set of responders that way :)
IrogSintaAuthor Commented:
James, Nick, and Pat.  Thank you for all your answers.  Quite enlightening.  Nick, I changed the zone to Oracle and .Net as you suggested but I'm not sure if there would be anymore responses since there are already a number of comments on this thread.  I'll leave it open anyway for a couple of days to see if anyone else would chime in.

Ron
slightwv (䄆 Netminder) Commented:
I would move to an SOA architecture.  GUI calls a web service that talks to the database.  If your database is close to a web based front end, you are asking to be compromised!

We have an Internet facing .Net website that makes web service calls.  The web service only has access to database stored procedures/functions.  This way, we only allow minimal database access.

As posted above, yes, we use ODP.Net.  Then ONLY way to get .Net to talk to an Oracle database.  We recently moved to the Managed Provider and are happy with it.
IrogSintaAuthor Commented:
Thanks, everyone for the advice.
Nick67Commented:
Best of luck!
.Net is a steep learning curve, but MS is just never going to give us VBA-on-the-Web so there's not much choice.
I do hope you found what we had to contribute helpful!

Nick67
IrogSintaAuthor Commented:
Thanks Nick, I did find everything useful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.