ms-access faq
Michael O Shea
Hi
About 6 or 8 weeks ago, I said that I'd write an Access FAQ.
I did this for basically one reason, I felt that there was an genuine
desire within the Newsgroup for text based 'old style' FAQ. Apparently
there wasn't. I through together a compilation of everything that i
could find, (knowing that some of it was probably wrong or out of
date) and then asked for some hardy volunteers, to help proofread the
FAQ. Now i got lots of ppl saying "Yeh sure send me a copy - I'll read
through it", and so I did.
But it was the old story of lots of people wanting something for
nothing, and very few ppl bothered to spend the few minutes to write a
reply (to suggest changes - or to point out errors or whatever) and
basically the upshot of it was that I lost intrest and forgot about
it.
Today I recieved an email out of the Blue which actually reminded me
that i still had the FAQ lying around.
So i decided that What the heck I might as well dump it on the
Newsgroup the way it is. Many moons ago someone told me that the best
way to find the right answer on the internet, is to post the wrong
answer. So here goes
Note:
It comes in about seven different parts the first of which is a
complete list of all the questions in the FAQ, and the rest are the
actual answers.
If parts go walkies,.. , it can also be gotten @
http://www.iol.ie/~moshea/access.htm
Michael
Michael O Shea
___________________________________________________________________________
The MicroSoft Access Faq
___________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now, 05/8/96, this file
is just beginning, and my main aim is to get it up and running. To do this, I'm
going to start just by compiling together a lot of the information that is
already out there. If i upset anyone by including something of their's and dont
give full credit, please email me and i will rectify it as soon as possible
_______________________________________________________________________________
General Access Questions
_______________________________________________________________________________
Where can I find information on microsoft Access?
Whats the Story with Microsoft Certification?
What are these access kb(KnowledgeBase) articles and where can I find them?
I just opened Access, and none of the toolbars appears, how can i get them
back?
As I develop form modules in Microsoft Access 95, over time, I
receive an "Invalid Page Fault in VBA232.dll" error message. Why's this?
Are there recognized naming conventions For Microsoft Access?
Can I run an Access database from a CD-ROM or other read-only device?
Do I need to have Share loaded in my Autoexec.bat file to use Access?
How do I activate the "snap-to" feature of the 9.0 MS Mouse driver with
Access forms?
How do I change the text of the Access title bar?
How do I get a complete list of Access error numbers and their messages?
I get a General Protection Fault trying to run Access. What do I do?
I just installed Access 2.0 and the Readme file is garbage when I open it in
Notepad. What do I do about this?
I've read that Access corrupts data files when used on a network.
What's the story?
In Access 1.x I could suppress the splash screen by including the
undocumented /s switch on the command line. How can I do this in Access 2.0?
What are .LDB files?
What can I do to try and make Access run faster on my computer?
What is the current version of Access?
When I hit shift-F2 to open the Zoom Box, nothing happens. Why?
or When i choose one of my wizards, i get a compiling error, whats
wrong with my wizards?
Why can't I launch Access 2.0 with the Microsoft Office Manager (MOM)?
Why is there no serial number on my copy of Access when I select Help|About
from the menus?
Will Access 2.0 work with Access 1.1 files?
How do i Hide the Database Window at Startup?
Can I Determine if a Specific Windows Program Is Running?
Can I make my Access Application be Modal within windows?
Can I create a custom Startup "Splash" screen for my Access Application
I'm having trouble making my VB program communicate with my Access 2 Database.
How Come?
How come when i maximize a form within Access, i can see its Control Box,
and restore button, even though I've have their properties set to no? (and also
all other open forms, now become Maximised)
_______________________________________________________________________________
3-1: Tables
_______________________________________________________________________________
How long can my table name be?
How large can my table be?
How do I reset counter fields. During testing I've used a lot of dummy values,
but I want my counters to be reset when I deliver my application.
Can I change the starting value of a counter type field?
Can i hide a table so that it doesnt appear in the Database window?
Can i display text field entries all in uppercase, no matter how they are
entered?
How do i enter the value of a field in the previous record in to a new record?
Can text stored in a memo field be formatted with different fonts or attributes
such as boldface or underlining?
How can I check to see whether an attached table is still attached?
How can I set referential integrity between my tables and tables in attached
databases?
Suddenly all my databases have extra tables starting with "Msys" and "USys" in
them. What are these and how do I get rid of them?
How can I re-connect attached Tables at Start-up?
_______________________________________________________________________________
3-2: Queries
_______________________________________________________________________________
Can i get a query to return a specified number of the highest or lowest values
in a field?
Can i use an access Basic variable as a criterion in a query?
I want my parameter query to select all records when a user doesnt specify
parameters, how can i do this?
Can I export a query directly without having to do a Make Table query first ?
How can I set up a Query By Form (QBF)? ( A From which generates SQL)
How can I write a query to return every fifth record from a table?
How do I find all the records that are in one table but do not have
matches in another table
How do I find duplicate key values?
What is Rushmore and how does it work?
What is the difference between Unique Values and Unique Records?
Why do large queries cause my Novell Netware file server to crash?
When I attempt to generate a certain report, I get the message "Query Too
Complex" but when I run the query the report is based on, the query runs OK.
What does this mean?
How can I create an app where a user can use the results of a query in
a new or existing Word template simply by clicking a button in Access?"
_______________________________________________________________________________
3-3: Forms
_______________________________________________________________________________
" Is there any way of detecting whether a form is open or not such that it can
be used in a conditional macro?"
Can i create something on myform like the tooltips labels that appear when i
point the mouse at a toolbar button?
How can I change the cursor on my forms to something bolder?
How can I determine the type of a control?
How can I display a listbox showing the last records rather than the first
records? How can I select the first item in a listbox as the default?
How can I set my forms to use the user's system colors as defined through
Control Panel?
How can I tell whether my user has applied a filter to one of my forms, and
retrieve the SQL statement for the filter?
How do I copy all the information from the previous record on a form to a new
record?
How do I create custom navigation buttons on my forms?
How do I display a form without the caption bar at the top?
How do I incorporate photos in my forms, as Microsoft did in the Employees for
the Northwinds database
How do I select a control on a form without accidentally moving it?
How do I tell when the user has moved to the blank record at the end of a table
by pressing the Next button on my form?
How do I use the ! symbol in my input masks to allow right-to-left data entry?
How do I use the .PictureData property of a command button?
How do I write information to the status bar and gas-gauge indicator at the
bottom of the screen?
I change the forecolor of a control using the color builder and I do not get the
text color I selected. Why not?
Is there a tool or method to convert Visual Basic forms to Access forms or vice
versa?
When I change a color on a form, why does the correct color only show in form
view, and not in design view?
When I copy a command button from one form to another, any code attached to the
button gets lost. How can I fix this?
When I Tab past the last control on a form, how can I keep Access from going to
the next record (or backwards when I Shift+Tab back from the first control)?
Why do I get a #NAME error for a calculated control on a form or a report?
How do I reconnect to attached Tables using Access Basic?
How do I search for records in an Access 2.0 form?
I have a control on a form which i use to accept data, this
control is a "memo" data type field. My problem is that when the data
in this memo field goes beyond the size of the control on the form, I
haven't found a way to print all of the data. This doesn't happen
often, however when it does, I would like an easy way to print all of
the information.
Is there some way to get Access to automatically adjust the
forms sizes based on what my screen resolution is ?
Is there a way to nest combo boxes?
What is a form's TAG property for?
How do I fill a listbox with a list of all the reports in my database?
How do I refer to a control on a subform?
How can I Hide a Control When It Loses the Focus?
Moving controls around forms can be tiresome, trying to align them
just right, is there anything i can do?
Can I Use a Combo Box to Find Records
Is it possible to Return to the same record next time a form is opened?
_______________________________________________________________________________
3-4: Reports
_______________________________________________________________________________
I want to show a total at the bottom of each page, but i keep seeing #error
instead, whats wrong?
Can i choose to not print a section of my report, depending on a specific
condition?
How do i make my report tell users that there are no records to print when
the reports query returns no records>
"How do I avoid ""Out of Memory"" errors on my report?"
"How do I print a certain number of labels based on a ""count"" field in a
record"
How do I print reports to a file?
I have overlapping rectangles on my reports, but all of both rectanges print on
the page even though the back one is properly hidden on the screen. How do I
fix this?
Why do I get extra blank pages between each page?
Why do I only get a single mailing label on each page of a Wizard-created
mailing
label report?
Where do I find more information on report techniques
_______________________________________________________________________________
3-5: Macros
_______________________________________________________________________________
How can i set Access to maximise my Database window automatically when i open
a database?
Can i get access to ignore a key such as F1?
How do I temporarily comment out a line in a macro?
_______________________________________________________________________________
3-6: Modules / Access Basic
_______________________________________________________________________________
I have a line of code that is very long. Can i continue it on to the next line?
How can I use the Windows API to retrieve the hwnd of a control on an Access
form and alter its properties?
How do I calculate someone's age from their birthdate?
How do I change a field to Proper Case (first letter of each word capitalized)?
How do I code an SQL string in Access Basic that includes a reference to a
variable or control?
How do I copy a file directly under control of Access
How do I dial a modem from Access?
How do I execute an internal DOS command such as COPY
How do I get the fully qualified path and file name of the currently-open
Access database?
How do I play a sound file?
How do I read and write information from .INI files?
How do I send a fax from Access using WinFax?
How do I set and retrieve global variables?
How do I use DDE to poke information into an Access table?
Is there a way in Access Basic to determine the current record number so
that the record number can be used in a GoToRecord action?
Is there any way to have access link to MS Word 6.0 to do spell checking on
text or memo fields?
"Why does Access put ""Option Compare Database"" at the top of every
module I create?"
How do i calculate the number of Business Days between 2 dates?
How can I Pad Numbers with leading Zeros?
How do I get the Windows File/Open common dialog to appear and return the
selected filename
How do I run an internal DOS command from within Access Basic, if I try
x=Shell("copy file1 file2",1) I get a message "File not found".
Now I know how to shell an internal command such as copy, I need to wait
for it to finish before continuing, at present Access will carry on to the
next statement, which needs to access the new file that's just been copied.
Am I able to Play an Avi file within Access?
I have some functions I've written that I could use in other applications.
How do I put them in a library?
How can i find how many of a given day are in any month?
Can i get my Access Basic Code to wait till a user closes a form before
continuing processing?
_______________________________________________________________________________
3-7: Wizards, Tools, OLE, DDE
_______________________________________________________________________________
How do I create a Help File to use with Access?
What Is the Microsoft Access Service Pack Version 2.0?
Is there any easy way to Link Microsoft Access data to Microsoft Excel?
_______________________________________________________________________________
3-8: SQL & ODBC
_______________________________________________________________________________
Im working with generating queries directly from SQL, but im having some
problems (incorrect syntax) with the date section of my SQl
Can I use ODBC to read FROM Access 2.0 files?
I'm getting random GPFs when attaching to Btrieve tables. What now?
I'm having a problem with the indexes on my attached FoxPro files.
How do I fix this?
What do I need to do to make Access work with Oracle files on my server?
What should I know to move my Access tables to MS SQL Server?
_______________________________________________________________________________
3-9: Security
_______________________________________________________________________________
Is an Access Database Secure?
After securing a database, what are some methods to assign permissions to users?
How do I secure an Access database?
How should I secure databases on a network?
"I just used Security | Users to create a new user, but when I try to sign
in using that new ID and password I get ""Not a valid account name or
password."" Why?"
I wrote some Access Basic to retrieve group and user identities, and it
worked fine when I tested it, but it fails when users run the code. Why?
If I use RWOP queries to help secure my tables, how can I prevent users
with full retail Access from altering the query definitions? "
Is there a SIMPLE explanation of Security? "How about a fun one instead?
Why is it that after securing my application following the manual, anyone
with another copy of Access can still get in?
_______________________________________________________________________________
3-10: Databases
_______________________________________________________________________________
How and why can I keep my data in one database and my code in another database?
How can I attach a custom bitmap to my toolbar buttons?
How do I get a list of all the objects in my database?
How do I see the code in the PIM database, or some other example database?
Is there a pre-made Access database to do my inventory/sales/order-tracking
/grading/etc.?
Is there a way to print out the Relationships window from Access 2.0?
_______________________________________________________________________________
3-11: ADT
_______________________________________________________________________________
Can i compile my access database and distribute it to non access owners?
How do I install the ADT on a network workstation that does not have full
Access installed locally?
How much memory will my Access application need when I use the ADT to
create a compiled version of it?
"If the workgroup administrator requires SHARE.EXE or VSHARE.386, do I need
to ensure that one of these files is loaded prior to running my custom setup
program?"
"Why do I get Reserved Error -1809 at the end of a custom setup program
created with the ADT setup wizard?"
_______________________________________________________________________________
3-12: Performance
_______________________________________________________________________________
"Access 2.0 now takes much longer to initially load, and my Access
application forms performance under 2.0 now appears 'sluggish'. What has
changed, and what can I do initially to improve my Access 2.0 performance?"
How can I make my combo and list boxes faster? "
"I expected to get major speed improvements and I have not seen in big
increases in performance. What is the Rushmore I hear about and why is it not
improving my performance?"
"I want to get all the performance gains that I hear about in 2.0 but my data
is used by users that will not be upgrading for awhile. Can I still use 2.0?"
"The more users connected to the databases the longer it takes to ""open""
the databases. This can sometimes be as long as 30-60 secs for each
database. Is there any way to speed up the opening of the databases?"
Why is my Access application running slower now that I've converted it from
1.1 to 2.0?
How Can I Optimize Query Performance?
_______________________________________________________________________________
3-13: Access 95/7
_______________________________________________________________________________
So whats new in Access 95?
Is There much that i should be aware of in deciding whether to convert to
Access 95 or not?
How do I retrieve a User's ID from Windows 95?
Michael O Shea
___________________________________________________________________________
The MicroSoft Access Faq
___________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now, (SeePART1), this file
is just beginning, and my main aim is to get it up and running. To do this, im
going to start just by compiling together a lot of the information that is
already
out there. If i upset anyone by including something of their's and dont give
full
credit, please email me and i will rectify it as soon as possible
_____________________________________________________________________________
Part II
_______________________________________________________________________________
_______________________________________________________________________________
3: Frequently Asked Questions
_______________________________________________________________________________
_______________________________________________________________________________
3-1: Tables
_______________________________________________________________________________
How long can my table name be?
Your table name can be up to 64 characters long, the name should be unique and
meaningful, but short enough to make easy to type later ( see naming conventions
in part one)
_______________________________________________________________________________
How large can my table be?
A table can contain an unlimited number of records, but it can be no larger
than 1 gigabyte. Since the 1 gig limit applies to the database as well, a table
this large would be the only object in the entire database. (note : a table is
also limited to a maximum of 255 fields)
________________________________________________________________________________
How do I reset counter fields. During testing I've used a lot of dummy values,
but I want my counters to be reset when I deliver my application.
After deleting your dummy data, compact the database, this will reset counter
fields to the next sequential value, e.g. for a table with no records this
will be zero but if the table still contains data then it will be 1 + the
highest value in the table
_______________________________________________________________________________
Can I change the starting value of a counter type field?
Yes, you can.. to do this:
Create the first table that contains the counter type field that you want to
start on another number. Dont enter any records.
Create a second table, with a single long integer number type field that has the
same name as the counter field inthe first table
Create a record in the second table by entering a number one less than the
number
you want to start the counter at in teh first table.
Create an append query, to append the one record in the second table to the
first
table, and run it
Delete the second table, and start entering data in the first table
_______________________________________________________________________________
Can i hide a table so that it doesnt appear in the Database window?
If you want to hide a table in the database windows so that other users can't
select it, preface the table's name with 'Usys'. For example, if your table's
name is employee, change it to UsysEmployee. This makes your table into a system
object which access does not display. To see this table later, choose view,
options, show system objects.
_______________________________________________________________________________
Can i display text field entries all in uppercase, no matter how they are
entered?
Yes. Go to design view of the table in question, go to the field and then
its format property. Enter > for this property to force all entries to
display in uppercase ( or < to force lowercase)
_______________________________________________________________________________
How do i enter the value of a field in the previous record in to a new record?
you may find that you use the same entry over and over again, in one field of
your table. To copy the entry made for the same fieldin the previous record,
just press CTRL+' (apostrophe). Access copies the same fields entry from the
previous recod to the new record. (note : When the same entry is used most
of the time, add that entry as the fields Defaultvalue Property
_______________________________________________________________________________
Can text stored in a memo field be formatted with different fonts or attributes
such as boldface or underlining?
Text displayed on a form or report can be formatted, but only with one
style per field; you can't mix bold and regular text, for example, in the same
field. If you need to do more flexible formatting, you should probably
investigate
using Microsoft Word to extract the fields from your database and format them
there.
With Access 2.0 supporting OLE Custom Controls, there is also the possibility
that
someone will create a custom control supporting RTF, but no vendor has announced
such a product yet.
_______________________________________________________________________________
How can I check to see whether an attached table is still attached?
Try accessing the count of the fields of the table with error trapping if you
want to avoid the overhead of actually creating a recordset. Eg.
' Check to see if tables in an external database are attached properly.
On Error Resume Next
wDummy = db.TableDefs("AttachedTableName").Fields.Count
If Err Then
'Attach tables here
End If
_______________________________________________________________________________
How can I set referential integrity between my tables and tables in attached
databases?
Access won't set up default relationships or check referential integrity
between attached databases of any sort. You'll have to handle any such
requirements in code, or import the files instead of attaching them. Note
that you can set up "on-the-fly" relationships in queries involving attached
tables as necessary, just by dragging the linking fields from one table to
another.
_______________________________________________________________________________
Suddenly all my databases have extra tables starting with "Msys" and "USys" in
them. What are these and how do I get rid of them?
These are the system tables, and you don't want to get rid of them because they
are necessary for Access to function. You can hide them by choosing Options from
the View menu, selecting the General category, and setting "Show System Objects"
to "No".
_______________________________________________________________________________
How can I re-connect attached Tables at Start-up?
Reconnect Attached tables on Start-up
If you have an Access application split into DATA.MDB and PRG.MDB
and move the files to a different directory, all tables need to be
reconnected. Peter Vukovic's function handles the reconnection.
Function Reconnect ()
'**************************************************************
'* START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'* AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'* WHEN THE 'DATA.MDB' AND THE 'PRG.MDB'
'* ARE IN THE SAME DIRECTORY!!!
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer
Set db = dbengine.Workspaces(0).Databases(0)
'*************************************************************
'* RECOGNIZE THE PATH *
'*************************************************************
For i = Len(db.name) To 1 Step -1
If Mid(db.name, i, 1) = Chr(92) Then
path = Mid(db.name, 1, i)
'MsgBox (path)
Exit For
End If
Next
'*************************************************************
'* CHANGE THE PATH AND CONNECT AGAIN *
'*************************************************************
For i = 0 To db.tabledefs.count - 1
If db.tabledefs(i).connect <> " " Then
source = Mid(db.tabledefs(i).connect, 11)
'Debug.Print source
For j = Len(source) To 1 Step -1
If Mid(source, j, 1) = Chr(92) Then
dbsource = Mid(source, j + 1, Len(source))
source = Mid(source, 1, j)
If source <> path Then
db.tabledefs(i).connect = ";Database=" + path + dbsource
db.tabledefs(i).RefreshLink
'Debug.Print ";Database=" + path + dbsource
End If
Exit For
End If
Next
End If
Next
End Function
Provided by Peter Vukovic <10070...@compuserve.com>
_______________________________________________________________________________
3-2: Queries
_______________________________________________________________________________
Can i get a query to return a specified number of the highest or lowest values
in a field?
Yes! by using the topValues property
Sort your query by the field from which you want to select thetop or bottom
records. Open the Queries properties. type the number or percentage of records
you want to display in the topValues property.
_______________________________________________________________________________
Can i use an access Basic variable as a criterion in a query?
The only way to refer to an access basic variable is by using access basic
code. therefore, you need to create a function that equals the value of
that variable, it could look like :
function GetValue()
getvalue = variablename
end function
_______________________________________________________________________________
I want my parameter query to select all records when a user doesnt specify
parameters, how can i do this?
To produce a parameter query that displays all records when no criteria are
supplied, you need to create a criterion that uses the wildcard character.
This query either uses the value provided by the user or selects everything
for Example:
like "*" & [Enter a Value] & "*"
_______________________________________________________________________________
Can I export a query directly without having to do a Make Table query first ?
"Although it is not documented anywhere that I know of you actually can export
directly from a query rather than having to do a Make-Table query first. The
trick is to do the export via a TransferDatabase (or TransferText or
TransferSpreadsheet) macro action. In the field where you specify ""table:""
you can just go ahead and enter the name of your query. When you run the macro
your query will run and will be exported in the appropriate format"
_______________________________________________________________________________
How can I set up a Query By Form (QBF)? ( A From which generates SQL)
See MSKB Article number Q95931, or the Solutions sample database that ships
with Access 2.0.
_______________________________________________________________________________
How can I write a query to return every fifth record from a table?
Here's a general-purpose way to write a query to return every nth record from
a table. It will choose equally-spaced records, but not start in any
particular spot, i.e. it might return the 1st, 5th and 9th or 2nd, 6th and
10th records if set to a spacing of 4.
First, create a new function:
Function PlusOne (var As Variant)
Static i As Integer
i = i + 1
PlusOne = i
End Function
Then, create your query. Drag down whatever fields you want to see in the
output. Add one more column to the query, with these properties:
Field Expr1: PlusOne([MyField]) Mod 5
Show No
Criteria 0
You can use any field in your output in place of MyField -- it doesn't
matter which one you use. If you want every 7th record, use Mod 7, for every
10th record, use Mod 10, and so on."
_______________________________________________________________________________
How do I find all the records that are in one table but do not have
matches in another table
"See MSKB article number Q95326 (online only), or use the new Query
Wizards to create a ""Not-In"" query."
_______________________________________________________________________________
How do I find duplicate key values?
See MSKB article number Q98230.(online)
SUMMARY
=======
When you import data from other databases, records may contain
duplicate information in the primary key fields. You can run a
make-table query to eliminate the duplicate data, but if key fields
for two or more records contain the same value, you may want to
reconcile the information manually to make the records unique.
This article includes sample queries and shows how to list duplicate
information for keys containing single fields or multiple fields.
MORE INFORMATION
================
Single-Field Keys
-----------------
Note that this example assumes you have a table called MyTable, with the
primary key on the Name field.
1. Open MyTable in Design view and index the Name field, as shown
below:
Table: MyTable
-------------------------------
Field: Name
Indexed: Yes (Duplicates OK)
2. Create the following query based on MyTable:
Query: Find Duplicates
----------------------
Field: Name
Total: Group By
Field: Name
Total: Count
Criteria: >1
The equivalent SQL statement is as follows:
SELECT DISTINCTROW
Name, Count(Name) As CountOfName
FROM
MyTable
GROUP BY
Name
HAVING
Count(Name)>1
WITH OWNERACCESS OPTION;
3. Run the query. Duplicate key values are displayed in the first
column and the number of times the value appears is displayed in
the second column.
Multiple-Field Keys
-------------------
This example assumes you have a table called MyTable, with a double
primary key on the First Name and Last Name fields.
1. Open MyTable in Design view and index the First Name and Last Name
fields, as shown below:
Table: MyTable
-------------------------------
Field: First Name
Indexed: Yes (Duplicates OK)
Field: Last Name
Indexed: Yes (Duplicates OK)
2. Create the following query based on MyTable:
Query: Find Duplicates
----------------------
Field: Last Name
Total: Group By
Field: First Name
Total: Group By
Field: First Name
Total: Count
Criteria: >1
The equivalent SQL statement is as follows:
SELECT DISTINCTROW
[Last Name], [First Name],
Count([First Name]) As [CountOfFirst Name]
FROM
MyTable
GROUP BY
[Last Name], [First Name]
HAVING
Count([First Name])>1
WITH OWNERACCESS OPTION;
3. Run the query. Duplicate key values are displayed in the first two
columns and the number of times the value appears is displayed in
the third column.
Copyright Microsoft Corporation 1995.
_______________________________________________________________________________
What is Rushmore and how does it work?
"Here's Rushmore in a nutshell:
Rushmore query optimizer
Jet 2.0 includes support for the Rushmore query optimizer. In Jet 1.x, only
one index could be used to solve a query. Using Rushmore techniques borrowed
from Microsoft FoxPro, Jet 2.0 can now use more than one index to restrict
records. Rushmore-based query optimization is used on queries involving
restrictions on multiple indexed columns of the following types:
. Index Intersection--the two indexes are intersected.
Used on restrictions of the form
"" Where Company = 'Ford' and CarType = 'Sedan' ""
. Index Union--the two indexes are unioned.
Used on restrictions of the form
"" Where CarType = 'Wagon' or Year = '1994' ""
. Index Counts--queries that return record counts only.
Used for queries of the form
"" Select Count(*) from Autos Where Company = 'Dodge' and CarType='Truck'; ""
And it will work from VB3 with the mapping layer also. Also, worth
mentioning: there are other non-Rushmore parts of the query engine
that have also speeded up in 2.0. MS has made efforts, for example, in the
area of ODBC queries that will often make them much faster
by sending more of the query to the server."
_______________________________________________________________________________
What is the difference between Unique Values and Unique Records?
"Perhaps an example to play with will make this clearer to you. Construct
two tables:
Table1:
Field-> T1 T2 T3
1 2 3
1 2 4
1 2 4
1 2 5
Table2:
Field-> T1 T2
1 a
1 b
When the tables are linked by T1, and T1 selected from Table2, you get -
8 records if UniqueRecords=No and UniqueValues=No
2 records if UniqueRecords=Yes and UniqueValues=No
1 record if UniqueRecords=No and UniqueValues=Yes
UniqueRecords restricts based on the whole record in Table2, not just the
field(s) selected, UniqueValues restricts based on the final output."
_______________________________________________________________________________
Why do large queries cause my Novell Netware file server to crash?
"There is a bug in Netware 3.11 TTS (Transaction Tracking System) which
can result in a server abending when running certain Access queries.
Access locks records in 2K chunks called pages. Each such locked chunk
grabs one lock from the Netware TTS.
The defaults in Netware allow a single workstation to have 500 locks at
any given time. This results in a limit of 1M of data which Access can deal
with in a single transaction. Since Access tries to lock every record
involved in either an update or a delete query before actually carrying out
the update or delete, it is quite possible to to bump into this limit on a
moderately large database.
The problem is that Netware 3.11 reacts rather poorly to having its lock limit
exceeded. It appears to count a lock violation every time it looks at the
connection in question, which is still trying to lock more records. Eventually
(3-5 minutes) some internal table overflows, and the entire server goes down,
with a frightening message that instructs you to cycle the power. Doing this
during working hours on large networks tends to result in unhappy users. There
are two things you can do if this happens to you. The first is to increase the
number of locks available, and the second is to apply the Netware patch that
prevents the abend of the server (humungous queries can still fail, but at least
the server doesn't fail along with them). To increase the number of locks
available, enter the following commands at the file server console or in your
AUTOEXEC.NCF file:
set maximum record locks per connection=10000
set maximum record locks=200000
The first parameter is the most locks any single connection can have, and the
second is the most the entire server can keep track of. These values (10,000
and 200,000) are the maximums that Netware 3.11 can accomodate. By setting the
maximum record locks per connection to 10,000, Access can handle a transaction
up to 20 MB. To fix the server abend problem, you need to download the latest
Netware 3.11 patch file. It can be found on CompuServe in the NOVFILES section;
as of this writing, the current version is 311PTD.ZIP. You will need to load two
of the NLMs from this file, either directly from the server console or in your
AUTOEXEC.NCF file:
load patchman.nlm
load ttsfix.nlm
Also See MSKB Article number Q102522
_______________________________________________________________________________
When I attempt to generate a certain report, I get the message "Query Too
Complex" but when I run the query the report is based on, the query runs OK.
What does this mean?
Access has just exposed one of it's limitations, it's 16 bit and therefore
stores data into 64K segments, you've just filled up one of those segments.
Consider making your query a Make-table query or an append query to a temp
table then base the report on the table, this will split the processing up
a bit and as the query and report are no longer one operation, Access will
no longer try to store both into the same 64K segment.
[T Best]
_______________________________________________________________________________
How can I create an app where a user can use the results of a query in
a new or existing Word template simply by clicking a button in Access?"
Create a Word template using bookmarks where you want your data to go.
You could create a mail merge with a letter template that contains the
bookmarks "Name", "Address", and "CityStateZip" (all three together).
Let's say you want the merge tied to the "Employee" table in your Access
database. Save the template as "EmpLetr.Dot". From Access you can create
a form with a command button. In the "OnClick" event insert the
following code.
Function PrintMerge()
Dim db as database
Dim rs as recordset
Dim objWord as object
Dim sName as string
Dim sAddress as string
Dim sCityStateZip as string
set db = DBENGINE(0)(0)
set rs = db.OpenRecordset("Employees")
set objWord = CreateObject("Word.Basic")
rs.MoveFirst
Do While Not rs.EOF
sName = rs.EmployeeName
sAddress = rs.EmpAddress
sCityStateZip = rs.City & ", " & rs.State & " " & rs.Zip
objWord.filenew "c:\EmpLetr.dot" 'Full path of template
objWord.editgoto "Name"
objWord.insert sName
objWord.editgoto "Address"
objWord.insert sAddress
objWord.editgoto "CityStateZip"
objWord.insert sCityStateZip
objWord.FilePrint 'Print the document
objWord.Fileclose 2 'Close it, but don't prompt for saving
rs.Movenext
Loop
set objWord = nothing
rs.close
End Function
This ought to do it. Now, if you wanted to make it more generic, you
could pass the function the name of the table or query you want the
merge to be based on. I like this method much better than a hard coded
merge, because you can substitue a data source so easy.
_______________________________________________________________________________
Michael O Shea
___________________________________________________________________________
The MicroSoft Access Faq
___________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now, 05/8/96, this file
is just beginning, and my main aim is to get it up and running. To do this, I'm
going to start just by compiling together a lot of the information that is
already
out there. If i upset anyone by including something of their's and dont give
full
credit, please email me and i will rectify it as soon as possible
_____________________________________________________________________________
Part I
_____________________________________________________________________________
NOTE: The microsoft Knowledgebase article numbers, generally refer to the
downloadable version, but some articles are not included in that,(and are
marked so), and so those articles should be got at the online site at
microsoft
_____________________________________________________________________________
This Faq is Divided into the Following headings
1: Sources of Information On access
2: General Access Information
3: Frequently Asked Questions
1- Tables
2- Queries
3- Forms
4- Reports
5- Macros
6- Modules
7- Wizards, Tools, OLE & DDE
8- SQL & ODBC
9- Security
10- Databases
11- ADT
12- Performance
13- Access 95
4: Thanks to....
5: Disclaimer
_______________________________________________________________________________
1: Sources of Information On Access
_______________________________________________________________________________
Q: WHERE CAN I FIND INFORMATION ON MICROSOFT ACCESS?
*** Microsoft Access Books recommended from the list ***
1. Microsoft Access 2 Developer's Handbook
by Ken Getz, Paul Litwin, Greg Reddick
Sybex 1994
ISBN: 0-7821-1327-3
The book is intermediate to advanced. Take the word 'intermediate'
seriously in this case. They assume a working knowledge of all the
common Access stuff. The writers took on the attitude that they
would cover all the stuff that the other books didn't touch or
elaborate on, but are necessary for most (if not all) working
applications and development situations.
Topics also covered include Using DLLs, Win API, DDE, OLE2 Auto,
OLE custom ctrls, in-depth DAO, Access SQL vs ANSI SQL,
query optimization, creating wizards/add-ins/builders/help,
programming and manipulating security, creating right-mouse-
click menus, auto-sizing forms to displays... lots more.
2. The Revolutionary Guide to Access -
Professional Developer's Edition (with CD Rom)
by Steve Wynkoop
WROX Press Ltd
ISBN 1-874416-39-7
$44.95 US, $62.95 Canada, 41.99 in the UK
Todd Green
1-800-814-4527
NOTE: A summary of contents is available at:
HTTP://www.primenet.com/~swynk
3. "Special Edition Using Access 95" pub Que.ISBN:0-7897-0184-7
The book contains 1290 pages and touches on all aspects.
4. PC World
Access 2 Bible
by Cary Prague/ Mike Irwin
order (800) 277-3117
6. "Essential Access 95" by Allen Browne and Alison Balter.500pp
(37 chapters) covering the key issues of Access 95. Details and
sample at Allen Browne's Home Page http://odyssey.apana.org.au/~abrowne/
7. Access 2 Developer's Guide, 2nd Ed, by Jennings, Pub Sams,
ISBN 0-672-30453-8, US$45
8. Crash Course in Access Basic, by Wyatt, pub QUE, ISBN 1-56529-927-2,
US$20 Good for beginner/intermediate wanting to venture into
Access Basic and to discontinue using macros. Recommended
9. Access Programming for Dummies, Robert Krumm, pub IDG, ISBN 1-5688-091-8.
$20 "It presumes the reader has a knowledge of Tables, Queries,
Forms and Reports. The first 25% of the book is an overview of the
basics and this leads into Macros and Access Basic. This suits someone
who has done a fair bit in Access but has not ventured beyond macros
into programming.". Newsgroup recommended
10. Beginning Access 95 VBA Programming, pub Wrox Press, ISBN 1-874416-64-8.
"Most of the stuff will work in 2.0, but not everything.
***** Microsoft Access Information Sites *****
1. J. Steel's CIASL WWW Access Developer's Pages
THE site for friendly info and problem solving:
http://www.innovision1.com/msadp
NOTE: These pages were created for programmers (us) by
programmers (us). They allow the sharing of code
and ideas for others to learn and use... a true
"Developer's Page"
2. ftp.microsoft.com info on all MS products
3. gopher.microsoft.com info on all MS products
4. on CompuServe GO MSL misc. MS files
5. comp.databases.ms-access Access news group
6. on CompuServe MS ACCESS Forum notes on Access from MS
7. How to ftp a file from MS:
a. ftp ftp.microsoft.com
b. ftp> bin
c. ftp> get softlib/mslfiles/<filename.ext>
NOTE: Check out softlib/index.txt for the index listing of
softlib/mslfiles. Beware of doing an 'ls' in softlib/
mslfiles (there's over 1500 files in there).
Additional WEB Sites:
Steve Wynkoop's Access and Mail
http://www.primenet.com/~swynk
Microsoft's Access Knowledge Base
http://198.105.232.5:80/kb/indexes/access.htm
ftp://ftp.microsoft.com/softlib/mslfiles/acckb2.zip
Infosoft's Home Page
http://www.access-developer.com
Dan's Down Home Page - Kreative Kreations
http://www.infi.net/~dmarkham/access.html
Allen Browne's Page
http://odyssey.apana.org.au/~abrowne/
http://www.leeds.ac.uk/ucs/docs/beg24/beg24.html
There is an online introduction to access available here.
http://www.microsoft.com/Accessdev/accinfo/accinfo.htm
Microsofts own Access developers Forum
http://www.yes.com.my/personal/csphoon/access.htm
Some fine access links
http://multsoft.intercom.com/access/access_main.html
A new Access site for support and developers
http://coyote.csusm.edu/cwis/winworld/msaccess.html
A list of Shareware add-ons for Access
__________________________________________________________________________________
Q: WHATS THE STORY WITH MICROSOFT CERTIFICATION?
Subj: Access 2.0 Certification ...
Date: 94-12-12 16:30:25 EST
At 07:54 AM 12/12/94 -0500, JimBr...@aol.com wrote:
>How do you go about taking this test? How much is it?
>
Call Microsoft and Sign Up, They will have you call Drake Training to
schedule an exam time and to pay for the tests.
It depends on what you are taking as far as the cost. $100 / Test (US Dollars)
When they started this program, they *Required* Win 3.1 and DOS to qualify
<!-- There is no DOS at this point, only Windows architecture I + II -->
<!-- Certified Developer (my path) is 2 compulsary exams, Architecture I and
II, plus 2 electives chosen from MS Access, VB, SQL Server, Excel, MFC,
etc... -->
for any other exams, That's $200 up front before you even get a specific
Application Exam. If you just want to be "Access Certified" then you
probably only need Three tests and they often run specials on WinARc I & II
Both for $100. They also have a Certified Developer Program that will required
at least one more test, but they haven't determined that yet, Probably WIN
API or VB.
Steele
NOTE: Sample Certification exams can be donwloaded from
ftp://ftp.microsoft.com/services/msedcert/
in the files ASM1.ZIP,ASM2.ZIP,ASM3.ZIP
<!-- also refer them too http://www.microsoft.com/train_cert/ -->
_______________________________________________________________________________
Maintainers Note:
** Before going in search of any access related answers, download the
Access Knowledge base. Quite a few answers in this faq are just pointers
to articles in the KB ****
Subj: Access KB Articles
Date: 95-10-17 03:46:17 EDT
David C MacGregor writes
>What are these access kb articles and where
>can I find them?
Reply
The Access Knowledge base is a help file that contains
questions that are frequently asked of MS support, as
well as tips tricks and work arounds. It is available
at ftp.microsoft.com in /Softlib/MSLFiles the self
extracting archive is called ACCKB2.EXE It is a big
file.
It's also available on other on-line services.
[full Online Knowledge Base is available from
http://www.microsoft.com/isapi/support/kb.idc?Product=Access&database=KB_access]
Nick Dowling.
______________________________________________________________________________
Maintainers Note: if you are serious about learning Access, subscribe to this
list.
Another Great source of Information is the Access Mailing List
For information on subscribing to the list, send an email to
LIST...@PEACH.EASE.LSOFT.COM with the command GET ACCESS-L ADMIN.
_______________________________________________________________________________
2: General Access Questions
_______________________________________________________________________________
I just opened Access, and none of the toolbars appears, how can i get them
back?
You've probably just turned off the the built-in toolbars , accidently. To turn
them back on choose options from the view menu, set the Built-in toolbars
option to yes and click OK. If a toolbar doesnt re-appear, you've probably
hidden the one that should, - to display a toolbar, choose toolbars from the
view menu, higlight the toolbar you want ot see in the list, and select show.
_______________________________________________________________________________
As I develop form modules in Microsoft Access 95, over time, I
receive an "Invalid Page Fault in VBA232.dll" error message. Why's this?
This is the result of a faulty VBA232.dll which was shipped with Access95.
To get over the problem you need to download the updated DLL from
http://www.microsoft.com/KB/SoftLib/MSLFiles/Vba232a.exe and this
should solve the problem.
Note: that the downloaded patch could have a date, prior to the date on
the original vba232.dll.
Also see
http://www.microsoft.com/kb/deskapps/access/q147529.HTM
_______________________________________________________________________________
Q: Are there recognized naming conventions For Microsoft Access?
- Taken from Rcu...@princeton.edu's Access notes
Access Naming Conventions
Access supports long file names for internal database objects like tables,
queries, reports, etc. These are sorted alphabetically
by the database so the naming process can become extremely important.
Currently, Access does not update object names.
So for example, if you have a table called Departments and you develop queries
and reports based on that name, and then
change the table name to Department tbl, none of the queries or reports will
work until you go in and reestablish a link to the
correct name of the object. This can be devastating if you have created
complex queries because the old query fields must be
completely redone. So pick good, descriptive names the first time around
to avoid having to do major cleanup. Based on
articles about Access and my own experience, I suggest using a uniform
naming scheme for all Access objects. Here is the
naming scheme I use.
Tables: * tbl Example: Leaders tbl
Forms: * frm Example: Trips frm
Subforms: * sbfrm Example: Leaders on Trips sbfrm
Queries: * qry Example: Senior Leaders qry
Report: * rpt Example: Senior Leaders rpt
Subreport: sbrpt Example: Leaders on Trips sbrpt
The reason for providing the abbreviation at the end of the object name is
that it identifies the type of object. When you are
using the Properties Dialog box in Reports and Forms and you want to change
to underlying query or table that the Report of
Form is based on, the list will show both queries and tables and if the table
is called Leaders and the query is called Leaders
you can't tell them apart (in the dialog box). Using the abbreviation at the
end is a big help. Some people suggest using an
abbreviation at the beginning of the name, however, I find it harder to scan
a long list for similar things. FOr example I find:
Group Equipment rpt
Leaders on Trips rpt
Mailing list rpt
easier to scan than
rpt Group Equipment
rpt Leaders on Trips
rpt Mailing list
_______________________________________________________________________________
The Leszynski/Reddick Guidelines for Access (Quickly becoming THE standard)
These Guidelines as published in SmartAccess, suggest that all objects should
have a descriptive tag, placed at the start of the object name
The L/R Naming Standard For Access Verison 2.0
Tags for Database Container Objects
Form frm frmCustomer
Form (dialog) fdlg fdlgLogin
Form (menu) fmnu fmnuUtility
Form (message) fmsg fmsgWait
Form (subform) fsub fsubOrder
Macro mcr mcrUpdateInventory
Macro (menu) mmnu mmunEntryFormFile
Module bas basBilling
Query (append) qapp qappNewProduct
Query (crosstab) qxtb qxtbRegionSales
Query (DDL) qddl qddIInit
Query (delete) qdel qdelOld Account
Query (form filter) qflt qfltSalesToday
Query (make table) qmak qmakShipTo
Query (select) qry (or qsel) qryOverAchiever
Query (SQL pass-through) qspt qsptOrder
Query (totals) qtot qtotResult
Query (union) quni quniMerged
Query (update) qupd qupdDiscount
Query(lookup) qlkp qlkpStatus
Report rpt rptInsuranceValue
Report (subreport) rsub rsubOrder
Table tbl tblCustomer
Table (lookup) tlkp tlkpShipper
Database Container Object Prefixes
Archived objects zz zzfrmPhoneList
System Objects zs zstblObjects
Temporary objects zt ztqryTest
Under development _ (underscore) _mcrnewEmployee
Tags for Control Objects
Chart cht chtSales
Check box chk chkReadOnly
Combo box cbo cboIndustry
Command button cmd cmdCancel
Frame fra fraPhoto
Label lbl lblHelpMessage
Line lin linVertical
List box lst lstPolicyCode
Option button opt optFrench
Option group grp grpLanguage
Page break brk brkPage1
Rectangle (shape) shp shpNamePanel
Subform/report sub subContact
Text box txt txtLoginName
Toggle button tgl tglForm
Tags for Access Basic Variables
Container con Dim conTables as Container
Control ctl Dim ctlVapour As Control
Currency cur Dim curSalary As Currency
Database db Dim dbCurrent As Database
Document doc Dim docRelationships as Document
Double dbl Dim dblPi As Double
Dynaset dyn Dim dynTransact As Dynaset
Field fld Dim fldLastName as Field
Flag (Y/N,T/F) f Dim fAbort As Integer
Form frm Dim frmGetUser As Form
Group gru Dim gruManagers as Group
Index idx Dim idxOrderld as Index
Integer int Dim intRetValue As Integer
Long lng Dim lngParam As Long
Object obj Dim objGraph As Object
Parameter prm Dim prmBeginDate as Parameter
Property prp Dim prpUserDefined as Property
QueryDef qdf (alternate: qrd) Dim qdfPrice As QueryDef
Recordset rec (alternate: rst) Dim recPeople as Recordset
Relation rel Dim relOrderItems as Relation
Report rpt Dim rptYTDSales As Report
Single sng Dim sngLoadFactor As Single
Snapshot snp Dim snpParts As Snapshot
String str Dim strUserName As String
Table tbl Dim tblVendor As Table
TableDef tdf (alternate: tbd) Dim tdfBooking as TableDef
Type (user-defined) typ Dim typPartRecord As
mtPART_RECORD
User usr Dim usrJoe as User
Variant var Dim varInput As Variant
Workspace wrk (alternate: wsp) Dim wrkPimary as Workspace
Yes/No ysn Dim ysnPaid As Integer
Access Basic Variable Prefixes for Scope
Global g glngGrandTotal
Local (none) intCustomerld
Module m mcurRunningSum
Passed parameter p pstrLastName
Static s sintAccumulate
Maintainers note: I would suggest that the choice of naming convention, is not
as important as making sure that you do implement 'A' naming strategy. Which one
is purely a personal preference
_______________________________________________________________________________
Can I run an Access database from a CD-ROM or other read-only device?
Normally, the .LDB file must be in a
directory where the user has read-write privileges. However, it is
possible to open a database in a directory or device where a user has
read-only privileges. To do this, select both the Read Only and the
Exclusive check boxes in the Open Database dialog box. The Open
Database dialog box can be found by selecting Open Database from the
File menu. The combination of read-only and exclusive means that users
will not be updating any database information, so record-locking
isn't required. Since record-locking isn't required, MS Access does
not attempt to open or create an .LDB file.
___________________________________________________________________________
Do I need to have Share loaded in my Autoexec.bat file to use Access?
Access requires some form of share to be available.Several messages have
referred to VSHARE and its ability to eliminate SHARE.EXE in your autoexec.bat.
VSHARE is included with Windows for Workgroups and Word for Windows version 6.0.
Microsoft has also made VSHARE available on the MSL forum. (GO MSL) The name
of the file is WW1000.EXE. This version will work with Windows 3.1 as well
as Windows for Workgroups.
This is a great way to increase conventional memory and free yourself from
file locks. Additional information on VSHARE is included in the file.
How can I use Access 2.0 data files with my Visual Basic 3.0 programs? For
Visual
Basic to communicate with Microsoft Access version 2.0 created
databases, a group of files referred to as the "Visual Basic Compatability
Layer", (or VBCL,) is required. Some of these files are new, others replace
or augment existing files of the same names.
The VBCL is available through four sources:
- The Microsoft Access Developer's Toolkit (ADT), version 2.0
- The Microsoft Office Developer's Kit (ODK), version 1.0
- CompuServe MSBASIC Lib 1 (COMLYR.EXE, 700k)
- MSDL 206 936-6735 (COMLYR.EXE, 700k)
The Microsoft Access Developer's Toolkit (ADT) version 2.0 supplies the VBCL
*and* the Microsoft Jet database engine version 2.0, along with numerous other
database development tools.
The Microsoft Office Developer's Kit (ODK) version 1.0 contains both the VBCL
along with other tools to aid in the development of Microsoft Office
applications, but *does not* include version 2.0 of the Jet database engine.
However, version 1.1 of the Jet database engine is included with it.
The On-line versions contain just the VBCL and not the Jet 2.0 engine.
The Compatibility Layer gives Visual Basic developers complete access to Jet
2.0 and all its new features, but there are a few limitations. These result
primarily because the Visual Basic language has not changed and so the
Visual Basic developer does not gain programmatic access to some of the new
features. In many cases, Access 2.0 can be used to access features that are
not directly available through Visual Basic.
- Cascading updates and deletes are supported but must be specified using
Microsoft Access 2.0
- Table level validation is supported but settings must be specified using
Microsoft Access 2.0
- Jet 2.0 now supports zero-length strings so some updates may fail if
validation rules require zero-length strings, which Visual Basic can not
use.
- Jet 2.0 databases using any of the eight new language sort sequences will
be read-only in Visual Basic.
- Jet 1.0 format databases can not be created by Jet 2.0 files
_____________________________________________________________________________
How do I activate the "snap-to" feature of the 9.0 MS Mouse driver with
Access forms?
Access forms process their command buttons in a non-standard
way. so the snap-to feature (which is supposed to move the mouse cursor to the
default button of the window on screen) won't work. Sorry about that.
_____________________________________________________________________________
How do I change the text of the Access title bar?
See MSKB article number Q92684. (online only)
PSS ID Number: Q92684
---------------------------------------------------------------------
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, and 2.0
---------------------------------------------------------------------
SUMMARY
=======
This article describes how to use a function called from a macro to change
the Microsoft Access window default title bar. If you name the macro
Autoexec, the title bar will be changed automatically every time the
database containing the macro is opened.
MORE INFORMATION
================
The following steps demonstrate how to change the title bar caption:
1. Open a new database and name it TEST.MDB.
2. Open a new blank form. Place at least one control on the form.
Save the form as MainForm and then close it.
3. Create the following new macro, save it as Macro1, and then close it:
Action Argument
-----------------------------------------------------------
RunCode ChangeAccessCaption("<Your Custom Caption Here>")
4. Open a new module, and enter the following declarations:
Option Explicit
Declare Function GetParent% Lib "USER" (ByVal hwnd%)
Declare Sub SetWindowText Lib "USER" (ByVal h%, ByVal lpstr$)
5. Enter the following code for the ChangeAccessCaption() function in
the module:
Function ChangeAccessCaption (Caption$)
Dim X%
Dim hParent%
' Example of how to set the caption bar
DoCmd OpenForm "MainForm" 'Open the main startup form
X% = Forms!MainForm.hwnd 'Get its handle
hParent% = GetParent(GetParent(X%)) 'Get the form' parent
Call SetWindowText(hParent%, Caption$)
End Function
6. Save the module as Module1 and then close it.
7. Run Macro1. The title for the Microsoft Access window should change
to the text you specified as the function parameter in step 3.
Additional reference words: 1.00 1.10 2.00 header caption
KBCategory: kbprg
KBSubcategory: PgmApi
=============================================================================
Copyright Microsoft Corporation 1995.
_____________________________________________________________________________
How do I get a complete list of Access error numbers and their messages?
Function csvUserErrors () As Integer
' From the "Access Basic Cookbook"
'
Dim FileNumber As Integer, T As String
Dim X As Long
T = Chr$(9)
X = 0
FileNumber = FreeFile
On Error Resume Next
Open "ERRORS.TXT" For Output As FileNumber
If Err Then
csvUsedErrors = False
Else
Do While True
X = X + 1
If Left$(Error(X), 10) <> "User-defined error" Then
If Err Then
Exit Do
End If
Print #FileNumber, X; T; Error(X)
End If
Loop
Close FileNumber
csvUsedErrors = True
End If
On Error GoTo 0
End Function
_______________________________________________________________________
I get a General Protection Fault trying to run Access. What do I do?
This problem is often related to incomplete implementations of video drivers.
Try switching Windows to the standard VGA video driver. If the problem
goes away, contact your video board vendor for their latest drivers.
PCANYWHERE's remote-control drivers seem to be a particular problem,
as do Diamond video drivers.
For Diamond cards, you can get new drivers from the GRAPHB forum on
CompuServe. For the Stealth 64, driver version 1.06 or later is reported
to fix the problems.
This problem, as many people who have used access extensively will tell
you is also related to the position of the moon in relation to Venus,
the precise time of yesterdays High tide, and also the amount of time
you have to get the particular task completed.
________________________________________________________________________
I just installed Access 2.0 and the Readme file is garbage when I open it in
Notepad. What do I do about this?
The Access 2.0 readme file,
ACREADME.HLP, is a Windows Help file. If your system is set up according to
the Windows defaults, just double-clicking on this file in File Manager will
open it for viewing. If this doesn't work, try using the File|Run option of
Program Manager with the following command line:
WINHELP C:\ACCESS\ACREADME.HLP
________________________________________________________________________
I've read that Access corrupts data files when used on a network.
What's the story?
This was a piece of irresponsible reporting on the part of ComputerWorld.
The single person they quoted in their story was running into a known
incompatability between Windows for Workgroups and the ASPI SCSI interface.
In general, forcing 32-bit Access on if Windows does not set it on
automatically will lead to data loss in heavy usage situations.
________________________________________________________________________
In Access 1.x I could suppress the splash screen by including the
undocumented /s switch on the command line. How can I do this in Access 2.0?
There is no switch on the command line to do this. The good news is
that you can modified the MSACC20.INI or your custom INI file to
include the following section and statement as follow:
[Run-Time Options]
StartupScreen=C:\MySplash.bmp
This is documented in the ADT Advanced Topics manual.
Note also that the BMP can be as small as one pixel in each direction.
Is there any way to change the size of the font in the Zoom window
First make a COPY of your UTILITY.MDA file. For example, call it UTILITYX.MDA.
Then OPEN this .mda file just like you would any other Access .mdb file.
You will get several warnings about duplicate definitions, but just ignore
them. Then find the form called ZoomBox. You can change the font to anything
that looks good to you. The Lucida Sans Typewriter font from the MS TrueType
font pack works well. Save the UTILITYX.MDA file. Exit Access. Open up your
MSACCESS.INI file (found in the \windows directory) with a text editor such
as Notepad. Change the line that says
UtilityDB=utility.mda
to say
UtilityDB=utilityx.mda.
Access will now use your modified Zoom Box whenever you hit Shift-F2.
__________________________________________________________________________
My toolbars seem to be out of sync with operations on screen, and I don't
know what I did. How can I fix this?
The built-in toolbars have three settings: Open (Yes), Closed (No), and
Where Appropriate. This is obvious in the ShowToolBar macro action, but not
so obvious from the UI. If you're in the database window and you open, for
example, the Form Design toolbar, it will stay open no matter where you go
in Access (showtoolbar for Form Design gets set to YES). On the other hand,
if you open a toolbar from the correct environment (for example, the Form
Design toolbar while in form design view), it'll open "when appropriate". One
way to reset it quickly is to build a macro that does a ShowToolbar action
for each of the eleven built-in "environment" toolbars and set Show to Where
Appropriate. Run the macro and you should be all back together.
___________________________________________________________________________
What are .LDB files?
.LDB files are associated with the .MDB files with the same name.
They are used to control file locking when you are using Access in
non-exclusive mode with sharing enabled. You can safely delete these files
if the database is not currently open. If you copy the .MDB file you do not
need to copy the associated .LDB file because Access will automatically
rebuild the .LDB file as needed. Microsoft has not documented the format of
LDB files in multi-user situations and you should not attempt to modify their
internal structure.
Also, see MSKB Q109957 INF: Questions and Answers About .LDB Files.
_____________________________________________________________________________
What can I do to try and make Access run faster on my computer?
Although a faster CPU will contribute to enhanced Access performance,
the single best thing you can do is boost your machine to 8MB of memory.
Although Microsoft Access will run in 4MB, it will run significantly better
in 8MB. For multitasking with other major Windows applications, 16MB is even
better. A math coprocessor will have little or no effect.
SEE ALSO: Search your Access 2.0 help for "Performance" to see other
performance tips.
_______________________________________________________________________________
What is the current version of Access?
The current version of access is both versions 2.0 and 7.0 (95). Version 2 is
the
latest version which is available to run under window 3.*, while the newer
access 7,
was released around the time of Windows 95, and is designed to run under it.
_______________________________________________________________________________
When I hit shift-F2 to open the Zoom Box, nothing happens. Why?
or
When i choose one of my wizards, i get a compiling error, whats wrong with
my wizards?
The Zoom Box is displayed by a function in UTILITY.MDA, and as with other
functions, if there are syntax errors elsewhere in your Access Basic code
it won't run. Open up any module in your database, select Run|Compile All
from the menus, and you will most likely find an erroneous line highlighted.
If your code is error-free and you still can't use the Zoom Box, check to
make sure that your UTILITY.MDA file is present and properly referred to in
your MSACCESS.INI file. Some people have also reported trouble with the Zoom
Box when their Windows Free Resources have fallen very low.
________________________________________________________________________________
Why can't I launch Access 2.0 with the Microsoft Office Manager (MOM)?
Older versions of MOM do not work with Access 2.0. This is documented in
MSKB article #Q113931. You can get an updated version by calling the
Microsoft Office Product Support group at (206) 635-7056 with your original
MOM disk in hand (they need the serial number to upgrade you). The part
number for the replacement is 021-051-S32 (3.5" disks).
You can also download the 4.2c revision of MOM from any location that carries
the MSL files, including the MSL forum on CompuServe. It's included in the
file WC1038.EXE. This revision fixes the Access problem and several others.
________________________________________________________________________________
Why is there no serial number on my copy of Access when I select Help|About
from the menus?
This was a problem with the very first round of Access 2.0
installation disks, and has since been corrected. You can get more
information and a workaround from MSKB article #Q114319. (online only)
If you left your setup directory on your system when you installed Access
(maybe not on a laptop), the serial number is stored as an ASCII, clear text
string in setup.ini in the setup directory in your Access home directory.
________________________________________________________________________________
Will Access 2.0 work with Access 1.1 files?
Access 2.0 can generally
load and run Access 1.x database files. However, to make changes to any object
requires converting the database to 2.0 format, after which it cannot be
used in Access 1.x any longer. Access 1.x files can also be used as attached
files from within Access 2.0.
INF: Overview of Conversion from Version 1.1 to 2.0 Issues
Article ID: Q112120
________________________________________________________________________________
How do i Hide the Database Window at Startup?
See the MSKB article #Q88160
________________________________________________________________________________
Can I Determine if a Specific Windows Program Is Running?
See the MSKB Article ID: Q88167
________________________________________________________________________________
Can I make my Access Application be Modal within windows?
See the MSKB Article ID: Q88171
________________________________________________________________________________
Can I create a custom Startup "Splash" screen for my Access Application
See the MSKB Article ID: Q101374
________________________________________________________________________________
I'm having trouble making my VB program communicate with my Access 2 Database.
How Come?
See the MSKB Article ID: Q114834
________________________________________________________________________________
How come when i maximize a form within Access, i can see its Control Box,
and restore button, even though I've have their properties set to no? (and also
all other open forms, now become Maximised)
See the MSKB Article ID: Q128196
________________________________________________________________________________
Michael O Shea
_____________________________________________________________________________
The MicroSoft Access Faq
_____________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now,(See Part 1), this
file is just beginning, and my main aim is to get it up and running. To do
this, im going to start just by compiling together a lot of the information
that is alreadyout there. If i upset anyone by including something of their's
and dont give full credit, please email me and i will rectify it as soon as
possible
_______________________________________________________________________________
Part IV
_______________________________________________________________________________
3: Frequently Asked Questions
3-4: Reports
_______________________________________________________________________________
I want to show a total at the bottom of each page, but i keep seeing #error
instead, whats wrong?
Your problem is that access does not allow the Sum() function in the page
footer. So to get around the problem, create a control in another section
of your report that performs the calculation, and set its visible property
to false. Then create another unbound text box in the page footer. Enter the
name of the control containing the calculation as the text boxes controlSource
property setting.
_______________________________________________________________________________
Can i choose to not print a section of my report, depending on a specific
condition?
Yes, you can attach a macro to the onformat property of the section that
executes the cancelevent action when a condition is met, since the cancelevent
action prevens access from formatting the section, it doesnt print with the
rest of the report.
_______________________________________________________________________________
How do i make my report tell users that there are no records to print when
the reports query returns no records>
You need to create a macro that counts the number of records in the query;s
dynaset (using Dcount). The macro displays a message box when the query has
no records, or prints the reprot when the query has some records. You can then
attach this macro to the onOpen property of the report.
_______________________________________________________________________________
"How do I avoid ""Out of Memory"" errors on my report?"
"First, it helps to visualize a big picture of how reports work when addressing
this issue.
Reports create a query, combined with the underlying record source, for every
section of the report. IE: a query for the report header, page header, group
header, detail section, group footer, page footer, and report footer.
All of these queries are then combined into what is called a Segmented Virtual
Table or SVT. The final output or SQL string needs to be compiled in a 64k
limit. If this limit is hit an ""out of memory"" error can occur.
Things to look at when trying to help the customer avoid this error are:
1) Long column names, table name, control names. Reducing a 30 character name
down to a minimum will help
2) Less expressions in the underlying queries. Reducing space used for
expressions in the Select list helps avoid the error. If at all possible,
place the expressions directly in the report instead.
3) Fewer stacked query objects. Avoid situations like query 1 used to pull
the data from 2 table, then query 2 just filters the data. The more information
that can be pulled together into one or fewer queries is far better than
multiple
queries each doing a portion of the tasks.
4) Avoid including fields in the query that are not used in the final output of
the report.
5) Look at the subreports and the queries they are based on. Generally,
subreports
would not be based on the same queries as the main report, usually a smaller
set of data. Again, check for fields that are not used in the final output.
Or possibly tables that do not need to be a part of this subset of data.
The solution for getting around this error varies for everyone, the more you
can describe the report and the underlying queries the easier it is to find a
solution.
* * * * *
In addition to what Kim has to say on the subject, if a report based on a select
query produces an ""Out of Memory"" error, try turning your select query into
a make-table query and basing the report on the resulting table instead."
_______________________________________________________________________________
"How do I print a certain number of labels based on a ""count"" field in a
record"
"First create a table called LabelDriver with only ONE field. Make it a counter
field and make it the primary key. Add as many records to this table as the
MAXIMUM number of labels you will want for any given individual. There should
be no gaps in the sequence numbers of the counter field. Now create a new query.
Add your NameAndAddress table to the query. The table should have a field which
contains the number of labels you want to print for any given record. Assume
this is a field called ""LabelCount"".
Now add the LabelDriver table to the query. Do NOT make any link between the
Name table and the LabelDriver. The reason for this is that you want to have
EACH of your name records link out to ALL of the records in the LabelDriver
table. This is called a ""cross-product"" or ""Cartesian"" join, and is not
something you would ordinarily want to do, but for this task it works fine.
Drag the appropriate fields as needed from the NameAndAddress table down to
the query grid. Now, in order to limit the number of labels to the LabelCount
field, drag the 'id' or counter field from the LabelDriver table down to the
query grid as well. (Uncheck the "show” checkbox because you don't need to see
this field in the query output.) In the selection criteria cell for the
LabelDriver.ID field put:
<=[LabelCount]"
_______________________________________________________________________________
How do I print reports to a file?
"You can use the built-in Output As menu item or the equivalent macro action to
send your reports to text, RTF, or XLS files. However, you should be aware of
several significant limitations. First, graphic items will not be output.
Second, subreports will not be output.
If you need more flexibility in reporting, your best bet may be to use OLE
Automation to drive Word or Excel directly from Access."
_______________________________________________________________________________
I have overlapping rectangles on my reports, but all of both rectanges print on
the page even though the back one is properly hidden on the screen. How do I
fix this?
"You can change this behavior by setting ""Fast Laserprinting"" for your form
to ""No"". Otherwise the rectangles are treated as rules, and overlapped rules
are never hidden."
_______________________________________________________________________________
Where do I find more information on report techniques
"The most frequently asked questions for reports can be answered by referring
to the documentation, searching Help, or reviewing the step by step examples
in the sample database Solutions.
(The references are for version 2.0 of Microsoft Access)
The User's Guide includes many common tips, such as:
""Combining Two or More Reports"" - chapter 22, for details on combining
subreports.
""Adding Page Numbers"" - chapter 23, for ""Page X of Y"" or total pages
example.
""Calculating Totals on Several Group Levels"" - chapter 23.
""Calculating Percentages"" - chapter 23.
""Using a Command Button to Print a Report"" - chapter 26.
""Asking for Criteria Before Printing a Report"" - chapter 26.
""Highlighting Data and Sections Dynamically"" - chapter 26.
""Providing Custom Page Numbers"" - chapter 26.
""Controlling the Number of Detail Line Printed on a Page"" - chapter 26.
""Placing Totals in Page Headers and Footers"" - chapter 26.
""Sending a Report to a Word for Windows Document"" - chapter 26.
Some common questions answered in Help are:
Referencing subreport controls - ""Referring to Controls on a Subform or
Subreport (Common Question)"".
Creating a dynamic sort for reports - ""Sorting a Report (Common Question)"".
Creating labels continuous mailing labels = ""Creating Mailing Labels
for a Dot Matrix Printer"".
Shrinking or not printing controls when there is no data - ""Reducing
White Space in Reports"".
Most common questions directly relate to controlling what you print on
reports. The Solutions sample database contains the following examples
plus ""show me"" Help topics for step by step instructions:
""Draw a circle around data to accentuate it"".
""Hide a section when it falls at the top of a page"".
""Hide and show controls based on the value in another control"".
""Hide and show sections that print on a preprinted form"".
""Print ""Continued"" at the bottom of a preprinted form"".
""Print report criteria that was entered in a dialog box"".
""Print the first and last entries on a page in a page header"".
""Repeat a group name at the top of a column or page""."
_______________________________________________________________________________
Why do I get extra blank pages between each page?
You probably have extended the design surface so that it extended beyond the
size of your printed page. To fix this situation, open your report in design
mode and pull in the right and bottom margins as far as possible to the top or
left. If the design surface is as small as possible and it still fails, check
the File-Print Setup menu option and make sure that the margins are correct for
your paper size.
_______________________________________________________________________________
Why do I only get a single mailing label on each page of a Wizard-created
mailing
label report?
"We indeed have found a problem when using the Mailing Label Report Wizard and
choosing one of the Avery mailing labels numbers: 4145, 4162, 4163, 4249,4250,
4251, 4253, 4254, which create a 15/16 x 3 1/2 one up continuous label,the
report created will produce a one page output. The additional data forthe
report will not be displayed.
The problem is related to the RowSpacing property available under Print
Setup. If the RowSpacing section lands exactly at the end of the page the
report will stop at page one.
Changing the RowSpacing property to 0, and increasing the Height property for
the Detail section to 1 inch will correct the problem for the Avery Mailing
Labels 4145, 4162, 4163, 4249, 4250, 4251, 4253, 4254."
_______________________________________________________________________________
Michael O Shea
_____________________________________________________________________________
The MicroSoft Access Faq
_____________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now,(See Part 1), this file
is just beginning, and my main aim is to get it up and running. To do this, im
going to start just by compiling together a lot of the information that is
already
out there. If i upset anyone by including something of their's and dont give
full
credit, please email me and i will rectify it as soon as possible
_______________________________________________________________________________
Part III
_______________________________________________________________________________
3: Frequently Asked Questions
3-3: Forms
_______________________________________________________________________________
" Is there any way of detecting whether a form is open or not such that it can
be used in a conditional macro?"
"Just to be different, here's the function I've always used. It returns True or
False, so you can use it in the condition column of a macro:
Function IsFormLoaded(strName as String)
Dim varName as Variant
On Error Resume Next
varName = Forms(strName).FormName
IsFormLoaded = (Err = 0)
On Error Goto 0
End Function"
_______________________________________________________________________________
Can i create something on myform like the tooltips labels that appear when i
point the mouse at a toolbar button?
Yes! you can hide the labels for your control and then set them to appear when
the user moves to that contro;, by putting something like
forms!main![labels control name].visible = -1
in the object ongotfocus property.. and vice versa for onLostFocus
_______________________________________________________________________________
How can I change the cursor on my forms to something bolder?
Access uses the default Windows cursor, and this decision is not exposed to your
control. If you have a recent version of the Microsoft Mouse utilities, you can
pick a larger cursor to use in all of your Windows applications.
_______________________________________________________________________________
How can I determine the type of a control?
" Function ControlType (ctl As Control)
' Return the control type of a given control, returning numbers that match
' those used by CreateControl.
If TypeOf ctl Is BoundObjectFrame Then
ControlType = 108
ElseIf TypeOf ctl Is CheckBox Then
ControlType = 106
ElseIf TypeOf ctl Is ComboBox Then
ControlType = 111
ElseIf TypeOf ctl Is CommandButton Then
ControlType = 104
ElseIf TypeOf ctl Is Label Then
ControlType = 100
ElseIf TypeOf ctl Is Line Then
ControlType = 102
ElseIf TypeOf ctl Is ListBox Then
ControlType = 110
ElseIf TypeOf ctl Is ObjectFrame Then
ControlType = 114
ElseIf TypeOf ctl Is OptionButton Then
ControlType = 105
ElseIf TypeOf ctl Is OptionGroup Then
ControlType = 107
ElseIf TypeOf ctl Is PageBreak Then
ControlType = 118
ElseIf TypeOf ctl Is Rectangle Then
ControlType = 101
ElseIf TypeOf ctl Is Subform Then
ControlType = 112
ElseIf TypeOf ctl Is Subreport Then
ControlType = 112
ElseIf TypeOf ctl Is TextBox Then
ControlType = 109
ElseIf TypeOf ctl Is ToggleButton Then
ControlType = 122
Else
ControlType = 0
End If
End Function"
_______________________________________________________________________________
How can I display a listbox showing the last records rather than the first
records? How can I select the first item in a listbox as the default?
"You can select the last record in the list easily in Access 2.0 with the new
ItemData and ListCount properties. Following the Requery, use a SetValue action
as follows:
SetValue
Item: [YourCombo]
Expression: [YourCombo].ItemData([YourCombo].ListCount - 1))
In contrast, you can select the first row with:
SetValue
Item: [YourCombo]
Expression: [YourCombo].ItemData(0)"
_______________________________________________________________________________
How can I set my forms to use the user's system colors as defined through
Control Panel?
"This could not be done in Access 1.x, but Access 2.0 defines a set of constants
for the system colors. Search the Online Help for ""BackColor"" and follow the
reference to the Windows System Colors to see these constants."
_______________________________________________________________________________
How can I tell whether my user has applied a filter to one of my forms, and
retrieve the SQL statement for the filter?
Unfortunately, you can't, at least not in current versions of Access. The very
knowledge that a form has a filter applied, and the SQL of the filter (if any)
are not exposed to the programmer in Access 2.0.
_______________________________________________________________________________
How do I copy all the information from the previous record on a form to a new
record?
Users can copy the contents of any single field by using the Ctrl+' combination.
If you want to duplicate an entire record on to a new record, see MSKB article
number Q88670.
_______________________________________________________________________________
How do I create custom navigation buttons on my forms?
To use this code, create five command buttons:
cmdFirst
cmdPrev
cmdNew
cmdNext
cmdLast
To each, assign a function call to their OnPush event:
=glrNavFirst(Form)
=glrNavPrev(Form)
=glrNavNew(Form)
=glrNavNext(Form)
=glrNavLast(Form)
In the Form's OnCurrent event, put a call to the glrEnableButtons() function:
=glrEnableButtons(Form)
Finally, put this code into a module:
Option Compare Database
Option Explicit
Private Function AtNewRecord (frm As Form)
'
' Check to see if the current record in the form
' is the ""new"" record. If so, return TRUE, otherwise,
' return FALSE. Note that this function works because
' attempting to retrieve the form's bookmark when at
' the ""new"" record triggers an error.
'
Dim varTemp As Variant
On Error Resume Next
varTemp = frm.BookMark
AtNewRecord = (Err <> 0)
End Function
Function glrEnableButtons (frm As Form)
'
' Attached to the specified form's OnCurrent event.
'
' This function enables and disables buttons as
' necessary, depending on which is the current
' record on the form.
'
' This function counts on buttons named cmdFirst,
' cmdPrev, cmdNext, cmdLast and cmdNew. One
' could code around this, but it seemed like
' overkill for this example.
'
Dim DS As Dynaset
Dim fAtNew As Integer
Dim fUpdateable As Integer
Set DS = frm.Dynaset
' Check to see if you're on the new record or not.
fAtNew = AtNewRecord(frm)
' If the form isn't updatable, then you sure
' can't go to the new record! If it is, then
' the button should be enabled unless you're already
' on the new record.
fUpdateable = DS.Updatable And (frm.DefaultEditing <= 2)
frm!cmdNew.Enabled = IIf(fUpdateable, Not fAtNew, False)
If fAtNew Then
frm!cmdNext.Enabled = False
frm!cmdLast.Enabled = False
frm!cmdFirst.Enabled = True And (DS.RecordCount > 0)
frm!cmdPrev.Enabled = True And (DS.RecordCount > 0)
Else
' Synch the record set's bookmark with
' the form's bookmark.
DS.BookMark = frm.BookMark
' Move backwards to check for BOF.
DS.MovePrevious
frm!cmdFirst.Enabled = Not DS.BOF
frm!cmdPrev.Enabled = Not DS.BOF
' Get back to where you started.
DS.BookMark = frm.BookMark
' Move forwards to check for EOF.
DS.MoveNext
frm!cmdNext.Enabled = Not (DS.EOF Or fAtNew)
frm!cmdLast.Enabled = Not (DS.EOF Or fAtNew)
End If
End Function
Function glrNavFirst (frm As Form)
NavMove frm, A_FIRST
End Function
Function glrNavLast (frm As Form)
NavMove frm, A_LAST
End Function
Function glrNavNew (frm As Form)
NavMove frm, A_NEWREC
End Function
Function glrNavNext (frm As Form)
NavMove frm, A_NEXT
End Function
Function glrNavPrev (frm As Form)
NavMove frm, A_PREVIOUS
End Function
Private Sub NavMove (frm As Form, intWhere As Integer)
'
' Move to the correct row in the form's recordset,
' depending on which button was pushed. This code doesn't
' really need to check for errors, since the buttons
' that would cause errors have been disabled already.
'
Dim DS As Dynaset
Dim fAtNew As Integer
Const ERR_NO_CURRENT_RECORD = 3021
On Error GoTo NavMoveError
If intWhere = A_NEWREC Then
DoCmd GoToRecord , , A_NEWREC
Else
fAtNew = AtNewRecord(frm)
Set DS = frm.Dynaset
DS.BookMark = frm.BookMark
Select Case intWhere
Case A_FIRST:
DS.MoveFirst
Case A_PREVIOUS:
If fAtNew Then
DS.MoveLast
Else
DS.MovePrevious
End If
Case A_NEXT:
DS.MoveNext
Case A_LAST:
DS.MoveLast
End Select
frm.BookMark = DS.BookMark
End If
NavMoveExit:
Exit Sub
NavMoveError:
If Err = ERR_NO_CURRENT_RECORD And AtNewRecord(frm) Then
Resume Next
Else
MsgBox Error$ & "" ("" & Err & "")""
Resume NavMoveExit
End If
Resume NavMoveExit
End Sub"
_______________________________________________________________________________
How do I display a form without the caption bar at the top?
See MSKB Article number Q103261.(online only)
_______________________________________________________________________________
How do I incorporate photos in my forms, as Microsoft did in the Employees for
the Northwinds database
MSKB article Q100169 describes how Microsoft did it.
_______________________________________________________________________________
How do I select a control on a form without accidentally moving it?
1.) Change the grid size on the form so it's got a little larger granularity
(select the form, and look for the gridX and gridY properties). Also, make sure
that Snap To Grid is On in the Layout menu.
2.) Rather than clicking ON the control, click outside the control and drag
over
the control. Then let go of the mouse. That'll select the control without any
chance of moving it.
3.) If you DO move the control, just press alt-Backspace (or use any other
method you like to undo).
_______________________________________________________________________________
How do I tell when the user has moved to the blank record at the end of a table
by pressing the Next button on my form?
"Here's a function that you can call to tell you if you're on the new record or
not:
Function AtNewRecord(frm as Form)
Dim varTemp as Variant
On Error Resume Next
varTemp = frm.Bookmark
AtNewRecord = (Err <> 0)
On Error Goto 0
End Function
This works because attempting to get the bookmark for the new record triggers
an error."
_______________________________________________________________________________
How do I use the ! symbol in my input masks to allow right-to-left data entry?
You don't. It's broken.
_______________________________________________________________________________
How do I use the .PictureData property of a command button?
"The PictureData property of buttons is used by wizards to copy the button
picture from one button control to another. For example, suppose you have 2
command buttons on your form called ButtonA and ButtonB. To set ButtonB's
picture to the picture used in ButtonA you could type the following in the
Immediate Window:
Forms![Form1]![ButtonB].PictureData=Forms![Form1]![ButtonA].PictureData
This property is not documented in help."
_______________________________________________________________________________
How do I write information to the status bar and gas-gauge indicator at the
bottom of the screen?
Search your on-line help for information on the SysCmd() command.
Code for Displaying Messages on the Status Bar
The following three sub-routines will display any message of your choice on
the status bar, at the bottom of the screen, when the mouse is moved 'over'
a control - button, textbox, whatever - allowing for about 80 characters
(depending on screen resolution) of description of the purpose / use of a
control. The following two sub-routine need to be in a module, so that they
can be generally available.
Global StatusCalled
Global CurrentStatusMsg
Sub StatusBarMsg (StatusMsg)
If StatusMsg <> CurrentStatusMsg Then 'test if message already displayed
Dim ss As Variant
ss = SysCmd(SYSCMD_SETSTATUS, StatusMsg)
StatusCalled = True
CurrentStatusMsg = StatusMsg
End If
End Sub
Sub ClearStatusBarMsg ()
If StatusCalled Then
Dim ss As Variant
ss = SysCmd(SYSCMD_CLEARSTATUS)
StatusCalled = False
CurrentStatusMsg = " "
End If
End Sub
The following code (with different messages, of course!) is attached to the
'mouse move' event of each control. When the mouse moves across a control
the appropriate message will be displayed in the status bar.
Sub CmdButton_MouseMove (Button As Integer, Shift As Integer, _
x As Single, Y As Single)
StatusBarMsg "Display this message on the status bar."
End Sub
This code is attached to the "background" of each part of a form, eg
detail, header, footer etc. In this way the message is removed from the
status bar when the mouse is moved 'off' a control.
Sub Detail0_MouseMove (Button As Integer, Shift As Integer, X As _
Single, Y As Single)
ClearStatusBarMsg
End Sub
This code is attached to the 'on timer' event of the main form of an
application. This bit of code is not strictly necessary but I have found
that a timer interval of 6500 leaves a message on the status bar long
enough to be read, yet gets rid of it quickly enough to avoid confusion
with any status bar text associated with the control which has focus.
Sub Form_Timer ()
If StatusCalled Then
ClearStatusBarMsg
End If
End Sub
This tip was supplied by jo...@ozemail.com.au (John Metcalfe)
_______________________________________________________________________________
I change the forecolor of a control using the color builder and I do not get the
text color I selected. Why not?
No matter what color you select, the forecolor will always default to one of the
original 16 in the palette. This is a know limitation of Windows and is not
caused by Access.
_______________________________________________________________________________
Is there a tool or method to convert Visual Basic forms to Access forms or vice
versa?
See ftp://ftp.csusm.edu/pub/winworld/msaccess/aphac2vb.zip
There is another one for Access to Visual basic named Visual Bridge but
it is not very good. (Chui Shuin, Phoon (Trisha))
_______________________________________________________________________________
When I change a color on a form, why does the correct color only show in form
view, and not in design view?
Any color not selected from the Color Palette will show this result when the
Grid is turned on. Under View menu un-check Grid and the new color will show
in design view also.
_______________________________________________________________________________
When I copy a command button from one form to another, any code attached to the
button gets lost. How can I fix this?
Unfortunately, this is the default behavior of Access 2.0 and can't be changed.
All you can do is open the modules for both forms and cut and paste code from
one to another. We all hope this will be fixed in the next version of Access!
_______________________________________________________________________________
When I Tab past the last control on a form, how can I keep Access from going to
the next record (or backwards when I Shift+Tab back from the first control)?
"A) Create two small, transparent buttons (set their Transparent property to
Yes).
Be careful that you don't make them invisible by setting their Visible
properties
to No.
B) place one of the buttons just to the left of the first user-editable control
on the form, and place the other just to the right of the last user-editable
control.
C) Create two macros that you will assign to the buttons' OnEnter events:
i) For the first button, use GotoControl to go to the FIRST control on your
form.
ii) For the other button, use GotoControl to go to the LAST control on your
form.
D) Choose the Tab Order command from the Edit menu and place the the button at
the
bottom of the tab order; place the last button at the top of the tab order.
HOW IT WORKS: As you tab through the controls and reach the last editable
control,
pressing Tab again enters the new, transparent button. Since its OnEnter event
triggers a macro that sends you back to the first editable control, it has the
effect of keeping you on the same record. Pressing Shift+Tab from the first
control
has the opposite effect.
See Also MSKB #Q112064"
_______________________________________________________________________________
Why do I get a #NAME error for a calculated control on a form or a report?
"You are probably using the same name for a calculated text box as one of the
terms in the expression. In the following example, Access produces a #NAME error
if you enter the expression in a control named City, State, or ZIP:
=[City] & "", "" & [State] & "" "" & [ZIP]
This creates a circular reference that may be resolved by changing the name of
the control that contains this expression to something (anything) other than
City,
State, or ZIP."
_______________________________________________________________________________
Subj: Attach files Thanks
Date: 95-03-23 15:13:54 EST
Thanks to ken pinchard!, his solution worked great!
------------------------------------------------
Function AttachDataTables ()
On Error GoTo AttachDataTables_Err
Dim cdb As Database, tbl As TableDef, i As Integer
Dim database_path As String
Dim Database_Name As String
Dim datapath As String
DoCmd Hourglass True
database_path = GetDataPath()' or hard code
Set cdb = dbengine(0)(0)
' For each table in set the connect property
For i = 0 To cdb.tabledefs.count - 1
Set tbl = cdb.tabledefs(i)
If tbl.connect <> "" Then ' Skip base tables.
Database_Name = GetDataBaseName' make function or hardcode
tbl.connect = ";Database=" & database_path & Database_Name
tbl.RefreshLink
End If
Next i
AttachDataTables_Exit:
DoCmd Hourglass False
Exit function
AttachDataTables_Err:
MsgBox "AttachDataTables: " & Error$
Resume Next
End function
----------------------------
john...@aol.com
_______________________________________________________________________________
Subj: CIAS:Attach all tables fun
Date: 95-05-20 08:18:51 EDT
To CIAS Library,
This is a life saver for me, I hope it's useful,
It's only about 1.7 seconds (40 attached) overhead if already attached.
john
Function Attach_tbl_qk_jjs ()
'from john...@aol.com jjs 5/1995 ver 3.0
're-connects Any database, Any name
'if in the same directory as the current Database.
'get for supporting many clients using different drives
'put in autoexec macro as a runcode.
Dim msg As String, DBType As String
Dim mydb As Database, tbl As TableDef
Dim MyNew_connect As String, my_dir As String
Dim ss As Integer, ii As Integer, LL As Integer, xx as integer
DoCmd Hourglass True
On Error GoTo Attach_tbl_qk_jjs_Err
Set mydb = DBEngine(0)(0)
ss = 1
Do Until ss = 0
LL = ss + 1
ss = InStr(LL, mydb.name, ")
Loop
my_dir = Mid$(mydb.name, 1, LL - 1)
For ii = 0 To mydb.tabledefs.count - 1
Set tbl = mydb.tabledefs(ii)
If tbl.connect <> "" Then ' Skip My DB tables.
ss = InStr(tbl.connect, "=")'find end of db type
DBType = Left$(tbl.connect, ss)
Do Until ss = 0
LL = ss + 1
ss = InStr(LL, tbl.connect, ")
Loop
MyNew_connect = DBType & my_dir & Mid$(tbl.connect, LL)
If (MyNew_connect) = tbl.connect Then GoTo sKip_refreshlink_jjs:
tbl.connect = MyNew_connect
tbl.RefreshLink
End If
sKip_refreshlink_jjs:
Next ii
Attach_tbl_qk_jjs_Exit:
DoCmd Hourglass False
Exit Function
Attach_tbl_qk_jjs_Err:
DoCmd Hourglass False
msg = "Jot down this error and continue."
msg = msg & Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10) & "Thank You!"
xx = MsgBox(msg, 52, ("Attach_tbl_qk_jjs: " & Error$ & " > " & tbl.name))
If xx = 7 Then DoCmd Close ' 7=no 6=yes
Resume Next
End Function
_______________________________________________________________________________
How do I search for records in an Access 2.0 form?
Answer:
2 solutions.
1) Get Access 7. Does it automatically. But you'll have to
suffer with the program.
2)The solution in 2.0
A)Put an unbound combo box on your form. Set the row source
property to the table or query with the records you want to search
through. Should be a relatated lookup table, usually.
B)Make a macro (or a little code module) that you'll run on the
After Update property of the combo box.
C)Tell the macro or code to "GoToControl" (the control you want
to search through on the form which is related to the lookup table
that's the source for the search box). Then tell it to "FindRecord."
Done. You'll click on the record you want, and boom, your form
will jump to it.
_______________________________________________________________________________
I have a control on a form which i use to accept data, this
control is a "memo" data type field. My problem is that when the data
in this memo field goes beyond the size of the control on the form, I
haven't found a way to print all of the data. This doesn't happen
often, however when it does, I would like an easy way to print all of
the information.
Anyone have suggestions?
On the form, set the Scroll bar property of the text box for the memo
field to Vertical. In a report, set the Can Grow property to Yes.
_______________________________________________________________________________
Is there some way to get Access to automatically adjust the
forms sizes based on what my screen resolution is ?
The easy answer is that all work must be done at the same size
to ensure the forms fit. The more difficult answer is that you can
determine the computers resolution and based on that value, scale every
form, subform, and control in your application each time it is run. The
book Microsoft Access Developers Handbook, by Ken Getz, Paul Litwin, and
Greg Reddick (Published by Sybex) goes through how to do this.
I tried doing this once, but I wasn't happy with the results. Some of
the text was hard to read after being resized and the applications
performance suffered a little.
Another, although not pretty, solution is to develope multiple sets of
forms based on each standard resolution. Then determine the computers
resolution and open the appropriate set of forms.
_______________________________________________________________________________
Is there a way to nest combo boxes?
This would be desireable if the list of possibilities is very long.
For example, if I have a list of 100 companies in five categories, it
would be a lot easier for a user to firstchoose one of the five, then
only have to scroll through twenty, insteadof scrolling through the
entire 100 names.
Answer: No, but, you can achieve the same effect using
INF: How to Synchronize Two Combo Boxes on a Form
Article ID: Q97624
from the Microsoft Knowledge Base
_______________________________________________________________________________
What is a form's TAG property for?
Nothing. Which makes it extremely useful. You can use it to hold
any number of things. One I frequently use it for is to hold the
name of a form's calling form. This assists me in form navigation.
Here's an example: Because opening an Access form can be time consuming,
I frequently open more than one form at a time, but make all but one
form invisible. When I want to go to the next form, instead of
closing one form and opening another, I use the following code as
an event procedure of the "On Click" event of a command button:
Sub cmdNext_Click ()
Me.Visible = False
Forms!NextForm.Visible = True
Forms!NextForm.Tag = Me.name
End Sub
Then, to go back to the calling form, I use the following code
in the "On Click" event of a command button:
Sub cmdPrevious_Click()
Dim CallingForm As String
CallingForm = Me.Tag
Me.visible = False
Forms(CallingForm).visible = True
End Sub
This is only one example of what you can do with the tag property,
but hopefully you can find even more uses for yourself.
_______________________________________________________________________________
How do I fill a listbox with a list of all the reports in my database?
You can use DAO methods to retieve a list of the objects in a database,
but an easier way is to use the system table MSysObjects which contains
a list of names and types of all objects in a database. The types are:
Tables = 1
Attached Tables = 6
Forms = -32761
Reports = -32764
Queries = 5
Macros = -32766
Modules = -32761
Using the [Type] field and [Name] field from MSysObjects, you can create a
query that can be used to fill a listbox:
SELECT DISTINCTROW [Name] from MSysObjects WHERE [Type] = -32764;
This tip was submitted by E. Maroto on the DBADVISOR forum on Compuserve.
_______________________________________________________________________________
How do I refer to a control on a subform?
Normally when you want to refer to a control on a form you just follow
the heirarchy from form to conrol like this:
Forms!frmMyForm.mycontrol
A subform serves two purposes, however. It's a control on the main form,
but a parent form to its own controls. In order to refer to controls on
a subform, you have to add an extra piece of code in your reference.
To refer to the control ctlEmplID on subform control subEmployees,
on main form frmSalesData, use the following code:
Forms!frmSalesData.subEmployees.Form.ctlEmplID
_______________________________________________________________________________
How can I Hide a Control When It Loses the Focus?
by Nicholas Couch
Have you ever wanted to hide a control as soon as it loses the focus?
If so, you've probably tried putting the following in the control's
LostFocus event procedure:
[txtMyTextbox].Visible = False
You'll find that this produces the runtime error "Can't hide the control
that has the focus." As far as Access is concerned, txtMyTextbox has not
really lost the focus yet when the LostFocus event fires.
To get around this problem, you could program the GotFocus event of all the
other controls on your form to hide txtMyTextbox. That could be a real
headache if your form has a lot of controls. A better workaround is to
use the form's Timer event instead. Use the following line in the control's
LostFocus event procedure:
Me.TimerInterval = 10
This will cause the form's Timer event to fire every 10 milliseconds. Then
put the following into the event procedure for the Form's Timer:
[txtMyTextbox].Visible = False
Me.TimerInterval = 0
This will hide your control and reset the form's TimerInterval so that the
Timer event doesn't continue to fire.
If your form has more than one control for which you need this functionality,
set the TimerInterval to a different value for each control and use a Select
Case block in Form_Timer to hide the appropriate control, depending on the
value of TimerInterval.
_______________________________________________________________________________
Moving controls around forms can be tiresome, trying to align them
just right, is there anything i can do?
Assigning Auto Keys for Aligning Controls
If you have used the Wizards to create forms and reports, you will have
also tried dragging controls to a new location in Design View. Moving
PostalCode beside Town makes sense, especially if all controls don't fit on
screen. You've probably figured out how to move a control separately from
its label (larger black square at top left when the control is selected),
how to select multiple controls (hold down Shift), and how to make a
control just the right size ("Fit to Size" from the Format menu).
Getting controls to align correctly after dragging them around can be a
chore! "Snap to grid" is useful, but often doesn't give the precise
placement you want.. Too often you find yourself roughly aligning the
controls and then going to the Format menu (Layout in Access 1) to get
Align, Left for text boxes and Align, Right for labels. Then you select the
ones on the same row and go back to the menu for Align, Top or Align,
Bottom.
A keystroke such as Ctrl+L for align left or Ctrl+R for align right could
speed things up. Here are the steps to get that result.
1. Create a new macro and save it with the name AutoKeys. (The name is
important: it must match the entry under View, Options, Keyboard, Key
Assignment Macro.)
2. If you cannot see a column called "Macro Name", check Macro Names on
the View menu.
3. In the first row of the Macro Name column, enter ^L i.e. a caret
(above the 6 at the top of the keyboard) and an L. Access interprets
the caret as referring to the Ctrl key.
4. In the Action column beside this, choose DoMenuItem. Then, in the
bottom of the window, fill in which menu item you want, i.e. Form
Design, Format, Align, Left. (For Access version 1 it will be Form
Design, Layout, Align, Left.)
5. In the second row, enter ^R under Macro Name, and DoMenuItem under
Action. Again fill in the menu item details in the bottom of the
window, i.e. Form Design, Format, Align, Right.
6. Enter another two rows using ^T for Align, Top, and ^B for Align,
Bottom.
7. Save the macro, and test it. Select two or three controls that are not
aligned correctly, press Ctrl and L together, and watch them move into
place.
There will probably be other operations to add to this macro to cover your
needs. Try to avoid reassigning standard Windows keystrokes such as Ctrl+C
and Ctrl+V (copy and paste).
By the way, did you know that an ampersand (&) can give you a keyboard
shortcut to a control? For example, type &Animals as the caption for a
button and Access underlines the "A" indicating that you can move the focus
to that button with Alt+A.
Provided by Allen Browne, abr...@odyssey.apana.org.au
_______________________________________________________________________________
Can I Use a Combo Box to Find Records
It is possible to use an unbound combo box in the header of a form as a
means of record navigation. The idea is to select an entry from the
drop-down list, and have Access take you to that record. You need at least
Access version 2 to take advantage of this tip.
Assume you have a table called "tblCustomers" with the following structure:
CustomerID Text (indexed as Primary Key).
Company Text
ContactPerson Text
A form displays data from this table in Single Form view. Add a combo box
to the form's header, with the following properties:
Name CboMoveTo
Control Source [leave this blank]
Row Source Type Table/Query
Row Source tblCustomers
Column Count 3
Column Widths 0.6 in; 1.2 in; 1.2 in
Bound Column 1
List Width 3.2 in
Limit to List Yes
Now attach this code to the AfterUpdate property of the Combo Box:
Sub CboMoveTo_AfterUpdate ()
Dim Rst As Recordset
Set Rst = Me.RecordsetClone
Rst.FindFirst "[CustomerID] = '" & Me![CboMoveTo] & "'"
' Note: Omit single quotes is CustomerID is numeric.
If Not Rst.NoMatch Then Me.bookmark = Rst.bookmark
End Sub
The three steps this function takes are:
* The RecordsetClone method creates a duplicate set of pointers to the
records behind this form ( "Me").
* The FindFirst matches the field to be searched with the contents of
the combo box.
* The record is displayed by matching the form's BookMark to the record
found in the clone recordset.
Provided by Allen Browne, abr...@odyssey.apana.org.au
_______________________________________________________________________________
Is it possible to Return to the same record next time a form is opened?
When a form is opened in some applications, you may like to automatically
load the most recently edited record. To do so:
* Create a table to save the record's Primary Key value between
sessions;
* Use the form's Unload event to save the current record's ID;
* Use the form's Load event to find that record again.
As an example, take a form that has CustomerID as the primary key field.
1. Create a table to save the Primary Key value between sessions
Create a table with these 3 fields:
Field Name Type Description
Variable Text, 20 Holds the variable name. Mark as primary key.
Value Text, 80 Holds the value to be returned.
Description Text, 255 What this variable is used for/by.
Save this table with the name "tblSys". In Access 95, you may care to mark
this as a hidden table.
2. Use the form's UnLoad event to save the record's ID.
Set the form's UnLoad property to "[Event Procedure]", and add the
following code. It finds (or creates) a record in tblSys where the field
Variable contains "CustomerIDLast", and stores the current CustomerID in
the field called Value.
Sub Form_Unload (Cancel As Integer)
Dim db As Database, rst As Recordset
If IsNull(Me![CustomerID]) Then Exit Sub
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("tblSys")
rst.index = "PrimaryKey"
rst.Seek "=", "CustomerIDLast"
If rst.NoMatch Then
rst.AddNew 'Create the entry if not found.
rst![Variable] = "CustomerIDLast"
rst![Value] = Me![CustomerID]
rst![Description] = "Last customerID value, to restore in form " &
Me.Name
rst.Update
Else
rst.Edit 'Save the current record's primary key.
rst![Value] = Me![CustomerID]
rst.Update
End If
rst.Close
End Sub
3. Use the form's Load event to find that record again.
Set the form's Load property to "[Event Procedure]", and add the following
code. It performs these steps:
* locates the record in tblSys where the Variable field contains
"CustomerIDLast";
* gets the last stored CustomerID from the Value field;
* creates a RecordsetClone of the records in the form;
* finds that CustomerID in the clone set;
* moves to that record by setting the form's BookMark.
Sub Form_Load ()
Dim db As Database, rst As Recordset, rstFrm As Recordset
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("tblSys")
rst.index = "PrimaryKey"
rst.Seek "=", "CustomerIDLast"
If Not rst.NoMatch Then
If Not IsNull(rst![Value]) Then
Set rstFrm = Me.RecordsetClone
rstFrm.FindFirst "[CustomerID] = " & rst![Value]
If Not rstFrm.NoMatch Then
Me.BookMark = rstFrm.BookMark
End If
rstFrm.Close
End If
End If
rst.Close
End Sub
That's it!
Incidentally, once you have a tblSys, you will find numerous uses for it.
Instead of hard coding your company name into perhaps a dozen reports, add
another record to tblSys where the field Variable contains "CompanyName",
and the field Value contains the actual company name. A text box at the
head of any report can then look up this value by setting its ControlSource
to:
=DLookup("Value", "tblSys", "Variable = 'CompanyName'")
Provided by Allen Browne, abr...@odyssey.apana.org.au
_______________________________________________________________________________
_______________________________________________________________________________
Michael O Shea
_______________________________________________________________________________
The MicroSoft Access Faq
_______________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now,(See Part 1), this file
is just beginning, and my main aim is to get it up and running. To do this, im
going to start just by compiling together a lot of the information that is
already
out there. If i upset anyone by including something of their's and dont give
full
credit, please email me and i will rectify it as soon as possible
_______________________________________________________________________________
Part V
_______________________________________________________________________________
3: Frequently Asked Questions
3-5: Macros
_______________________________________________________________________________
Can i get access to ignore a key such as F1?
Yes, you can disable one of the keys so thatg other users cannot use them in
your database. This is useful in an application where you want to limit the
users options. To do this create an autokeys macro.
by...
create a macro group with the name Autokeys
Choose macro names frim the view menu to display the macro name column
Assign macro names usign the sendkeys code of the key you want to disable
Add the sendkeys action and leave the keystrokes argument empty
Close and save the macro group
Save and close the database.
The next time you opent the Database, Access performs the Autokeys macro. The
effect of the autokeys macro remains as long as the database is open.
_______________________________________________________________________________
How do I temporarily comment out a line in a macro?
"Although macros do not support the REM statement you may be used to from batch
files or BASIC, you can achieve a similar effect. Simply activate the Condition
column for your macro and enter ""False"" as the condition for any action you
wish to temporarily skip. If there's already a condition column, simply add
""False and "" to the start of the condition to make it always False."
_______________________________________________________________________________
How can i set Access to maximise my Database window automatically when i open
a database?
You need to create a macto that maximimizes the window, and then set this
macro to execute automatically when you open the database, to create
this macro :
1. Create a new macro, by selecting new on the macro tab.
2. Enter Sendkeys in the action column of the first row. This macro action
sends keystrokes sto access as if you had typed them
3. Move to the keystrokes argument in the bottom half of the window,
and enter %-x. The % represents ALT. ALT + - (hyphen) opens the
windows control-menu, and x chooses maximize from the menu
4. Move to the wait argument and enter NO
5. choose save from the file menu
6. Enter Autoexec as the macro's name, and select OK. Access automatically
executes any macro named Autoexec when you open its database
_______________________________________________________________________________
Why aren't there more questions about macros here?
Most professional Access developers tend to use modules rather than macros in
their databases. A module can do anything a macro can do except execute
automatically at startup and modify menus. The key difference is that modules
can be written to trap and cure errors, while any error in a macro brings
operations to a halt.
_______________________________________________________________________________
3-6: Modules / Access Basic
_______________________________________________________________________________
I have a line of code that is very long. Can i continue it on to the next line?
In Access 7.0, the line continuation character is " _", note thats a space
and an underscore. In Access 2.0 No. there is no continuation character in
Access Basic. But if you are having to say build up a very long line of SQL
then you do it the following way in order to make your code more readable.
Sql = "blah de blah"
Sql = Sql + "blah de blah" etc
_______________________________________________________________________________
How can I use the Windows API to retrieve the hwnd of a control on an Access
form and alter its properties?
"Yes, you can retrieve the window handle: but there's only one control at a
time in Access that has such a handle, the active control that has the focus.
The problem here is that the control becomes ""active"" at different points,
depending on how you got to the control (via the keyboard, the mouse, or via
Access Basic). Basically, you can't count on the window handle of that active
control being set correctly at any specific time in your Access Basic code if
you want it to be correct for all events."
_______________________________________________________________________________
How do I calculate someone's age from their birthdate?
** Solution 1 **
"This solution is longer, runs slower, but is easier to understand. It just
gets
the current number of years, and then subtracts one from the age if the
requested
birthday hasn't happened so far this year.
Function GetAge (varBD As Variant)
Dim varAge As Variant
varAge = DateDiff(""yyyy"", varBD, Now)
If Now < DateSerial(Year(Now), Month(varBD), Day(varBD)) Then
varAge = varAge - 1
End If
GetAge = varAge
End Function
** Solution 2 **
This solution works basically the same, but counts on the fact that a TRUE value
is
the same as -1, so it just adds on the value of
Now < DateSerial(...)
which will return -1 if the current date is less than the birthdate passed in.
Function GetAge (varBD As Variant)
Dim varAge As Variant
GetAge = DateDiff(""yyyy"", varBD, Now) +
-> (Now < DateSerial(Year(Now), Month(varBD), Day(varBD))
End Function"
_______________________________________________________________________________
How do I change a field to Proper Case (first letter of each word capitalized)?
This article contains two versions of the Proper() function:
- One version is used in the AfterUpdate property of a control on a
form, primarily for data entry.
- The other version is used in calculated query fields, calculated
controls on forms and reports, macro SetValue expressions, and Access
Basic expressions.
NOTE: Microsoft Access version 2.0 includes a sample Proper() function in
the sample database SOLUTION.MDB. The Proper() function is located in the
Proper module. The NWIND.MDB also includes this sample function in the
Utility Functions module.
MORE INFORMATION
================
To create the Proper() function, enter the following code in a module:
'**********************************************************
'Declarations section of the module
'**********************************************************
Option Explicit
'==========================================================
'The following function is designed for use in the AfterUpdate
' property of form controls.
'Features:
' - Leading spaces do not affect the function's performance.
' - "O'Brian" and "Wilson-Smythe" will be properly capitalized.
'Limitations:
' - It will change "MacDonald" to "Macdonald."
' - It will change "van Buren" to "Van Buren."
' - It will change "John Jones III" to "John Jones Iii."
'==========================================================
Function Proper (AnyValue As Variant) As Variant
Dim ptr As Integer
Dim TheString As String
Dim currChar As String, prevChar As String
If IsNull(AnyValue) Then
Exit Function
End If
TheString = CStr(AnyValue)
For ptr = 1 To Len(TheString) 'Go through each char. in string.
currChar = Mid$(TheString, ptr, 1) 'Get the current character.
Select Case prevChar 'If previous char. is a letter,
'this char. should be lowercase.
Case "A" To "Z", "a" To "z"
Mid(TheString, ptr, 1) = LCase(currChar)
Case Else
Mid(TheString, ptr, 1) = UCase(currChar)
End Select
prevChar = currChar
Next ptr
AnyValue = CVar(theString)
End Function
How to Use This Function in the AfterUpdate Property of a Control
-----------------------------------------------------------------
1. Create a form based on a table or query.
2. Type the following in the AfterUpdate property of a text box
control:
=Proper(<control name>)
For example, if the ControlName is [Full Name], type the following:
=Proper([Full Name])
How to Modify This Function for Expressions and Calculated Controls
-------------------------------------------------------------------
1. Enter the code shown above to create the Proper() function.
2. Modify the following section of code
AnyValue=CVar(theString)
End Function
To read as follows:
Proper=CVar(theString)
End Function
Examples
--------
The examples below all require the second version of the Proper()
function.
Using Proper() in a Query:
Field: Full Name:Proper([Last Name] & " " & [First Name])
This will concatenate the first and last names and capitalize the
first letter of each.
Using Proper() in a Calculated Control on a Form or Report:
TextBox
---------------------------------
ControlName: AddressP
ControlSource: =Proper([Address])
NOTE: In calculated fields, the ControlName must be unique.
Using Proper() in a Macro:
Action: SetValue
Item: Screen.ActiveControl
Expression: Proper(Screen.ActiveControl)
NOTE: You can call this macro from the AfterUpdate property of a control
on a form. This has the same affect as the first version of the Proper()
function.
=============================================================================
Copyright Microsoft Corporation 1995.
_______________________________________________________________________________
How do I code an SQL string in Access Basic that includes a reference to a
variable or control?
"In this situation it’s important to remember that controls and Access Basic
variables are out of scope to the JET engine when it processes your SQL
statement.
Therefore, in creating the SQL string you must include as literal values any v
ariables or controls you want in the string. Do this by concatenating them into
the string. For example:
Dim intKey as Integer
Dim strSQL as String
Dim strName as String
intKey = 12345
strSQL = ""SELECT * FROM MyTable WHERE MyKey = "" & intKey
or
strName = ""Thomas Jefferson""
strSQL = ""SELECT * FROM MyTable WHERE MyName = ‘"" & strName & ""‘""
or
Dim F as Form
Set F = Forms!MyForm
strSQL = ""SELECT * FROM MyTable WHERE MyKey = "" & F!MyControl
This syntax applies to any usage of SQL within Access Basic, including filter
and criteria clauses as well as complete SQL sentences."
_______________________________________________________________________________
How do I copy a file directly under control of Access
"Function Copy_File (from_name, to_name) As Integer
On Error GoTo Copy_file_Err
Dim whole As Integer
Dim part As Integer
Dim buffer As String
Dim start As Long
Dim x As Integer
Open from_name For Binary As #1
whole = LOF(1) \ 32000 'numer of whole 32768 byte chunks
part = LOF(1) Mod 32000 'remaining bytes at end of file
buffer = String$(32000, 0)
start = 1
Open to_name For Binary As #2
For x = 1 To whole 'this for-next loop will copy 32,000
Get #1, start, buffer 'byte chunks at a time. If there is
Put #2, start, buffer 'less than 32,000 bytes in the file,
start = start + 32000 'whole = 0 and the loop is bypassed.
Next x
buffer = String$(part, 0) 'this part of the routine will copy
Get #1, start, buffer 'the remaining bytes at the end of the
Put #2, start, buffer 'file.
Close
Copy_File = True
Exit Function
Copy_file_Err:
MsgBox Error
Copy_File = False
Exit Function
End Function
----------------------
Ken Getz has posted a second method which draws on some of the support
code in one of the Access DLLs:
Add this code to the declarations section of a module:
Declare Function wlib_CopyFile Lib ""MSAU200.DLL"" Alias _
""#4"" (ByVal lpszSrc As String, ByVal lpszDest As String) As Long
And then just use it:
retVal = wLib_CopyFile(""SourceFile"", ""DestinationFile"")"
_______________________________________________________________________________
How do I dial a modem from Access?
You can use the Button Wizard in form design mode to build an autodialer
button. If you then examine the CBF the Wizard creates, you'll see the
library function that it uses.
_______________________________________________________________________________
How do I execute an internal DOS command such as COPY
"Function csvExecuteCommand (ByVal CommandText As String, ByVal
-> ShowFlag As Integer) As Integer
' From the ""Access Basic Cookbook""
'
' Declare Function csvWinExec Lib ""Kernel"" Alias ""WinExec"" (ByVal
' -> ApplicationName As String, ByVal State As Integer) As Integer
'
' Accepts: CommandText The name of the internal DOS command,
' including any command-line arguments. For
' example, ""copy a:\*.* b:\""
' ShowFlag One of these constants indicating how the
' DOS window is displayed:
'
' csvSW_HIDE hidden
' csvSW_MINIMIZE minimized
' csvSW_SHOW current size/position
' csvSW_SHOWMAXIMIZED maximized
'
Const csvSW_HIDE = 0
Const csvSW_MINIMIZE = 6
Const csvSW_SHOW = 5
Const csvSW_SHOWMAXIMIZED = 3
Dim ComSpec As String, Msg As String, Reason As String, X As Integer
Dim CRLF As String
CRLF = Chr$(13) & Chr$(10)
csvExecuteCommand = True
ComSpec = Environ$(""comspec"")
ComSpec = IIf(ComSpec = """", ""command.com"", ComSpec)
If CommandText <> """" Then
ComSpec = ComSpec & "" /c "" & CommandText
End If
X = csvWinExec(ComSpec, ShowFlag)
If X < 32 Then
Select Case X
Case 0: Reason = "Out of memory"
Case 2: Reason = "File not found"
Case Is < 32
Reason = "Error code" & Str$(X)
End Select
Msg = "Couldn't execute" & CRLF & "'" & ComSpec & "'." & CRLF & CRLF
-> & "Reason: " & Reason
MsgBox Msg, csvMB_ICONINFORMATION, "Execute Command"
csvExecuteCommand = False
End If
End Function"
_______________________________________________________________________________
How do I get the fully qualified path and file name of the currently-open
Access database?
"Function GetDBName() As String
Dim dbCurrent As Database
Set dbCurrent = DBEngine.WorkSpaces(0).Databases(0)
GetDBName = dbCurrent.Name
End Function"
_______________________________________________________________________________
How do I play a sound file?
"If your computer is equipped with a sound card or a speaker driver, you
can play .WAV (waveforms). First, add the following declaration (all on
one line) to the Declarations section:
Declare Function csvSndPlaySound Lib ""MMSYSTEM.DLL"" Alias
-> ""SndPlaySound"" (ByVal FileName As String, ByVal How As
-> Integer) As Integer
Next, enter this funciton:
Function csvPlayWave (ByVal FileName As String, ByVal How As
-> Integer) As Integer
csvPlayWave = csvSndPlaySound(Trim(FileName), How)
End Function
You can call this from a form or field event like so:
=csvPlayWave(""c:\windows\tada.wav"", 1)
_______________________________________________________________________________
How do I read and write information from .INI files?
"Declare Function GetProfileInt Lib ""Kernel"" (ByVal
-> lpApprofSection As String, ByVal lprofKeyName As
-> String, ByVal nDefault As Integer) As Integer
Declare Function GetProfileString Lib ""Kernel""
-> (ByVal lpApprofSection As String, ByVal lprofKeyName
-> As String, ByVal lprofDefString As String,
-> ByVal lRetStringedString As String, ByVal nSize
-> As Integer) As Integer
Declare Function WriteProfileString Lib ""Kernel""
-> (ByVal lpApplicationName As String, ByVal
-> lprofKeyName As String, ByVal lpString As String) As Integer
Declare Function GetPrivateProfileInt Lib ""Kernel""
-> (ByVal lpApplicationName As String, ByVal
-> lprofKeyName As String, ByVal nDefault As Integer,
-> ByVal lpFileName As String) As Integer
Declare Function GetPrivateProfileString Lib ""Kernel""
-> (ByVal lpApplicationName As String, ByVal
-> lprofKeyName As Any, ByVal lprofDefString As String,
-> ByVal lRetStringedString As String, ByVal nSize As Integer,
-> ByVal lpFileName As String) As Integer
Declare Function WritePrivateProfileString Lib ""Kernel""
-> (ByVal lpApplicationName As String, ByVal lprofKeyName
-> As Any, ByVal lpString As Any, ByVal lplFileName As
-> String) As Integer
Function DeleteProfileEntry (profSection As String,
-> profKey As String, INIFileName As String) As Integer
Dim StringWritten As Integer
Dim xnull As Long
xnull = 0
StringWritten = WritePrivateProfileString(profSection,
-> profKey, xnull, INIFileName)
If StringWritten = 0 Then
MsgBox "Error writing to " + INIFileName$, 0, "ERROR"
DeleteProfileEntry = False
Exit Function
End If
DeleteProfileEntry = True
End Function
Function GetEntriesInSection (profSection As String,
-> profDefString As String, INIFileName As String) As String
Dim nSize As Integer
Dim RetString As String
Dim CharsReturned As Integer
Dim xnull As Long
xnull = 0
RetString = Space$(2048)
nSize = 2048
CharsReturned = GetPrivateProfileString(profSection, xnull,
-> profDefString, RetString, nSize, INIFileName)
If CharsReturned = 0 Then
GetEntriesInSection = "error"
Exit Function
End If
GetEntriesInSection = RetString
End Function
Function GetPrivateProfile (profSection As String,
-> profKey As String, profDefString As String,
-> INIFileName As String) As String
Dim nSize As Integer
Dim RetString As String
Dim CharsReturned As Integer
RetString = Space$(80)
nSize = 80
CharsReturned = GetPrivateProfileString(profSection, profKey,
-> profDefString, RetString, nSize, INIFileName)
If CharsReturned = 0 Then
GetPrivateProfile = "error"
Exit Function
End If
RetString = Trim(RetString)
RetString = Mid(RetString, 1, Len(RetString) - 1)
GetPrivateProfile = RetString
End Function
Function WritePrivateProfile (profSection As String,
-> profKey As String, profNewString As String,
-> INIFileName As String) As Integer
Dim StringWritten As Integer
StringWritten = WritePrivateProfileString(profSection, profKey,
-> profNewString, INIFileName)
If StringWritten = 0 Then
MsgBox "Error writing to " + INIFileName$, 0, "ERROR"
WritePrivateProfile = False
Exit Function
End If
WritePrivateProfile = True
End Function
Here is an example which retrieves the 'shell=' entry from your SYSTEM.INI file:
Dim shell_name As String
shell_name = GetPrivateProfile("boot", "shell", "", "system.ini")
MsgBox "Your Windows shell program is: " & shell_name"
_______________________________________________________________________________
How do I send a fax from Access using WinFax?
"The sample code below assumes you are on a form and want to fax out a
report of the current record. The code can be attached to the "on push"
property of a button. It would look like this
=FaxRecord([phone],[contact name])
This does not address starting up WinFax first or choosing it as your
default printer. There is sample code for the printer routines in a file
called SETPRT.ZIP in Library #7.
Dick Cullom
Microsoft Access, PSS
'==============================================================
'WinFax should already be running.
'Grabbing the telephone number and name from the form will
'eliminate seeing the WinFax "send" dialog screen, unless you
'have preview set to "on". You will still get a "send
'confirmation" message.
'==============================================================
Function FaxRecord (FaxNumber As String, Receiver As String)
On Error GoTo FAX_Error
Chan = DDEInitiate("FaxMng", "Transmit") 'Begins Conversation
DDEPoke Chan, "Receiver", Receiver 'Fills-in who the fax goes to
DDEPoke Chan, "Fax Number", FaxNumber 'Fills-in telephone number
'Opens report in print mode on the current record of the form
DoCmd OpenReport "ReportName",0,"", "[id]=Forms![formname]![id]"
DDETerminate Chan
FAX_Exit:
Exit Function
FAX_Error:
MsgBox "Error: " + Error$, 0, "FAX"
Resume FAX_Exit
End Function"
_______________________________________________________________________________
How do I set and retrieve global variables?
"You actually can't refer directly to global (or local or static) variables
directly in a form event or a field event or a macro. You need to write a sort
of ""helper"" function to return the value for you:
Function GetTheVar () as variant
GetTheVar = TheVar 'a global variable previously set
End Function
Then you can use this function wherever you would use the actual variable
itself, for example in the 'criteria' field of a query:
like GetTheVar() & "*"
If you have a lot of these variables you can write a couple of generic "set"
and "get" functions:
Function SetGlobalVar (var as variant)
Select Case var
Case "First_Var":
First_Var = var
Case "Another_Var":
Another_Var = var
'and so on
Case Else:
msgbox "Unrecognized variable name: " & var
End Select
End Function
Function GetGlobalVar (var as variant) as variant
Select Case var
Case "First_Var":
GetGlobalVar = First_Var
Case "Another_Var":
GetGlobalVar = Another_Var
'and so on
Case else:
MsgBox "Unrecognized variable name: " & var
End Select
End Function
You will need to modify these functions whenever you have another variable
you would like to handle, but this is better than having to write
separate functions for each variable. If you have a lot of Select
Case statements this method can be slow, but for just checking global
parameter values, this method works fine.
However whenever possible instead of using global variables I create
a hidden form and store information there. Forms that are open are
available from anywhere, and since the form is invisible the user
doesn't need to know that they exist."
_______________________________________________________________________________
How do I use DDE to poke information into an Access table?
Although Access does not act as a DDE server or accept DDEPoke commands,
there is a workaround for this problem which involves using DDEInitiate
to fool Access into running a function. See MSKB Article number Q100167
for the details.
_______________________________________________________________________________
Is there a way in Access Basic to determine the current record number so
that the record number can be used in a GoToRecord action?
"Tables and query results (dynasets) don't really have ""record numbers""
in the dBase sense. Yes there is a number shown at the bottom of forms and
table datasheets, but that number is dynamic. The record number will vary
depending on the sort order, or the filter, or the query used. Records
are distinguished from one another by their Primary Key -- a unique field
or combination of fields. If you need to return to a particular record
you can save that location in a Bookmark variable, or you can save the
values in the key fields and do a seek or a .findfirst method to re-locate
the records.
If you feel that you really must have the record number, there is a
function for retrieving it in MSKB #Q103260."
_______________________________________________________________________________
Is there any way to have access link to MS Word 6.0 to do spell checking on
text or memo fields?
There is a method for this in MSKB Article number Q96544 which depends on
writing temporary files out and reading them in to Word. Microsoft has
also recently issued documentation on the Common Speller Applications
Programming Interface (CSAPI), which is the standard speller interface
used in Microsoft products. However, the CSAPI license explicitly
prohibits using any of the existing MS product dictionaries to
spell-check other applications, so this does not appear to be a
viable alternative. There is a new commercial product which you can
use for spell-checking from Access. Called VT-Speller, it is a VBX/DLL
available for $99 from VisualTools, 800-884-8665 or 913-599-6500.
_______________________________________________________________________________
"Why does Access put ""Option Compare Database"" at the top of every
module I create?"
"Option Compare Database tells Access to use its standard order when sorting
data -- the name order it uses in queries. You can search on
""Option Compare"" in Access Help to see the other available sort
orders. Most developers recommend you add ""Option Explicit"" directly
after this line, which will force you to declare all your variables
and help cut down on code errors due to mistyping."
_______________________________________________________________________________
Subj: Business Days between 2 dates
Date: 94-12-16 18:58:08 EST
To calculate the number of business days between 2 dates create a module
called Business Days with the following functions:
----
Function BusinessDaysBetween (d1 As Double, d2 As Double) As Integer
Dim days As Integer
Dim weeks As Integer
days = DaysBetween(d1, d2)
' Trim of weekends at start and end of range
If DayOfWeek(d1) > 5 Then days = days - (8 - DayOfWeek(d1))
If DayOfWeek(d2) > 5 Then days = days - (DayOfWeek(d2) - 5)
' Calc the whole number of weeks
weeks = days 7
' A whole week implies at least 1 week end (of 2 days)
days = days - (weeks * 2)
BusinessDaysBetween = days
End Function
---
Function DayOfWeek (d As Double) As Integer
DayOfWeek = Format(d, "w", 2)
End Function
---
Function DaysBetween (d1 As Double, d2 As Double) As Integer
DaysBetween = Int(d2) - Int(d1) + 1
End Function
---
Now all you have to do is pass 'BusinessDaysBetween' both the date of
the start of the period and the date of the end of the period inclusive
and it returns the number of business days between the dates.
For public holidays you will have to create a table containing at least the
dates of public holidays and subtract the result of the following SQL query:
SELECT COUNT(1) FROM PublicHolidays
WHERE holiday_date Between d1 And Between d2
AND DayOfWeek(holiday_date)<=5
The last part of the condition is used to make sure that public holidays on
weekends are not counted because weekends have already been removed.
_______________________________________________________________________________
Can someone write some code to include the SQL in the Access Basic function?
--
Greg Turner (gtu...@qitgbsd2.telecom.com.au)
_______________________________________________________________________________
Subj: Business Days between 2 dates
Date: 94-12-16 04:57:04 EST
I have managed to add code to handle public holidays to my function. You
could even alter it to allow for leave as well.
Create a table called: tblHolidays which contains the fields:
holiday_date Date/Time
holiday_name Text Field size: 50
Add the date and name of each public holiday to the table.
You will have to update this table each year as holidays such as Easter move
around and you also have to update the year. With a little more work you set
my code up to handle holidays such as Christmas and New Year with always
fall on the same date and then have algorithms to calculate Easter and the
other 'floaters'.
Now we need a query called qryHolidaysBetween that has 2 data parameters
called p1 and p2 and using the SQL:
PARAMETERS p1 DateTime, p2 DateTime;
SELECT DISTINCTROW count(tblHolidays.holiday_date) AS n
FROM tblHolidays
WHERE (([tblHolidays].[holiday_date] Between [p1] And [p2] and
DayOfWeek(holiday_date)<=5));
Change the code for the BusinessDaysBetween fn to:
----
Function BusinessDaysBetween (d1 As Double, d2 As Double) As Integer
Dim days As Integer
Dim weeks As Integer
days = DaysBetween(d1, d2)
' Trim of weekends at start and end of range
If DayOfWeek(d1) > 5 Then days = days - (8 - DayOfWeek(d1))
If DayOfWeek(d2) > 5 Then days = days - (DayOfWeek(d2) - 5)
' Calc the whole number of weeks
weeks = days 7
' A whole week implies at least 1 week end (of 2 days)
days = days - (weeks * 2)
days = days - HolidaysBetween(d1,d2)
BusinessDaysBetween = days
End Function
And add the following function:
Function HolidaysBetween (d1 As Double, d2 As Double) As Integer
Dim db As Database
Dim qry As QueryDef
Dim rst As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set qry = db.OpenQueryDef("qryHolidaysBetween")
qry![p1] = d1 'set the parameter
qry![p2] = d2 'set the parameter
Set rst = qry.OpenRecordset()
HolidaysBetween = rst![n]
End Function
--
Greg Turner (gtu...@qitgbsd2.telecom.com.au)
_______________________________________________________________________________
Subj: Subj: Working days
Date: 95-05-23 06:25:45 EDT
J.C braun writes
<How can i exclude the weekdays and possibly even the holidays
<from a certain period varying from 1day op to 6 weeks or more,
<to estinate the number of workingdays.
Reply:
The WeekDay function will return the day of the week for a given
date. However if you want to exclude holidays from your calculation
you will have to keep a record of each holiday in a table.
The following function returns the number of working days between
two dates for a given country (in UK the public holidays are
different in Scotland, N. Ireland...)
Function CalculateWorkingDays (strCountry As String, _
varStartDate As Variant, varEndDate As Variant)
Dim dblDateSerial As Double
Dim intWeekDay As Integer
Dim intWorkingDays As Integer
Dim varHolidayName As Variant
Dim strCriteria As String
dblDateSerial = CDbl(varStartDate)
intWorkingDays = 0
Do Until DateDiff("d", dblDateSerial, varEndDate) < 0
intWeekDay = Weekday(dblDateSerial)
If intWeekDay < 7 And intWeekDay > 1 Then
intWorkingDays = intWorkingDays + 1
strCriteria = "[CountryName] = " & Chr$(34) & _
strCountry & Chr$(34) & " And [DateOfHoliday] = " &
dblDateSerial
varHolidayName = DLookup("HolidayName", "tblPublicHolidays",
strCriteria)
If Not IsNull(varHolidayName) Then
Debug.Print varHolidayName
intWorkingDays = intWorkingDays - 1
End If
End If
dblDateSerial = dblDateSerial + 1
Loop
CalculateWorkingDays = intWorkingDays
End Function
Notes:
1 Underscore represents line continuation but will need to
be removed for code to work.
2 tblPublicHolidays has this format:
CountryName Text Key
DateOfHoliday Date Key
HolidayName Text
3 Dates are converted to serial numbers because DLookup
expects dates to be in US format. This may not be
necessary with Dutch version of Access.
4 The function is OK for short date ranges but is
slow when dealing with large ranges.
Nick Dowling
BBC World Service
London
An expert is one who knows more and more
about less and less - Nicholas Butler
_______________________________________________________________________________
Subject: Re: Padding Numbers with leading Zeros
>I have a table with a field that contains numbers, field size = 9.
>Most of the entries are less than 9 characters long but I need to display
>leading zeros in that field. How do I add the leading zeros to that
field?????
>I don't do any math here so the field doesn't have to be numeric.
Instead of storing numbers in the table why not store the padded string
instead - this will save time while displaying data. Try using this
function - Pass it the numeric value and the number of characters to pad
to
e.g. PadLeftWithZeros(CStr(AnyNumber),9).
----------------------------------------
FunctionPadLeftWithZeros(in_strAsString,placesAsInteger) _
AsString
Dim pad_no AsInteger
pad_no = places - Len(Trim(in_str))
If pad_no > 0 Then
PadLeftWithZeros = String(pad_no, 48) & Trim(in_str)
Else
PadLeftWithZeros = Trim(in_str)
End If
End Function
---------------------------------------
Sumeet
_______________________________________________________________________________
How do I get the Windows File/Open common dialog to appear and return the
selected filename
There is a demonstration of this in the Startup Database in the Downloads
Section.
http://easyweb.easynet.co.uk/~trevor/AccFAQ/downloads.htm#startupdb
_______________________________________________________________________________
How do I run an internal DOS command from within Access Basic, if I try
x=Shell("copy file1 file2",1) I get a message "File not found".
Dos internal commands are processed by the command processor shell so you
should be calling this program and not trying to execute the internal
command itself.
The command processor is usually command.com but this can vary and so
can it's location (indeed it can even reside outside of the dos search
path) for this reason it's not a good idea to hard code it's location
or name into an application as this may change if the application is
moved to another pc. Use the Environ function to retrieve the %COMSPEC%
environment variable as this always points to the current command
processor shell. e.g.
Dim hTask As Integer
hTask = Shell(Environ("COMSPEC") & " /C COPY file1 file2", 1)
Note: Some of the DOS internal commands are available in Access Basic
(in the long form of the command), e.g. RmDir, ChDir, MkDir. The Dir
command can be emulated using the Dir() function although this only
returns files, not hidden/system files or directory names.
[Trevor Best]
_______________________________________________________________________________
Now I know how to shell an internal command such as copy, I need to wait
for it to finish before continuing, at present Access will carry on to the
next statement, which needs to access the new file that's just been copied.
This is usual for a multitasking environment such as Windows, however as
"Shell" is a function in Access Basic it returns the handle to the task
(or module) that it just started so you can use this return value to see
when it's finished, you'll need an API call for this.
(declarations)
Declare Function GetModuleUsage Lib "Kernel" (ByVal hModule As Integer) As
Integer
(somewhere within a module)
Dim hModule as Integer
hModule = Shell("whatever.exe",1)
Do While GetModuleUsage(hModule)
DoEvents
Loop
DoCmd Echo True, "This message will only come up when whatever.exe
has finished"
--------------
The DoEvents within the loop ensures that Access relinquishes processor
time to the called program otherwise it could wait forever.
[Trevor Best]
_______________________________________________________________________________
Am I able to Play an Avi file within Access?
Yes! and it can be done without any need for extra OCX's etc.
Use this code
Declare Function mm_mciSendString Lib "mmsystem.dll" Alias "mciSendString"
(ByVal mcicommand As String, ByVal retinfo As String, ByVal retlen As
Integer, ByVal hwnd As Integer) As Long
Declare Function mm_mciGetErrorString Lib "mmsystem.dll" Alias
"mciGetErrorString" (ByVal wError As Long, ByVal errString As String,
ByVal buflen As Integer) As Integer
Function MM_Play (ByVal mm_file As String) As Long
Dim retinfo As String
Dim retcode As Long
Dim retbool As Integer
retinfo = Space(128)
mm_file = "play " + mm_file + Chr$(0)
retcode = mm_mciSendString(mm_file, retinfo, 127, 0)
If retcode <> 0 Then
retinfo = Space(128)
retbool = mm_mciGetErrorString(retcode, retinfo, 127)
If retbool <> 0 Then
retinfo = Trim$(retinfo)
MsgBox retinfo, 16, "MM System"
Else
MsgBox "unknown MCI error", 16, "MM System"
End If
End If
MM_Play = retcode
End Function
_______________________________________________________________________________
I have some functions I've written that I could use in other applications.
How do I put them in a library?
Libraries are one of the most useful features of Access. You can
create an mdb with nothing but generic functions that can be
used in any of your applications, or it can contain wizards
or forms that you use often.
Here's how you create and use a library: Let's say you had written
a function that runs any kind of action query. The function
looks like the following:
Function RunActionQry (sqryName As String) As Integer
On Error GoTo ActionQry_Error
Dim db As Database
Dim Qd As QueryDef
Set db = DBEngine(0)(0)
Set Qd = db.QueryDefs(sqryName)
Qd.Execute
Qd.Close
RunActionQry = True
ActionQry_Exit:
Exit Function
ActionQry_Error:
MsgBox Error
RunActionQry = False
GoTo ActionQry_Exit
End Function
Nothing fancy, it's just that all you have to do is call the function,
passing it the name of an action query, and it does all the hard stuff
for you. AND it's generic. There's nothing database specific about this
function. All these qualities make it perfect for a library function.
Here are the steps for adding this function to a library:
1. Create a new database with an .MDA extension.
2. Import your function. You might rename it to something that won't
likely have the same name as any other function. You could
name this one "zlibRunActionQry".
3. Exit Access and open your MSACC20.INI file in notepad.
4. In the [Libraries] section, add the name of the database,
plus "RO" for "Read Only" (if you won't be writing data
in this file).
5. Save the .INI file.
6. Open Access. Whenever you open Access your library will load
with it, making your function available to other applications
you create.
7. To use the function, just call it from anywhere you would normally
use the function, and make sure you check the return value for errors.
You've just created a library of code!
_______________________________________________________________________________
How can i find how many of a given day are in any month?
You sometimes need to know the number of a given day -- for example, the
number of Mondays -- in a specific month. You can use the DaysInMonth()
function to get the information you need.
DaysInMonth() involves three parameters (all integers):
* intDOW: the numeric day-of-week you're interested in, where Sunday is
1, Monday is 2, and so on.
* intMonth: the number of the month you're interested in, where January
is 1, February is 2, and so on.
* intYear: the year you're interested in (1994, for example)
Given these three pieces of information, DaysInMonth() will return the
number of times within the given month that the requested day occurs. For
example, DaysInMonth(4, 3, 1994) will return 5 since there were five
Wednesdays in March 1994.
DaysInMonth() takes three steps to retrieve the information you need:
* It finds the first day in the month you've requested information
about.
* Starting on that date, it walks forward in the month, if necessary,
until it finds the first day-of-week that matches the day-of-week you
requested.
* Starting there, it counts ahead, skipping weeks, until it finds that
it has left the month in question.
Once you've copied DaysInMonth() into a module, you should be able to call
it like any other built-in Access function from anywhere in your
application. Here's the function:
Function DaysInMonth (ByVal intDOW As Integer, _
ByVal intMonth As Integer, ByVal intYear As Integer)
Dim varFirst As Variant
Dim intCount As Integer
' Find the first day of the month
varFirst = DateSerial(intYear, intMonth, 1)
' Move varFirst forward until it hits the
' matching day number.
Do While Weekday(varFirst) <> intDOW
varFirst = varFirst + 1
Loop
' Now, varFirst is sitting on the first day
' of the requested number in the month. Just count
' how many of that day type there are in the month.
intCount = 0
Do While Month(varFirst) = intMonth
intCount = intCount + 1
varFirst = varFirst + 7
Loop
DaysInMonth = intCount
End Function
This tip first appeared in the Smart Access newsletter
_______________________________________________________________________________
Can i get my Access Basic Code to wait till a user closes a form before
continuing processing?
Yes. One simple way is to just open a form as modal, and this will
keep control of the Application until the form is closed.
However, I often want to allow the user to perform other events in the
application that may be unrelated to the event I am trying to control
with the modal form and yet still have my Access Basic code wait until
the user closes the form. Access provides no direct means by which to
do this. By defining a global variable and a simple function, however,
you can open a non-modal form and have your code pause until the user
exits the form.
Here's the basic function:
Global intWaitVar As Integer
Function Wait ()
Do
DoEvents
If intWaitVar = False Then Exit Do
Loop
End Function
Start by creating the above global variable and function. Then,
from your Access Basic code, set intWaitVar to True right before the
OpenForm action that opens the form. Immediately after the OpenForm
action, call the Wait() function. This will cause the rest of your
Access Basic code to stay in the wait loop until the global variable
is set to False. Finally, to make this work you'll need to reset the
global variable to False in your form's OnClose event. This way, when
the form is closed, your Access Basic code will resume. Thus, the form's
OnClose event procedure would look like this:
Sub Form_Close (Cancel As Integer)
intWaitVar = False
End Sub
Putting it all together, the function would look something like this:
' ...
' Set the global wait variable
intWaitVar = True
DoCmd OpenForm "MyForm", A_NORMAL, , , , A_NORMAL
varRet = Wait()
' Rest of code will wait until MyForm is closed.
' ...
_______________________________________________________________________________
Michael O Shea
_______________________________________________________________________________
The MicroSoft Access Faq
_______________________________________________________________________________
This File is maintained by Michael O Shea (mos...@iol.ie). Please direct all
Suggestions, hints, tips and complaints to me. As of now,(See Part 1), this file
is just beginning, and my main aim is to get it up and running. To do this, im
going to start just by compiling together a lot of the information that is
already
out there. If i upset anyone by including something of their's and dont give
full
credit, please email me and i will rectify it as soon as possible
_______________________________________________________________________________
Part VI
_______________________________________________________________________________
3: Frequently Asked Questions
_______________________________________________________________________________
3-7: Wizards, Tools, OLE & DDE
_______________________________________________________________________________
How do I create a Help File to use with Access?
You will need the Microsoft Help Compiler to create a Windows help file.
You can download the latest version from the WINSDK forum; the file name is
HC505.EXE (this is a recent update which works with version 6.0 of Word; the
previous version did not). While you can create the source code for help
files directly in any word processor that understands the RTF format (such
as Microsoft Word), many people prefer to use a dedicated front-end tool
for the purpose.
_______________________________________________________________________________
What Is the Microsoft Access Service Pack Version 2.0?
Updated Tuesday, November 07, 1995
The Microsoft Access version 2.0 Service Pack contains files that
replace existing files in your current Microsoft Access version 2.0
installation. The Service Pack includes the latest versions of:
- The Microsoft Jet database engine version 2.5
- The OLE dynamic-link libraries version 2.02
- The Btrieve, Paradox, and xBASE installable ISAM drivers
The Microsoft Jet database engine has been modified to enhance
database repair functionality and to correct the erroneous setting
of an internal database flag that in some cases results in the
message "Couldn't open SYSTEM.MDA." The OLE dynamic-link libraries
(DLLs) have been modified to allow the use of OLE custom controls
created by third-party vendors and also to correct problems with
diminished system resources when printing reports containing embedded
Microsoft Graph objects. The installable ISAM drivers have been
modified to correct various minor problems.
Related Knowledge Base Articles
WX1124: Microsoft Access Version 2.0 Service Pack Q122927
http://www.microsoft.com/kb/deskapps/access/Q122927.htm
_______________________________________________________________________________
Is there any easy way to Link Microsoft Access data to Microsoft Excel?
Use the following steps to link data from Microsoft Access databases to
Microsoft Excel using dynamic data exchange (DDE) to link the two products.
1. Open a Microsoft database, and within the database window (database
container) highlight the table you want to link to Microsoft Excel.
2. From the Microsoft Access Edit menu, choose Copy.
3. Switch to Microsoft Excel, and from the Edit menu, choose Paste Link.
The formula resulting from the Paste Link command should look
something like this:
{=MSAccess | 禅EST.MDB;Table Students!All}
where Students is the name of the table you copied.
This link is now created however, Microsoft Access does not support hot
links. To update data in Microsoft Excel do the following:
1. Open the Microsoft Excel document you wish to update. Choose Links
from the Edit menu.
2. In the Links list box, select the link or links to be updated (if you
only have one link it is automatically highlighted).
3. Click the Update button.
4. Choose the close button. Your data has now been updated.
Try linking Access data to Excel and you will end up using this tip
frequently!
Tip provided by Paul....@nau.edu
_______________________________________________________________________________
3-8: SQL & ODBC
_______________________________________________________________________________
Im working with generating queries directly from SQL, but im having some
problems (incorrect syntax) with the date section of my SQl
Its important to remember that regardless of where you are using access,
If doing sql from code you need your dates to be in US format dates
eg:" WHERE DateField >= #" & Format$(startDate, "m/d/yy") & "# "
_______________________________________________________________________________
Can I use ODBC to read FROM Access 2.0 files?
"Not right now. A limited ODBC driver ships with Access 2.0, but it will
only work when called from other MS Office applications, since it does
not implement minimum ODBC functionality.
Microsoft currently ships ODBC desktop drivers that provide data access to a
variety of popular formats including Access, Btrieve, Foxpro, dBase, and
Paradox. ODBC accomplishes this via a separate DLL included with ODBC known
as the SIMBA driver (SIMBA.DLL). While SIMBA has served us well in the past,
it does has it's limitations:
1) It is a 16 bit driver.
2) It does not support Access 2.0 file formats.
3) It does not support the Excel 5.0 format.
4) It is not available on non-Win31 platforms.
5) Feedback from customers demonstrates that the Access driver is slow
relative to Access.
We elected to implement a long-term solution that not only provides access to
2.0 data, but does it as a standard ODBC driver to all ODBC-enabledapplications.
Pursuing this longer term solution extends the developmentcycle time, as I'm
sure you can understand, since many more permutations of tests and products
must be conducted in order to meet our quality standards.
This longer term solution will provide an ODBC driver in not only a 16-bit
configuration, but also in a 32-bit configuration. Furthermore, it is being
done in a manner to allow us to more easily provide this solution on
additional Microsoft platforms, to support the new file formats for Access
2.0, Excel 5.0, and Paradox 4.x, to improve performance, and to provide
better synergy with Access 2.0.
Both drivers use the Jet Engine and its Query Processor, and eliminate the
need for SIMBA in the architecture. By switching to this configuration, it
will make it very easy for us to continue to provide easy access to the
desktop IISAMS accessed by Access, since ODBC will, by this improved
architecture, have automatic access to the Jet (and Access) supported IISAMS.
It would have been impossible for us to release a stable product that meets
all of the above needs in time for the release of Access 2.0. I hope that
you understand some of our reasons for taking the time to implement a more
global solution that not only addresses the need for support for the
Access 2.0 format, but that meets all of the other requirements mentioned
above."
_______________________________________________________________________________
I'm getting random GPFs when attaching to Btrieve tables. What now?
One place to look is at your PATH environment variable. The BTRV200.DLL ISAM
driver has problems if your path is over 128 characters, which is allowed be
several versions of COMMAND.COM and replacements such as 4DOS.
_______________________________________________________________________________
I'm having a problem with the indexes on my attached FoxPro files.
How do I fix this?
"Try opening the INF file in the same directory where the FoxPro file is
located, with Notepad. This should look something like this:
[FoxPro 2.5]
MDX1=C:\data\fp25exam\fp25.cdx
[UIDX1 CHARACTER]
MDX1=C:\data\fp25exam\fp25.cdx
Actually it is supposed to look like this:
[FoxPro 2.5]
MDX1=C:\data\fp25exam\fp25.cdx
UIDX1=CHARACTER"
_______________________________________________________________________________
What do I need to do to make Access work with Oracle files on my server?
1) Have the phone number of a good witch doctor on hand; better still,
have him on-site if possible.
2) Buy the LARGEST bottle of Maalox you can find.
3) Keep a few chickens and sheep around, they will likely be needed for
sacrificial purposes. Live ones work best, but if you can't find any live
ones, make sure you get ones that are as fresh as possible.
4) Don't attempt to implement during a full moon, during a month that contains
the letter 'R', or on dates divisible by the numbers 7 or 13.
5) Make sure your mother, young children, and members of the fairer sex are
evacuated, so as to not be exposed to the language that will inevitably
ensue. Oh, yes, get the longshoremen out of there, they'll be embarrased,
too.
So much for the serious answers, here are a few more:
1) If you're running Oracle7, get the Oracle7 ODBC driver from library 4 of
the Oracle User Group forum (GO ORACLE). It's more or less free; you
won't get support for it unless you have some type of license agreement in
effect with Oracle.
2) You need SQL*Net for Windows. I think it comes with the Oracle
installation package.
3) Make sure you have the most current version of ORA6WIN.DLL or ORA7WIN.DLL,
and make sure it matches your version of Oracle.
4) When you attach tables, Access sends a query ""SELECT * FROM ALL_CATALOG""
(or some list of column names instead of ""*"") to find out what you can attach.
I've found that you may have to A) select View/Options/General and set
""Show System Objects"" to YES in order to get a list of Oracle tables,
and/or B) explicitly GRANT rights to USERs or ROLEs instead of relying on
Oracle's ""SELECT ANY TABLE"" privilege. (But I think the witchcraft plays a
large role in this, too.)
5) For the most part, ""Oracle support"" is an oxymoron.
6) If you're using Access 1.x, make sure you define the datatype of ALL
parameters used in your queries -- that includes references to controls on
forms.
7) Don't do hetergeneous joins between Access tables and LARGE Oracle tables
under Access 1.x; it fetches the ENTIRE Oracle table in order to resolve the
join locally. Access 2.0 handles this MUCH better.
8) Don't do multi-column outer joins on Oracle tables from Access 1.x, they
also get resolved locally. (Again, fixed in 2.0)
9) If you are using Netware, be prepared for the infamous ""Black Screen of
Death"", where your PC's screen suddenly goes black except for a blinking
cursor about 4 lines down from the top left corner .... only remedy is to
reboot. This seems to be more of a problem if you are using both Access
and another tool such as Oracle Data Browser from the same workstation,
whether simultaneously or switching back and forth.
10) Using Ctrl-Break to cancel a runaway remote query is iffy, at best.
11) If you use MakeTable queries from Access to create table on Oracle, make
sure your tablenames and column names are ALL UPPERCASE. Access sends the
names wrapped in quotes, so Oracle doesn't translate the case. If you
forget to do this, then you'll have to put all your table and column name
references in quotes whenever you use an Oracle utility like SQL*DBA.
- A few more additions by Mike Gunderloy -
12) If your reattachments start mysteriously failing, you need a new driver.
The one on the ORACLE forum as of 8/24/94 seems to work.
13) In Oracle 7, the CHAR() data type pads the field with spaces. This
causes no end of fun with Access. VARCHAR2() does not pad and so maps to
Text much better.
14) Oracle searches are Case-Sensitive, which may break some existing
queries when you port data over from Access."
_______________________________________________________________________________
What should I know to move my Access tables to MS SQL Server?
"A couple of things:
1. Don't use spaces in any of your table or field names if you're doing
initial design work in Access. They won't export properly to SQL Server.
2. After exporting an Access table to SQL Server, you need to use the SQL
Server management tools to add a unique index to the table. If you don't do
this, Access will treat the table as read-only.
3. Be sure you understand how to backup and dump transaction logs so that
they don't get full and crash the whole darned thing at three in the morning
(the voice of experience...)
4. If you opt for the 10 user/25 connection version of SQL Server, it
installs out of the box as 10 user/10 connection. You need to run a SQL
procedure to up the connection count, since each Access user will take at
least two connections."
_______________________________________________________________________________
3-9: Security
_______________________________________________________________________________
Is an Access Database Secure?
If you are using Access 2.0, then in a word, no, if you are using access 95
then more reasonably so.
_______________________________________________________________________________
After securing a database, what are some methods to assign permissions to users?
"Here it is important to remember, that a user who is using a new/clean
system.mda is logging onto the system as the Admin user who is a member
of the Users group. Because of this, there are three scenarios you can
follow when assigning permissions.
Scenario A. Users are required to logon to the system. Without logging on,
users have no access to any of the objects.
Remove all permissions from the Users group. Create new groups and assign
permissions to these groups. Create new users and add users to groups as
needed.
Scenario B. Users can logon to the system, but it is not required. If they do
not logon to the system, they will have limited access.
Assign permissions to the users group for the objects you wish anyone not
using your system.mda to have access to. Next, add groups and users as needed.
Assign permissions as needed.
Scenario C. Similar to step 2, but users cannot logon to the system. Users are
never prompted for a logon and access to objects may be limited.
Assign permission to the users group only. There is no need to create other
users or groups. Save the system.mda you used to create the database as your
administrator system.mda. When using this system.mda, you are prompted for a
logon. The only active user is DEV. When you need to make changes to the
database, use this system.mda. All other users will use a clean/new system.mda.
They will not be prompted and will only have the permissions you assigned
to the users group."
_______________________________________________________________________________
How do I secure an Access database?
"There is only one MicroSoft approved method to fully secure an Access
database. This is to use the MicroSoft Security Wizard, available as
SECWIZ.ZIP. However, the 2.0 Security Wizard is not yet available.
Until it is, you should be able to get close following this procedure.
Step I. Open your database, say NWIND.MDB (or whichever database you wish
to secure), under a developer account, say DEV with PIN 1234. Be sure that
you have permissions to everything in the database. One way you may achieve
this is:
A. Open NWIND.MDB (substitute your database here)
B. Set password for Admin User
C. Add New User (DEV in this example with PIN 1234)
D. Make User DEV a member of the Admins and Users groups
E. Logout/Close Access
F. Start Access and Login as DEV
G. Remove Admin User from Admins Group
H. Logout/Close Access
I. Start Access
J. Login as DEV user
It would be a good idea to save a copy of your SystemDB file, which was
orginally named SYSTEM.MDA, say as SYSTEMOR.MDA, for possible later use
when you don't want security. You will not be able to recreate the
original Admin user any way short of reinstalling if you don't backup.
Step II. Export all of your items to another Access database, which
will be the secure database. Use a new name for the secure database,
such as NWINDSEC.MDA. NWIND.MDB will not be modified.
Step III. Open NWINDSEC.MDA and use the security dialogs to remove all
permissions from the Users group.
Your new database is now secure. All permissions in your new database
have been removed from the three original groups: Guests,Users and
Admins. Only the DEV user has permissions to logon to the system and
access the objects. From this point, you must assign security
permissions to the groups/users you want, possibly creating new groups
such as App_Read and App_Rd_Write."
_______________________________________________________________________________
How should I secure databases on a network?
"By default, Microsoft Access allows you to secure a database across a
network. The key is to place the system.mda file on the network. Users
can then modify their MSACCESS.INI file to point to the location out on
the network. You can also use the Change Workgroup Icon to change the
ini setting. There are also some sample applications on CompuServe that
allow you to change workgroups more easily. There are two basic
strategies to use.
A. Create one system.mda and place it on a network. When developing new
MDB files, use this system.mda. This will enable users to only have to
use one system.mda. All databases are secured by this system.mda. This
is good if one person is designing all databases used.
B. Create one system.mda per database. This is easier for developers,
but is more difficult for users since they have to change workgroups
each time they change databases. This is good if several different people
are creating databases."
_______________________________________________________________________________
"I just used Security | Users to create a new user, but when I try to sign
in using that new ID and password I get ""Not a valid account name or
password."" Why?"
When you create a new user, Access prompts you
to enter a user name and a PID (Personal ID number). That PID is *not* a
password -- it is just a unique identifier for the user. When you first
create a user, it is always with a *blank* password, so try just logging
in with the proper user name and no password. Then you can use Security |
Change Password to put a new password in place.
_______________________________________________________________________________
I wrote some Access Basic to retrieve group and user identities, and it
worked fine when I tested it, but it fails when users run the code. Why?
In order to perform any security operations, either through the UI or
in code, you must be a member of the Admins group. If you have security
code that must run while regular users are logged in, you'll need to
open a second workspace as an Admins user and run the code in that
workspace. In this case, since you'll need to embed an Admins user
name and password in code, you'll also have to encrypt your database.
_______________________________________________________________________________
If I use RWOP queries to help secure my tables, how can I prevent users
with full retail Access from altering the query definitions? "
If you want to use RWOP and ensure that users don't muck around with
queries with the retail version then:
1) Secure all your tables for no access by users (no read definitions, no
nothing). All access will now have to be through queries with RWOP.
2) Set your RWOP queries for appropriate security (whatever you want the user
to have). If the user tries to design these queries, Access won't let them
since they do not have read design permission on the underlying tables (you
have to have read design permissions on the underlying tables to be able to
design a query). Also, note that even if a user could get the query in design
mode, any changes they make cause the query to be run with their permissions
(contrary to one response I read).
3) In every Query put a Killit function. For example if I have a query
SELECT zippity, doo, dah FROM song WHERE Location = ""South"";
then I might make it SELECT zippity, doo, dah FROM song WHERE Location =
""South"" AND Killit() = true. Function Killit is as simple as:
Function killit% ()
If syscmd(SYSCMD_RUNTIME) <> True Then application.Quit
Killit = true End Function
Now the user can't get to your queries since if they use the retail version
any attempt to run the queries causes the database to be closed. Any
attempt to design the queries is also met with failure."
_______________________________________________________________________________
Is there a SIMPLE explanation of Security? "How about a fun one instead?
Analogies have this habit of breaking down, but let's try one.
Imagine Access as a huge plain full of refrigerators and chairs and 57
Studebakers -- these are the Access objects. Every one of these objects has
a bunch of locks on pieces of it; the Studebaker has a lock on the door, and
one on the steering wheel, and one on the radio, and more. These are the
permissions of the objects, and you can't do anything with them unless you
can unlock the lock.
You and I are wandering around this enormous plain. We each have a key.
As we walk around and bump into things, we find that our key (user identity)
opens some of the locks, and we can do things, like get a ham sandwich outof
the refrigerator and drive the Studebaker down the block to buy beer.
We also each have a curious machine that manufactures new locks. But it will
only make new locks from the little key. I can sit down and make a new lock
from my little key, and use it to lock up this huge castle I just built from
Legos (my very OWN Access object). Your little key, unfortunately, opens the
lock too. Humph.
Aha, what's this in my pocket? It's a membership card for the Admins Country
Club. It says ""Admin"" under ""Member Name"", but I figure if I'm holding it,
that's good enough. I stroll down the road to the Country Club (security
menus), show them my card, and they let me in.
Darn, you're still following me. Turns out you've got an Admins membership
card under the same name too. I'm starting to feel a bit schizophrenic, but
what the heck. There are all sorts of things here in the Country Club, not
just a golf course and a weight room, but an elaborate machine for making
other locks, some big keys labeled things like Users and Guests and Admins,
and hubcaps for the Studebaker (which, however, do not enter into our story
again).
Well, while you're looking at all this, I nip down to the membership office
and join under my own name (create a new user). I also pass the Membership
Secretary a twenty and have Admin erased from the membership rolls; after
all, I've got the card right here, so it must be OK for me to do that.
I join you at the lock-making machinery and make some new locks that only
the big Admins key here in the country club fits. Then we wander off, and I
lock up the city of Seattle with the new lock. Hmmm, the key is still in the
Country Club. No problem, I stroll back and get it -- they check my name at
the door, and no problem, there I am. I open the City, get myself a decent
cup of coffee, and go sit by the Studebaker to drink it.
Now you want to do the same. But you don't have the big key either. So you
go back to the Country Club -- but what's this?! You have a membership card
for Admin, and we threw that low-life right out. No key for you!
Well, fine, you've got another trick up your sleeve. You drag the entire
city of Seattle out of my little universe and into your own. Sure enough,
there's another Country Club there, and your Admin card gets you right in.
You grab the Admins key, get your coffee cup ready, and stroll confidently
down to the lock.
Rats! Your key doesn't fit the lock. Examining it alongside mine, you see
that our Admins keys are different, just as the Admins group in each system
file is different. And you can't get the right key from MY Admins group, so
you can't get in that way either. You're outta luck, and I get to go to
Starbucks without you.
I dunno if that really helped or not, but I had fun writing it.
Mike Gunderloy, Pyramid Computers
PS -- don't get upset. I'll bring you back a cup of coffee if you want."
_______________________________________________________________________________
Why is it that after securing my application following the manual, anyone
with another copy of Access can still get in?
There is a Microsoft white paper on security, SECURE.ZIP, in Lib 12 that
explains the security model in detail. Briefly the situation is this:
whoever is logged in when the new database is created is the owner of
the database (called the Creator). The database owner has lasting
permissions that can not be removed in any direct way. But in every
newly installed copy of Access, the default user is Admin of the Admins
group. In addition the default user Admin has the same Security ID
(SID) in every system of Access. So if you created the database as
Admin, no manner how you logged in later, how many new groups you
added, or how much security you added, any user with another copy
of Access would have Creator permissions on your database. To see
how to simply and reliably secure your system, see the next question.
_______________________________________________________________________________
3-10: Databases
_______________________________________________________________________________
How and why can I keep my data in one database and my code in another database?
"Professional Access developers often split their designs into two databases,
with all of the data residing in one and all other objects (forms, queries,
reports, macros, modules) in the other. This allows easy updating by simply
replacing the ""code"" database while leaving the ""data"" database intact.
There are several examples of this in the Forum libraries: NWLOCL.ZIP in
Lib 6, ATTINI.ZIP in Lib 9 (rewritten for Access 2.0), ATTMNG. in Lib 9,
ATTACH.ZIP in Lib 15 and MULTDB.ZIP in Lib 2 will get you started.
You'll also find another viewpoint in the November 1993 issue of Smart
Access journal. Paul Litwin (editor) wrote a more complete application
to handle attachments and supplied it with the issue on disk."
_______________________________________________________________________________
How can I attach a custom bitmap to my toolbar buttons?
You can't. Access 2.0 is limited to using the toolbar face bitmaps that are
built into the Customize dialog.
_______________________________________________________________________________
How do I get a list of all the objects in my database?
Access ships with a tool called the Database Documentor, which will collect
information about all of your Access objects into a series of tables and
produce reports based on that information. To run the Documentor, choose
File|Add-Ins|Database Documentor from the menus after loading your Access
database. If you need more detail than Analyzer will provide, check into
Total Access, from FMS, Inc., 8027 Leesburg Pike #410, Vienna, VA 22182,
(703) 356-4700.
Another way is to set options!view system objects to yes. Then there
will be a table called msysobjects. And in one of the columns in this
is a complete list of all database objects
_______________________________________________________________________________
How do I see the code in the PIM database, or some other example database?
"When you first open an MS Access database Access executes a macro called
""AutoExec"" if it is present. The PIM database is loading some forms and
changing some menu items when it first opens by executing various
statements in its autoexec macro file. In order to bypass running the
autoexec macro when you first open the database you can hold down the
SHIFT key. If this technique is ineffective, you are most likely running
an application under the Access Distribution Toolkit (ADT) runtime,
instead of full retail Access."
_______________________________________________________________________________
Is there a pre-made Access database to do my inventory/sales/order-tracking
/grading/etc.?
"Probably not. There are various sample databases in the MSACCESS Forum
library, but most are intended to demonstrate a technique rather than to
provide a full application. We encourage you to design and build your own
application. If you run into problems, there are plenty of people who will
help you out via CompuServe. You can also contact professional database
developers via the forum if you would rather pay for work than do it
yourself. Another useful reference is MSKB article number Q100139,
""Database Normalization Basics"". Microsoft also sells the Microsoft
Access Solutions Pack, which does have four fully-developed applications
in it, for sales contact management, asset tracking, conference
registration and help-desk automation."
_______________________________________________________________________________
Is there a way to print out the Relationships window from Access 2.0?
Not directly. The best you can do without a third-party documentation
package is to use Alt-PrtSc to put the window on the clipboard, paste
it into something like Paintbrush, and print from Paintbrush.
_______________________________________________________________________________
3-11: ADT
_______________________________________________________________________________
Can i compile my access database and distribute it to non access owners?
No! In order to do this you must get the Access Developers Toolkit. This
provides a run time program MSARN200.EXE, which can be used to use access
databases, and which you are free to distribute to your clients
_______________________________________________________________________________
How do I install the ADT on a network workstation that does not have full
Access installed locally?
See MSKB article #Q114878.
_______________________________________________________________________________
How much memory will my Access application need when I use the ADT to
create a compiled version of it?
"The ADT does not actually ""compile"" Access applications at all. Instead,
it provides a separate executable (MSARN200.EXE) which is the ""runtime""
Access environment. Your .MDB file is not changed in any way when you use
the ADT to distribute it. The only difference between MSARN200.EXE and
MSACCESS.EXE is that all of the design tools and views are hidden in the
runtime version. It takes the same amount of memory and resources to run
an application under the runtime as under full Access."
_______________________________________________________________________________
"If the workgroup administrator requires SHARE.EXE or VSHARE.386, do I need
to ensure that one of these files is loaded prior to running my custom setup
program?"
"No, but only if you suppress the activity of the workgroup administratorby
including a workgroup database named SYSTEM.MDA in the distribution set.
When the workgroup administrator is invoked by SETUP.EXE, it searches the
application directory for a file named SYSTEM.MDA. If it does not find a
file with this name, it attempts to create one. Alternatively, if it finds
a file with this name (as it will if one has been included in the
distribution set),it simply returns control of the operating environment
to SETUP.EXE. At thispoint, SETUP.EXE invokes the after-setup executable
(if applicable) and aRestart Windows dialog box."What is the licensing
policy for the Data Outline custom control?
"*** Effective Immediately ***
Due to overwhelming customer demand the licensing agreement of the Data
Outline Control that ships in the Microsoft Access Developer's Toolkit has
been changed to unlimited, royalty-free distribution with run-time Access
applications.
This will be reflected in an insert into any new boxes shipped. The insert
(updated licensing agreement) will be made available to this forum when it
is written."
_______________________________________________________________________________
"Why do I get Reserved Error -1809 at the end of a custom setup program
created with the ADT setup wizard?"
"This error occurs because neither SHARE.EXE nor
VSHARE.386 were loaded prior to running SETUP.EXE. After copying files
in the distribution set to their specified locations, SETUP.EXE instructs
the workgroup administrator(WKGADM.EXE) to create a workgroup database
(SYSTEM.MDA). The workgroup administrator cannot create a workgroup
database unless SHARE.EXE or VSHARE.386 is loaded."
_______________________________________________________________________________
3-12: Performance
_______________________________________________________________________________
"Access 2.0 now takes much longer to initially load, and my Access
application forms performance under 2.0 now appears 'sluggish'. What has
changed, and what can I do initially to improve my Access 2.0 performance?"
"The new functionally-richer Access 2.0 software has a larger systems
footprint, requiring a larger Windows extended memory execution space,
as compared with a smaller Access 1.1 systems footprint, which required
asmaller Windows extended memory execution space. If, after upgrading
to Access 2.0, you have not yet made any changes to your Windows memory
parameters, you could experience a general slowdown in Access 2.0
performance, as compared with Access 1.1 performance.
To remedy this, for a 386/486 class machine with 8 MB of installed
physical RAM and Microsoft Windows 3.1, you should override the default
SMARTDRIVE WinCacheSize setting, to reduce the amount of extended real
memory initially allocated to the Windows SMARTDRIVE disk cache.
To accomplish this, in your MS DOS AUTOEXEC.BAT startup file,
modify your SMARTdrive Command line, as follows:
C:\WINDOWS\SMARTDRV.EXE 2048 256 (your actual directory may differ)
The SMARTDRIVE settings above will allocate 2048 KB of memory
(InitCacheSize) to SMARTDRIVE for MS-DOS applications executing in
native MS-DOS, and will allocate just 256 KB of memory (WinCacheSize)
to SMARTDRIVE for Windows applications executing in Windows 3.1.
This will enable Access 2.0 to now execute in a larger extended
real memory execution space."
_______________________________________________________________________________
How can I make my combo and list boxes faster? "
- Include only fields from the record source that are absolutely
necessary. Extra fields can decrease combo or list box performance.
- Index the first field that is displayed in the combo or list box.
- Index any other fields used for criteria (such as when a combo or list
box is based on a criteria query).
- In combo boxes, set the AutoExpand property to No if it is not needed.
- Create a default value for combo boxes. Combo boxes try to match
whatever is entered in them, so if there is no default value for the
combo box, the combo box tries to match a null value when it is first
opened. An example of a default value for a combo box is:
=[combobox].ItemData(0)
In the example above, [combobox] is the name of the combo box.
- Use unbound subforms to display data when there is a large number of
records."
_______________________________________________________________________________
"I expected to get major speed improvements and I have not seen in big
increases in performance. What is the Rushmore I hear about and why is it not
improving my performance?"
Rushmore is Microsoft's name for the technology they use to take advantages
of multiple indexes when processing a query. In 1.1 the query engine (Jet)
would pick the best index and use that. Only one index would be used. In
2.0 with Rushmore as many indexes as are available (within reason) will
be used to process the query. This can have dramatic gains. Even if your
data still resides in 1.0/1.1 format Rushmore can use it. If you have
data in the old format (most likely because 1.1 apps still need access
to it) all you need to do is go in and create an index on likely columns.
I regularly get 2-3 X speed improvements. If you have a join and you can,
put all criteria on the many side. This allows Rushmore to kick in.
Otherwise, it might do the join first and not be able to take advantage
of the indexes on the many side. The best thing to index is single field
columns. If you know that several fields are used in frequent joins that
you do then add a multi-field index.
_______________________________________________________________________________
"I want to get all the performance gains that I hear about in 2.0 but my data
is used by users that will not be upgrading for awhile. Can I still use 2.0?"
If you have your data separate from your app then yes, you can. Just have
2.0 point to the same system.mda that the 1.0/1.1 apps point to. Although
2.0 has a new format system.mda you do not have to use it. It will continue
to work properly with the old system.mda. Also, 2.0 will take advantage of
its Rushmore technology with 1.1 MDBs as long as you add indexes to your
data.
_______________________________________________________________________________
"The more users connected to the databases the longer it takes to ""open""
the databases. This can sometimes be as long as 30-60 secs for each
database. Is there any way to speed up the opening of the databases?"
"Yes there is. I had exactly the same problem and it took a while to figure
out what was happening. Originally when I rolled out my app to 100 users
things worked great. The database itself at that time was not secured.
Latter, we decided to implement the security features of Access 1.1
using the security ""wizard"". After that we got the same result as
you, VERY slow connect times.
I spent weeks trying to track this down. I finally added code to the app
to time each of the various aspects of database activity and found that
the delay was actually in the connection to system.mda not the database
itself. We then put a lan analyzer on a node and watched to see what
was going on.
Apparently, when you connect to system.mda for security verification,
Accessneeds to establish a lock in the system.ldb file. This is where
it gets scaryand I think a bit stupid, to find a place to set up
this lock it SEQUENTIALLYreads the system.ldb file to find the next
available spot. The mechanism foreach of the sequential reads also
seemed a bit strange, each one took 100-300 milliseconds! I can't
remember the actual type of Novell transaction that was generated
but, again, it wasn't what I would have expected. Anyway, the bottom
line is the first few users would get quick attachment, and subsequent
users would take longer and longer. If one of the early connected
users exited the app the next attach would find that open spot and
use it, creating a quick connect for that user. I was able to verify
this as my metering code in the app logged the userid as well as the
timings for each step, including when a user logged off. I could
see that if the, say, the fifth user logged off, the next user to
log in would get exactly the same response time as the fifth user
had earlier in the day.
I relayed all of this to Microsoft, and Ryan was very helpful in
verifying my conjecture with the Access development team.
(Who by the way acknowledge that this scheme needs to be looked
at in a future Access release.)
Now the solution, and it's very simple. Put a copy of the
system.mda file on each users machine and point Access to that.
This way there will be nolocking contention on the system.ldb file
and everyone will get quickconnects. What I did in my code (VB by
the way) was to look at the date and time stamp of the MASTER copy
of the system.mda file on the network in the same directory as my
actual database. If that date and time was different than the one
associated with the local copy I'd copy the master system.mda file
to the local PC and then go on with connecting and using the database.
Notice that I said the date and time ASSOCIATED with the local copy.
You can't look at the actual date and time stamp of the local file as
it is updated every time you use it. So what I did was to create a
local text file with the date and time stamp of the MASTER system.mda
file when I did the copy to the local machine and I'd use that to
do my comparison."
_______________________________________________________________________________
Why is my Access application running slower now that I've converted it from
1.1 to 2.0?
"The most likely cause of this is memory. Access 2.0 requires more memory
than 1.1 (all the new ease of use features paid a price and that is
greaterrequirement for RAM). So, a configuration that was fine in
1.1 does not work in 2.0. The official minimum is considered 6 Meg
with 8 Meg suggested. The 6 Meg is like 4 Meg was in 1.1. If you try
running in 6 Meg you need to follow all the suggestions for a 4 Meg
1.1 configuration. You might be able to run in 4 Meg with 2.0 but it
is not suggested. If you have at 8 Meg and are stillfinding that 2.0
is much slower than 1.1 then you need to look at yourconfiguration.
Do the following to get good performance from 8 Meg.
First thing is disk caching. If SmartDrv is being used make sure
that it is set for no higher than 512 K. You might even want to
try 128 K.Access makes much better use of your RAM itself. If you
are running WFWG 3.11 then make sure that your COMBINED disk
caching is no more than 512 K. In setting up for WFWG 3.11 they
suggest using SmartDrv for caching floppies and CD ROM drives. I
personally do not worry about caching these and do totally without
SmartDrv in low (8 Meg) memory situations. SmartDrv allows setting
the cache size for Windows as well as specifying the size if you are
in DOS. The WFWG 3.11 disk cache size is set the same place that 32
bit drive access is set: Control Panel;386 Enhanced Icon; Virtual Memory.
Next, if you have a RAM drive get rid of it. Disk caching is
worthwhile in an 8 Meg system but putting memory into a RAM drive
is no longer a good thing to do.
Once you have looked at how your memory is used the next thing
to do is tocheck out your hard disk. Is the hard disk compressed.
If so you want to make sure that your swap file is on an
uncompressed portion of it.
The next good thing to do is to go into all your objects, opening
them indesign mode and saving them. This is especially important
for queries. Somepeople have gotten significant speed ups by doing
this.
Finally, set up your data and queries to be used efficiently by 2.0.
That iscovered in the next question."
_______________________________________________________________________________
How Can I Optimize Query Performance?
Updated Tuesday, November 07, 1995
Microsoft Access version 2.0 uses Rushmore, a data-access technology
that permits sets of records to be queried efficiently. Here is a
summary of query performance tips:
- Index the fields used in sorts and criteria.
- Index the fields used in joins in both tables.
- Use multiple-field indexes on fields where there are multiple-column
joins between the tables.
- If a table has a single-field primary key, do not add a separate
index to the primary key field. If a table has a multiple-field
primary key, it may help to have a separate index on each field.
- Use outer joins only when necessary. Outer joins limit the options
available to the query optimizer.
For more information about query performance, search for "Rushmore
technology" then "Optimizing Queries with Rushmore Technology"
using the Microsoft Access Help menu.
Related Knowledge Base Articles
For more information about this topic and other related issues, see the
Microsoft Knowledge Base. It contains thousands of articles that include
answers to common questions about using Microsoft products. See the
following Knowledge Base articles:
INF: How to Optimize Queries in MS Access Versions 2.0 and 7.0
http://www.microsoft.com/kb/deskapps/access/Q112112.htm
_______________________________________________________________________________
3-13: Access 95/7
_______________________________________________________________________________
So whats new in Access 95?
General:
Requires Windows 95 or Windows NT.
Improved 32-bit performance (given 12MB+ RAM).
GDI limits are no longer a problem.
Replication: databases can be used by separate users simultaneously,
and records synchronized later.
Start-up options: Can specify auto-open form, whether to show toolbars
and database window.
Custom Toolbars are easier.
Database Wizard creates tables, queries, and forms for several
common databases.
Database Window can show details of objects (description, create
date, modification date)
Record locking: still performed by 2K page, but multiple users
can add new records at once.
"Password" input mask displays each character as a star.
Filters and Sorting:
For Tables, Queries, Forms, and Reports, the last filter and sort-order are
saved as Filter and OrderBy properties.
Reuse the saved filter with the ApplyFilter icon, or the FilterOn
(boolean) property.
Filter by Selection: Limit recordset to those matching currently
selected field.
Filter by Form: Simple method to specify wildcards or multiple
And/Or criteria.
Tables:
Counter replaced by AutoNumber - can be Long Integer or ReplicationID,
sequential or random.
Fields have a property to define the default control type, used
in datasheets, new forms.
Lookup Wizard builds lookup properties for foreign key fields.
Forms:
Open multiple instances of a form.
Add picture backgrounds to forms and reports.
Set AllowEdits, AllowAdditions, AllowDeletions properties (boolean).
Read/write filter properties (Filter, FilterOn, OrderBy, OrderOn).
Trap filter events: Filter and ApplyFilter.
In Datasheet or continuous view, formatting is correct, and the
lines dividing records can be removed.
Form Design: Palette replaced with toolbar icons (incl. Format
Painter). Multiple controls can be spaced evenly, sized to
widest etc.
Utility to convert a form's macros to VBA code.
Controls:
ToolTipText for all controls.
Faster Combo boxes (handles thousands of records).
Morph a control into another type, e.g. text box into combo.
Spell checking built in, including AutoCorrect.
Look-up between keystrokes with the Change event.
Memo fields automatically get a vertical scroll bar and
EnterKeyBehavior setting.
Event Procedures are still orphaned when you rename a control.
Reports:
Trap reports with no data: NoData event.
Faster previews: events are triggered once only.
Filter report to match the current filter in a form, using
Filter and FilterOn properties.
Print icon (toolbar) prints immediately without confirmation/
cancellation question.
Visual Basic (VBA):
Colour-coded text, multiple undo, improved debugging facilities.
Conditional Compilation is supported.
OLE operations are vastly extended.
Scoping: Can call functions stored in other CBF (Form or
Report) modules.
New data types: Boolean, Byte, Date.
User-defined types can contain user-defined types, and can
be returned from functions.
Functions can take optional arguments with ParamArray.
Can use a function without assigning it. (No dummy variable
needed.)
Constants are different, e.g. acDataErrAdded instead of
DATA_ERRADDED.
Error handling: use Err.Number, Err.Description,
Err.Clear, Err.Raise.
Nz() function converts Null to whatever you like (e.g.
zero, blank string, ...).
(Allen Browne)
_______________________________________________________________________________
Is There much that i should be aware of in deciding whether to convert to
Access 95 or not?
Crucial Issues:
1.An Access 7 database cannot be saved as a previous version
(same as Ver 2 => Ver 1). Only tables can be exported from 7 to
previous versions.
2.Immediately after converting, open any module, and choose "Run" |
"Compile All Modules". Solve any conversion problems and then choose
"File" | "Save Modules". Using the database without solving these
issues is inviting disaster.
3.Any 16-bit API calls need replacing, including data types for DDE
channels, hWnd, etc. The file "accport.exe" (see link on previous
page) includes generic conversion information, or use the specific
declarations from "win32api.txt" distributed with Visual Basic 4.0.
4.Parent property: if you used this property for attached labels or
buttons in an option group, change your code: the Parent is no
longer the form.
5.Module Names: Access 1 and 2 permitted a procedure with the same name
as a module. Access 95 doesn't cope with this, presumably because
the module name can be specified as part of the function name
(enabling you to call functions in code-behind-form modules). So
if a converted database complains about an invalid call to one of
your functions, you may need to rename the module or the function.
Optional Issues:
1.SendKeys: To set dialog box options, use SetOption instead of SendKeys.
To drop combos down, use the new DropDown method instead of
Sendkeys "{F4}". Check any references to menu items.
2.OwnerAccessOption: This option is no longer the default for queries.
3.Jet 3 DAO requires some changes to your style. For example, MS
recommends using CurrentDB() instead of DBEngine(0)(0).
4.Data Tyes: You may wish to modify code to take advantage of the Date
type (instead of Variant), or Boolean (instead of Integer).
5.Constants: Access 95 uses a new style of constant. The Object Browser
(toolbar when a module is active) lists these.
Problem Issues:
The worst problems are those that yield no error message, but return results
different from the previous version.
1.Access Basic considered a space and an empty string equivalent
(i.e. ? "" = " " returned True). The same expression in VBA
returns False. Watch this in any code that parses strings.
2.International dates: VBA ignores the international date settings
in Control Panel, if it can get away with it. For example, with
a British date setting (dd/mm/yy), VBA treats #10/12/95# and
#14/12/95# as 12-Oct-95 and 14-Dec-95! Access Basic always
respected the Control Panel setting, and did not have this
inconsistency.
If you have experienced other conversion problems, please e-mail
abr...@odyssey.apana.org.au with the subject "gotcha27.html".
_______________________________________________________________________________
Q: How do I retrieve a User's ID from Windows 95?
A:Unlike Windows 3.x, 32 bit Windows platforms have one API that fits
all for User IDs, called GetUserNameA. The following Function and API
will retrieve the ID in an Access or VB application:
Declare Function GetUserName Lib "advapi32" Alias "GetUserNameA" _
(ByVal buffer As String, buffersize As Long) As Long
Function GetUser() As String
Dim UserName As String * 255
Dim NameSize As Long
Dim errno As Long
NameSize = Len(UserName)
errno = GetUserName(UserName, NameSize)
GetUser = Left(UserName, NameSize - 1)
End Function
_______________________________________________________________________________
4: Thanks to....
John W Locke & all the people who helped
him put together his Trials and Tribulations
All The guys at compuserve who put together their
Access Faq
All The contributors to the Access-l mailing list,
which remains a truly invaluable source of information
Allen Browne, for the Access 95 stuff (great site at
http://odyssey.apana.org.au/~abrowne/) -
The Trevor Best Collective Entity
"You will be assimilated"
http://easyweb.easynet.co.uk/~trevor/AccFAQ/
Peter Vukovic <10070...@compuserve.com>
willi...@cix.compulink.co.uk (John Williams)
yat...@csee.usf.edu (Randy Yates)
"Donald Neufeld" <c...@common.net>
Barry Westwood <usb0...@interramp.com>
"Gary Tatum" <Ta...@sago-admin.tamu.edu>
Trisha Phoon <csp...@pc.jaring.my>
"Hirons, Nicola " <nicola...@grg.co.uk>
_______________________________________________________________________________
5: Disclaimer...
[Insert Token Disclaimer Here]
"I Didn't Do it!
Nobody saw me do it!
You can't prove Anything"
-Bart
_______________________________________________________________________________
Clyde Sisler
>date) and then asked for some hardy volunteers, to help proofread the
>FAQ. Now i got lots of ppl saying "Yeh sure send me a copy - I'll read
>through it", and so I did.
>
>But it was the old story of lots of people wanting something for
>nothing, and very few ppl bothered to spend the few minutes to write a
I have a bunch of stuff I've collected over the past year or so but
haven't had the time yet to create an index. You could possibly add it to
your faq but proper credit couldn't be given to the authors as I didn't
save most of that info.
Send an email if you want to add it.
Trevor Best
Yikes, how many is there?
\|||/
/ \
C o o D
-----------------ooO--u--Ooo-------------------------------
MS Access FAQ now available on my site below.
Trevor Best - tre...@easynet.co.uk
http://easyweb.easynet.co.uk/~trevor/
Michael O Shea
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote:
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote:
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote:
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote:
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote:
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote:
Michael O Shea
mos...@iol.ie (Michael O Shea) wrote: