Macro Vs. Code (SQL)

[DavidVDT]

"However, exceptions are made for actual sample files that are required to get assistance with your Access issue."

Thank You for that......

A quick intro..

I am "Reentering" the Access World as I really enjoy working w/ this,
I just purchased 2007 and WOW What a difference over 03...

In the past I have built 3 complete applications,
1) Complete app for tracking ALL aspects a Tool Grinding business I had,
2) Tracking for a day care "Home Business",
3) Tracking tasks for Home Builders.

This I TRULY Enjoy, and now want to get back into it.
The "PROBLEM" is I always needed to call in a Local Programer to do all the "Trick" stuff for me, I would design the flow and build the app but when it came to the "Complex Code" (SQL) I called in Mike.

This is something I Really want to learn for myself, to be able to put "The Magic Behind The Buttons" to build the apps I want to build in the future.

Could you please give me some direction? - Now the question for "Macros"

I just in attempting to "Relearn" what I might, I came across a video here .. http://www.brainstorminc.com/cbt/msa2007/index.html - the one called "Filtering a report", this is one of the problems I had in the past was to:
1) call up the report,
2) setup a diag box w/ a "Start Date" + "End Date"
3) Populate the two "Date" fields
4) Display the report w/ the "Filtered" Date Data

The Question is (Please see video @ link) can I "Record" a "Macro" to perform thses tasks, as apposed to writing Code?

Is one "Safer", More Reliable than the other?

Please understand, I do want to learn the code but I am looking for options.

If you have any Ideas as how I can best learn to build apps, (on a Small) budget, I have been laid off now for 8 months, dead here in Michigan,
please let me know.

Thank You VERY Much for any and all help
David

 

[HiTechCoach]

david,

AFAIK, Access does NOT have a macro record.

I also find Macros very difficult to edit, comment, and debug. Macro are also very limited in what they can do.

Until Access 2007, macros did not have any error handling so I never used them. Macros in 2007 have improved, but are still very limited for security reasons.

You may also what to check out:

http://www.access.freak.com for for the tutorials on 2007.

Converting to Access 2007 ( http://allenbrowne.com/Access2007.html )

Access Basics ( http://www.hitechcoach.com/index.php?option=com_content&task=view&id=18&Itemid=9 )

TIP: I would probably be sure to install SP2 so you will have less bugs. IMHO, 2003 is stable and can be used in a business environment, while 2007 is just not there yet ( I wouldn't use Access 07 to run my business ).

Hope this helps ...

 

[DavidVDT]

" ( I wouldn't use Access 07 to run my business ). "

Ouch ! I just purchased 2007 thinking it was the cats meow.....

I just started a small app a few days ago in 07,
Should I abondand this and get back to Access 2003?

2003 I also know much better,

Are there any other thoughts on this....

Thank You HiTechCoach for the direction

 

[gemma-the-husky]

as i understand it (up to A2003 certainly) , other than autokeys macros, and autoexec macros, any macro can be constructed in code. (autokeys are useful for some things)

personally I think code is generally easier to use IMO, and i would use code rather than macros. Having said that a lot of MS demo stuff uses macros.

i have no problem with Access even in critical business environments- perhaps Boyd means a2007 rather than earlier versions.

---------
i think the best way to learn is try things - trying developing something you are REALLY REALLY familiar with - normalisation is very important, so get the table design right first - thats why doing something you know inside out is better, as you won't miss things.

 

[SOS]

" ( I wouldn't use Access 07 to run my business ). "

And I would argue that does not represent everyone's viewpoint. I've worked with several clients with 2007 and haven't had issues (AFTER SP1) that would cause me to say something like that. Prior to SP1, maybe.

 

[DavidVDT]

Well One thing I am hearing from you experts is "Go For The Code" (SQL) ...

I am certainly going to pursue this, HiTechCoach has pointed me to some great sites,

Is there any Specific (Sequential) training I should acquire in learning SQL?

Keeping in mind my desire is to be able to build “Complete Relational Database Applications”.

Where(What you get out, Reports, etc. (Improved Process’s)) Is Greater Than, (what data you put in)

david

 

[SOS]

Here's a couple of great (and FREE) tutorials

On SQL.

Also on VBA.

 

[DavidVDT]

Thanks SOS

I am printing and studying - Thank You ALL

 

[Mr. B]

DavidVDT,

I would just add this thought.

As you start to develop your next project, you can create a macro or two and then have Access to convert these macros to VBA code. Then you can examine the code to see how macros equate to the actual code.

Writing VBA code is essential if you are going to develope complete applications using MS Access. There are some things that you simply would not be able to accomplish if you do not use VBA code. Two specific things that come to mind immediately are:
1) Creating SQL (query) statements on the fly and
2) Writing user defined functions (these prevent having to duplicate the same code)

These are just some of my thoughts on the matter.

One other thing that is essential is that you can use Google and/or searching here for answers to your issues and you will find that most of the questions that you might have, have already been asked by someone and have been answered. If you cannot find an answer to your specific question by searching the above mensioned resources, you can also post back here with specific questions about how to accomplish specific tasks and someone will most likely be able to help. That is how some of us (myself included) learned. That is also why some of us hang around here and try to help others like yourself when we can.

HTH

 

[DavidVDT]

Thank You Mr B…

You said

• “you can create a macro or two and then have Access to convert these macros to VBA code. Then you can examine the code to see how macros equate to the actual code”…..

I am approaching in every way I can think of,

• “Writing VBA code is essential if you are going to develope complete applications using MS Access.”

(This is why I MUST and Want to learn SQL)

• Creating SQL (query) statements on the fly and
  2) Writing user defined functions (these prevent having to duplicate the same code)

This is where I needed in the past to call in for help…….

Also can’t Access design a query and “Convert to SQL”?

· “can use Google and/or searching here for answers”

I have a Very Patient wife as I have been “Goggling” for the past several days and watching many videos and exploring Access 07 web sites.

Learning to navigate 2007 is “Interesting”, I personally think 2003 had a more “Solid” feel to it.

The following is what I want to learn, to bring life to the application,

Right now this is “Real Foreign” to me L

This was real simple as Mike wrote this “Registration Sequence” to a Builders Software I put together.
This is the “Specific Instruction” I am looking for.

Option Compare Database
'Public lngActiveChild As Long
Public Validnbr As Boolean
Option Explicit
Function validate() As Boolean
' Comments :
' Parameters : -
' Returns : Boolean -
' Created :
' Modified :
'
' --------------------------------------------------------
On Error GoTo proc_err
Dim mydb As Database
Dim rst As Recordset
Dim tmpCount As Long
Dim tmpKey As String
Dim tmpKeyEval As Long
Dim tmpLeft As Long
Dim tmpDate As Date
Dim tmpJulian As Long
Dim tPath As String

Application.SetOption "confirm action queries", False

Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset("select * from msysobjectsBE where name = 'Student Information'")
'Set rst = mydb.OpenRecordset("select * from msysobjectsBE where name = 'administrative information'")

'check date
If Format(rst!DATEcreate, "short date") <> #7/21/2001# Then '10/7/00 5:16:48 PM
Call MsgBox("You are illegally trying to create a data file. Program will end.", vbOKOnly, "Warning")
Application.Quit
End If
rst.Close

Set rst = mydb.OpenRecordset("select * from [Student Information]")
rst.MoveFirst
tmpCount = rst!open_count
tmpKey = rst!regkey
tmpDate = rst!regdate
tmpJulian = CLng(Left(CStr(CDbl(tmpDate)), 5))
tmpJulian = tmpJulian + 99000 + Asc(rst![StudentsLastName]) + 969
tmpKeyEval = CLng(Right(tmpKey, 6))

'set global variable
If tmpJulian <> tmpKeyEval Then
Validnbr = False
Else
Validnbr = True
End If

tmpLeft = 20 - tmpCount
'if trial lock less than 20 logins and no valid key prompt user
If tmpCount <= 20 And (tmpJulian <> tmpKeyEval) Then
Call MsgBox("You have " & tmpLeft & " login(s) before trial lock mode expires. Please contact Parousia Software Toll Free for registration information at 877-869-4300.", vbOKOnly, "Expiration Notice")
DoCmd.OpenForm "switchboard", acNormal, , , acFormPropertySettings

'if trial lock greater than 20 logins and no valid key block entry
ElseIf tmpCount > 20 And (tmpJulian <> tmpKeyEval) Then
Call MsgBox("Trial lock mode has expired. Please contact Parousia Software Toll Free for registration information at 877-869-4300.", vbOKOnly, "Expiration Notice")
'disable all except admin button
DoCmd.OpenForm "Student Information", acNormal

'Forms!administrative.TabCtl123.Pages(1).Enabled = False
'Forms!administrative.TabCtl123.Pages(2).Enabled = False
'Forms!administrative.TabCtl123.Pages(3).Enabled = False
'Forms!administrative.TabCtl123.Pages(4).Enabled = False

DoCmd.Close acForm, "switchboard", acSaveNo
Exit Function
Else 'good ID regardless of count
DoCmd.OpenForm "switchboard", acNormal, , , acFormPropertySettings
End If
'Forms!switchboard.Caption = "Builders Home Study Course " & GetAttachedPath_TSB("", "[Student Information]")

DoCmd.OpenQuery "qryincrementcounter"
'Stop

Exit Function

proc_err:
If Err.Number = 3024 Then
Exit Function
Else
MsgBox "The following error occured: " & Error$ & Err.Number
Resume Next
End If
End Function

Thank You again for your help
david

 

[Olav]

My favourite tutorial site is w3schools. (I am a new user, so I cannot post the link, just add com to the name.) This is mainly a web-programmers tutorial site, but the SQL-"classroom" is relevant to access also.
This site provides a nice step-by-step approach with a comfortable learning curve. -I am to impatient to learn it to my fingertips, but w3schools offer an excellent code-reference.

Good luck with your reading, and remember not to try to learn everything at once, it may kill your interrest..

 

[namliam]

Quote: Also can’t Access design a query and “Convert to SQL”?
Yes it can, just make a query in the designer, now right click it and view the SQL. This is "converted" SQL that you can use in VBA if you want.

While I concure with others here, "anything worth doing, is worth doing in VBA, not macro's" for mostly the reasons mentioned. I cannot really find what you are really asking... Might be me, but .... it seems a little hidden what you are asking.

 

[DavidVDT]

Hello Mailman,

First allow me to say how sorry I am to hear of your sisters death, our condolences are with you and your family.

You said..
“I cannot really find what you are really asking... Might be me, but .... it seems a little hidden what you are asking.”

No, it is not you at all, I am continually “Shaking The Bushes” and “Listening”,
There are a LOT of Kind folks here on this forum and I am listening as to what they each have to say, each from there own perspective…

What “I am specifically seeking” is the following:

1) Globally
a. Overall best learning curve to achieve “Complex Application Designs” using MS Access platform.
b. To provide the Best “Conceptual” database structure to ANY customer ….. This being said,
i. All apps MUST be designed to return Optimal feedback to the end user to ..
1. Increase there bottom line by means of “Continuous Improvement”
2. Improve there Processes, whether manufacturing, supplying, etc.
3. Streamlining of there business
4. Giving the customer the best value for there dollar, Knowledge is key.
5. Just help make there life a Lot easier.

2) Specifically
a. I need to learn SQL – (How I Learn Is Important) Now that is my weird idiosyncrasy.
b. I have received a lot of feedback from folks on this forum and it is Greatly appreciated.

3) Details of what I need to understand is…
a. The underlying specifics of the line by line code.
i. What comes first, these instructions or those ?whatever?.
ii. Some of this is obvious; you must make a grocery bag before you can bag the groceries.

b. Dim tmpCount As Long
i. 3 or 4 things going on here, I should see and know this instantly.

c. Call MsgBox("Stop Now or program will end.", vbOKOnly, "Warning")
i. Opps, forgot those quotes.

d. Set rst = mydb.OpenRecordset("select * from [Student Information]")
i. You understand what I am saying, this is what I want (Need) to learn.

4) What I do know
a. Tables, Structuring of, “Normalizing”, key fields, relationships,
b. Queries – calculating in, for reports (and forms) (Need to understand SQL uses here)
c. Forms – “Forms Rule”, this w/ good tables results in Great Data to later Manipulate.
i. I need to understand sql code to make a form open that contains those “New Orders” in addition to the switchboard, (and MUCH More)
d. Reports – “The Feed Back”, you will never get from here what you do not put in up front.
i. I love working w/ this aspect as this is where we obtain the info to improve from.
e. Macros – From the talk on this forum, use sparingly,
f. Modules – I just lost the battle.

5) Simply Put
a. Just as there is a “Logical Methodology” to building a great relational database,
i. First the tables
ii. Then the queries
iii. Then the forms
1. You can not build a form without a table containing data….
b. The 1st things I need to understand is “What comes 1st” ! When it comes to SQL.
i. Instead of understanding “The way Dim works”, I need to understand 1st the where and why.
1. This pertains to ALL commands I believe.

6) This is what I am not seeing in the training videos on line, “the application of using Dim”, where, when and how to use any given command.
a. I need to understand the “Where To Use” and “Why To Use Here” and not there.


