Visual Basic Q&A

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 install Data Transformation Services (DTS) Package event handlers in a Visual Basic application, and the package you call uses an Execute Package task to call another package, an Exception Access Violation (AV) similar to the following may occur:

(1:Child Package) SubStep ‘<title of step> Step’ failed with the following error:
Need to run the object to perform this operation
(Microsoft Data Transformation Services (DTS) Package (80040005):
Provider generated code execution exception:EXCEPTION_ACCESS_VIOLATION)The Access Violation occurs when these conditions are true:You use DTS package event handlers in the Visual Basic application.You use a parent DTS package that uses an Execute Package task to call a child DTS package.
Resolution
Threading limitations in the design of Visual Basic 6.0 prevent the nested child package from reporting it’s events up through the parent package’s event handler.

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 Microsoft Data Object Libraries may be referenced in your Visual Basic project. When the Package and Deployment Wizard (PDW) is used to build the installation, the PDW scans the project files and determines which dependency files need to be distributed.
This article provides the general information you need to distribute any of the following Data Object Libraries with your Visual Basic project: Microsoft Data Access Objects (DAO) 3.51 Object LibraryMicrosoft Data Access Objects (DAO) 3.6 Object LibraryMicrosoft Remote Data Object (RDO) 2.0Microsoft ActiveX Data Objects (ADO) 2.0, 2.1, 2.5, or 2.6 Library
Resolution
The files are listed in the Setup’s file that is created by the PDW for distribution.
For additional information on how each section in the Setup.lst file is used, click the following article number to view the article in the Microsoft Knowledge Base:
189743?(http://support.microsoft.com/kb/189743/) Description of Setup.lst sections The following sections describe points to be aware of when you distribute any of the Data Object Libraries.
Microsoft Data Access Objects (DAO) 3.51 Object LibraryIntrinsic data control incompatibility with Microsoft Access 2000 (Jet 4.0) If you use the intrinsic data control in your project and you attempt to connect to a Microsoft Access 2000 database, you may receive the following error message:

Unrecognized Database Format This error message occurs because the DAO generic data control is based on Jet 3.51 and does not recognize Jet 4.0 database formats. Access 2000 is a Jet 4.0 format database. Prior to Microsoft Visual Studio 6.0 Service Pack 4 (SP4), the workaround for this problem was to open a recordset with DAO code, and then assign it to the recordset property of a data control. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
238401?(http://support.microsoft.com/kb/238401/) PRB: Unrecognized database format error message when upgrading to Access 2000 This problem does not exist with the data control that ships with Microsoft Visual Studio 6.0 Service Pack 6.
Visual Studio 6.0 Service Pack 6 can be obtained at the Visual Studio 6.0 Service Pack Web site:
http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx(http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx)Jet 3.51 OLE DB Provider is not included in MDAC 2.1 or later The Jet OLE DB Provider requires the version number of the provider in order to connect to an Access database. If your application specifies Version 3.51 of the Jet OLE DB provider in a connection string or a UDL, and you install ADO by redistributing MDAC 2.1, you are likely to receive the following error message:

3706: ADO could not find the specified provider This error message occurs because MDAC 2.0 installs version 3.51 of the Jet OLE DB provider while MDAC 2.1 installs version 4.0 of the Jet OLE DB Provider, but does not install version 3.51. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
197902?(http://support.microsoft.com/kb/197902/) PRB: Jet 3.51 OLE DB Provider is not installed with MDAC 2.1 or later
Microsoft Data Access Objects (DAO) 3.6 Object Library DAO version 3.6 is required for applications that use DAO to read and write to Access 2000 databases. If Access 2000 (or DAO 3.6) is already installed on the destination computer, then no additional steps are required. If it is not, it is necessary to distribute DAO 3.6. However, there is currently no redistributable for DAO 3.6. In order to redistribute DAO 3.6, it is necessary to install DCOM, redistribute MDAC 2.1(GA) or later, and ensure that the DAO DLL is also distributed and registered on the destination computer.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
233002?(http://support.microsoft.com/kb/233002/) How to redistribute DAO 3.6
Microsoft Remote Data Object (RDO) 2.0 In order for RDO to be properly distributed and installed, ODBC must already be installed on the destination computer. ODBC can be installed through the odbcst32.exe file, which is located in the ODBC Folder under the SQL/i386 directory on Visual Basic 6.0 Disk 2. ODBC can also be installed by including the MDAC redistributable in the distribution that is outlined in the “References” section covering distributing ADO/MDAC. To determine all of the files needed to distribute for RDO to work successfully on a computer that does not have Visual Basic installed, you can generate a dependency (.dep) file with the Package and Deployment Wizard. To generate a dependency file that shows the files needed for RDO when running the PDW, complete the following steps: Create a Standard EXE project in Visual Basic. Form1 is created by default.Add a reference to Microsoft Remote Data Object 2.0.Save this project and run the Package and Deployment Wizard on it.Select the Package option.On the Package Type, choose Dependency File, and then proceed through the wizard to completion. When you are finished, you have a file with a .dep extension that can be opened with Microsoft Notepad. MSRDO20.dll and its dependencies are shown in this file.
You want to ensure that these files are included with the distribution. If these files are not listed on the Included Files dialog box when you run the Package and Deployment Wizard, include them by clicking Add in the dialog box.
Microsoft ActiveX Data Objects (ADO) 2.0, 2.1 or 2.5 Library The PDW does not distribute mdac_typ unless there is a specific reference to an ADO Library (any version) in the project.
You can also add mdac_typ.exe manually by clicking Add in the Included Files dialog box when you run the Package and Deployment Wizard. The wizard uses the MDAC_Typ.exe file in the …\Wizards\PDWizard\Redist folder. Obtain the MDAC Components at the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/data/aa937695.aspx(http://msdn2.microsoft.com/en-us/data/aa937695.aspx) For additional information how the PDW distributes MDAC, click the following article number to view the article in the Microsoft Knowledge Base:
217754?(http://support.microsoft.com/kb/217754/) How to control which MDAC version the Package and Deployment Wizard (PDW) distributes The installation of Microsoft Data Access Components (MDAC) requires that DCOM be already installed on the destination computer: For Windows 95 computers, install DCOM95.For Windows 98 computers, install DCOM98.Download the latest version of DCOM from the following Microsoft Web page:
http://www.microsoft.com/downloads/details.aspx?familyid=08B1AC1B-7A11-43E8-B59D-0867F9BDDA66&displaylang=en(http://www.microsoft.com/downloads/details.aspx?familyid=08B1AC1B-7A11-43E8-B59D-0867F9BDDA66&displaylang=en) If DCOM is not already installed on the destination computer, you may receive one of the following error messages:

Unable to load file ‘msdadc.dll’ to register it
-or-

DLL registration failed For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
191704?(http://support.microsoft.com/kb/191704/) PRB: Unable to load file to register it during setup

Symptoms
Attempting to access a DCOM Server from a remote client application sometimes results in the following error:

Run-time error ‘70′:
Permission Denied
This article describes the most common scenarios in which this error is raised.
Resolution
Run-time error ‘70′ is typically the result of a security or permissions issue. The following is a list of possible causes of run-time error 70 but is by no means a complete or definitive list.
DCOM Is Not Enabled If the Server machine does not have DCOM enabled, client machines will receive run-time error 70 when attempting to access the server. This scenario applies to Windows 2000, Windows NT, Windows 95, Windows 98, and Windows Millennium Edition (Me) servers:
On the Server machine, run DCOM Config (DCOMCNFG.EXE). Choose the Default Properties tab. Ensure that Enable Distributed COM on this computer is checked. This value is stored in the Windows Registry at the following location:
HKEY_LOCAL_MACHINE\Software\Microsoft\OLE
The Client User Does Not Have Sufficient Permissions If the client user does not have the correct permissions, access to the DCOM Server can be denied. There are several steps to take in order to ensure your client has valid privileges.
If the Server is Windows 95, Windows 98, or Windows Me:
Run DCOM Config. Select the DCOM Server application from the list of available applications. Select the Properties button, or double-click the DCOM Server application in the list. Test the server with “Default Access Permissions.”
If run-time error ‘70′ still occurs, the default access permissions are restricting your user. If this is the case, then modify the Default Access Permissions from the Default Security tab in DCOM Config. Grant the client user access permissions.
If run-time error ‘70′ does not occur running with default access permissions, it is likely that the custom access permissions are restricting your client from accessing the DCOM Server. Choose custom access permissions and select the Edit button. Grant the client user access permissions. If the Server is Windows NT or Windows 2000:
Run DCOM Config. Select the DCOM Server application from the list of available applications. Select the Properties button, or double-click the DCOM Server application in the list. Test the server with “Default Access Permissions,” “Default Launch Permissions,” and “Custom Configuration Permissions.”
If run-time error ‘70′ still occurs, it is likely that the default access permissions are restricting your user. If this is the case, modify the Default Access Permissions from the Default Security tab in DCOM Config.
If run-time error ‘70′ does not occur, it is likely that the custom access permissions are restricting your client from accessing the DCOM Server. Choose to use Custom access permissions and choose the Edit button. Grant the client user account access permissions, or grant a group the client user belongs to access permissions. For more information regarding security groups on Windows NT see the table to follow.
There are several group accounts you will find when you configure users and groups on Windows NT and Windows 2000. The following list is a summary of who belongs to each group:

GroupDescription————————————————————————–InteractiveIncludes all users who log onto a Windows NT orWindows 2000 system locally (at the console). Itdoes not include users who connect to NTresources across a network or are started as aserver.NetworkIncludes all users who connect to Windows NT orWindows 2000 resources across a network. It doesnot include those who connect through aninteractive logon.Creator/OwnerThe Creator/Owner group is created for eachsharable resource in the Windows NT orWindows 2000 system. Its membership is the set ofusers who either create a resource (such as afile) and who take ownership of them.EveryoneAll users who access the system, whether locally,remotely, or across the network.SystemThe local operating system.
The above list includes the group accounts which are intrinsic to Windows NT and Windows 2000 systems. Your particular network may include more groups from which you may choose. In order to determine the membership of each custom group account, you must contact your network administrator.

The DCOM Server Raises Events to the Client If your DCOM server component raises events that are handled by the client application, you must configure DCOM security on the client computer to allow access, and you must configure DCOM security on the server computer. This allows the server to make callbacks to the client, so the event can be raised. If you do not configure DCOM security in this way, error 70 is generated whenever the client application calls the server. This results in an event being raised back to the client. If the server application does not raise events, you do not have to configure DCOM security on the client computers.
If the client computer is running Windows 95, Windows 98, or Windows Me, follow these steps:
Run DCOM Config (DCOMCNFG.exe). Click the Default Security tab. Click the Edit Default button. Click the Add button. Click to select The World, and then click the Grant Access button to grant permissions. Click Ok to close the Add Access Permissions dialog box. Click Ok to close the Access Permissions dialog box. Click Ok to close the DCOM Config Properties dialog box. Test the application again.
The client application successfully handles the event.
If the client computer is running Windows NT orWindows 2000, follow these steps:
Run DCOM Config (DCOMCNFG.exe).Click the Default Security tab. Click the Edit Default button. Click the Add button.Click to select the Everyone account, and then click to select Allow Access in the Type of Access box. Click Ok. Click Ok again to close the Registry Values Permission dialog box. Click Ok to close the DCOM Config Properties dialog box. Test the application again.
The client application successfully handles the event.
Attempting to Access DCOM Server Across Non-Trusted Domains If your DCOM Server resides in one Windows NT or Windows 2000 domain, and your client logs on to a second Windows NT or Windows 2000 domain that is not “trusted” by the first, you will receive run-time error ‘70′ when attempting to access the DCOM Server.

Symptoms
Microsoft Visual Basic ships with an add-in utility known as the Visual Data Manager (VISDATA) for which source code is also provided in the form of a sample project named Visdata.vbp. Visdata.vbp is installed with other Visual Basic samples as part of the MSDN Library installation.
This article describes how to modify the source code for the Visual Data Manager add-in in order to use the add-in with Microsoft Access 2000 format database files.

Resolution
Adapting VISDATA to work with Microsoft Access 2000 format databases requires changing only one project reference, adding one menu item, and adding one line of code to process the new menu item.
Update the DAO Reference from DAO 3.51 to DAO 3.60. (This step alone is sufficient to allow you to open and work with Access 2000 files.)Open Visdata.vbp in the Visual Basic design environment.On the Project menu, open the list of References.Deselect the existing reference to Microsoft Data Access Objects 3.51, and then locate and select Microsoft Data Access Objects 3.60. If you are using Visual Basic 6.0, be sure to move the DAO 3.6 reference above the ADO library reference in the list.Add a new menu item and code to allow creating a new Access 2000 database.Open the main form, frmMDI, in the design environment.From the Tools menu, open the Menu Editor.In the Menu Editor window, scroll down in the existing menu items to File / New / Microsoft Access.In order to insert the new menu item after the existing entry for Access Version 7.0 MDB files, select the following entry: dBase. Click the Insert button, and then click the RIGHT ARROW button to make the new, blank entry a subitem under the Microsoft Access heading. Enter Access 2000 as the Caption and mnuDBNMDB2K as the Name. Close the Menu Editor by clicking OK.Open the frmMDI code module and select the mnuDBNMDB2K_Click event procedure in the drop-down list. Enter the following line of code:

NewMDB dbVersion40In Visual Basic 6.0 only, update ADO connection strings to support Access 2000.VisData uses both DAO and ADO. Use the Search function on the Visual Basic Edit menu to locate all references in the project to the Microsoft Jet OLE DB Provider version 3.51 and update 3.51 to 4.0.In Visual Basic 6.0 only, add code that will enable the Grid toolbar button, which displays a table of records in a DBGrid control rather than one record per form.Open the module modVisData and find the OpenTable procedure.After the lines:

If gsDataType = gsMSACCESS Then.ConnectionString = “PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=” & gsDBName insert the additional line:

.CursorLocation = adUseClient Then open frmDataGrid and find the Form_Load event procedure.After the line:

Set datDataCtl.Recordset = mrsFormRecordset insert the additional lines:

datDataCtl.RefreshSet grdDataGrid.DataSource = datDataCtlgrdDataGrid.Refresh If you were to omit this step, you might see an empty grid; or the grid may display with the very first cell empty.Recompile the VISDATA project and copy the compiled executable to the Visual Basic directory (which is \Program Files\Microsoft Visual Studio\Vb98 by default in Visual Basic 6.0).