September 9, 2009 at 9:16 pm
Comments posted to this topic are about the item A Good Word for ODBC
Best wishes,
Phil Factor
September 10, 2009 at 4:38 am
Thanks Phil, there I was thinking I was a decrepit old dinosaur sticking with my completely functional ODBC connections and eschewing Linq and suchlike on the basis that I could do everything I needed to.
These youngsters and their bleeding edge complexity, eh?
September 10, 2009 at 7:27 am
I've relied heavily on ODBC, combined with MS Access for the past 10 years. At my last company virtually all the reporting out of the Syteline ERP system was via a pair of MS Access databases that connected through the Progress DB ODBC connection. This was a small company where the number of concurrent users in Access DBs was never an issue.
Currently I use MS Access and ODBC to connect to a Syteline ERP System running on SQL Servers for custom and adhoc queries and reports. I have found this combination to be extremely fast for both development and execution.
Unfortunately, our corporate IT department refuses to allow anyone else to use these tools. They restrict users to only the existing ERP system reports and a limited number of custom queries written in a product called IEV. Their apparent reasoning is "ODBC puts too much workload on the SQL server."
However, I find this hard to believe. I recently created a set of Access/ODBC queries that extract/merge/extrapolate data to an excel spreadsheet for each of 3 companies. It takes less than 30 seconds for the queries to run and create the spreadsheet for 1 company, so all three are completed in less than 2 minutes.
Prior to my creating this application, users had to run an IEV query that only dumped data for each company (about 6 minutes to execute for a single company), then spend an hour or two merging and extrapolating data.
I have read several SQL related posts that pretty much state "never allow anyone to connect to a SQL server using MS Access and ODBC.
However, I have never seen any explanation as to why.
Can someone please explain in nice techincal terms why using a tool that ties up a SQL server for over 18 minutes (IEV for 3 companies) is better than using a tool that ties up a SQL server for less than 2 minutes (Access via ODBC)?
September 10, 2009 at 7:57 am
Good old SQL Query Analyzer that shipped with SQL Server 2000 uses Microsoft's ODBC driver to connect. The funny thing is that you can use Query Analyzer to connect to SQL Server 2000, 2005, and 2008. I actually prefer Query Analyzer over the new management studios for 2005 and 2008.
September 10, 2009 at 8:08 am
I always liked the abstraction of using a DSN from the actual connection that ODBC provides, and that you could easily deploy a DSN to a client computer in a file. For SQL Server, you can setup aliases in the SQL Server Configuration Manager for abstraction, but that is propriatary to SQL Server.
Speaking of complexity in connection strings, OLE-DB connection strings used by ADO/ADO.Net are pretty horrific so I don't see any improvement there. I think the bigger problem with ODBC was older applications accessed it through RDO and DAO objects. When Microsoft developed OLE-DB and ADO, they shoved ODBC into the background by making an extra layer of complexity to the connection. Instead of (propriatary driver -> ODBC -> RDO or DAO) it was now (propriatary driver -> ODBC -> OLE-DB provider for ODBC -> ADO). That's when ODBC started getting a bad name, and as more database vendors build their own OLE-DB providers, ODBC beame less relevant. I've noticed that many non-Microsoft products tend to support ODBC better than OLE-DB, especially in the open source world.
September 10, 2009 at 8:15 am
bob.willsie (9/10/2009)
...I have read several SQL related posts that pretty much state "never allow anyone to connect to a SQL server using MS Access and ODBC.However, I have never seen any explanation as to why...
The biggest problem with that combination was always on the MS Access side, not ODBC. Many people who created Access front end applications that used ODBC data sources didn't know to use pass-through queries. If you don't use pass-through queries then Access had a tendancy to try to copy the entire table from the server to the application and then run the query locally. If you use Access with pass-through queries or with Access 2000 and later create an "Access Project", then you are using the back end database's query engine, not the local Access engine.
September 10, 2009 at 8:29 am
Chris;
Maybe your getting to what I need to know. And you have given me a chance to show my ignorance.
I typically write a string of queries that extract small chunks from the server and store them locally, then merge, extrapolate, etc. locally. I found this executes much faster than trying to do lots of complex joins and subjoins all in one query and passing that query to the server. Typically I never join more than three tables if at all possible.
Are small, highly targeted queries what you mean by "pass-thru" queries? If not, please explain.
I must confess that on the occasions I've pulled down the full contents of one or more tables, but if I do that I download the complete table(s) to a local table in my Access DB, then mix and match locally to my hearts desire. I usually on do this with extremely large historical data that I know I'll be thrashing quit a bit.
September 10, 2009 at 8:59 am
Depends what you're trying to achieve Bob.
A pass through query will just literally pass the query string to the DB, execute it, then return the results. Lord knows what Access attempts to do architecturally if you do local queries on linked tables, maybe someone else on the forum knows the answer.
There's nothing intrinsically wrong with copying a whole table or a chunk of data to the local client when you need to do further data manipulations on it - indeed it might save server resources when you have some serious number crunching/transformations to do on the data that is being returned. Having said that you should bear in mind that the SQL server is going to be a lot better at that number crunching than Access and the timing of these queries may well be important in terms of when the business needs the server performance (I run some jobs against stored procedures at night to get around this).
I just take everything on a case by case basis, if a query is putting a load on the server then see what you can do to calm things down - either by cutting the data you're bringing over (nothing worse than SELECT * when you only need a few columns), or moving your basic queries into views on the server. If server performance is more important than client performance then you're probably better off taking the data in a relatively raw form and crunching it on the client.
It sounds to me like you're not the DBA at your company, and DBAs can be pretty prescriptive where their servers are concerned, you really need to make a strong business case for running queries against the server. Personally I would be OK letting people have access to Views or Stored Procedures I had created myself if they approached me and told me what they wanted (so in your case each View would be your basic three table joins which I'd allow you to download into Access to work on further) but I would not allow ad hoc connections to the server, I'd lose control of database performance and security, and I am responsible for that.
September 10, 2009 at 9:20 am
This is an excellent editorial and exactly right on point. ODBC enabled many coders of my generation to make great software systems and in that, a great living. Even better ODBC offers a flexibility not found easily today. In the 90's I worked for a company where we wrote software in Visual FoxPro, and connected to any database that had a solid ODBC driver; SQL, Oracle, DB2, and more. It was "cool", flexible, and it just worked.
As well, down in the "trenches" ODBC was and is overall very easy to work with. You could "ignore the driver behind the curtain" and depend that data would flow in your app as needed. Sure, there were glitches but...
These days we do .NET and for all the wonder, hype, and ill-percieved "must have" of .NET, working directly with data is frankly woefully lacking. Especially so compared to the ODBC days. Data is astounding slow in operations that would otherwise be astoundingly simple, and one of my own biggest complaints is that you would think SQL and .NET would work well together - after all being both MS products. Not so, in fact Microsoft has miles and miles to go in the .NET world to even catch up to the ease of use and performance of ODBC.
Every year we seem to make programming and data management more and more complex with no real gain or benefit. Microsoft's long played-out excuse that this must be the case to support web apps is just simply ridiculous. As well, each year we see old technologies repackaged and then released as something "new", or even more insulting, "revolutionary".
Whatever happened to the KISS principle? ODBC was and is that. It works - so naturally, it will be discontinued and then re-released in another 10 years and sold as "new and revolutionary".
September 10, 2009 at 9:29 am
Thanks for the info Richard.
You are correct, I am not the DBA, I am the lead system analyst for our division.
Perhaps the issue is the difference between static information needs which views and stored procedures handle nicely, and ad hoc needs.
Because many ERP systems come with limited or very generic queries and reports, it's been my experience that first I have to build ad hoc queries and reports to fill an immediate need, about 50% of which may get turned into static information needs.
Our IT department has set up a "reporting" server, however it's not working out very well because connections get blown away every 30 minutes when the server gets updated.
As far as security goes, I'm using strictly read only connections with no write permissions.
I am however very concerned about performance issues, as I wrote in my original post. And, I admit to having inadvertently written a "couple" of open ended queries in my past. However, those are few and far between, and quickly killed by the DBA once I realize what has happened.
Perhaps I am too "old school". I take the view that if a user has permissions to the data they should be able to access it and manipulate it with whatever tools are at their disposal that can make things happen the quickest and easiest.
Unfortunately, it appears to me that the move to SQL server and similar products has resulted in the exact opposite happening. We appear to have traded utility and functionality for security.
September 10, 2009 at 9:56 am
Blandry;
I think your honing in on my complaint.
It takes our IT department days, weeks, and months to meet user needs because they are forcing themselves to use overly complex tools for relatively simple tasks. And, more importantly, they usually have to outsource the solution because they no longer have the needed skill sets internally.
In one instance they even told a user that it was impossible to meet the users needs because it couldn't be done in with their "tool set." However, it took me less than 4 hours, including several "is this what you want" sessions with the user to put it all together in Access using ODBC.
BTW, I recently ported an application from an AS400 to MS Access in several days, then spent the next 6 weeks on and off trying to build it in VB.NET linking to a SQL server. Because, "VB.NET and SQL Server are our corporate standards."
I have since run out of time, and given up. We are turning it over to an outside consultant that will make a nice tidy sum of money when it is all over and done.
However, we will have a "web based solution" when we are done. Although 100% of the 30 or so users are all physically located within a few hundred feet of each other on the same internal network.
What a shame that simple, elegant solutions such as ODBC are no longer the accepted norm.
September 10, 2009 at 1:57 pm
Having said that you should bear in mind that the SQL server is going to be a lot better at that number crunching than Access
This is one of the most arrogant, presumptuous and unfounded assertion I ever read.
September 10, 2009 at 2:28 pm
This is one of the most arrogant, presumptuous and unfounded assertion I ever read.
'Ere that's a bit harsh! Were not in one of those NET.ANGST forums you know.
Best wishes,
Phil Factor
September 10, 2009 at 2:32 pm
rf44 (9/10/2009)
This is one of the most arrogant, presumptuous and unfounded assertion I ever read.
Strong words indeed...would you care to elaborate....maybe some examples.
..
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 10, 2009 at 2:34 pm
Agreed. These products each have their time and place for use.
I for one wouldn't want to try to run ERP for a manufacturing firm on Access unless it was a pretty small firm with only one or two products...
I suspect running MRP in Access would be futile in any other circumstance.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply