It is possible to open a connection to a remote database server without first creating a permanent DSN entry by specifying additional information in the Connect property. Basically, the connect string must provide the minimum information contained in the DSN: Driver, Server and usually Database. Note that if your queries do not specify a database, the default database as chosen by your system administrator is used unless you override this selection by using the DATABASE= argument in the connect string.

The following example shows how to open an RDO connection to Microsoft SQL Server without using a DSN:

Microsoft SQL Server Stored Procedures

When working with Remote Data Objects (RDO), Data Access Objects (DAO), VBSQL, or the ODBC API and Microsoft SQL Server stored procedures, you can create procedures that contain one or more SELECT statements or one or more SELECT statements in combination with one or more UPDATE, INSERT or DELETE statements. If you submit these queries in batcheswithout benefit of a stored procedureeach statement returns a result set, and the non-SELECT statements return the number of rows affected. If, however, you create a stored procedure that contains UPDATE, DELETE, or INSERT statements, these statements do not return result sets when the stored procedure is executed. In this case you cannot determine how many rows are returned through conventional meansas with the RecordsAffected or RowsAffected properties with DAO or RDO, respectively.

Just to be clear, however, you can execute batch SQL queries and run stored procedures that contain a mix of select and action statements using RDO. The action statements execute and if they fail, an error is returned to RDO which causes a trappable Visual Basic run-time error which you can trap using On Error syntax. However, the actual number of rows affected by the action statement is not available because this information is not returned to ODBC from the SQL Server.

If any portion of the query fails, including the action query, a trappable error is generated, so for most situations, you don't need to know the actual number of rows affected by the statement. However, if you need to know the actual number of rows affected, you can add:

Select @@ROWCOUNT

after any UPDATE, INSERT, or DELETE statements in your stored procedure. This produces a one-column, one-row result set containing the number of rows affected by the action statement.

Orphaned Stored Procedures

When using the ODBC API, Remote Data Objects, or the Microsoft Jet database engine to connect to an ODBC data source, the SQL Server driver might not remove temporary stored procedures created by the ODBC driver in the course of working on your application. This is most likely to occur when working in design mode and constantly starting, stopping, debugging and restarting applications. Loading new applications does not clear unused stored procedures. After working for some time, the TempDB database might become full. In this case, try ending Visual Basic or terminating the SQL Server connection. This might free sufficient space to continue working.

If this does not work, you must restart the SQL Server to release these resources. Note that this situation is exacerbated in development environments where a number of users are accessing the same SQL Server, each creating their own temporary stored procedures. This problem does not affect ODBC executables as their temporary stored procedures are released when the application ends.

Specifying Quoted Strings When Using ODBC Data Sources

"Communication Link Failure" Error

Accessing SQL Server 6.0 Tables with Identity Columns

If you attempt to access a SQL Server 6.0 table that includes an identity column, you can trigger an erroneous 3622 error. To prevent this problem, use the dbSeeChanges option when using the OpenRecordset method or 512 in the Options property of the Data control. This applies to attached SQL Server tables as well as those opened directly.

Can't Open FoxPro Table Contained in a Database Container

Jet cannot open a FoxPro 3.0 table that is contained in a FoxPro 3.0 database container. Tables that are not contained in a database will open correctly. If you attempt to attach to one of these tables, you will get a blank dialog box.

Cascades, Local Tables and Replication Don't Mix

Creating a Recordset Against an ODBC Data Source

When creating a Recordset against an ODBC data source with Microsoft Jet data access objects (DAO), you must move to the last record before any additional Recordset objects can be created. Using the MoveLast method against the Recordset fully populates the result set and frees the connection for additional operations. If you open an additional Database against the same data source, Jet attempts to share the first connection so you still cannot create additional Recordset objects until the first Recordset is fully populated.

DBEngine IniPath Now Uses Registry Entry

The DBEngine.IniPath property on 32-bit systems now uses a Windows Registry entry, not an .INI file. This is an update to the Help topics 'Customizing Data Access INI Settings' and 'Managing Connections to ODBC Data Sources.'

Inserting CHAR Data Columns

