vb123.com.au                                                          

  Welcome To Tips-FX - Editions 11-15
See all newsletters

Tips-FX is an email newsletter that provides free tips,  help and information for skilled Microsoft Access users and related software disciplines.

In this edition there are only two topics.  I hope you find them useful.

-------------------------------------------------------
Excel to Access - Building a Database Application from Spreadsheets

A number of the Microsoft Access projects I've been involved in are, in essence, rescue missions for spreadsheet systems that have outgrown themselves. These systems share common themes, such as 12 individual monthly spreadsheets, one yearly spreadsheet that draws the results from the monthly sheets, and another set of weekly report sheets jumbled into the system. By the time these interlinked spreadsheets hit their second or third year, all the monthly spreadsheets have changed in design, and the old information is only accessible with a lot of manual cutting and pasting.

This article that has just appeared in the latest edition of “Microsoft Office & VBA Developer” tells you how to go about converting from spreadsheets to a database.

http://www.vb123.com/Toolbox/00_docs/excelaccess.htm

Issue 14

-------------------------------------------------------
COMPACT YOUR SCREEN DUMPS BEFORE EMAILING

Want to send someone a copy of screen dump in email. 

First hit the Print Screen button (top right of keyboard)  or
Alt Print screen for the current window
Open Word and paste the screen into a blank document.
Now on the File Menu choose Send To ... Mail Recipient
This will send out a compressed version of the bitmap.

What ever you do, do not send out an uncompressed bitmap saved using Microsoft Paint.  They are gigantic and can be compressed into a zip file of 100 the size.

-------------------------------------------------------
TRACKING CHANGES TO RECORDS

In this months featured online article, Nirmala has come up with a sensible way of keeping tracking of changes to records in a database.   I liked the idea because it was similar to an approach that I used successfully in an Informix database system about 10 years ago. The article is summarised as follows

Very often, we need to audit all changes made to a record. This is useful for sensitive data or in cases where accountability needs to be established. This approach involves

 Storing all changes made plus
 Ascertaining who made each change and when

Either way, why not read all about it at

http://www.vb123.com/Toolbox/00_access/auditrecords.htm

Author Bio:

Nirmala Sekhar is a software consultant working from Singapore.

-------------------------------------------------------
 ACCESS SECURITY AND PASSWORDS

The second feature online article this week provides an interesting insight into the world of security and passwords.  This is a pretty useful read if you have ever worried about people hacking into your code or finding out your salary in that "secured database".   Read about it at

http://www.vb123.com/Toolbox/00_accvb/accesssecurity.htm

This article was brought you by

Dmitry Sumin,
Security Expert
http://www.LostPassword.com



-------------------------------------------------------
VB6 ADO ADD-IN RELEASED

Adam Cogan first told us about this add-in at Devcon Oz. Here the official release

This Add-In automates the task of mapping stored procedure parameters to ADO Command object parameters. Head to this site to download!   But beware that you need to be into SQL Server and VB and ADO to use this great new Microsoft Add-In.

http://www.vbtt.com/

-------------------------------------------------------
 Good Reading and Useful Sites

Programming your own graphics applications in visual basic.  Try this link
http://www.dev-center.com/articles/multimedia/graphics_with_vb/

What is SQL.   Here is an overview for those of you who do not know.
http://www.dev-center.com/articles/databases/structured_query_language/

VB123.com wins another award.  Lots of vb links on this page
http://www.vbgreatone.8m.com/links.htm

Access 97 Database password retrieval.
http://www.dev-center.com/code/code.asp?CodeID=55

Running your PC on a permanent connection.  Then you should consider a firewall.
http://www.zonelabs.com/

But before you consider the firewall, test your computers stealth using the tools at
http://grc.com/

Printing graphics using visual basic.
http://www.dev-center.com/articles/multimedia/printing_graphics/

Microsoft Excel Statistics Book
http://cw.prenhall.com/bookbind/pubbooks/levine/chapter1/deluxe.html


Lots of Active Server Pages Sites - Try this monster URL
http://dir.yahoo.com/Computers_and_Internet/Software/Internet/World_Wide_Web/Servers/Microsoft_Windows/Active_Server_Pages__ASP_/

Grabbing Table Columns from Other Web Pages
http://www.4guysfromrolla.com/webtech/031000-1.shtml

Worried about using FrontPage for ASP development.  Ed uses Frontpage and here is an article that argues the For case.
http://asp.superexpert.com/articles/articleredir.asp?aid=18

Creating a User-Friendly Search Engine For An Access Database
http://www.asptoday.com/default.asp?art=19990803.htm

A good resource for Internet, ASP and Databases
http://www.tcp-ip.com/

and their database knowledgebase
http://www3.tcp-ip.com/ResourceIndex/Showcat.asp?cat=ASP+Online+References%2FDatabase+Reference

A Microsoft Link for Email from ASP pages
http://msdn.microsoft.com/workshop/server/feature/morqa.asp

Links to lots of good ASP articles.
http://www.aspforums.com/directory/Development/

An Access Web Site written in German
http://www.fullaccess.de/


Microsoft Magazine
http://msdn.microsoft.com/msdnmag/

Got a Visual Basic Story that you want to submit, then try
http://vbwire.com/addnews.asp

Issue 13

 

PUT THE DESKTOP IN YOUR EXPLORER FAVORITES

Go to Internet Explorer and click on the down arrow on the address bar. Now choose Desktop. This will show all the shortcuts on your desktop. Save it as a Favorite and you will now be able to go to your desktop using Explorer favourites without minimising all your windows.

-------------------------------------------------------

QUICKLY OPEN THE PROPERTIES WINDOW IN ACCESS

Make an Access macro called AutoKeys
View The Macro Name column

The Macro Name will be called ^X
The action will be SendKeys
The keystrokes will be %VP

This will open the properties window whenever you hit Control X

Those of you who use Control X for Cutting, will need to choose different key strokes e.g. Control Q

 

-------------------------------------------------------
MORE ABOUT THE CALENDAR AND DATE TIME PICKER

by Eric Hertig

I enjoy the newsletter and it has lots of helpful info. I found some
other informative vb stuff at this link
http://www.microsoft.com/officedev/articles/integoff.htm .

I also tinker with Visual C++ and the microsoft date-time picker control is another alternative for a clock/calender which I have successfully used in Access,
VB6.0, VC++5.0/6.0. It does require registration since it is an ActiveX
control but that can be done with Basic or C++ without too much pain.

For VB I found it on codeguru at

http://codeguru.developer.com/vb/articles/1771.shtml .

It was a winner for their VB contest. As mentioned you can also use the regsvr32.exe to do it but this does it in code that can be embedded in your own app. The date-time picker control comes with VC and VB and resides in mscomct2.ocx.

Pickup your free calendar and clock forms for Access from

http://www.gr-fx.com/wizards

-------------------------------------------------------

MAPPING AND MINING SOFTWARE IN ACCESS

Garry Robinson, with some help from Scott McManus, demonstrates techniques that can be used for statistics, graphs, and extending your crosstab queries, regardless of how you want to lay out your data.

Purchase this revolutionary article from Pinnacle for only $5 and find out how to do some really interesting things with grids using Access queries ….

http://www.pinpub.com/access

 

-------------------------------------------------------

VB123 WINS VB-ZONE SITE OF THE WEEK.

VB123 gets a good review from one of the biggies

http://www.devx.com/free/links/siteview.asp?content_id=1335

For a new site every week try

http://www.vb-zone.com/free/site.asp

Issue 12

 

-------------------------------------------------------
GOING TO THE VERY NEXT LINE

Have you ever hit carriage return in Frontpage and ended up with
a large break between the two lines ?
Similarly have you ever enter text into a button control and just
wished that you could put a word on the next line without changing
the size of the button.

Try holding down the the Shift Key and then hit the Enter Key

