Re: Simple Insert Into...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

If you haven't come across it yet check out www.mztools.com as well. It's
not necessarily a learning resource, but I would be lost without it.

www.cpearson.com is another excellent site for detailed and complete
information on VBA (this site is written for excel users, but the majority of
it can be applied to Access). I've picked up tons of stuff from this site
where I've had trouble finding out about certain subjects elsewhere.
Everything form what a variable is to how to programmatically insert lines
into the VBA code window via code from another project. I would definately
say its worth a quick peruse, and probably a bookmark.

This has turned into quite the thread from a simple insert into... <g>


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


"Robert5833" wrote:

Hello Tom,

At the risk of stringing this thread beyond what may be proper etiquette, I
thought your posted reply warranted some feedback on the generous resources
you and others have made available (DymondJack this includes you <smile>). It
isn’t lost on me.

So here goes; comments in line:


"Tom Wickerath" wrote:

Hi Robert,

Thanks again for your input and the help.

You're certainly welcome. Thanks for letting those of us who try to answer
questions how much our help means.

It means a lot! Yours and everyone else’s, and the least I can do in return
is to be clear about it! (And hope in the mean time I don’t wear out my
welcome <humble smile>.)


Debugging; or better, the methods and techniques one uses to debug is
something I’m beginning to understand more clearly; and I’ve found the
Debug.Print and other uses of the Immediate Window very helpful tools.

Sprinking Debug.Print statements throughout a complicated procedure,
especially one that calls other procedures, can certainly help pinpoint where
an error first crops up. You might also want to experiment with setting
Watches in the VBE (Visual Basic Editor), to, for example, suspend code
execution when a variable has a certain value. At that point, you might want
to single step through the code (F8 key), or perhaps use the F5 key to
advance to the next break point (or to the end of the procedure, if there are
no more break points). By the way, have you seen how to set a break point?
This is done by clicking your mouse in the grey margin to the left of the
code, although you cannot set a break point on any Dim statements. You should
see a maroon-colored dot when a break point is set.

Thank you, and yes I do set break points and that has proven invaluable in
troubleshooting. I haven’t used the Watch function yet, but I’m thinking that
will be my very next progression in learning and applying good technique.


As always, I appreciate your help and your patience with me and others like
me; who are interested in learning the “tricks of the trade” even as we
struggle to understand.

Here are some additional resources for you:

1.) Bookmark the http://www.mvps.org/access/ web site. Make sure to read the
Ten Commandments of Access.

Did that; which is when I learned among other things that lookup fields in
tables weren’t the best idea (possibly the worst, depending on who you’re
talking to… <smile>).


2.) Bookmark Access MVP Allen Browne's web site, and read as much as you
can. Allen has lots of wonderful information here:
http://allenbrowne.com/tips.html

Got that; refer to his site frequently.


Check out the Utilities section for "Database Issue Checker".

Downloaded and used several times (I actually got a pretty clean report!) I
have to admit that I hadn’t EVER compiled my project db until just a week
ago, and to my astonishment I didn’t have that many compile errors? And I
fully expected a puff of smoke followed by a complete meltdown of my hard
drive…


3.) Bookmark Access Alumni Jeff Conrad's sites:
http://www.accessmvp.com/JConrad/accessjunkie.html
and
http://accessjunkie.com/default.aspx (for Access 2007)

Got it; been to his site several times (there’s so much information out
there if you know where to look!). Thanks!


Grab a copy of his free add-in known as "CSD Tools" from the first site
listed above. Disregard the warning that it does not work with Access 2007;
it actually does work, but has not been fully regression tested with this
newest version of Access.

Got the download; this looks like an awesome tool! I can’t wait to use it.
I’ve been keeping track of some object definitions and such, long hand in a
table. Ugh!


4.) Perhaps check out a presentation that I gave to the Seattle Access
User's Group during Jan./Feb., 2007, "DAO - Back to Basics":
http://www.seattleaccess.org/downloads.htm

Will do!


Also check out a Word document that I make available, which I call "Access
Links". This document is filled with lots of hyperlinks and other useful
information. The first four pages includes information that anyone working
with Access should be familiar with. The rest of the document is worth
skimming, to get a general familiarity of it's contents. You can download a
zipped copy from my web site:

http://www.accessmvp.com/TWickerath/

Got it already; and have used it and the resources and links several times!



5.) Check out Access MVP Crystal's tutorials, here:

http://www.accessmvp.com/Strive4Peace/Index.htm

Will do! Thanks!



Okay, that should keep you busy for a while <smile>.

Yep; it will, but it’s all good!

Thanks again for your help and your consideration!

Best regards,
Robert




Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Robert5833" wrote:

Hi Tom,

This is great information, and I appreciate your time and guidance. For all
that I’ve read in the “formal” publications, I seem to get the most, and the
best “real world” context from this discussion group and those who support it.

