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
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));

Symptoms
This article explains how to create and use a parameter query using DataAccess Objects (DAO). A parameter query is a type of QueryDef specific tothe Microsoft Jet database engine used by Visual Basic, Microsoft Access,and other products. Parameter queries enable you to automate the processof changing query criteria. With a parameter query, you can set new valuesfor the parameters each time you run the query.
Resolution
A parameter query is created in a program by using the CreateQueryDef()function. Following is the syntax for the CreateQueryDef() function:
Set querydef = database.CreateQueryDef(name, sqltext)
querydef – a QueryDef object
database – a Database object
name- string containing query name
sqltext- string containing the SQL query text The sqltext string is optional or it can be defined by using the .SQLproperty of the QueryDef. To create a parameter query, place thePARAMETERS statement in the sqltext string. Here is the syntax for thePARAMETERS statement:
PARAMETERS parametertext datatype
parametertext – name of the parameter
datatype- type of the parameter The following table lists the appropriate Microsoft Jet SQL data typethat should be used with the PARAMETERS statement as well as thecorresponding Microsoft Access field type, Visual Basic variable type,and constant value from the DATACONS.TXT file.
NOTE: Visual Basic 4.0 and later and Office 95 and later versions of Basicsupport additional data types (Boolean, Byte, Byte Array). The table belowlists equivalent Basic types for earlier versions of Basic:

MicrosoftMicrosoftVisualAccess SQLAccess FieldBasic TypeDATACONS.TXT Constant———————————————————————BitYes/NoInteger/BooleanDB_BOOLEAN = 1ByteByteInteger/ByteDB_BYTE = 2ShortIntegerIntegerDB_INTEGER = 3LongLong IntegerLongDB_LONG = 4CurrencyCurrencyDoubleDB_CURRENCY = 5IEEESingleSingleSingleDB_SINGLE = 6IEEEDoubleDoubleDoubleDB_DOUBLE = 7DateTimeDate/TimeVariantDB_DATE = 8BinaryBinaryString/Byte ArrayTextTextStringDB_TEXT = 10LongBinaryOLE ObjectString/Byte Array DB_LONGBINARY = 11LongTextMemoStringDB_MEMO = 12 Following the PARAMETERS statement in the sqltext string, place the query.The query can refer to the parameter (parametertext) named in thePARAMETERS statement. Wherever the query refers to a parameter, the currentvalue will be substituted when the query is executed.
For example, if the query text is:
PARAMETERS i SHORT; SELECT fld FROM tbl WHERE fld=i and the parameter i was set to 42 in the program, the parameter i would besubstituted and the resulting query would be equivalent to:
SELECT fld FROM tbl WHERE fld=42
Multiple Parameters in a PARAMETERS Statement It is also possible to have multiple parameters in a PARAMETERS statement.To do this, use commas to separate the parameters as follows:
PARAMETERS parametertext datatype, parametertext datatype, … Prior to executing the query, set the parameters using this syntax:
querydef!parametertext = value
querydef- a QueryDef objectparametertext – the name of the parameter in the PARAMETERS statementvalue- the value the parameter will have In the previous example, you would use QD!i=42 before executing the query.
Once the parameters are set, you are ready to execute the query. There arethree methods (Execute, CreateDynaset, and CreateSnapshot) supported by aQueryDef that will cause the query to be executed.
Example Parameter Queries The following example illustrates the use of a short parameter in a query.The example has two parts. The first part creates a new QueryDef forBIBLIO.MDB (the sample Microsoft Access database that ships with VisualBasic) and should be executed only once. The second part uses the QueryDefto create a snapshot, which is then displayed. To test the example, placeeach of the following code segments in a CommandButton Click eventprocedure.
NOTE: There are some minor differences between the older DAO syntax used byVisual Basic 3.0 and Access 1.x and the newer syntax used by the otherproducts this article applies to. Old and New DAO syntax are indicated bycomments:

‘Create QueryDef “by date”Dim Db As DatabaseDim Qd As QueryDefSet Db = OpenDatabase(“C:\VB\BIBLIO.MDB”)’ OldSet Db = DBEngine(0).OpenDatabase(“C:\VB4-32\BIBLIO.MDB”) ‘ NewSet Qd = Db.CreateQueryDef(“By date”) ‘Create the query “By date”QdText = “PARAMETERS dp Short; “QdText = QdText & “SELECT * from Titles WHERE [Year Published] = dp”Qd.SQL = QdTextPrint Qd.SQLQd.Close’ Create Snapshot from QueryDefDim Rs As Snapshot’ OldSet Qd = Db.OpenQueryDef(“By Date”)’ OldDim Rs As Recordset’ NewSet Qd = Db.QueryDefs(“By Date”)’ NewQd!dp = 1991′Set the value of the dp parameterSet Rs = Qd.CreateSnapshot()’ OldSet Rs = Qd.OpenRecordset(dbOpenSnapshot)’ NewDo Until Rs.EOFFor i = 1 To Rs.Fields.Count – 1Print Rs(i);’Display results of queryNextPrintRs.MoveNextLoopRs.CloseQd.CloseDb.CloseThe second example shows how to use an action parameter query. Note thataction queries are invoked with the Execute method, not CreateDynaset orCreateSnapshot, because they do not return records:

‘Create QueryDefDim Db As DatabaseDim Qd As QueryDefSet Db = OpenDatabase(“C:\VB\BIBLIO.MDB”)’ OldSet Db = DBEngine(0).OpenDatabase(“C:\VB4-32\BIBLIO.MDB”) ‘ NewSet Qd = Db.CreateQueryDef(“Delete by name”) ‘Create the queryQdText = “PARAMETERS p1 Text; “QdText = QdText & “DELETE * FROM Authors WHERE Author = p1;”Qd.SQL = QdTextPrint Qd.SQLQd.Close’ Execute the QueryDefSet Qd = Db.OpenQueryDef(“Delete by name”)’ OldSet Qd = Db.QueryDefs(“Delete by name”)’ NewQd!p1 = “Bob”Qd.Execute’Perform the action queryQd.CloseDb.Close

Symptoms
In Access, when you first link (attach) an external table using an ODBCdriver, you have the option to store the User ID and password for the tablelocally. If you do not store the ID and password locally, you will beprompted later for such information when you open the table.
This article demonstrates how to bypass the Login prompt when you open anAccess linked table by pre-connecting to the database and providing User IDand password programmatically in Basic.
Resolution
The Microsoft Jet database engine caches authentication information foreach DSN. This prevents users from being prompted to login to remotedatabases each time a table is opened. You can take advantage of thisbehavior by pre-connecting to the database directly and programmaticallyproviding user ID and password to prevent the login prompt from appearingwhen opening linked tables that don’t have the user ID and password cached.In Access, create a new database, db1.mdb, and a linked table,dbo_authors, from SQL Server Pubs database.In Visual Basic, start a new project and choose “Standard EXE.” Form1is created by default.In Access, create a new database and create a new form (Form1).In Visual Basic 4.0 and later, add a Reference to:

Microsoft Data Access Object 2.x(VB4 16-bit)Microsoft Data Access Object 3.x(VB4 32-bit; VB5) Paste the following code in the General Declarations section of Form1:
Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

Sub Command1_Click()Dim db1 As DatabaseDim db2 As DatabaseDim rs As RecordsetDim strConnect As String’*** You have to modify the path to where db1.mdb is locatedSet db1 = OpenDatabase(“C:\MyTest\db1.mdb”)strConnect = UCase(db1.TableDefs(“dbo_authors”).Connect) & _”;Username=<username>;PWD=<strong password>”Set db2 = OpenDatabase(“”, False, False, strConnect)db2.CloseSet db2 = NothingSet rs = db1.OpenRecordset(“dbo_authors”)Debug.Print rs(0)Debug.Print “Recordset Opened Successfully”rs.Closedb1.CloseSet rs = NothingSet db1 = NothingEnd Sub NOTES:
You must provide correct login information, User ID and Password, instrConnect to establish the connection.If you know which DSN the table is linked to, you can hard-code thevalue of strConnect.The Microsoft Jet database engine will first try to log you in with thesame user ID and password that you log into the Jet database with(default is Admin/no password). If you make the local login match theserver login, you will not get any login prompts.Microsoft SQL Server can integrate its security mechanism with MicrosoftNT domain accounts. If the user has a valid account in the domain, youwill not get any login prompts.