Similarly a message box can look a little messy when you have
a big long sentence in it.   Here is how you break up the lines.

MsgBox "First Line " & vbCrLf & "Second line"



-------------------------------------------------------
NEW FEATURES IN ACCESS - TRY THEM OUT IN GRAF-FX

Here are two newish programming features for Access that I am
very enthusiastic about because they seem to provide benefits for
end users.  Try them out at

--->    http://www.vb123.com/explore

SubDataSheets
Access 2000 has a feature called SubDataSheets that will allow
you to see related records from tables/queries in another table or
query.  It does this by providing an expander button to display
the records.  Read more at

http://www.vb123.com/Toolbox/00_access/subdatasheet.htm

My big tip here is not to be scared of giving your end users
access to these subdatasheets. Simply opening the enabled tables
in readonly mode.

DoCmd.OpenTable "MyTable", , acReadOnly

If you want to see the power of these subdatasheets and have
Access 2000, download the latest version of Graf-FX where you will
will see the detail rows for any consolidation query enabled
on the fly.

Other features added in the latest version of Graf-FX include
. Consolidation queries and graphs can be easily sorted in order
. Total number of rows can now be limited to say 50% or Top 10 etc.
  which compliments the new sorting options


-------------------------------------------------------
GETTING ( ACCESS ) ARTICLES FROM MICROSOFT
by Richard Killey

The Microsoft Support site has hundreds of articles, free for the
asking. There are two common ways to get these articles.

(1)  Via the Web

Begin by pointing your browser to  http://support.microsoft.com .
It used to be that the first time you did this you were asked to
register. I'm not sure if this is still required. It's worth it,
but if you don't want Bill to know anything about you, there is
always method 2 below.

Once in, you are presented with a search screen. Fill in all the
criteria and click "go". I spent hours here when I first found
the site.

(2)  Via E-mail

This method is even simpler, and less time consuming, as long as
you know the article's reference number. Just send an e-mail to
mshelp@microsoft.com with the article number as the subject.

As an example, a common question on the Access newsgroups is,
"How do I get a combo box to affect the data that shows up in
another combo box?"

To get an answer to this question, send an e-mail to
mshelp@microsoft.com with a subject of Q97624. The answer will
usually come back to your e-mail in-box within a few minutes.
There have been times, however, when it took overnight. If I am
in a hurry, I can always revert to method 1.

Here are some other Access 97 articles to get you started:

· Q162067 - articles about Reports
· Q162066 - articles about Forms
· Q162065 - articles about Queries
· Q162062 - articles about User Interface and Miscellaneous
· Q162064 - articles about Tables and Database Design
· Q162065 - articles about Queries
· Q162068 - articles about Modules, Macros, and Expressions
· Q162069 - articles about Internet Features
· Q162070 - articles about Interoperability

Here's a problem I have seen mentioned often:
· Q191224 - Microsoft Access cannot open because there is no
license for it on this machine.

A comprehensive list of Access article numbers can be found at

http://www.netfolk.co.uk/netfolk/access/articles/index.html


Richard Killey is an Access trainer/programmer in Winnipeg, MB,
Canada.  You can visit his Access Tips Site at
http://www.comeandread.com/access or e-mail him at
msaccess@comeandread.com .

-------------------------------------------------------
ACCESS 97 PROPERTIES? - HAS ANYONE GOT AN ANSWER

In A97 how do you create the DB Title property (and others) in
a DB that is created in code as part of an application. The DB
is empty when initially created and has no properties set and
indeed some don't even exist until they are initially set. It is
easy enough to create the property in code from within the New DB
itself, but the requirement is to create it in the New DB from the
first DB immediately after the first database creates the New one.
The particular properties are those you see from the System tab in
File|Properties menu.

Any insights into this would be appreciated.

Regards

Peter Hallinan <peter @ 3rdmillennium.com.au>

Anyone how answers this question will receive a free copy of
"The Toolbox" plus a copy of Peter's software database
synchronizer.



