Connecting to a SQL Server CE Database
Before you can manipulate information in a database, you must open a connection to a valid data source. The Connection object is used to represent a connection to a data source. To open a connection to a data source, create a variable that represents the connection, and then create a Microsoft® ActiveX® Data Object for Windows® CE (ADOCE) Connection object by using the Set statement and CreateObject function. The following example shows how to do this:
Dim cn As ADOCE.Connection Set cn = CreateObject("ADOCE.Connection.3.1")
Note When you use the CreateObject function to create a reference to the ADOCE 3.1 control, you must include the version number. If the version number is omitted from the string, an earlier version of the control is used. If no earlier version of the control exists on the device, an error is returned. Microsoft SQL Server™ 2000 Windows CE Edition (SQL Server CE) can be accessed only through ADOCE 3.1 or later.
After a Connection object is created, you can use the properties and methods of the Connection object to open, close, and manipulate a connection. The following example shows how to open a connection to a database on the device by using the Open method:
cn.ConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=\Northwind.sdf" cn.Open
Caution You must specify the SQL Server CE provider string when you open a SQL Server CE database. If you do not specify a provider string in the Open method, Open defaults to using the proprietary Windows CE data source and creates a new Windows CE data source file named Test.sdf. This is the equivalent of specifying CEDB for the Provider property in the connection string.
In the previous sample, the connection string property is set before the Open method is executed. The Open method is used without any parameters. A connection string can also be used as a parameter of the Open method. When connecting to a SQL Server CE database, you must specify both the provider and data source properties in the connection string. The data source property must be set with the full path and database name.
Disconnecting from a Database
After you make modifications and save them to the database, close the connection to the data source. The following example shows how to use the Close method to close a connection:
cn.Close Set cn = Nothing
Note You can have only one open connection to a SQL Server CE database at a time, and this connection must be closed before starting replication or remote data access (RDA).