7) Any assistance in this direction would be greatly appreciated
a. First (# 6a and 5bi)
b. Then the understanding of # 3 will fall into place.
i. I hope this better explains.
ii. I know I am a bit wordy, sorry

Again Thank You all for taking the time to read this and your assistance,
david

 

[Mr. B]

David,

It almost seems that you may be trying to "eat the whole elephant at one big bite". LOL

Try taking things one at a time.

You are correct in saying that you need data before anything else. Developing the other aspects of any database application is strickly dependent on what you are trying to accomplish. There is no magic fromula. I know in reason that if you are looking all of this and trying to get your head around all of it, you are most certainly feeling a little overwhelmed. Try not to do that. If you really want to "eat the elephant" then just take it one small bite at a time.

First, make sure that your data is structrued and normalized as best you can.

Then you can start to worry about just how to present the data using forms.

Along with data presentation will come the queries and then later the reports for the output.

As for the Dim statement; one easy way to learn about some of this is to open the VBA code window and type in "Dim". Then place your cursor in that term and press th F1 key and read the Help file. There is actaully a lot that can be learned from there all the time.

Ask specific questions here. We will all try to help.

HTH

 

[namliam]

It almost seems that you may be trying to "eat the whole elephant at one big bite". LOL

I concure with Mr. B., baby steps my friend, Rome wasnt build in a day. Niether will your Database or your skills to build it.

Most important, and you seem to understand that, is your base. The base design and normalization of your database. If this base is right that is 50% of your work done...

SQL you can mainly pick up using the designer to build your queries... Basicaly your tables are for storing data. Forms and reports are for displaying data (one way or another).
Queries are the means that go inbetween, as a base rule no form/report or even code should be based directly upon Tables.
There are some possible exceptions, but they are few and far inbetween.

Making the "optimal" application is IMPOSSIBLE, all you can do is make what your customer wants. Or failing that with your growing experience learn that a customer will say one thing, mean another, while wanting yet something else.
I call this the ABC, Customer says A, means B, wants C. Once you learn the ABC, thats where you start adding value. Not your app, not your tables/queries, not your design... Its this ABC that is important, the only way to learn your ABCs is to practice, practice and practice. Do Do Do...

Different people have different "business language", understanding the language can be the hardest part of the job. Translating that into your own developer language to be able to make what the customer wants.... Note, that is C, not A.

 

[DavidVDT]

Thanks Mailman,

Quote:
"Note, that is C, not A."

I agree completely this would be the most Challenging aspect, understand there “Communicated” “Needs””.
​

Quote:
"as a base rule no form/report or even code should be based directly upon Tables. "

Please explain, this is new to me as I thought, This being where the data was stored, (Tables), (And we are reporting on data collected) this would be the Only "Basis", (Origin, reason) for Displaying/reporting "Solutions".

​

Am I looking to achieve incorrect ideas? (Barking up the wrong tree)

What is the reasoning for your thoughts here?

david

 

[namliam]

Tables are where your data is stored... Queries is how you access that data...
So you would always have something like:
Table > Query (s) > XYZ

XYZ can be code, form, report or even another query... but you should mostly try to not have a situation where you simply go:
Table > Form
or
Table > Report

More explicitly, you should never have as a Record Source simply a table name ("YourTable") .
It is 'good practice' to atleast do "Select * from YourTable" or better yet...
"Select Column1, Column2, ... from YourTable"

 

[Olav]

Hi David,

I once was adviced to KISS (Keep It Simple Stupid).
The way I create my Access applications is to create many querys, and use macros as far as possible. And only use VBA where macros cannot do the job. This makes it much more simple to fix errors on an Access app that is a few years old. -It usually is hard to remember to comment the code that seems so obvious.
-It is also incredible how complex operations you can perform with just macros and queries. If you play with macros, you may find the Conditions button. This allows you to add conditions to each macro line, useful for checking e-mail fields and date field, and also comparing fields before performing an action. You may also combine multiple macros in one macro, e.g. MainMenu.ExitClick, MainMenu.OpenPartList...

But over to your questions:
5)
1. You can in fact make forms before making any tables, such as a Main Menu form, custom dialog boxes etc. The table is only an option to make the form more useful. -Look at the form as a window in Windows.
b) SQL is short for Structured Query Language. It is a way to ask the database for speciffic data. There are four types of questions you need to know:
SELECT, DELETE, UPDATE and INSERT. The others are not relevant, as you may create/modify/delete the tables manually.