=================================================

Online Software and Book Purchases From Amazon.Com

Try out the new electronics section at Amazon.

Click here to enter the store at places suited to
the readership of this magazine

    http://www.vb123.com#software

================================================


-------------------------------------------------------
ACCESS 2000 CONVERSIONS - GOTCHA (PLUS DECOMPILE)

I was writing an Access article where I need to change the properties
of a query in the database using DAO (the old method) because the
new method in ADO did not provide that sort of Jet functionality.

The declarations in my code were

   Dim db As Database
   Dim tdf As TableDef
   Dim prp1 As Property

Most of the software worked but one thing didn't and the error
message suggested something that was ambiguous in the help.   The
reason I eventually found was that the software was using the ADO
library rather than the  DAO library for my declarations and some
methods were not supported.   So after many hours of hacking
around, I found that I had to changed the declarations to

   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim prp1 As DAO.property

This fixed my problem.   The simpler but more error prone
alternative is to look at the library references and move
the DAO reference to be higher than the ADO reference.
This would be good at the start of a conversion but would
still leave open the issues encountered here.

Other DAO references you need to change using Find and Replace
will be

as Container  -> as DAO.Container
as Workspace  -> as DAO.Workspace
as Document  -> as DAO.Document
as Property   -> as DAO.Property
as TableDef  -> as DAO.TableDef
as QueryDef  -> as DAO.QueryDef
as Recordset  -> as DAO.Recordset
as dbEngine  -> as DAO.dbEngine
as Field  -> as DAO.Field

Read more at
http://www.msOfficePRO.com/features/2000/01/vba200001sf_f/vba200001sf_f.asp

Now I went to convert a database and when I started compile all
modules I continued to get a GPF (General Protection Fault).
Remembering the / decompile option from a Sydney Access users
group meeting, I came up with the following addresses on the topic

http://www.mvps.org/access/bugs/bugs0008.htm

http://www.trigeminal.com/usenet/usenet004.html?1033

After decompiling the problem went away !!!

Another bonus is that the database did actually shrink in size.
Graf-FX.mdb  shrunk from 4.4 to 3.5 megabytes.

Editor NOTE:   Please do this decompile on a backup database
               as you may damage your existing database.


-------------------------------------------------------
DIFFERENT VISUAL BASIC GRAPH OBJECTS

Every now and again I get questioned as to alternatives to the
Microsoft chart and graphs controls that can used in Visual Basic.
Cannot make a recommendation but zdnet had a pretty good write up
about the different graph objects as follows

Chart FX
ChartPro
Graphics Server
ProEssentials
Olectra Chart

http://www.zdnet.com/products/stories/reviews/0,4161,316854,00.html


------------------------------------------
GOOD READING AND USEFUL SITES

A really serious discussion on how to add your own splash screen
to an Access application and replace the Access application
splash screen.  Good article

http://www.advisor.com/wArticle.nsf/w/AV9901.FISHS01

Paul Letwin's site
http://www.mcwtech.com/Downloads.htm

Access 2000 - Useful Changes
http://www.zdnet.com/zdhelp/stories/main/0,5594,2214470,00.html

DAO isn't going away
http://www.microsoft.com/Accessdev/ARTICLES/AC201.HTM#WORKING

Parameters in Access queries - A quick tip
http://www.advisor.com/wArticle.nsf/w/AV9912.EDWAK015

Saving and returning default values in Access
thttp://www.advisor.com/wArticle.nsf/w/AV9912.DUNND

Thinking about converting your Access 2000 applications to ADO
Try these thoughts from Mary Chipman
http://www.advisor.com/wArticle.nsf/w/AV9911.CHIPM13

Ken Getz passes some early comments on converting from DAO to ADO
http://www.advisor.com/wArticle.nsf/w/MMB9902.GETZK15

This link presents a broad array of resources to help IT professionals
deploy Office 2000 more easily and efficiently. Thats Microsofts
view anyway !
http://microsoft.com/office/enterprise/deployment/default.htm

