Professional Database Developers (1 Viewer)

[123James]

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?

 

[Lister]

Not sure where you are going with this. I have and am, developing db's in MS Access/VBA for commercial use. But I also work with project teams that use SQL Server, Delphi, VB.net, C#.net, ADO.net, XML etc...

So what is your Question?

 

[Uncle Gizmo]

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?

A very interesting question James, and one I feel able to provide a view on because of a presentation by Luke Chung At the UK Access users group which I attended.

I T. professionals have a problem with MS Access as they do not consider it a professional solution. This misconception arises from the fact that MS Access is part of the Microsoft office professional edition, and consequently every Tom Dick and Mary comes up with a database which in many cases works fine, however when they develop problems the database gets taken to the IT department! Therefore in the eyes of the IT professionals MS Access is often perceived as a problem. Some IT departments solution is to ban MS Access!

The fact is MS Access is a brilliant tool for 90% of business database problems. It is cheap, return on investment in terms of the actual software Cost/Time and the development of the database is excellent. There are seldom situations where the number of users and the volume of records exceeds MS Access limits. When you do start hitting these problems, then you have a very good model of the process, ready to move into MS SQL server.

I think IT professionals would prefer to use MS SQL server because it is secure, they have control over it. however if the cost in terms of money and in development time are taking into consideration, it is very often much better to do a project in MS Access.

You can download the PowerPoint slides of Luke Chung's presentation here:

Where Access Fits in the Overall Database Strategy of an Organization - Luke Chung, FMS Inc

 

[123James]

Thanks Gizmo, great answer!

The reason I asked was that I am currently taking a gap year before university to earn some money and have managed to get a job as a schools data manager. I have created an e-learning system using access and wondered if this is how I would be doing it at university and in future jobs or whether I wouldn’t be using access much in the future.

Thanks!

 

[FoFa]

You have a good question. Gizmo has a good response, but let me give you my take on it. Working in an I.T. dept. I have found that Access, being what it is, is a good tool, and cheap solution. I use Access more for data analysis than maybe any thing else. I also design systems in MSAccess when something like Sql Server or Oracle is over kill. However that said, the rest of the I.T. dept. pretty much won't touch it, which is a shame. I find a small (10 or less concurrent users) system designed for a department, excellent for access. You just have to make sure it is someplace that is backed up every night. I also use it to handle 3rd party extracts and file formatting (like for our benefits providers) where it pulls the data from SQL server, and formats in some fashion for their needs. How ever I also know the limitiations of Access, and if I think SQL Server or Oracle would be a better fit, I let them know. Development is usually longer with a large scale DB, but Access should not be ruled out in a lot of circumstance.

 

[Uncle Gizmo]

I don't think you can go far wrong with MS Access. There is so much free information available about it on the Internet, this forum for a start!

I particularly like this free online book which is for MS Access 97, however much of the information in it is still valid for today's Access applications.

In particular, part three, covers advanced forms. I am studying this at the moment with a view to creating my own custom properties of forms. I am discovering that properties give me an extra, and in some cases a much better way of using MS Access, but the main reason I am studying properties is to help me with VB.Net which I am also learning.

My theory is that if I learn how to handle them within the MS Access environment which I understand, I will have a better transition into VB.Net.

I was doubtful about the wisdom of sticking with MS Access, Hence my interest in VB.Net, However when I saw Luke's lecture, I realized that MS Access is the main work horse of many businesses, and there will be vast amounts of work for MS Access programmers for years to come.

Studying MS Access will give you a sound grasp of data structure, you can also pick up SQL , ADO and DAO.

Another presenter at the same seminar Peter Vogel suggested that it was a good idea to learn a cutting edge technology, XML springs to mind. he did point out that sometimes you would learn a new technology which would crash and burn before it was established!

The main advantage to learning the new stuff was that you could command a very good salary, and as the technology is new, no one knew how long projects would take! A very nice position to be in I am sure. The other technology he mentioned that was worth learning was Crystal Reports. Good Crystal Report programmers are few and far between and I understand that MS Access reports are a good starting point for learning Crystal as they are very similar.

 

[123James]

Wow! Another great reply Gizmo! You are a fountain of knowledge.

I have taken onboard your comments regarding new technologies and market demand for specific skills. This brings me to another point that I have been thinking over for a long time now. If I want to become an IT consultant and move into IT project management or systems analysis should I be looking at doing a degree or should I keep my data management job, gain experience and work towards Microsoft and PRINCE certification?

 

[giovi2002]