Thanks again for your input and the help.

Comments in line below:


"Tom Wickerath" wrote:

Hi Robert,

Also, I’ve made sure that all of my modules are Option Explicit, although
until recently (more reading from writings of yours and others) I didn’t know
the importance of that statement.

Here is a "gem tip" that I wrote on this subject. It also shows you how to
change the default behavior, so that all new modules you create will
automatically include these two very important words:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Good advice; and making the settings changes (I’ve seen that feature, but
just haven’t done it yet). And I'll be reading the linked material shortly.
Thanks!


While you're at it, you might as well remove the not-so-helpful auto index
"feature":

http://www.access.qbuilt.com/html/gem_tips.html#AutoIndex

Good advice; I’ve already disabled Auto Correct Names, but didn’t think
about this setting or what it meant.



It does seem that this technique makes it easier to use the Immediate window
tool; but I’m curious…are there benefits beyond that?

No--the only real benefit is to aid in debugging.

Debugging; or better, the methods and techniques one uses to debug is
something I’m beginning to understand more clearly; and I’ve found the
Debug.Print and other uses of the Immediate Window very helpful tools.


If it’s not too much to ask; would you mind commenting further on the use of
one technique over the other?

I routinely set a string variable myself, rather than try to include the SQL
within the .execute method directly. And when I need to troubleshoot other's
databases that include db.execute "Some SQL String" (where "db" has
previously been set = CurrentDB), I usually go ahead and separate it out, so
that I can look at the SQL statement that results.

This helps me understand the different approach much better. I did find that
when I changed to this technique, I was able to do a lot more with the
Immediate Window.


I also tend to declare a database variable, and set it, like this:

Dim db as DAO.Database
Set db = CurrentDB()

so that I can use db.execute to run an action query. I generally prefer
doing that versus using CurrentDB.Execute, however, it doesn't really matter
performance wise, unless you have a need to reference CurrentDB more than one
time in the same procedure (for example, you have a procedure that runs
several action queries). In that case, setting the variable once and reusing
it will be faster versus making a call to CurrentDB each time.

I am doing this routinely now in my procedures; and I can see the savings in
time it provides. I understand better now why and how it is done. Thank you!


However, when you set a database variable, you should ensure that you also
set it equal to nothing as a part of the ExitProc part of your procedure.

I am doing this too. Thank you!

As always, I appreciate your help and your patience with me and others like
me; who are interested in learning the “tricks of the trade” even as we
struggle to understand.

Best regards,
Robert




Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Robert5833" wrote:

Hi Tom,

Thank you for the reply and the suggestions. I have incorporated them into
my design.

I’ve only recently learned how to use the Debug.Print technique and how
handy it can be. And you’re right, it has helped me so many times to identify
flaws in my code. I’ve also learned to be disciplined in compiling code as I
build procedures, and that has pointed out obvious errors as well.

Also, I’ve made sure that all of my modules are Option Explicit, although
until recently (more reading from writings of yours and others) I didn’t know
the importance of that statement.

The technique you pointed out to declare a string variable in the procedure
is another good one. I’ve done that with others, but without knowing why
(other than for the fact that examples I’ve used or been provided were
written that way).

It does seem that this technique makes it easier to use the Immediate window
tool; but I’m curious…are there benefits beyond that?

If it’s not too much to ask; would you mind commenting further on the use of
one technique over the other?

Thanks again for your time and the help!

Best regards,

Robert
.

Relevant Pages

  • Re: Simple Insert Into...
    ... "Tom Wickerath" wrote: ... Debug.Print and other uses of the Immediate Window very helpful tools. ... will be my very next progression in learning and applying good technique. ... Here are some additional resources for you: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Giving an application a window icon in a sensible way
    ... learning a chunk of new API and either ... figuring out how to get Eclipse to tell an app where to find resources ... Changing the icon to be done your way is equivalent to externalizing ... some strings, say for localizability. ...
    (comp.lang.java.programmer)
  • Re: Simple Insert Into...
    ... will be my very next progression in learning and applying good technique. ... Here are some additional resources for you: ... Bookmark Access MVP Allen Browne's web site, and read as much as you ... I routinely set a string variable myself, rather than try to include the SQL ...
    (microsoft.public.access.modulesdaovba)
  • Re: Programming Language Comparison
    ... language they would like to use for teaching people how to program. ... Another part of this is evaluating existing resources. ... learning how to program for each particular language. ... I think Ruby should be yellow here. ...
    (comp.lang.ruby)
  • Re: OT: Fitness experts
    ... Very traditional in terms of teaching westerners I feel ... Do you feel you're learning much? ... My current school drills basic attack / defense techniques (so far for just ... you only have to worry about what technique as opposed ...
    (uk.rec.motorcycles)