------------------------------------------------------

 

GOOD READING AND USEFUL SITES

Search the net from inside visual basic. This is really easy to install and works straight away. Highly recommended search tool but it only works in Visual Basic. If you are programming in Access, this site is good for things such as ADO, Office Automation and other external stuff.

http://www.codehound.com/

* Is There Any Way to Limit the Amount of Memory Used by ADO?
http://www.inquiry.com/techtips/thevbpro/answer.asp?pro=thevbpro&docID=4285

Codeguru ran a Visual Basic competition.

http://codeguru.developer.com/vb/vb_codeguru/codeguru.shtml

Here are some of the entries

Raising ColumnResize event in MSFlexGrid
HTML Syntax Highlighting
Leap Year Validation
Standard Name Formatting Routine
Improved Mid$ Statement
VB Syntax Coloring
Formatting Strings
Run an application inside of the previous application instance

Database web sites and articles at CodeGuru

http://codeguru.developer.com/vb/Database/index.shtml

Advanced ADO for the VBA Programmer
By Stephen Forte

http://www.microsoft.com/ACCESSDEV/Articles/lavs305.htm

 

Notes On The Access 97 Treeview Control
http://www.vb123.com/Toolbox/00_accvb/treeview.htm

Searching for help online. Try
http://www.myhelpdesk.com/

All about using the debugger.
http://www.msOfficePRO.com/features/2000/03/vba200003ec_f/vba200003ec_f.asp

If you think you are not being watched when on line, then look at the patent from one of the biggest banner distribution companies. They know where you have been, what you like and even how many times you have seen an advertisement. Think of that for a database.

http://www.patents.ibm.com/details?&pn=US05948061__&s_detd=1#detd

 

Issue 11

COLOUR THOSE DULL REPORTS  by Terry Bell

When you have a report that has blocks of similar, single line
details, it's hard on your eye picking out a single line.  So,
you can put a ruler under the line you're interested in - or you
can alternate the backcolor of successive detail lines between
white and pale grey.

Here's a quick way to do it:

(1) Put the following function into a standard module

Function ShadowAlternateLines()
  CodeContextObject.Section(0).BackColor = _
  Abs(CodeContextObject.Section(0).BackColor - 31646433)
End Function

(2) Put "=ShadowAlternateLine()" in the OnFormat event of the Detail
section of your report.

If you already have some code being executed in the OnFormat event,
just add "ShadowAlternateLines" to the code.

(3) Make sure all the controls on your detail line have the BackStyle
set to transparent, and the initial BackColor of the detail line
should be white (the default).

That's all.  From then on, you can apply alternate shadow lines to
any report by following steps 2 and 3.

Incidentally, the code has an interesting way of toggling between
two values. The color White has a value 16777215.  The particular
shade of Grey I use has a value of 14869218. Add the two together
gives 31646433, and the Abs function does the rest.
If you want to pick a different color, add that color's value to
the white value and use the result in the Abs expression. Or
alternate between two different colors.

Of course, you can achieve the same result with an If statement,
and it would be a lot easier to understand what the hell is going
on. But you've got to have fun sometimes ...

Terry Bell, Melbourne, Australia



-----------------------------------------------
CONVERTING EXCEL DATA - ITS THE WRONG WAY ROUND

Have you ever tried to convert data from Excel to a database
such as Access and realised that the first column of data actually
holds the fieldnames that you want to import and the data goes from
left to right rather than down the page.  Well the trick that you
need to use in the spreadsheet is the transpose option.

Select the cells that you want to switch.

Click the Copy button (Ctrl C).

Select the upper-left cell of the paste area.
The paste area must be outside the copy area.

On the Edit menu, click Paste Special.

Select the Transpose check box.

This will rotate the data and make it suitable for importing.

You may then want to give the data a range name to make it
easier to import.

-------------------------------------------
ACCESS 2000 TIP - USEFUL PROPERTIES

