PRB: File '(unknown)' , Exception Occurred, or No Data Using Recordset DTCs (234205)

SYMPTOMS

When you use the Recordset Design-Time Control (DTC) to connect to a Microsoft Access, Microsoft FoxPro, or other file-based database through an Active Server Pages (ASP) page, you may receive one of the following errors:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.

/AccessErrors/SysDE.asp, line 26

-or-

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
/AccessErrors/SysDE.asp, line 26

Also, with DECommands selected as the object in the Recordset DTC, the following error appears:

error '80020009'
Exception occurred.
/AccessErrors/SysDE.asp, line 23

Or no errors occur, but no data is displayed. This may happen if the Recordset DTC is on an .htm page, or if the Scripting Platform is specifically set to use Client-side.

RESOLUTION

Workaround 1

Following is the recommended workaround, unless you are using the DECommand:

Create two system data connections. One on the Server (Production ) machine, and one on the Client (Development) machine. Make sure that they have the exact same name.
When your page is ready to be viewed in the browser, Modify the information in the Global.asa so that both the client and server are able to understand it. Look for an application variable set up in the Application_OnStart subroutine of the Global.asa that resembles the following code:
```
Application("Connection1_ConnectionString") = "DSN=SysDSN;DBQ=M:\Databasename.mdb;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
						
```
Change this to the following code:
```
Application("Connection1_ConnectionString") = "DSN=SysDSN;"
						
```
This forces the Web server to use the information in the data source contained on its own system to connect to the database rather than the inconsistent pathing information in the connection string.

NOTE: If you make additional changes to the Global.asa file, the full path returns to the ConnectionString, and you will need to modify them again. This is by design.

Workaround 2

If you are using the DECommand object, the information to connect to the database is held in the DataEnvironment.asa file, which is a binary file, and cannot be modified in the same way as the Global.asa. In this case, to resolve the issue you must make sure that the path given is one that both the client and server understand. The best way to do this is to set up the same directory structure on the two machines, sometimes referred to as "mirrored directories."

Create a folder off of the C: drive called "Data" on the development machine.
Do the same on the server machine.
Place a copy of the database in C:\Data on the client machine.
Place another copy of the database in C:\Data on the server machine.
Create a system data source name (DSN) on the client that connects to the database on the client.
Create a system DSN on the server with the same name as the DSN on the client.

NOTE: The above method works, but when you are working with the Data Tools you are manipulating the database on the development machine, and when you are viewing in the browser, you are manipulating the database on the server machine. This also works for the table object in lieu of modifying the Global.asa.

MORE INFORMATION

If the Recordset DTC is using the client-side scripting platform, the server is not able to display the errors that occur. All of these problems are usually caused by the same scenario. From within Visual InterDev the Data Connection is made based on the path from the computer running Visual InterDev to the database. When viewing in the browser, you are attempting to make a connection from the Web server to the database. If these paths differ, these errors occur.

For example, if the client machine has a mapped drive to the database (M:\DataFolder\Database.mdb), it works fine at design-time. However, when viewed in the browser, the Web server processes the code based on the information given. The Web server checks its M: drive, which more than likely does not have a valid path to the database.

Steps to Reproduce Behavior

Make a New Web Project connecting to an IIS Server on a different computer.
On the server, share out the folder that holds an Access database.
From the client machine, map a drive to the folder on the server holding the .mdb file.
In the Visual InterDev project, right-click the Global.asa file, and select System Data Source. Click Next.
Select the Access Driver.
Click Next.
Click Finish.
Type in a data source name and Click Select.
Click the drop-down list under Drives, and select the drive that you have mapped to the server.
Select the .mdb file from the list and click OK.
Click OK again.
Select your new data source and click OK. This adds the data connection to the project.
Add an ASP page to your project.
Add a Recordset DTC to the project, and make sure it is using your connection.
Right-click the page and select View In Browser. One of the errors listed in the Symptoms section appears.