Monday, January 31, 2005

Get Your Head Around ADO

Visit MSDN's ADO Programmer's Guide which provides an overview of how to use ADO objects to work with data from various data sources.

Sunday, January 30, 2005

Latest MS Access KB Articles

Courtesy of kbalertz.com:

101084 ACC97: Storing SQL Database Login IDs and Passwords Locally
109829 ACC97: Sample Function to Provide Password for Attached Tables
120911 ACC97: Ability to Add Users Without Permission to View Design
128808 ACC97: Optimizing for Client/Server Performance
135378 ACC97: Connection String Is Accessible in Secured Databases
135379 ACC97: New SQL Records Appear Deleted Until Recordset Reopened
209656 ACC2000: "CREATE TABLE Permission Denied" Error Message in Export to SQL
223220 ACC2000: SQL Server Security Administration Dialog Box Does Not Open
889588 ACC2000: How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients
198915 ACC2000: Insert Data Permission Required to Modify Table Design
209179 ACC2000: SQL Server System Administrator Cannot See System Tables
210278 ACC2000: How to Set AllowZeroLength Property to Yes in All Tables
836178 ACC2003: "Invalid product key" error message when you start an Office 2003 program after you install Office Professional Edition 2003
New Tool Makes Using MS Access a WHOLE lot Easier!

Access is intimidating for new users and the learning curve is steep. I have created a new free tool, Ezy Access 2000, that makes using Access a WHOLE lot easier: an Access database for retail MS Access that lets you easily create Access databases with little or no knowledge of Access:

Friday, January 28, 2005

Use Dynamic Control in MS Excel

Excel 2003 and Visual Studio Tools for Office, Version 2005 Solution: Using Dynamic Controls and Views Use the .NET Framework in Visual Studio 2005 Beta 1 to utilize managed controls that present and navigate data in ways that Excel does not support out-of-the-box.

A Fool-proof Way to Add a Library Reference in MS Access

From my MS Access Tips page at aadconsulting.com:

Those of you who develop Access add-ins or library databases, know that adding a reference to the addin or library database to the user .mdb can be tricky, as the library file may not be where it is expected to be. I use the following function, which is called each time the library file is loaded and uses the Name property of the CodeDb function to return the full path of the library file:

Private Function aad_AddLibraryReference() As Boolean
On Error GoTo AddLibraryReferenceError

Dim aad_refBuilderLibrary As Reference

Set aad_BuilderLibrary = References.AddFromFile(CodeDb.Name)
aad_AddLibraryReference = True
Exit Function

AddLibraryReferenceError:

If Err = 32813 Then 'Reference already exists
Resume Next
Else
MsgBox "Add LibraryDB Reference Error" & Err & ". " & Err.Description
aad_AddLibraryReference = False
Exit Function
End If

End Function


Major flaws in Microsoft Office file encryption

January 28, 2005 A major problem with the way Microsoft's encryption tool handles Word and Excel files has been discovered. This flaw could allow a cracker with basic cryptography skills to decrypt files. Details

Monday, January 24, 2005

EXCEL: Integrate Data into Your Spreadsheets

Integrate Data into Your Excel Spreadsheets with Office Web Services This MSDN Article discusses:
  • How you can integrate data from several Web services using Excel & VBA
  • The features of the Office Web Services Toolkit
  • How you can manipulate your data in Excel and use it to create reports
  • A sample application to get you started
Haloscan commenting and trackback have been added to this blog.

Sunday, January 23, 2005

The Easy Way to Parse Your MS Access SQL String for Insertion into Your Code

My MS Access Add-In and Wizard, Ezy SQL Editor & Builder, easily creates formatted SQL strings for your code. Ezy SQL automatically parses any SQL string, for example, from:

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship] FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"FROM SuppliersORDER BY City, CompanyName;

to:

strSQL = _
"SELECT City, CompanyName, ContactName, 'Customers' AS [Relationship] " & _
"FROM Customers " & _
"UNION " & _
"SELECT City, CompanyName, ContactName, 'Suppliers' " & _
"FROM Suppliers " & _
"ORDER BY City, CompanyName; "






Latest MS Access Knowledge Base Articles

Courtesy of kbalertz.com:

You may receive the "Method 'CurrentDB' Of object '_Application' failed" error message in Access 2000 or a later version (887033)

Slower performance in Access-based or Jet database-based programs after you upgrade from Windows NT 4.0 to Windows 2000 or to Windows XP (891176)

How to use TOP N Query from Microsoft Access 2.0 in VB (147724) - One of the new types of queries added to Microsoft Access version 2.0 for Windows is called a TOP N query. If you have Microsoft Access version 2.0 or higher, and Microsoft Visual Basic for Windows version 4.0 and higher, you can use this type of ...