Even for small projects i preferrably use the access adp solution (access tied up with sql server in the backend).
You might lose some development speed when using sql server as a backend (access will then not be the easy click development application) in the adp solution and you really need to know much of sql server to fully profit from this solution. But the wins are enormuous, besides a rapid application (queries at great speed) less coding in the interface and less overall coding ('do it on the database' with stored procedures) and huge scalability (to 100's of users). And the big fun: when properly programmed you have a full featured database with business rules in SQL server ready to be easily interfaced to any other environment (like asp pages)

The bad thing about access (like mentioned by other people) is that many people think they can develop with access but don't have professional knowledge and habits. The same way with excel-sheets, when abused 'as a database solution' (you know the guys sending around their data, everyone keeping its own data) it becomes a problem. It's not the tool but the person who thinks to be a developer without datamodelling knowledge and proper programming knowledge will be the prob.

Best you can do is start studying sql server as its lifespan will be much longer than access, access as a front interface will disappear within a few years as a development solution for new projects and will be taken over by web-based interfaces .

 

[123James]

Thanks for the reply giovi. It is true what you say about front ends becoming web based as i have noticed this in my work place. We have recently purchased a couple of pieces of data management software which store certain data on and off site but they are both accessed through a URL, even the data on our own servers!

I am now sure i will learn SQL server as this seems vital, especially for developing dynamic, coporate websites. Is there anything else i really should be learning such as XML, server 2000 or oracle?!

Thanks in advance.

 

[Pat Hartman]

Even for small projects i preferrably use the access adp solution (access tied up with sql server in the backend).

the .adp is being phased out. I would recommend not creating new applications in the .adp format. Unlike the .adp, Access .mdb's can be used just as effectively as front ends to ANY back end database for which there is an ODBC driver. The limitations of the .adp are too restrictive and SQL Server centric. The format has not been widely adoped and so there is no big push to enhance the feature. The .adp will still be supported in Access 12 when it is released but no enhancements have been made. In fact, although existing .adp's will be able to use SQL Server 2005, they will not be able to create new databases through the Access GUI as they can with older versions of SQL Server.

I wouldn't bury the client/server applications just yet. The web interface is still too slow and inflexible and unable to support the rich features of the client/server GUI. For those of us who have been around since the beginning, the web applications are actually a steb back in technology. They are prettier but otherwise very similar to the applications I was creating with CICS and IMS 30 years ago. XML is certainly useful as a data transfer method but it is not suitable as a database.

 

[giovi2002]

Pat thanks for your info, good to know that ADP is being phased out.

The only thing I'm missing in Access is to easily deploy web applications. Desktop solutions are losing ground to the web solutions so If you can tell us anything about access 12 and web solutions I would love to hear it

 

[WindSailor]

I don't work in IT... or in my workplace it is known as IR... but "their" opinion is VERY similar to FoFa's. In fact I would say... BINGO!

The right application for the right job.

I think Access was generically used as an introductory tool to get small businesses to up-size to MS Server... Which is fine... it has its place.

My only grimace with Access is that it is fine when everyone has the same version, the same operating system, hardware etc. or simply used in a 'local' company environment. But developing desktop apps and distributing them to be ran on computers that run different environments a real pain in the arse... So yes... enter .Net.

Uncle Gizmo is right. There is a ton of information for using Access, and some very talented people here! I have to 'snicker' at the thought of a teacher who is saying "structure... structure... structure..." and learn to apply it...

 

[WindSailor]

In fact to stir the pot a little... here are a few links...

Another Microsoft's direction...

http://blogs.msdn.com/stevelasker/archive/2006/04/10/SqlEverywhereInfo.aspx

Which kind of makes one ponder exactly what role Access is going to provide in the future besides being a (very small) company based program...

As always there are others trying to tap into this market...

http://www.vistadb.com/default.asp

http://www.nexusdb.com/showpage.asp?id=97

http://www.versant.com/en_US

And many, many others... so it is a very good question indeed!

 

[RickDB]

I just want to say this is one of the most insightful posts I've ever read. Thanks all for excellent reading material!

The link to the online '97 book is also excellent, if anyone else has links like that, they would be very appreciated!

Thank you!

 

[giovi2002]

It's a little bit odd to notice ADP is being phased out, maybe because (like concluded in here) access heavily depends on using same versions and hardware. Like someone else concludes use .net if you have the chance to overcome version and hardware issues.

Personally I would use Java instead of .net as an alternative. The eclipse IDE (comparable to visual studio) is open source and supported by IBM,SAP,Actuate and other big players. Microsoft has put big cards on business intelligence, but in open source you do also see BI development tools becoming available very rapidly (like Birt reporting for eclipse).

Initiatives like Eclipse are 'phasing out microsoft in its whole', the big players use open source as the trojan horse. Looks like a timely coalition between big IT players and a large community of developers with an anti-microsoft spirit. They should be worried in redmond.

 

[Len Boorman]

Previous posts very interesting so here is my slant

I work in Engineering ( I am time served apprentice looooong time ago)

I create Access Applications ranging from relatively simple data analysis to Defect Recording across company, FMECA's MSG3 Analysis, Failure frequency analysis.

A fairly wide range. Literally there is only myself who has had proper training. Lots of other people use Access generally to produce single table applications that sort of work until they want to do some analysis when they find that dta duplication make searching, grouping and all the other goodies a problem.

IT dept not bothered about Access, They refer all queries, questions to me.

Engineering Management are the problem. From my slant they do not actually understand that Access, SQL, Oracle basically all "operate" under the same umbrella. The opinion is that Access must be inferior because it is cheap.

Somewhat short sighted.

I have and still are persuading them that Access offers a good, quick and user friendly solution to a lot of their problems if the right people are involved. i'e those that understand relation database design.

I support A97, A2000 and soon apparently A2003. We have OS's of 95,97 2000,NT,XP with generally 95,97 being phased out as the pc are replaced.

As UncleGizmo posted from the User Group presentation there is a place for Access in every organisation.

That place however should also be in the hands of a competant developer and not a 2 day trained ex spreadsheet user.

I do get a bit terse when people say "I have a database" and when you look it is a single table. I say "No you have a spreadsheet". gets a few peoples backs up but in truth that is the situation.

So in conclusion

Yes Access is frowned upon because Bill Gates has essentially made it available to everybody. The majority of everybody have had no training and have no idea that they have at their fingertips a technology that is so powerful that it gets scary at times.

Len

 

[giovi2002]

Yes Access is frowned upon because Bill Gates has essentially made it available to everybody. The majority of everybody have had no training and have no idea that they have at their fingertips a technology that is so powerful that it gets scary at times.
Len

Access is a nice tool but we're moving toward ASP (application service providers) or in-house client-server computing using your webbrowser as the client interface.
A desktop program needed to run your program doesn't stand the philosophies of modern time even if it's architecture is capable of client-server like Access. I don't know how Microsoft lines up Access within their portfolio (would love to hear). Still find it one of the best RAD tools, at least prototyping could always be done via access.

 

[Len Boorman]

It does seem that asp is becomming very popular. Sometimes I wonder if it is the view that "Web is Wonderful" approach that drives these things.

I am not an expert is asp by any means but am learning. Certainly the deployment becomes easier.

I get a lot of pressure also that Sharepoint can do everything. Again not an expert.

A lot of the applications that I develop have quite a lot of data manipulation within them.

This output is frequently distribution charts but here again the selection criteria are very variable and determined by the User. I try to give comboboxes for all criteria so that the User does not specify illogical, erroneous or plain daft criteria.

No doubt asp could handle this but I struggle to determine the advantages of asp over Access.

As a development tool Access I find very useful indeed. Frequently people do not really know what they want and as a RAD tool I think Access really does have the edge.

Everything that I write is for internal use and generally quite a narrow audience. The audience varies naturally but number of concurrent users is not a problem.

Perhaps I will get to grips with asp before I retire but time is running out

Len

 

[giovi2002]

It does seem that asp is becomming very popular. Sometimes I wonder if it is the view that "Web is Wonderful" approach that drives these things.
I am not an expert is asp by any means but am learning. Certainly the deployment becomes easier.
Len

Sorry Len in this context I mean ASP as application service provider: a web hoster which provides access to applications (like salesforce.com) in this way to overcome the need for organizations to administer local servers .

You do also have the ASP technology as offered by microsoft like you mention, that's a technology, and can be used within the ASP software distribution concept .

It would be nice if you had an Access to microsoft's asp technology or access to java converter, if someone has time I would say jump into this hole ,might be profitable because there's a large access community.
Converters might decrease the need for permanent education

 

[Len Boorman]

Sorry Len in this context I mean ASP as application service provider: a web hoster which provides access to applications (like salesforce.com) in this way to overcome the need for organizations to administer local servers .

No problem

Could have been me having a "Senior Moment". An excuse I find I can get away with more and more

Len