[RESOLVED] Getting started in access vba

[gphillips]

Hi ,

Want to get started in access vba- how do I get started - any good tutorial examples - begginers through to advanced??????

I can't think of why you would want to manipulate data in access rather than excel, can somone provide some standard commonly used useful code in acesss vba that demonstrates the advantages over excel.

Borris

[Webtest]

From my personal perspective:

Some applications drive you to Access ... multiple users is a big one. Shared workbooks get nasty in a hurry unless you are in a very tightly controlled environment. We have users spread out over 5 states with various types of connections to the WAN.

Manipulating data in an Access application ... reformatting user input, automatically generating regular date ranges for queries, reorganizing or grouping records ...

The big problem with access is documenting everything. It just isn't possible without 3rd party support. I've never gotten a chance to try this out. See some of my previous rants in this forum.

[RobDog888]

Excel is not a database nor should it be used as one. That would be the biggest benefit from using Access over Excel.

Excel has a row limitation of 65,535 where as Access has no limitation to the number of rows but only limited by size for a single table -

1 gigabytes - Acc 2000
2 gigabytes - Acc XP
2 gigabyte minus the space needed for the system objects - Acc 2003

[malik641]

[color=navy]Excel is not a database nor should it be used as one. That would be the biggest benefit from using Access over Excel.

I've heard that so many times I wonder myself if I should start learning Access and Access VBA...I probably will soon enough.


Hey RobDog888, what do you think is a benefit(s) of using excel over access?

I only ask because I want to know the opinion of a MVP, as I feel your opinion to be considered really good advice

[RobDog888]

There are several advantages of Excel over Access. It all boils down to the old saying "Use the best tool for the job". If your performing accounting or financial analysis, light statistics, deviations, or other formulas then Excel is a much better choice but only if its limitations dont outweigh the benefits. Excel is loved so dearly in the accounting/financial industry but still should not be used for heavy scientific analysis. Access is less desireable if you have multipe concurrent users that are making adds and updates as you can easily get into deadlock issues and if not properly programmed in its connection cleanup code you could get database corruptions etc.

I like both products allot but each under the proper situation. If your doing heavy database work then upgrading to MSDE or SQL Server is great when possible. Performing heavy stistical anaylsis you should be using a program targetted directly towards the scientific community as Excel has a limitation of accuracy for general anaylsis and deviates noteably in heavy calculations - http://vbforums.com/showthread.php?t=418752

[disruptivehair]

Hi ,

Want to get started in access vba- how do I get started - any good tutorial examples - begginers through to advanced??????

I can't think of why you would want to manipulate data in access rather than excel, can somone provide some standard commonly used useful code in acesss vba that demonstrates the advantages over excel.

Borris

Wrox has some Access VBA books but I thought they were pretty poor. There are some VBA books out there but I've never seen one that I thought was good...the 'For Dummies' series is particularly bad. If you already know the fundamentals of programming, a good reference might be all you need but if you want something more in-depth, I've heard good things about 'Access Database Design & Programming' by Steven Roman, though I have never read it myself. We have the O'Reilly Access Cookbook and while it is definitely NOT a tutorial, we've learned a lot from it. We used to have a girl on the team who taught herself VBA solely from reading the Access Cookbook.

Basically what I would do is go to a really good bookstore and just start thumbing through some books. You don't have to buy them there, you can get them online...but as is often the case with computer books, the titles and descriptions can be misleading so it's a good idea to see what's between the covers before buying.

[Ecniv]

I learnt Access (97) using Sybex. The format is great for a beginner and did take you up to upper intermediate. Also came with a VBA only book. I didn't use it much but it has loads of examples of codes.

Both can be used as a reference book and in the case of the Access one, you can read through like a normal book. It is also in sections and and had mostly useful examples of code and things to do.

If you are working, it is worth finding something in excel which you do but could be tighter (ie data entered is correct, duplicate rows of data) then follow normalisation etc through the book.

SQL/MSDE/ORACLE - heavy data holding/processing
Access - office processing - usually team sized (most people say max 5-10 people at once - in theory you could have 255, but there is an increased chance of corruption)
Excel - office - formatting, analysis of data (usually automate an output from Acess/Sql and drop into excel to show pretty graphs etc. Although Excel does have a useful pivot option.

[gphillips]

Hi,

Thnaks for the great source of advice.

Reason I say this is because most end users of say a reporting application want to view their data in spreadsheets not access. I always want my data in spreadsheets not access because it doesn't have many data manipulation function. Excel seems much better for data manipulation. Therfore I always thought it seems a little pointless using access or SQL server when you can create an application in vb6.0 to open excel documents/reports etc etc.

What is sybex ? How do I get this?

Many thanks,

Borris.

[AIS4U]

gphillips:

Sybex is a book publishing company and can be found here:

http://www.sybex.com/WileyCDA/

Some Access/VBA Web sites are:

http://www.functionx.com/vbaccess/index.htm

http://www.mvps.org/access/resources/othersites.htm

http://visualbasic.about.com/od/lear.../aa030803b.htm

http://office.microsoft.com/en-gb/as...361681033.aspx

http://www.techonthenet.com/access/tutorials/index.php


Hope this helps.

Good Luck