How to work with the Outlook E-mail Security Update in an Access database (263084) - Describes that you may receive "A program is trying to automatically send e-mail on your behalf" message when you run the SendObject macro action. Or, you may receive a "run-time error '2293'" when calling the SendObject VBA method in Access.

Cannot edit or update a record on an Access form (209571) - You may not be able to edit a record on a form even though the AllowEdits property of the form is set to Yes . When you try to update data on the form, you may receive the following error message: This Recordset is not updateable. If the form is ...

ACC2000: How to Use a Query to Format Data for a Mail Merge (197520) - In a Microsoft Access table, if you've added formatting to the numbers in a Number field, and you send that data to Microsoft Word through a mail merge, the numbers will lose their formatting when they appear in Microsoft Word. In a mail merge, ...

Objects are removed when you convert partial replicas from Access 97 to Access 2000 (263171) - You convert a Microsoft Access 97 partial replica to Access 2000. Then you synchronize the Access 97 partial replica with a converted full replica or with Design Master. All the database objects are removed from the converted partial replica ...

No records returned in query between an Access 2000 table and a linked Access 97 table (244137) - No records are returned for a query that joins a Microsoft Access 2000 (Jet 4.0) table to a linked Microsoft Access 97 (Jet 3.5) table. This happens even though you are certain that there are matching records. This behavior can occur only when all ...

You receive a "Not a Valid Password" error message when you open an Access 97 database in Access 2000 (246033) - When you open a Microsoft Access 97 database in Microsoft Access 2000, and if the database password has 14 or more characters, you may receive the following error message: Not a valid password. To work around this behavior, follow these steps: ...

A newly created Access 97 database is not secure when you convert a security-enhanced Access 2000 database to Access 97 format (198986) - When you convert a security-enhanced Microsoft Access 2000 database to Microsoft Access 97 format, the newly created Access 97 database is not secure. Permissions are reset to their defaults in the newly created Access 97 database. This problem ...
Latest MS Office Stuff from Microsoft:

Smart Tags: Realize the Potential of Office 2003 by Creating Smart Tags in Managed Code Learn how to build and deploy managed smart tags, and how they work with Microsoft Office Smart Tag List XML schema, advanced managed smart tags for Office 2003, and Office XP.

Office 2003 Smart Document Software Development Kit (SDK) This download provides the software development kit (SDK) for creating smart documents with the Microsoft Office System (1/14/2005)

Installing and Using the Office 2003 Primary Interop Assemblies Find out how to get, install, and use the primary interop assemblies (PIAs) available for Office 2003.

Excel: Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services Make the most of the data returned from your Web services with the Office 2003 Web Services Toolkit API and take advantage of the latest capabilities to customize list views, graphs, and charts.

Friday, January 14, 2005

More MS Office Articles

Handy Tips for Working with Excel 2003 Make your applications more robust and provide more options for your users when you employ and modify these tips from the Excel newsgroups.

Working with PowerPoint Presentations from Access Using Automation Learn to use automation to work with Access and PowerPoint. This gives you the ability to work from inside Access to create a slide presentation as well as use an existing PowerPoint presentation inside an Access form.

Is Your MS Access Installation Fully Patched?

A summary of all essential service packs including download links for Access 2 through to Access 2002.

Wednesday, January 12, 2005

New MS Articles and Sample Applications

Office 2003 Sample: Working with PowerPoint 2003 Presentations from Access 2003 Using Automation Download this sample to work with PowerPoint 2003 presentations directly from Access 2003. This sample accompanies the MSDN article "Working with PowerPoint 2003 Presentations from Access 2003 Using Automation," available from Related Resources.

Smart Access: Access Interfaces: Building Explorer-style Forms This article and sample application looks at the surprisingly simple process of implementing an Explorer-style form architecture using the TreeView component that's part of the Windows XP Common Control Library.

Creating an Office 2003 Research Service Using the Amazon Web Service API Search for books at Amazon.com using the Amazon.com research service, then include the results returned in your sheet or document without leaving your Office 2003 application.

Creating a FrontPage Drop-Down Menu with JavaScript If you've ever tried to create a drop-down menu, you probably know it’s not always easy. This article provides the detail you need to start developing your own—as well as JavaScript for three kinds of drop-down menus.

Handy Tips for Working with Excel 2003 Make your applications more robust and provide more options for your users when you use and modify these tips from the Excel newsgroups for your own use.

Tuesday, January 11, 2005

New MS Access Tips Page

I recently posted a new Ms Access Tips page to aadconsulting.com with over 85 tips for Access users and developers. Check it out.

Wednesday, January 05, 2005

Getting Up to Speed: An Access Book Recommendation

Jak in a post asked for a recommendation on a beginner/intermediate Access book. My first Access book was Access 2 Developers Guide by Roger Jennings, and his latest is my recommendation Special Edition Using Microsoft Office Access 2003.