Visual Basic Q&A

Symptoms
Jet does not support named QueryDefs on a non-attached ODBC database. A nonattached ODBC database is one that is opened directly with theOpenDatabase method of the WorkSpace object without the use of an .mdb file.
Resolution
The preferred method for opening an external ODBC table is to attach it toan .mdb file. For additional information, please see the followingarticle(s) in the Microsoft Knowledge Base:
150716?(http://support.microsoft.com/kb/150716/EN-US/): DAO: How To Attach to and Create QueryDefs on ODBC Tables
If this method is not appropriate for your application, the followingexample shows how to createQuerydefs with no name:

Set qd = db.CreateQueryDef(“”)qd.SQL = “Select * from authors”

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
When using the Data Access Objects (DAO), Remote Data Objects (RDO) or the ADO GetChunk method, on a long binary field of a Microsoft Access database, binary data may appear corrupt if the binary information previously stored was read from disk using a String (BSTR) variable instead of a Byte array. The problem occurs under one of the following conditions:
You recently upgraded your computer to Windows 98 or Windows NT version 4.0 SP4. Previously stored data now appears corrupt.
-or-You are using a shared database. The corruption occurs when the binary data was stored by clients running on Windows 95 or Windows NT 4.0 SP3, and then extracted by clients running Windows 98 or Windows NT 4.0 SP4 or vice versa.
Resolution
The problem is that the information stored to the database is mistakenly being converted to Unicode before being stored. The “corruption” occurs when reading the information from disk using the Visual Basic or Visual Basic for Applications Get statement.
By design, the Get statement relies on the data type of the variable to determine how the information should be read into Visual Basic. If you pass a String (BSTR) variable to the function, Visual Basic identifies the information as being a text string, and converts it to Unicode. However, since the data is binary and not text, this conversion corrupts the data before it is ever stored to the database.
Although binary data stored in Unicode format can be “decoded” in some cases, it requires that the Unicode symbol table used to map 1-byte ANSI characters to 2-byte Unicode characters be the same for both the conversion to and the conversion from Unicode. Due to the recent addition of the European Currency symbol, the symbol table for Windows 98 and Windows NT 4.0 SP4 is different from those used previously. These changes will have no effect on text strings, but can effect binary data mistakenly converted to Unicode.

Symptoms
If you try to gain access to an Access 2000 or Access 2002 database, you may receive one of the following errors within your Visual Basic program.
If you are using Microsoft ActiveX Data Objects (ADO) (or the ADO Data Control), you receive the following error message:

Run-time error -2147467259 Unrecognized Database Format XXXIf you are using Data Access Objects (DAO) (or the DAO generic Data Control), you receive the following error message:

Run-time error 3343 Unrecognized Database Format XXX
Resolution
Access 2000 and Access 2002 use the Jet 4.0 engine, which creates Jet 4.0 format database files. Jet 3.5 components do not recognize such a format.If you are using ADO, you get error -2147467259 when you try to connect to your Access 2000 or 2002 database through the Microsoft.Jet.OLEDB.3.51 provider.If you are using DAO, you get error 3343 when you use the Microsoft DAO 3.51 Object Library.The DAO generic Data Control does not work against Access 2000 or 2002 databases and always generates error 3343 unless it is used as instructed in the “Resolution” section of this article. This occurs because this control is based on Jet 3.51 and only recognizes Jet 3.51 (or before) database formats.

Symptoms
If you attempt to use the Data control to connect to a password-protected Access database at design time, you receive the following error message:

Not a valid passwordSpecifically, this error occurs when you set the RecordSource property of the Data control.
Resolution
To use the Data control with a password-protected Access 2000 or later database, you must set the Recordset property of the data control at run time as follows:In your project, click References from the Project menu, and then select the Microsoft DAO 3.6 Object Library check box.In the Form_Load event of your form that contains the Data control, place the following code where “Data1″ is the name of your Data control. Make sure to change the path, table name, and password in the following code to reference your database.

Private Sub Form_Load()Dim db As DAO.DatabaseDim ws As DAO.WorkspaceDim rst As DAO.RecordsetSet ws = DBEngine.Workspaces(0)Set db = ws.OpenDatabase _(“C:\Atest.mdb”, _False, False, “MS Access;PWD=aaa”)Set rst = db.OpenRecordset(“Table1″, dbOpenDynaset)Set Data1.Recordset = rstEnd Sub

Symptoms
The information below includes the documentation and workarounds for VisualBasic 6.0. This information can also be found in the README.htm file thatships with Visual Basic 6.0 on the Visual Basic 6.0 CD-ROM. Please see theREFERENCES section of this article for a list of the Microsoft KnowledgeBase articles relating to the Visual Basic 6.0 readme.
Following is a list of all parts of the readme file:

Part 1.Important Issues – Please Read First!Part 2.Data Access Issues and DataBinding TipsPart 3.Control IssuesPart 4.Language IssuesPart 5.Samples IssuesPart 6.Wizard IssuesPart 7.Error Message IssuesPart 8.WebClass Designer IssuesPart 9.DHTML Page Designer IssuesPart 10. Extensibility issuesPart 11. Miscellaneous IssuesPart 12. Microsoft Transaction Server (MTS) IssuesPart 13. Dictionary ObjectPart 14. Visual Component ManagerPart 15. Application Performance Manager
Resolution
Known Problems in Application Performance Explorer:
Configuring Remote Automation Security When Using Remote APE ComponentsIn order to use Remote Automation (RA) to communicate with remote APEcomponents, you may have to configure RA security using the RemoteAutomation Connection Manager (Racmgr32.exe).
To configure RA security:
Start Racmgr32.exe and click the Client Access tab.Select either “Allow All Remote Creates” or “Allow Remote Creates byKey”.If “Allow Remote Creates by Key” is selected, make sure the “AllowRemote Activation” check box is checked for each APE component.RA supports the following levels of authentication:

NameValueDescription———————————————————————Default0Use Network default.None1No authentication.Connect2Connection to the server is authenticated.Call3Authenticates only at the beginning of eachremote procedure call, when the serverreceives the request. Does not apply toconnection-based protocol sequences (thosethat start with the prefix “ncacn”).Packet4Verifies that all data received is from theexpected client.Packet5Verifies that none of the data transferredIntegritybetween client and server has beenmodified.Packet Privacy 6Verifies all other levels and encrypts theargument values of each remote procedurecall.
APE profiles are initially installed with an authentication level of 1(“None”) because Windows 95 supports only that level of authentication.However, if additional security is desired, the level of authentication ofa profile can be changed by modifying the profile collection file (theAemanagr.ini file) by using a text editor such as Notepad.
Each profile in the profile collection file begins with the name of theprofile within square brackets, such as [Peak performance, synchronous(CPU, Pool)]. The attributes of the profile follow, using the format<name>=<value> (such as “Task Duration=1″). To change the authenticationlevel, change the value of the “Authentication” attribute of the selectedprofile and save the file.
Compatibility Issues Between the Application Performance Explorer (APE)that Ships with Visual Studio 6.0 and the Version that Shipped with Visual
Basic 5.0There are known compatibility issues between the Application PerformanceExplorer (APE) that ships with Visual Studio 6.0 and the APE that shippedwith Visual Basic 5.0.
To avoid the compatibility issues, do one of the following:
Before installing Visual Studio 6.0 and APE on the computer that has theversion of APE shipped with VB 5, first uninstall APE from Visual Basic,and then install Visual Studio and APE.If you have installed Visual Studio 6.0 and APE on the same computerthat has the VB5 APE, uninstall the VB APE and then reinstall the VisualStudio APE.
Adjusting Default Settings To Use APE and MTSAfter installing the APETEST database onto your SQL Server, you must adjustsome of the default settings in order to use APE and MTS.
NOTE: If you haven’t already installed the APETEST database on your SQLServer, you should do that first. To learn how to install the APETESTdatabase, search for the topic “APE Database Setup Wizard” in MSDN LibraryVisual Studio 6.0.
To configure the APETEST database installation to work with MTS:
Start Microsoft SQL Enterprise Manager.In the Databases folder, right-click the APETEST database and clickEdit.Click the Options tab.Select the Truncated Log on Checkpoint check box and click OK.In the Databases folder, right-click the tempdb database and click Edit.Click Expand.In the Data Device box, select <new>.In the New Database Device dialog box, in the Name box, type tempdbData.In the Size (MB) box, type 10.Click Create Now, and finally click OK.Click Expand Now.Click Expand.In the Log Device box, select <new>.In the New Database Device dialog box, in the Name box, type tempdbLog.In the Size (MB) box, type 10.Click Create Now, and finally click OK.To configure the allowable number of user connections:
Start Microsoft SQL Enterprise Manager.Right-click the server and click Configure.For example, if your server is named CORONA, in the Server Manager childwindow, right-click CORONA and then click Configure.Click the Configuration tab.In the Configuration box, increase the number of user connections by atleast 15.
NOTE: If you are running APETEST on an established production databaseserver, you may not have access permission to adjust the current number ofuser connections. In this case, you should ask your database administratorto increase the number of current user connections by at least 15connections to support APE testing.
Application Performance Explorer Server-Side Setup May Generate ErrorWhile installing the APE server-side components, you may see an errorreferring to an incorrect version of OLEAUT32.dll. You may dismiss thiserror and continue with the installation.
However, this error message may indicate that the Microsoft TransactionServer Package was not installed correctly. To confirm that it wasinstalled correctly, run the Transaction Server Explorer and look for allinstalled MTS packages on your computer. Visual Studio APE Package shouldbe listed.
To install the package, AEMTSSVC.pkg, run the MTS Transaction ServerExplorer from the Start menu and install the package to the local computerusing the MTS Explorer.