|
|
I am looking for a way to bypass the
Outlook security prompt that pops up. It is very annonying, and it
makes it impossible for me to automate Outlook with VBA. Is there a
workaround for this issue? |
|
There are a couple of workarounds. One popular
approach is to use Express ClickYes (http://www.contextmagic.com/express-clickyes/). |
|
|
I have a table that is exceeding 2 GB.
Can you recommend a solution that would allow me to continue using Access? |
|
Try creating three databases from your current
single database. Say your application contains 18 tables.
Place 9 in one database, and the other 9 tables in a second database.
Create a third database with links to the 9 tables in each database.
Place your forms and reports in this third database. |
|
|
I
am trying to get a 'Caption' to read:
GRAND
TOTAL
Instead of GRAND TOTAL
Any ideas on how to make this work? |
|
Enter
Ctrl+Enter after Grand. Heighten label to show both lines. |
|
|
Still
looking for information on how to print multiple reports in a batch
command. I have a seven page report that I need to print all at once,
but can only fit one page per report. Any tips for me? |
|
One
easy way to go about doing this is with macros. That way, you do not
need to know VBA syntax for setting up the task. However, it is not hard
to print a report with VBA. Lookup the OpenReport method for the DoCmd
object. My book, Programming
Microsoft Access Version 2002, includes numerous samples for this
method. |
|
|
I
am looking for a way to search the contents of other documents for user
specified keywords and return the files found within my access database.
Is there any way to do this? |
|
Yes.
Use the FileSearch object. This is a Microsoft Office Object that
is available to Access as well as other Office XP applications.
The FileSearch object was introduced with Access 2000. The
TextOrProperty property for a FileSearch object instance lets you
specify a text string in the documents within the scope for a
search.
My book, Programming Microsoft Access Version 2002, includes numerous
code samples illustrating the use of this object. See pages
556-577. |
|
|
How
should developers upgrade Access 2 and Access 97 solutions to Access
2000 and Access 2002? |
|
Several
practical guidelines can help upgraded efforts. First, upgrade one
version at a time instead of multiple versions in one step. A
second guideline is not to upgrade, but rather run new and old
applications side-by-side as long as possible. A third general bit
of advice is to get the data first, the queries next, and the interface
last. The theory on this last tip is that you can re-build the
queries and interface if you must, but the data is very expensive to
replace. Happily, getting data from older to newer versions of Access is
pretty straightforward.
There is no silver bullet that you can fire, hit the bull's eye, and
make everything alright with an upgrade effort. Upgrading is a
dirty business that requires a deep understanding of multiple versions
of Access as well as the business issues that the legacy Access
applications address. Because upgrade efforts require scarce
technical as well as business resources, they are not likely to be
resolved unless corporations are ready to spend heavily on the upgrade
projects. Since the business issues are the most important ones,
it is important that business persons act as key resources in upgrade
projects.
Upgrade efforts requires lot of hands-on resources from dedicated,
experienced database professionals as well. For this reason, it is a
good candidate for attack by the resources of the Database
Developers Group, which is an association of Access and SQL Server
developers that are geographically dispersed and many so they can
address individual client concerns on an as-needed basis. |
|
|
How
do I query the catalog for an Access database? |
|
Use
ADOX. The ADOX object model has a catalog object model that you can use
for querying. Recall that you need to set a reference to the ADOX
library before you can reference its object model. Both Programming
Microsoft Access 2000 and Programming
Microsoft Access Version 2002 include numerous code samples
demonstrating the use of the ADOX object model. |
|
|
How
do you create a reference to a library? |
|
Failing
to set a reference to the library file for an object model can cause a
syntactically correct program to fail. It is easy to reference the
library file for an object model. You can open the References
dialog box with Tools, References command. Then, scroll through
the dialog until you find the library name and select its check
box. Click OK to confirm your new reference. |
|
|
I
was in your Access 2002 seminar in Chicago this year. You mentioned that
DAO could be installed to allow code that uses it to still function. How
do you install just that piece? |
|
The
DAO library installs along with Office XP, but it is not selected as a
Reference by default. To select a reference to the DAO 3.6 object
library for any project, open any module in the project in Design view.
Then, choose Tools, References. Scroll down the available references
until you find Microsoft DAO 3.6 Object Library. Select it. Use the
Priority arrows to adjust the position of the reference among your list
of references. For example, if you may prefer to place the DAO reference
before any reference to an ADO library. |
|
|
I am looking for a form(s) and code that would ..... let me refer
to the model I am attempting to replicate. In accounting software like
Money or Quickbooks, you can take a transaction like a check and split
the expense to more than one expense account. You write one check to
the gas station for gas and food - $30. When recording the check in
Money you can split the expense to two or more expense accounts, $20
dollars to gas expense and $10 dollars to food. The check record
retains the original amount yet reports of expenses splits the amount
of the check to the two different expense catagories. I am frustrated.
The more I work on this the more I realize the complexity....I see
this functionality in just about every accounting app I know of
but I have yet to successfully replicate.
|
|
I read your problem, it sounds like you are describing a parent-child
relationship between checks and check charges. If this is so, then you
can handle it with two tables -- one for checks and a second for check
charges. The checks table may have two columns (checkid,
checkamount). The check charges table may have three columns (checkid,
chargeaccountid, chargeaccountamount). To make this concrete,
let's consider you $30 check to the gas station. It appears in
the checks table with a checkid of 1 and checkamount of $30. In
addition, you also need to enter two rows in the check charges tables.
The first row has a checkid of 1, a chargeaccountid for gas, and a
chargeaccountamount of $20. The second row also has a checkid of
1, but its chargeaccountid is for food, and its chargeaccountamount is
for $10.
In the Relationship window, relate the check charges table back to the
checks table. After you build your tables and specify their
relation, you can create a form with just one click as we discussed
during the seminar. Select the checks table in the Database
window. Then, click the AutoForm tool.
|
|
|
Is
it possible to have a field which gives the total number of other
defined fields have data entered in them. For example a field giving the
total number of items when there are four fields for individual items
which do not necessarily have data entered? |
|
Yes.
Any form is an instance of the Form class. All form instances have a
Controls collection. You can enumerate the members of the Controls
collection and count them, classify them by type of control, and assess
whether the controls contain data. You can even assign data. See my
latest book, Programming
Microsoft Access Version 2002, for several code samples that
illustrate the general guidelines for programming controls on forms. In
particular, look at samples on pages 343-354 and 374-376. Although the
book explicitly references Access 2002, the code samples apply to
earlier versions of Access as well. |
|
|
I'm
trying to connect to a relational database, such as ORACLE via ACCESS
2000. Can you tell me the code that allows me to do this? |
|
That
information is covered in the Programmatic Querying of Remote Databases
section of my Programming
Microsoft Access 2000 book. The specific reference is page 240-242. |
|
|
If
I switch to Access 2002 will I be able to share my database files with
others in the office using Access 2000? |
|
Access
2000 users can read Access 2002 files, but Access 2000 users cannot take
advantage of the PivotTable and PivotChart views. As an Access
2002 user, you can explicitly set the file format for your databases to
Access 2000 or Access 2002. In Access 2002, choose Tools,
Options. Then, select the Advanced tab, which contains a Default
File Format section. |
|
|
I
like the code samples, but I wish that I could have more like them. |
|
One
solution to this issue is to buy one of the books by the webmaster for
this site. A book page
includes links with more information about each of the books by Rick
Dobson. These books cover a wide range of topics and experience
levels so that you will likely find one or more that appeals to
you -- especially if you want more samples like the ones at this site. |
|
|
I have a book
on Access programming by another author. It appears that the
following sample -- copied verbatim from the book -- does not
work. Since I copied it right out of the book, I'm frustrated in trying to figure it out can you help?
Public Sub CreateTable()
Dim Table As New Table
Dim Catalog As New ADOX.Catalog
Dim Key As New ADOX.Key
Catalog.ActiveConnection = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\CONTACTS.mdb"
Table.Name = "CONTACTS"
Table.ParentCatalog = Catalog
Table.Columns.Append "ID", adInteger
Table.Columns("ID").Properties("AutoIncrement") = True
Table.Columns.Append "FIRST_NAME", adVarChar, 20
Table.Columns.Append "LAST_NAME", adVarChar, 20
Table.Columns.Append "PHONE_NUMBER", adVarChar, 36
Table.Columns.Append "EMAIL", adVarChar, 50
Table.Columns.Append "WWW", adVarChar, 50
Catalog.Tables.Append Table
Key.Name = "ID"
Key.Type = adKeyPrimary
Key.Columns.Append "ID"
Catalog.Tables("CONTACTS").Keys.Append Key,adKeyPrimary
Set Catalog.ActiveConnection = Nothing
End Sub
|
|
There
are at least three errors with the code sample. First, the table
declaration is wrong. Second, the the text data type assignments
are incorrect. Third, the syntax for appending the primary key is
erroneous.
I devote Chapter 4 of Programming
Microsoft Access Version 2002 to designing tables
programmatically. While the code samples in the chapter explicitly
reference Access 2002, they will also work with Access 2000. The
chapter covers all three of three of these points and other valuable
issues relating to the programmatic design of tables with the ADOX
library. For example, pages 205-208 cover Column properties.
Reading this section will clarify that the data type specification
should be adVarWChar -- not adVarChar as the other author indicated. |
|
|
I
am just starting to do Access programming, but I do not understand
whether to learn DAO or ADO. In addition, I wonder about using SQL
Server with Access. Can you help me? |
|
The
replacement for DAO in Access is ADO. In addition, I highly
recommend Jet SQL for working with ADO and Jet Databases with
Access. Starting with Office 2000, Microsoft released MSDE, which
lets Access developers gain experience with SQL Server development
without any extra costs. There are two different versions of MSDE
one ships with Office 2000 and a second ships with Officexp.
The version of MSDE shipping with Office 2000 corresponds with SQL
Server 7, and the the one shipping with Officexp is
like SQL Server 2000.
If you are just starting out as a developer, and you want a book to
get started with advanced topics such as ADO, I recommend Programming
Microsoft Access 2000. If you are prefer to start with more advanced
coverage and you will be using Access 2002 that ships with Office XP, I
recommend Programming Microsoft
Access Version 2002. |
|
|
If
"Select Top 100 * from table order by field" will return top
100 records, how do I query to get second page, i.e. records from 101
till 200? |
|
The
code for paging through the result set from any SELECT statement
(whether or not it includes a TOP predicate) appears on pages 501-503 of
Professional SQL Server
Development with Access 2000.
BTW, if you are using FrontPage, you can use its Database Wizard to
accomplish the task without any code at all. See pages 450-464 of Professional
SQL Server Development with Access 2000 for a collection of examples
that demonstrate the ability of the FrontPage Database Wizard to create
code for generating pages in several typical scenarios. |
|
|
I'm
trying to modify a DATE field in MS Access with the following SQL
statement: UPDATE Companies SET Date = #02/03/2001# WHERE ID = 89.
How do I make it work? |
|
Both
Date and ID are reserved words place them in brackets. For example:
UPDATE Table1 SET [Date] = #09/09/1944# WHERE [ID]=3 |
|
|
How
do I use parameters with an ADO Command object? Can you refer me
to source with an example and some commentary? |
|
Recall
that parameters are important because they let you create solutions that
users can change at run-time. ADO Command objects enable you to
perform select as well as action queries. By using parameters with
ADO commands, you enable solutions to determine the result set from a
select query. In addition, you permit users to dynamically
determine the behavior of action queries.
There are four steps to using parameters with ADO commands.
First, instantiate the command. Then, instantiate one or more
parameters for the commands. Third, assign values to the
parameters. Fourth, execute the command. The sample solution
on pages 104-106 of Programming
Microsoft Access 2000 demonstrates how to implement these steps in a
practical example. |
|
|
I
have a database application that I am thinking of converting to Access
from Dbase. It contains tables that range in size from 23,000
records or over 900,000. Is Access able to handle a database of
this size? What if our tables grow? Will it be able to
handle it then? |
|
Access supports two database engines: Jet and the SQL Server.
Jet is appropriate for small to medium-sized projects, such as the
ones you describe. SQL Server targets medium-sized to very large
database applications. Jet is free with Access. SQL
Server delivers its best performance when you buy it as a
stand-alone package, and then use Access to develop solutions for it. There
is a version of SQL Server that ships with Microsoft Office for free,
but this is not a high-performance version. SQL Server is more
complicated to develop for, but it can handle larger databases
and more users than can Jet.
There are three ways to make databases go fast. First, choose a
database engine appropriate for the amount of data you are
referencing. Your data is on the borderline between Jet and SQL
Server. If you anticipate your databases to grow in size
substantially over the next year or two, then you should definitely
consider using SQL Server. Second, buy new, faster hardware.
For example, if you are using a single processor on your database
server get a multi-processor or a faster processor. This has two
costs associated with it. The cost for the hardware, and the
cost for migrating your database solution. If you want to stay
with Access, then get the fastest computer with a single processor
that your budget permits. If you want to go with SQL Server,
consider going to a multi-processor server. Third, you can
increase the performance of your database by tuning your database
design and improving the design of your application. This
includes such steps as adding appropriate indexes, redesigning tables,
and rethinking how you access the data in tables. You should
consider hiring an outside consultant for the later activity to get a
fresh perspective on design issues if you pursue this option.
|
|
|
I
am interested in all the free developer training that I can find about
Microsoft Access 2000. Can you help? |
|
My
site includes hundreds of pages all of which are available for free.
There are many code samples and FAQs to help you ramp up to speed on
Access 2000 development techniques. In addition, there are presentations
and book excerpts right at the site as well as links to published
articles that are available without charge on the web. Beyond these
resources, you will find at the site links to Microsoft.com resource
pages that offer more help with Access 2000 development techniques.
If you find that you want or need more structured assistance,
consider either of my books. Many site visitors report them a valuable
training aid. Learn more about either book at www.programmingmsaccess.com/thebook/.
Finally, consider attending one of seminar
tours on Access and SQL Server development. They are not free,
but they are priced very competitively. When you seek quality
training, from an experienced professional, in a relaxed and comfortable
learning environment, these seminars offer an excellent value. |
|
|
How
do I make a report's content dynamic from an Access form? |
|
There
are at least a couple of approaches to this topic.
First, you can change the SQL string that specifies the RecordSource
property for the report. You can also use form field values to
update the Caption property of label controls on a report. With
dynamic labels, you can modify column headers in a Page Header section
or a report's title in the Report Header section. Pages 297-299 of
Programming Microsoft Access 2000
includes a sample that illustrates how to make a report dynamic with
this approach.
Second, you can dynamically set the InputParameters property for a
report. This property lets you specify input parameters for query
statements or database objects that serve as a report's RecordSource
property. Professional SQL
Server Development with Access 2000 on pages 425-426 presents a
sample that shows how to the set a report's InputParameters property
from an Access form. The example targets SQL Server, but the
general technique works for both Jet and SQL Server databases. |
|
|
How do I create a form for a data source
with bound images? |
|
Access has an AutoForm Wizard. Simply highlight the
table with the bound images in the Database window, and click the
AutoForm Wizard button on the toolbar. This generates a form bound
to each field in your table. The images need to be in bitmap
format for this to work. This same technique works for Access
database files and SQL Server data sources. |
|
|
Not strictly a recordset question, but here goes:
How can I Save a report on a preview window as a HTML file without altering the
Format. I tried to Export it as a Html file but the Data format is not the same (the values are not Aligned)?
|
|
One
answer to this question is to publish the report as a snapshot.
Snapshot files faithfully reflect the formatting in an Access
report. Did you know that ProgrammingMSAccess.com features a
tutorial on snapshots? |
|
|
How
do I send snapshot reports with Access 97? |
|
To
do this, you need to install Microsoft Access Service Release 1 or
Microsoft Office 97 Service Release 1. If you already have Microsoft
Access 97 or Microsoft Office 97 Professional Edition, you can download
Service Release 1 from Microsoft's World Wide Web site at http://www.microsoft.com/office/downloads.htm.
For more detailed information about report snapshots and Microsoft
snapshot viewer, please refer to our snapshot
tutorial. |
|
|
This
is not really a MOD question, but MOD folks will likely experience the
difficulty. When I create tables programmatically as you describe in
your book, the Database window does not refresh automatically. Is
there an automatic way to show my new tables that I create
programmatically? |
| |
Microsoft
created a method especially for this issue. Go to the Object Browser
and look up the RefreshDatabaseWindow method. This method works for
AccessObject objects (this is not a typo), but not SQL Server objects. |
