Visual Basic Q&A

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.

Symptoms
When importing a text file into a Microsoft Jet database through a Microsoft SQL statement or the Microsoft Access TransferText macro command when there is no Schema.ini file present, the Jet engine can sometimes interpret a column with Text data as Currency.
Resolution
The Microsoft Jet database engine examines the column for currency formatting symbols.

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
When you run an action query by using Data Access Objects (DAO) and theMicrosoft Jet database engine against an ODBC table, if you specify thedbFailOnError argument, the RecordsAffected property always returns 0,regardless of the number of records affected by the statement.
Resolution
This is a confirmed problem in Microsoft Jet database engine versions 3.5and 3.51.