SELECT [columns] FROM

This is the most basic query. This selects data for viewing. If you add the word DISTINCT after SELECT, you get a result with only unique data. E.g. SELECT lastname FROM cutomers will return only one record for each lastname in the customers table, creating a list of all lastnames in the customers table.

DELETE FROM

This deletes records from the table.

UPDATE

SET [column]=[value]
This updates data in the table.

INSERT INTO

([columns]) VALUES ([values])
Inserts a new record into the table. If you know the arrangement of the columns, you may skip the ([columns]) part.

Optional, but recommended additions (Not relevant to INSERT):
WHERE [conditions]
Sorts out records that match the given conditions. E.g. DELETE FROM items WHERE type='Paintbrush' will only delete the records in the items table that is of the type "Paintbrush"
Use AND and OR to add multiple conditions.

ORDER BY [columns] ASC|DESC
Sorts the data by the given columns ascending or descending.

These are the most basic SQL queries you should know. For a more detailed description, visit w3schools, and under Server Scripting, you find Learn SQL.
(Free, and very good tutorial site)

I believe you are mixing SQL and VBA. VBA is a programming language (Visual Basic for Applications)
This is the place you use 'Dim'.
I believe Dim is short for Dimension. This code word is used to create empty variables.
Look at a variable as a box, or a container and the data as an item. If you want to keep your items for later use, you must put them in a box. So when you have an apple you would like to keep, you must ask for a suitable container. You could ask for a hotel-room for your apple, but that is a huge waste of space. You could ask for a matchbox, but then much of the apple will be wasted. Later, you may want to replace the apple with a banana. If you have chosen a good container, like a fruit basket, this would not be a problem.
This is why you use Dim. When you write Dim [name] as [datatype], you create an empty variable of the given datatype. Later, you may fill the variable with data.

E.g. 3.b. Dim tmpCount as Long
Dim (Create a box) tmpCount (the label on the box) as Long (The size and shape of the box)
This code creates a variable tmpCount to hold a number. You should do a quick search on google on Visual Basic datatypes to find out that the words Double and Long really means numbers
The Long is used because this is a 32 bit container for numbers, and since most computers still have a 32bit processor, this is the most obvious choice.

The other lines of code:

3.c Call MsgBox("Stop Now or program will end.", mbOKOnly, "Warning")
Call is just confusing you if you do not know any other programming languages, so just delete/ignore this word.
MsgBox is a function wich requires some information.
The first is the text in the messagebox, surrounding it with " means that it is a text-string.
The second is a constant number. the mbOKOnly is a number that identifies that there should only be an OK button on the messagebox.
The last is the title of the messagebox. This also has to be a text-string.

3.d Set rst = mydb.OpenRecordset("select * from [Student Information]")
this sets the data in the variable rst to be equal to the return value from the function OpenRecordset() with the parameter "select * from [Student Information]" available to the datatype of the variable mydb.

Also a good tips is to put your cursor in the word you want information on, and press F1 for more details.

If you have never programmed in any programming language before, I would suggest that you google on VBA tutorials, and begin with the "Hello world" applications. I know that this is extremely boring, but this help you understand the language.
Trying to create the perfect application before you are comforable with the terms Variable, Constant, Function, Datatype, Class, Void, Public, Private, Sub etc. is like trying to swim across the atlantic ocean without knowing how to swim.
This is why I prefer to do most by Macros, and only VBA when macros can not do what I want.
Also, remember not to eat the whole elephant in one bite, this will kill your interrest in programming. Learn it slow and gentle, small achievements rewards you with big joys!

Good luck!

 

[DavidVDT]

Mailman, Thank You VERY MUCH
OK I understand,

Reports and or forms should derive from a query as apposed to directly from any table,
And this is what I have done in the past,

I Think I am correct here in saying, this is (queries preceding the reports/forms) where I would build any calculated fields also, as apposed to building them on the form/report, Correct?

I understand the reason being the "Speed of Reporting" is greater working through a query, (Manipulate the data, Prior to reporting, Not during reporting)

Thank you again
david

 

[gemma-the-husky]

what i would do is start by finding somethnig you are REALLY REALLY familiar with

say you are a golfer, so design a database to record courses you have played, and your results.

interesting, challenging and instructive, but not too hard


--------
one other thing - at this stage, writing SQL directly really isnt so imprtant. the vast majority of things can be done with a visual query, which you can run by

docmd.openquery "myquery"

or

currentdb.execute "myquery"