Visual Basic Q&A

Symptoms
The ExcelADO.exe sample illustrates how you can use ActiveX Data Objects (ADO) with the Microsoft Jet OLE DB 4.0 Provider to read and write data in Microsoft Excel workbooks.
Resolution
The following file is available for download from the Microsoft Download Center:
ExcelADO.exe(http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe)Release Date: December 12, 2000
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/)How to Obtain Microsoft Support Files from Online ServicesMicrosoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.Why Use ADO?The use of ADO to transfer data to or retrieve data from an Excel workbook gives you, the developer, several advantages over Automation to Excel: Performance. Microsoft Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.Scalability. For Web applications, it is not always desirable to automate Microsoft Excel. ADO presents you with a more scaleable solution to handle data in a workbook.ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format ismaintained. If you require “conditional” formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.Jet OLE DB Provider Specifics for Excel WorkbooksThe Microsoft Jet database engine can be used to access data in other database file formats, such as Excel workbooks, through installable Indexed Sequential Access Method (ISAM) drivers. In order to open external formats supported by the Microsoft Jet 4.0 OLE DB Provider, you specify the database type in the extended properties for the connection. The Jet OLE DB Provider supports the following database types for Microsoft Excel workbooks: Excel 3.0Excel 4.0Excel 5.0Excel 8.0NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks. The ExcelADO.exe sample uses Excel workbooks in the Excel 97 and Excel 2000 format.
The following samples demonstrate an ADO connection to an Excel 97 (or 2000) workbook:

Dim oConn As New ADODB.ConnectionWith oConn.Provider = “Microsoft.Jet.OLEDB.4.0″.Properties(“Extended Properties”).Value = “Excel 8.0″.Open “C:\Book1.xls”‘…..CloseEnd With ?????-or-

Dim oConn As New ADODB.ConnectionoConn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;” & _”Extended Properties=”"Excel 8.0;”"”oConn.Close Table Naming Conventions
There are several ways you can reference a table (or range) in an Excel workbook:Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.

oRS.Open “Select * from [Sheet1$]“, oConn, adOpenStatic Use a range with a defined name (for example, [Table1]).

oRS.Open “Select * from Table1″, oConn, adOpenStatic Use a range with a specific address (for example, [Sheet1$A1:B10]).

oRS.Open “Select * from [Sheet1$A1:B10]“, oConn, adOpenStatic Table Headers
With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).
Data Types
Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to “guess” the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.Files Included with the SampleThe ExcelADO.exe file contains a Visual Basic Standard EXE project, Active Server Pages (ASP), Excel 97 and Excel 2000 Workbooks that act as templates, and a Microsoft Access 2000 database. The files included are as follows:
Visual Basic Standard EXE Project FilesExcelADO.vbpForm1.frmForm1.frxActive Server PagesEmpData.aspOrders.aspMicrosoft Excel WorkbooksOrdersTemplate.xlsEmpDataTemplate.xlsProductsTemplate.xlsSourceData.xlsMicrosoft Access DatabaseData.mdbHow to Use the SampleExtract the contents of the .exe file to a folder.
To use the Visual Basic project: In Visual Basic, open the ExcelADO.vbp file. On the Project menu, select References, and then set references to Microsoft ADO Ext. for DDL and Security and Microsoft ActiveX Data Objects Library. This sample code works with both ADO 2.5 and ADO 2.6, so select the version appropriate to your computer.Press the F5 key to run the program. A form for the demonstration appears.Click Sample 1.This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:On the Insert menu in Excel, select Names, and then select Define. In the list of defined names, select Orders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel’s OFFSET function, to compute a total on the data added to the worksheet. Quit Microsoft Excel and return to the Visual Basic application.Click Sample 2.This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses the Execute method of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.
Click Sample 3.This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADO Recordset is then obtained for the new table and data is added by using the AddNew/Update methods. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in the Open event for the Workbook. The macro runs when the workbook opens; if the new “Products” worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any “conditional” formatting that might not be possible in a template alone to run at the client.
NOTE: To examine the macro code, view the ThisWorkbook module in the VBAProject for ProductsTemplate.xls.
Click Sample 4.This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.
The last sample illustrates how you can read data from an Excel workbook. Select a table in the drop-down list, and then click Sample 5. The Immediate window displays the contents of the table that you selected. If you select an entire worksheet (“Sheet1$” or “Sheet2$”) for the table, the Immediate window displays the contents of the used range for that worksheet. Note that the used range does not necessarily begin on row 1, column 1 of the worksheet. The used range starts at the upper left-most cell in the worksheet that contains data.
If you select a specific range address or a defined range, the Immediate window displays the contents of only that range on the worksheet.To use the Active Server Pages (ASP):Create a new folder named ExcelADO in the home directory of your Web server. Note that the default path for the home directory is C:\InetPut\WWWRoot.Copy the following files to the folder you created in the previous step:EmpData.aspOrders.aspData.mdbEmpDataTemplate.xlsOrdersTemplate.xls
The ASP scripts in this sample create copies of the workbook templates with the Copy method of the FileSystemObject. For the Copy method to succeed, the client that is accessing the script must have Write access to the folder that contains the ASP.Navigate to Orders.asp (that is, http://YourServer/ExcelADO/Orders.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 1 of the Visual Basic application.Navigate to EmpData.asp (that is, http://YourServer/ExcelADO/EmpData.ASP), and note that the browser displays an Excel workbook similar to the one in Sample 2 of the Visual Basic application. (c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

Symptoms
According to Microsoft Knowledge Base article 275561?(http://support.microsoft.com/kb/275561/EN-US/) “ACC2000: New Features in Microsoft Jet 4.0″:
To minimize the impact of the increased page size and respond to a long-standing request from developers building applications based on the Microsoft Jet database engine, row-level locking was added to Jet 4.0.However, row-level locking of an Access database is not available with Data Access Objects (DAO) 3.60.
Resolution
To resolve this problem, use ActiveX Data Objects (ADO) to enable row-level locking on an Access database, and then open DAO connections to the database. All subsequent attempts to open DAO connections to the database will respect the locking mode that you set.

Symptoms
Error 3167 “Record is Deleted” is a common error when using the Data AccessObjects (DAO) or a data control to access ODBC tables. This is due to theway that the Microsoft Jet Database Engine manipulates its cursor for therecordset. It is not limited to DAO. Similar errors can be raised by anyengine that maintains a cursor. The ODBC cursor library and serversthemselves can and will raise similar errors. Understanding why and howthese errors are caused requires a knowledge of resultset and cursorbehavior.
Resolution
The “Record is Deleted” error (error 3167) is a byproduct of the Jetengine’s keyset cursor for the dynaset type recordset. A keyset cursor isfixed in membership, but there is nothing stopping another user fromdeleting a row in the underlying table that you have selected in yourkeyset. When you attempt to get the data or update the data in a deletedrow, the “Record is Deleted” error message is generated. Again, thisbehavior is not limited to the Jet engine, but can occur in any keysetcursor.
This is not the only cause of the error. There are several other causesthat are far more subtle and depend on the keyset implementation. Becausethe Jet engine uses a keyset based on a unique index in the underlyingtables, it is possible to get this error if something changes the indexinformation. When the fields that the keyset is built from are changed inthe underlying table for a given record, the Jet engine is not able to findthe record to read or update the data and raises the “Record is Deleted”error. In most cases the Jet engine knows that the indexed fields changedin the underlying table if it made the changes, but the following couldchange the indexed fields without the Jet engine being aware of it:
Other users. Other users may change the values in the indexed fields.When the Jet engine is unable to find that record based on the valuethat it is storing, it raises an error.Triggers. Triggers can change the values in the indexed fields. Sincea trigger changes the values from what the Jet engine thinks it putin there, the cached keyset value and the actual value in the tablediffer. When the Jet engine tries to fetch the record, it will raisean error.Null and Empty String behavior. Many databases automatically changedata if necessary without alerting the Jet engine. For example, if theuser were to add a record where the indexed column was a varchar() andthe user submitted a “” value for that field, SQL Server would changethe “” into a space (” “) and not alert the Jet engine. The Jet enginewould then try to find that record with “”, fail to do so and raise the”Record is Deleted” error.Functions. Many indexed fields are updated with server functions such asGetDate(). These can change the indexed data without the Jet engineknowing it.Indexes on non-standard datatypes or floating point datatypes. Someserver datatypes have no ODBC or Jet engine equivalent datatype. In mostcases, the Recordset is created as read-only, but sometimes it is not.Rounding or conversion errors on the datatypes can cause the error aswell.

Symptoms
You can edit the Jet Registry keys to alter the data access behavior of theMicrosoft Jet database engine. However, doing so affects all Jet-basedapplications, and may have unintended negative consequences for some. Byusing custom profiles, you can tune Jet on a per-application basis.
Resolution
In 16-bit versions of Visual Basic and Microsoft Access, Jet settings arestored in an .ini file (for example, Msaccess.ini or Msacc20.ini). You canprovide a custom .ini file with your application that contains settingsspecific to your application.
The 32-bit versions of Jet read their settings from the registry.
In Visual Basic 4.0, 5.0, 6.0, and Jet versions 3.0 and 3.5 use the defaultregistry location:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5
If using Microsoft Access, settings are read from the following branchfirst, and then the default location (above) is used for any additionalvalues:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\Jet\3.5
To prevent multiple applications from making custom changes to thesesettings and negatively impacting other Jet-based programs, the applicationcan tell Jet to read its settings from a different branch of the registry.These alternate branches are known as Profiles.
You don’t have to reproduce the entire registry branch in your profile.Values you specify in your custom profile override values specified in thedefault registry location, which in turn override internal Jet defaultvalues.
Example ProfileWARNING: Using Registry Editor incorrectly can cause serious problems thatmay require you to reinstall your operating system. Microsoft cannotguarantee that problems resulting from the incorrect use of Registry Editorcan be solved. Use Registry Editor at your own risk.
For information about how to edit the registry, view the “Changing Keys AndValues” Help topic in Registry Editor (Regedit.exe) or the “Add and DeleteInformation in the Registry” and “Edit Registry Data” Help topics inRegedt32.exe. Note that you should back up the registry before you edit it.If you are running Windows NT, you should also update your Emergency RepairDisk (ERD).
This example illustrates registry changes for a custom profile that lowersthe LockRetry setting from the default value of 20 to 5.
In the Registry Editor, expand HKEY_LOCAL_MACHINE\SOFTWARE and add keyvalues for your company name (MyCorp), application name (MyApp), andversion (1.0):
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0The above is sufficient for Visual Basic, but to maintain compatibilitywith Microsoft Access applications, add keys for “Jet” and “3.0″ or”3.5″:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5This is an empty Profile. From here, add keys and values that affectJet’s behavior. To make the LockRetry changes, add the keys “Engines”and “Jet”:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0\Engines\Jet
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5\Engines\Jet
Then, add a REG_DWORD Value, called LockRetry, and a value of 5.
The profile is complete.
Using the Profile in Visual BasicTo use the profile in Visual Basic, set DBEngine.IniPath to the profilename prior to using any database functionality:
In Visual Basic 4.0:

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0″
In Visual Basic 5.0:

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5″
Using the Profile in Microsoft AccessUnlike visual Basic, where you can set the profile name programmatically,Jet is already initialized by the time your Microsoft Access code isrunning. To specify a profile name, you need to add another registry key tospecify a “friendly” profile name and use a command-line switch whenstarting Microsoft Access:
Expand the following registry branch:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\AccessAdd a key “Profiles”.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0\Profiles
-or-
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Access\ProfilesAdd a REG_SZ value with the name being your “friendly” profile name(for example, MyProfile) and the value being the registry branch wherethe profile settings are located. For example:

Name:MyProfileType:REG_SZValue: HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0Start Microsoft Access with the command-line option:
MSACCESS.EXE /PROFILE “MyProfile” MYDATABASE.MDBMicrosoft Access 95 will automatically choose the …\Jet\3.0 branch,Microsoft Access 97 will automatically choose the …\Jet\3.5 branch, inVisual Basic you must specify the complete profile path.
Because you can’t guarantee that Microsoft Access was started using thecorrect profile, you can check your startup code to see if the userstarted the application properly or just double-clicked the icon for the.mdb file. For example:

If SysCmd(acSysCmdProfile) <> “MyProfile” ThenMsgBox “To use this database, run MyApp from the Start Menu”DoCmd.Quit acExitEnd If
Compatibility with Earlier Jet DatabasesIf the database you’re opening is used by earlier versions of Jet (1.0,1.1, 2.0, or 2.5), Microsoft Jet 3.x doesn’t read these files directly. Ituses an Installable ISAM engine in the same way it would when reading adBase or Paradox file.
For example, to set the LockRetry value for accessing a Jet 2.x database,add the Value to the following location:
HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0\Engines\Jet 2.x\ISAM
You still only reference the profile location as indicated in the MicrosoftAccess and Visual Basic usage sections above. For example:
In Visual Basic:

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.0″
-or-

DBEngine.IniPath = _”HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0\Jet\3.5″
In Microsoft Access:
Value: HKEY_LOCAL_MACHINE\SOFTWARE\MyCorp\MyApp\1.0
Table of Engine KeysThe following table lists registry branches for the Jet 3.x engine and thevarious other engines:

EngineRegistry Branch—————————————————-Jet 3.x…\Engines\JetJet 2.x…\Engines\Jet 2.x\ISAMODBC…\Engines\ODBCXbase…\Engines\XbaseParadox…\Engines\ParadoxExcel…\Engines\ExcelLotus…\Engines\LotusText…\Engines\Text
Jet 3.5 NotesWith Jet 3.5, you can tune some values dynamically using the new SetOptionmethod of the DBEngine object. This means you can use some values for onepart of your program, that is, to favor a bulk update, while using othervalues in another part of your program, that is, to favor interactive dataentry. Using DBEngine.SetOption affects only the current instance of yourprogram. It can be used in Microsoft Access 97 and Visual Basic 5.0.
The following table lists the settings you can change:

SettingConstant—————————————————PageTimeoutdbPageTimeoutSharedAsyncDelaydbSharedAsyncDelayExclusiveAsyncDelaydbExclusiveAsyncDelayLockRetrydbLockRetryUserCommitSyncdbUserCommitSyncImplicitCommitSyncdbImplicitCommitSyncMaxBufferSizedbMaxBufferSizeMaxLocksPerFiledbMaxLocksPerFileLockDelaydbLockDelayRecycleLVsdbRecycleLVsFlushTransactionTimeout dbFlushTransactionTimeout
Usage:

DBEngine.SetOption dbMaxBufferSize, 128

Symptoms
The following error occurs when using RDO or ADO to update datain an Access (Jet) database:

Query is too complexDue to a limitation of the Jet database engine, this error can occur if the recordset to be updated contains morethan 40 fields.
Using the Jet 3.51 engine, the error occurs if the recordset to be updated contains more than 50 fields. With the Jet 4.0 engine and later, the error occurs if the recordset to be updated contains more than 99 fields.
Using the Jet 4.0 engine and later, you may also see the errors:

Expression too complex -or-

Too many defined fieldsThis article describes the problem scenario, the Jet limitation, and several workarounds.
Resolution
When executing the Update() or BatchUpdate() methods of RDO’s rdoResultsetobject, or the Update() or UpdateBatch() methods of ADO’s Recordset object,the ODBC error S1000 “Query is too complex” (Visual Basic run-time error40002) occurs. An error with the same description occurs using the Microsoft Jet OLE DB providers versions 3.51 and later.
This behavior occurs because the default behavior of ADO and RDO is to useeach field in the recordset to determine the record to be updated on theserver. That is, when the update is attempted, a SQL Update query is sentto the server. Part of this query is a WHERE clause that is used toidentify the record to be updated. An AND clause appears within the WHEREclause for each field to be used in that identification. Updating arecordset with more than 40 fields involves a WHERE clause with more than40 ANDs.
The Jet engine allows only 40 AND clauses within a SQL WHERE or HAVINGclause. Jet 3.51 increased this limit to 50 AND clauses, and Jet 4.0 increased the limit to 99 AND clauses. The Access engine returns a “Query is too complex” error whenit encounters such a SQL statement. See the REFERENCES section of thisarticle for more information.
There are several ways to avoid this behavior:For new applications, use DAO when working with Access databases. DAOwas designed for this purpose, and will not issue a SQL query to performthe update. Note that RDO and especially ADO were designed to operateefficiently with many other types of databases and are recommended forapplications that will interact with various servers or that will beupsized in the future to use Microsoft SQL Server, for example.When opening the recordset to be updated (such as the RDO OpenResultsetor ADO OpenRecordset methods), select specific, and 40 or fewer fields.Use a server-side cursor:
To do this in ADO, set the CursorLocation location property of theRecordset object to adUseServer before opening the Recordset:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseServerrs.Open …… In RDO, set the CursorDriver property of the rdoEnvironment objectbefore calling the OpenConnection() method:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseServerSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)… Ensure that a unique key is used to identify the record to be updated,rather than the default behavior of using every field. A unique key(though not necessarily a primary key) must be defined in the underlyingrecordset for this technique to work. To do this in ADO (this will not work in versions prior to ADO2.0), set the Recordset object’s “Update Criteria” property toadCriteriaKey. This will work both for the Update() and UpdateBatch()methods:

…Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordsetrs.CursorLocation = adUseClientrs.Properties(“Update Criteria”).Value = adCriteriaKeyrs.Open …… For RDO, use the ClientBatch cursor library and ensure that theResultset object’s UpdateCriteria property is not set tordCriteriaAllCols. By default, this property is set to rdCriteriaKey,which will cause the use of the primary key to determine the row(s) tobe updated on the server:

…Dim eng As RDO.rdoEngineDim cn As RDO.rdoConnectionDim env As RDO.rdoEnvironmentDim rs As RDO.rdoResultsetSet eng = New rdoEngineSet env = eng.rdoCreateEnvironment(…)env.CursorDriver = rdUseClientBatchSet cn = env.OpenConnection(…)Set rs = cn.OpenResultset(…)rs.Edit…rs.UpdateCriteria = rdCriteriaKeyrs.Update…

Symptoms
Visual Basic allows you to retrieve data from Jet databases (MDB files) byusing Structured Query Language (SQL). These query operations can be mademore efficient by implementing some of the suggestions in this article.
This article assumes that you are using the Microsoft Jet database engine.If you are querying ODBC tables, many of these points still apply. Formore information regarding improving performance of ODBC queries, pleasesearch on the following words in the Microsoft Knowledge Base:
ODBC and Optimizing and Tables
Resolution
Here are some tips for optimizing your SQL queries:
Performance AnalyzerIf you have Microsoft Access 95 or 97, you can open the database and usethe Performance Analyzer to profile your queries and suggest improvements.
Table DesignWhen defining a field in a table, choose the smallest data typeappropriate for the data in the field. This increases the number ofrecords that can fit on a page.
Fields you use in joins should have the same or compatible data types.
Compact the DatabaseThis has two performance benefits:
The Microsoft Jet database engine uses a cost-based method ofoptimization. As your database grows, the optimization scheme may nolonger be efficient. Compacting the database updates the databasestatistics and re-optimizes all queries.As your database grows, it will become fragmented. Compacting writesall the data in a table into contiguous pages on the hard disk, improvingperformance of sequential scans.
To compact your database, use the CompactDatabase statement. This examplecompacts the database and makes a backup:

DBEngine.CompactDatabase “C:\VB\BIBLIO.MDB”, “C:\VB\BIBLIO2.MDB”Kill “C:\VB\BIBLIO.BAK”Name “C:\VB\BIBLIO.MDB” As “C:\VB\BIBLIO.BAK”Name “C:\VB\BIBLIO2.MDB” As “C:\VB\BIBLIO.MDB”
If your database is updated heavily, you may want to consider compactingnightly.
Avoid Expressions in Query OutputExpressions in query output can cause query optimization problems if thequery is used later as input to another query and you add criteria tothe calculated output. In the following example, Query1 is used as inputfor a second SELECT statement:

Dim DB As DatabaseDim RS As RecordSetSet DB = DBEngine.Workspaces(0).Opendatabase(“Biblio.MDB”)DB.CreateQueryDef(“Query1″, _”SELECT IIF(Au_ID=1,’Hello’,'Goodbye’) AS X FROM Authors”)Set RS = DB.OpenRecordSet(“SELECT * FROM Query1 WHERE X=’Hello’”)
Because the IIF() expression in Query1 cannot be optimized, the WHEREcondition in second SELECT statement also cannot be optimized. If anexpression gets buried deeply enough in a query tree, you can forget thatit is there. As a result, your entire string of queries cannot beoptimized.
If you can, merge the SQL into a single level of nesting:

Set RS = DB.OpenRecordSet(“SELECT * FROM Authors WHERE Au_ID=1″)
For more complex nested queries, expose the fields that make up theexpression:

DB.CreateQueryDef(“Query1″, _”SELECT IIF(Au_ID=1,’Hello’,'Goodbye’) AS X, Au_ID, FROM Authors”)Set RS = DB.OpenRecordSet(“SELECT * FROM Query1 WHERE Au_ID=1″)
If you cannot avoid calculated values in query output, place them in thetop-level query and not in lower-level queries.
Output Only the Fields NeededWhen creating a query, return only the fields you need. If a field doesn’thave to be in the SELECT clause, don’t add it. The above example ofexposing additional fields to make nested queries more efficient is anexception.
GROUP BY, Joins, and AggregatesThis is an issue when you are joining two tables. For example, if you jointwo tables on the Customer Name field, and also GROUP BY the Customer Namefield, make sure that both the GROUP BY field (Customer Name) and thefield that is in the aggregate (Sum, Count, and so on) come from the sametable.
NOTE: This query is less efficient because the SUM aggregate is on the Ordtable and the GROUP BY clause is on the Cust table:

SELECT Cust.CustID,FIRST(Cust.CustName) AS CustName,SUM(Ord.Price) AS TotalFROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustIDGROUP BY Cust.CustID
A more efficient query would be to GROUP BY on Ord.CustID:

SELECT Ord.CustID,FIRST(Cust.CustName) AS CustName,SUM(Ord.Price) AS TotalFROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustIDGROUP BY Ord.CustID
NOTE: The First and Last functions do not have the overhead of otheraggregates and should not weigh very heavily in this decision.
GROUP BY As Few Fields As PossibleThe more fields in the GROUP BY clause, the longer the query takes toexecute. Use the First aggregate function to help reduce the number offields required in the GROUP BY clause.
Less efficient:

SELECT Cust.CustID,Cust.CustName,Cust.Phone,SUM(Ord.Price) AS TotalFROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustIDGROUP BY Cust.CustID, Cust.CustName, Cust.Phone
More efficient:

SELECT Ord.CustID,FIRST(Cust.CustName) AS CustName,FIRST(Cust.Phone) AS Phone,SUM(Ord.Price) AS TotalFROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustIDGROUP BY Ord.CustID
Nest GROUP BY Clause Before JoiningIf you are joining two tables and only grouping by fields in one of them,it may be more efficient to split the SELECT statement into two queries.making the SELECT statement with the GROUP BY clause into a nested queryjoined to the non-grouped table in the top-level query.
Less efficient:

SELECT Ord.CustID,FIRST(Cust.CustName) AS CustName,FIRST(Cust.Phone) AS Phone,SUM(Ord.Price) AS TotalFROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustIDGROUP BY Ord.CustID
More efficient:

Query1:SELECT CustID, SUM(Price) AS TotalFROM OrdGROUP BY CustIDQuery2:SELECT Query1.CustID, Cust.CustName, Cust.Phone, Query1.TotalFROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
Index Both Fields Use in JoinWhen joining tables, try to index the fields on both sides of a join. Thiscan speed query execution by allowing the query optimizer to use moresophisticated internal join strategy.
However, if you know one table is going to remain relatively small (occupy1-2 2K pages), it may be more efficient to remove indexes in thattable because fewer pages will have to be read into memory. You should trythis on a case-by-case basis.
Add Indexes to Speed Searches and SortsPlace an index on all fields that are used in a join or in a restriction.With the use of Rushmore query optimization technology, the Microsoft Jet2.0 and later database engine is able to take advantage of multipleindexes on a single table, which makes indexing multiple fieldsadvantageous.
Avoid restrictive query criteria on calculated and non-indexed columnswhenever possible.
Use sorting judiciously, especially with calculated and non-indexed fields.
Use Optimizable ExpressionsTry to construct your queries so that Rushmore technology can be used tohelp optimize them. Rushmore is a data-access technology that permits setsof records to be queried very efficiently. With Rushmore, when you usecertain types of expressions in query criteria, your query will run muchfaster. Rushmore does not automatically speed up all your queries. You mustconstruct your queries in a certain way for Rushmore to be able to improvethem.
Use the REFERENCES section at the end of the article to locate morespecific information.
Use COUNT(*) Instead of COUNT([Column Name])The Microsoft Jet database engine has special optimizations that allowCOUNT(*) to be executed much faster than COUNT([Column Name]).
NOTE: These two operations also have slightly different behavior:
Count(*) counts all rows returned.Count([Column Name]) counts all rows where [Column Name] is not NULL.
Avoid LIKE on ParametersBecause the value of the parameter is unknown at the time the query iscompiled, indexes will not be used. You can gain performance byconcatenating the parameter value as a literal in the SQL statement.
Use the REFERENCES section at the end of the article to locate morespecific information.
Avoid LIKE and Leading WildcardIf you use the LIKE operator with a wildcard to find approximate matches,use only one asterisk at the end of character string to ensure that anindex is used. For example, the following criteria uses an index:

Like “Smith”Like “Sm*”
The following criteria does not use an index:

Like “*sen”Like “*sen*”
Test Joins with RestrictionsIf you use criteria to restrict the values in a field used in a join, testwhether the query runs faster with the criteria placed on the “one” sideor the “many” side of the join. In some queries, you get fasterperformance by adding the criteria to the field on the “one” side of thejoin instead of the “many” side.
Use Intermediate TablesUse SELECT INTO statements to create work tables, especially if theresults are going to be used in a number of other queries. The more workyou can do up-front, the more efficient the process.
Avoid NOT IN with SubSelectsUsing sub-selects and NOT IN is poorly optimized. Converting to nestedqueries or OUTER JOINs are more efficient. The following example findscustomers without orders:
Less efficient:

SELECT Customers.*FROM CustomersWHERE Customers.[Customer ID]NOT IN (SELECT [Customer ID] FROM Orders);
More efficient:

SELECT Customers.*FROM Customers LEFT JOIN OrdersON Customers.[Customer ID] = Orders.[Customer ID]WHERE ((Orders.[Customer ID] Is Null));