When you have a form open and wish to see the source code
under a button, click on the View menu and choose properties.
Now switch to the Events Tab control and select the On Click
event.  Now you can click into the code without closing the
form.  Same applies for all properties on a form. 

If anyone knows how to turn off the properties sheet permanently for
end users, let us all know !!!!


-------------------------------------------
UNIQUE NUMBER TIP

Want to add the next unique number to a field but do not want to
or cannot use autonumbers, try this little bit of code for a field
called ClientNo.  The code follows the command button code for
Add Record wizard

    DoCmd.GoToRecord , , acNewRec
    Me!Clientno = DMax("ClientNo", "Client") + 1

Not the most efficient bit of code ever, but it works.

-------------------------------------------
ACCESS 2000 - SQL SERVER BUG


An interesting insite into the wonderful world of finding and
posting of Microsoft bugs and then tracking down the issues. 

15seconds.com posted this warning in their newsletter

"Bug Warning: Don't use Microsoft Access 2000 against your SQL
Server.  There is a Microsoft confirmed bug in Microsoft Access
2000 (no patch available) that over rides inserts to tables with
the previous row.  Causing you to lose the data you are
entering for data that exists in the next row up.  This only
happens with big tables, or slow connections."

Read updates for this bug in a new posting at 15seconds.com
ftp://ftp.15seconds.com/AccessBugKB.txt

Alternatively try the Access Newsgroup for all the latest comments.
news://msnews.microsoft.com/microsoft.public.access.odbcclientsvr


Peter Vogel Editor of www.pinpub.com/access (Smart Access) says

"The bug, by the way, isn't quite what it seems. The record only
appears to be duplicated. If you requery the recordset you see
your new record and only one copy of the previous record. You can
get around this by issuing an SQL Insert query (which is what's
being done in the background, anyway)."

Here's a reply from Lisa Gurry, Office product manager at Microsoft
that was sent to Ken Spencer at www.32x.com

"When a new record is added to a linked SQL table and a cursor is
moved to a different record, the new record that has been added will
appear to disappear and be replaced by a duplicate of the previous
record. This issue will occur only if the SQL Server table has an
identity column and contains more than approximately 400 records.
When a record is added, a duplicate of the previous record
appears. The new record is not displayed until you refresh the
recordset. This behavior is related to the way Access requeries
the SQL Server database. This issue can affect Access 2000 users
on any operating system using SQL Server 6.5 and SQL Server 7.0
in this particular scenario.
"Customers should know their data is not lost and they should
not try to delete the apparent duplicate record because this will
actually delete the original record. Instead, they should refresh
the recordset to see the new record.
"Customers can avoid this issue in three ways: First, use an
Access project and open the SQL table directly. Second, if you
see this behavior in a form, use Visual Basic for Applications
(VBA) code in the BeforeInsert and AfterInsert events to requery
the data and move the form to the newly added record. Third, if
you see this behavior in a table, resort the table or close and
reopen the table."
Lisa said that Microsoft will soon post a Knowledge Base
article on its Web site about this problem.

"Glad we sorted all that out" - Ed


------------------------------------------
GOOD READING AND USEFUL SITES

Lots of good resources for programming in visual basic.
http://www.vbinformation.com/tutor.htm


Lots of Access links
http://www.somuch.com/listem.asp?TopicID=1&CategoryID=2


------------------------------------------

-------------------------------------------------------  

PREVIOUS EDITIONS OF TIPS-FX
http://www.vb123.com/news/

Explore your data visually using our popular Access
data mining shareware

---> http://www.vb123.com/explore

View our web site on your computer rather than the slow
old web and have access to all the software discussed in
the articles and information pages at www.vb123.com

---> http://www.vb123.com/Toolbox/

So thanks for reading our popular newsletter.

Feel free to make comments, copy the email to a friend
or maybe even contribute to the next edition.

°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°`°º¤ø,¸¸,ø¤º°`°

Garry Robinson - Software Consultant



Click on this button to read the previous edition of Tips-FX 

Published  2000-05