When you use ODBC to insert or update values in a CHAR field, ANSI conventions dictate that the number of characters written to the field equal the total field length. Because of this ANSI requirement, the ODBC driver is designed to pad all fixed-length fields with spaces (Chr$(32)). Therefore, when you insert or update CHAR fields with Jet DAO, the Data control, RDO, the RemoteData control, or the ODBC API directly to, the data length returned by a subsequent query might be longer than the inserted or updated value's data length. To remove trailing spaces, use the RTrim or Trim functions. Note that if your data value is supposed to contain trailing spaces, it might be difficult to determine the correct number of trailing spaces.

Miscellaneous Jet Database Replication Issues

Update Conflicts Should be Resolved Periodically

For databases containing autogenerate (random autonumber) columns, update conflicts should be resolved periodically, to prevent sync failures. For a table containing an autogenerate (random autonumber) column, if two replicas update the same record, cause a conflict, and therefore create an entry in the side-table

AND, before the conflict is cleared, the replicas attempt to update the same two records again, and the same replica "loses" (does not succeed in updating the record),

THEN, when the system attempts to update the side-table with this new conflict, the synchronization will fail. The workaround is to clear the initial conflict before having the same replicas sync. This could be accomplished by several different methods:

a) delete the specific rows in the conflict table,

b) delete the whole conflict table,

c) using DAO (see the Help topic "Resolving Synchronization Conflicts").

In Access, you can also use the Conflict Resolution Wizard.

Cascade Updates and Deletes

Cascade updates and deletes are not supported between local tables in a replicated database. If you have local tables in a replicated database, and you have cascades turned on for these local tables, then updates and deletes at the primary table are not supported.

 Excess Lock Conflicts

Two clients programs on one Windows 95 machine accessing the same remote database may experience excess lock conflicts. This issue is particularly noticeable when running replication and the transporter.

Difference in Behavior of Error Message 'Data Has Changed'

There is a difference in the behavior between Jet 1.1 and Jet 2.0-3.0. This difference is in how it processes the Data Has Changed error message. In Jet 1.1, the error was triggered by default to warn users that data had changed in their tables. In Jet 2.0-3.0, this error does not get triggered. To cause the database engine to trigger the error message, you need to set the Options property for the Recordset to dbSeeChanges (512).

To change the Options property on a Data control to the proper value there are two methods you can use:

In the design environment, make sure the Data control is the selected item on your form and go to the Properties window. Set the Options property to 512.

In the run-time environment, use the following line of code to set the Options property of a Data control named data1.

data1.Options = dbSeeChanges

To create a Recordset with the Options property set to dbSeeChanges, you can use the following code segment:

Dim db As Database

Dim rs As Recordset

Set db = DBEngine.WorkSpaces(0).OpenDatabase("biblio.mdb")

Set rs = db.OpenRecordset("authors", dbOpenDynaset, dbSeeChanges)

After the Options property of the Recordset is set, you can trigger the error after the following sequence of events.

1. Two programs have the same record open in an edit mode.

2. Both programs make changes to the record.

3. One program saves its changes.

1. When the second program attempts to save its changes, the error message is triggered.

 GetRows Example Incorrect Code

The example provided with the GetRows method is incorrect. It uses the CurrentDB() function, which is not supported in Visual Basic 4.0. This function returns the "current" Database but is only applicable in Microsoft Access. The code also incorrectly codes the arguments to the OpenRecordset method. Instead of a "+" between the dbOpenSnapshot and dbForwardOnly arguments, you should code a ",". The example also uses the NorthWind example database as supplied with Microsoft Access. The corrected code is shown below:

Dim dbsCurrent As Database, rstTitle As Recordset

Dim avarRecords As Variant

Dim intFields As Integer, intRows As Integer

Set dbsCurrent = OpenDatabase("biblio.mdb")

Set rstTitle = dbsCurrent.OpenRecordset("Select * from titles", _

dbOpenSnapshot, dbForwardOnly)

avarRecords = rstTitle.GetRows(50)

intField = 0

intRecord = 0

' Use intField and intRecord as array indexes to enumerate

' the returned records, locate particular values, and so on.

Miscellaneous Jet Issues

The following list describes miscellaneous Jet issues.

