Professional Database Developers

I'll toss in my two cents' worth on this one.

I have used Access many times for many things and have absolutely no problem with it. The problem comes most often for people who THINK they are database designers because they got Access to work for something. They THINK they are programmers because they got some trivial piece of code to work. They think Access must be easy because it is part of Office and everyone knows how easy Office can be.

This is not intended to be an elitest remark, but it should be treated as at least a cautionary one: Access goes a LOT deeper than what people see. For that matter, so do Word, Excel, and PowerPoint. The ones who have scratched the surface and stopped there are content; they will continue to be happy. But for some, they cannot leave "well enough" alone. The real problem is simple: The more ambitious you get, the farther Access can take you until a programming version of the "Peter Principle" traps you. I.e., you can delve to the level of your own incompetence.

If you look at what Access allows you to do, you realize that to use it wisely you must be a graphic designer. You must understand asynchronous (and synchronous) interrupts (in the form of events). You must understand SQL. You must understand set theory the first time you have any kind of complex JOIN query or junction table. You must understand security issues the first time you deal with a multi-user DB. You must understand object-oriented concepts whenever you have to use VBA to control object attributes. You must understand relational database theory.

Oh, by the way - you ALSO need to know everything about the problem you are trying to solve. In other words, an expert in TWO fields.

Wait, you say, isn't that true of every shop? No. Companies that have a separate IT or DP shop get in-house consultants to handle the messy details for the products they prefer - like SQL Server, ORACLE, FOCUS, etc. But the odds are that those consultants are immersed in the big products so sneer at the little products.

Unfortunately for them, the power of Access that makes it so popular is two-fold. (1) Rapid Application Development. (2) Hidden power to take you a long way towards your final goal - maybe ALL the way for something that doesn't exceed size requirements for an .MDB file. These days, the gigaherz boxes with 10,000 rpm, 100+ gigabyte disks can handle a very huge stand-alone DB so quickly that you almost wonder how they could work so well.

As many others have mentioned, Access is a cheap way to start your project. It is a great springboard to the final implementation when you were just using Access for prototyping.

Our shop uses ORACLE as its final solution, but many times we have used Access constructively to get from point A to point B with very few stops along the way. In case you were wondering, we use ORACLE because our DB way exceeds the Access size and concurrent user limitations.
 
When you prototype with Access, do you migrate data over to ORACLE or just start a new database based on successful prototype?
 
Do IT professionals and database developers use Access and VBA to make their databases or do they only use knowledge of SQL and Server 2000 etc?

You know… I am going with the point of I think Access is actually more functional with IT or IR departments than database developers (except for prototyping).
 
When you prototype with Access, do you migrate data over to ORACLE or just start a new database based on successful prototype?

In our case, ORACLE requires import/export operations through text files, for which use the old standby "comma delimited" format. But that's just the version of ORACLE and the mind-set of the DBAs who are running it. It could at least in theory go either way.
 
When you prototype with Access, do you migrate data over to ORACLE or just start a new database based on successful prototype?
I give the "whatever" DBA a printout of the table schema and let him create the tables in whatever database we are moving to. Sometimes, the client has tools such as VISIO that can convert a schema from Access to Oracle and we use that. To get the data loaded, I usually link the server tables and run append queries.

My applications are all designed to work with a client/server back end even if the BE is Jet. That way 99.99% of the time, nothing has to change to convert to a different back end server.
 
Doc Man

I think you summed the situation very well indeed.

Lots of people "Think" they are Developers. I believe I am a Developer because of my knowledge of the theory, SQL,VBA Security etc but I am also aware that there is still more to learn.

As a Problem Solver I use my knowledge to provide Solutions to the best of my knowledge but there is still a great deal to learn

len
 
Pat Hartman said:
My applications are all designed to work with a client/server back end even if the BE is Jet. That way 99.99% of the time, nothing has to change to convert to a different back end server.

A very sensible thing to do.

I'd like to know if anyone knows of a good link enumerating what needs to be done to ensure compatibility for moving from desktop database to server/client DBMS? All I know is that generally Jet/Access does early binding which may not always fly with SQL's late binding and am sure there is more to the big picture.
 
There are articles in the KB and MSDN library on optimizing Access for client server.

The biggest change you'll need to make to your present style is to base all forms and reports on queries that include selection criteria. Most Access developers use tables or unrestricted queries as the RecordSource for their forms. They then filter the data if they want to reduce what they are looking at. The way to take the most advantage of having a database server is to filter at the server level and only bring over the records you actually need. That is done by adding criteria to your queries.

If you have an application built in the traditional Access style and convert the be to SQL Server, Oracle, etc. without modifying anything, your application will likely run slower with the ODBC back end than with the Jet back end. Plus the DBA will hate you because of what you are doing to his precious database server.
 
Currently. I am using Access to extract data from a large Oracle database. Some of the queries take days to run. I run the big ones after hours and the results are collected in an Access table. The data is then passed along to my users where they will further analyze it with Excel and/or SPSS.

In the past, I have used Access for small multi-user applications. Between 3 and 6 users. Works very well and it is quick to set something up.

My favorite Access use is desgning and prototyping databases. It lets you play around and you get a pretty good tool to setup and display relationships. I have built MySQL and PostgreSQL databases after designing them on Access. It saves an awful lot of SQL coding. And I print out the relationship diagram and use that as a roadmap. I have yet to see a better tool for doing this kind of work.
 
If the queries are taking days to run, you might consider determining why. I've run queries that retrieve millions of rows from a server and they take minutes to run.
 
If the queries are taking days to run, you might consider determining why. I've run queries that retrieve millions of rows from a server and they take minutes to run.

It takes so long to run because I am doing a group by on an un-indexed column. Nothing I can do about it. The design of the database is out of my hands. I have tried extracting the rows from the table with the un-indexed column and doing the group by locally. It still takes a very long time.

I am evaluating a new ODBC driver but in evaluation form, it does not allow pass through queries. When I have finished with the evaluation, I will try pass throughs and see if that works any better.
 

Users who are viewing this thread

Back
Top Bottom