The Data Access Objects (DAO) BeginTrans method (on the Database or Workspace objects) allows at most five levels of nested transactions.

Jet (Access) 2.0 databases containing more than 32 relationships cannot be converted to Jet 3.0 format and will result in the error Couldn't create index; too many indexes already defined. The solution is to remove enough indexes or relationships to allow the conversion to proceed. The maximum number of indexes is 32, and in Jet 3.0 both sides of relationships use an index.

Multiple users attempting to access Paradox tables over a network may see the error: Invalid file format. This error is caused by not having registry entries for ParadoxNetPath that point to identical drive letters and net files on both users machines.

For Access 2.0 databases with multiple sessions running on one machine (using optimistic concurrency), if two sessions try to update the same record, the second session will fail (correct behavior), but the error message will be Write conflict error which may be unclear.

Attempts to import extremely small values from text files (less than 2.225E-308) into IEEE double fields will not succeed and will return the error Conversion failure.

If a user exceeds the server lock count while running a Windows 95 client against a Novell NetWare server, the users application will not complete properly. Workarounds include:

Increase the number of locks on the server.

Open the table as 'exclusive', which should prevent locking.

Recordset Object Doesn't Support CreateDynaset Method

The Data control in Visual Basic 4.0 creates a Recordset object, whereas the Data control in Visual Basic 3.0 created a Dynaset object. The CreateDynaset method does not exist on the Recordset object. To verify this, follow this procedure.

1. Start Visual Basic; Form1 is created

2. Add a Data control (Data1) and a TextBox (Text1).

3. Set the following control properties in the Properties window:

Control Property Value

Data1 DatabaseName "BIBLIO.MDB"

Data1 RecordSource "Authors"

Data1 RecordsetType 1 - Dynaset

Text1 DataSource Data1

Text1 DataField "Author"

4. Add a CommandButton (Command1) to Form1. Add the following code to the Click event of Command1:

Private Sub Command1_Click()

Dim ds As Dynaset

Set ds = Data1.Recordset.CreateDynaset()

ds.Close

Set ds = Nothing

End Sub

5. Press F5 and click the command button; error 3251 generated.

To avoid the error, change this code to Data1.Recordset.OpenRecordset() instead of Data1.Recordset.CreateDynaset().

SystemDB Property of the DBEngine Object

Returns or sets the full path and filename of the System Database (.MDA) file.

Syntax

object.SystemDB [= value]

The SystemDB property syntax has these parts:

Part Description object

An object expression that evaluates to a DBEngine object.

value A string expression that points to a system database file (typically named "SYSTEM.MDA"). This is also known as a "workgroup file."

Remarks:  Jet provides the ability for you to define a workgroup and give varying permissions to each object in the database to different users in the workgroup. The workgroup is defined by the workgroup file, typically called "SYSTEM.MDA".

For your users to gain access to the secured objects in your database, DAO needs the location of the workgroup file that specifies the database. This can be set either by specifying it in the Windows Registry or by using the SystemDB property.

This property is only available in the 32-bit version of Visual Basic.

Using a Table Name When Addressing Fields in Recordset Objects

In Visual Basic version 3.0, you could reference the fields in a Recordset using a variety of syntax. For example, all of the following code used to reference the Title field of a Dynaset object created against the Titles table are valid:

Dim db As Database, ds As Dynaset

Set db = OpenDatabase("biblio.mdb")

Set ds = db.CreateDynaset("Select * from Titles")

Print ds(0)

Print ds!Title

Print ds("title")

Print ds.Fields("title").Value

Print ds("titles.title")

In Visual Basic 4.0, using the Table name in the string when the Dynaset, Snapshot, or dynaset-type or snapshot-type Recordset is generated from an SQL string is no longer valid.

Using CompactDatabase with Microsoft Access Databases

You should not use the CompactDatabase method when converting databases created or maintained with Microsoft Access if you expect to subsequently use them with Microsoft Access version 7.0. To convert Microsoft Access databases from one version to another, use the Compact Database menu command within Microsoft Access version 7.0.

If you use the CompactDatabase method from Visual Basic version 4.0, the database cannot be opened by Microsoft Access version 7.0, and you must revert to a backup copy and use Microsoft Access version 7.0 to perform the conversion.