Exploring Multiple Active Record Sets with MS Access - Connecting to SQL 2005 using ADO
(Page 2 of 4 )
Create a blank MS Access application. Since it is a blank application there are as yet no objects in this database. Create a new form from design and save it with the name Martian. To the load event of the form add the code shown here. The recordsets are assigned by the connection's execute method, and are available for reviewing.
Private Sub Form_Load()
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=HODENTEKMYSORIAN;" _
& "Database=Northwind;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open Dim recordsaffected As Integer
Set rst1 = con.Execute("SELECT OrderDate, OrderID FROM Orders where
OrderDate>'5/5/1998'", recordsaffected, adCmdText)
Set rst2 = con.Execute("SELECT ProductID, ProductName, UnitPrice
FROM Products", recordsaffected, adCmdText)
Set rst3 = con.Execute("SELECT UnitPrice, OrderID From [Order Details]",
recordsaffected, adCmdText)
Set rst1 = con.Execute("SELECT OrderDate,OrderID FROM Orders where
OrderDate>'5/5/1998'", recordsaffected, adCmdText)
'------Place code from next section to populate a list box-----
'--------------------------------------------------------------
MsgBox ("Number of Columns in the rst1 recordset is = " &
rst1.Fields.Count)
MsgBox ("Number of Columns in the rst2 recordset is = " &
rst2.Fields.Count)
MsgBox ("Number of Columns in the rst3 recordset is = " &
rst3.Fields.Count)
con.Close
Set con = Nothing End Sub
Connection String
The SQLNC connection string has several components to it. The provider now is called SQLNCI, short for SQL Native client, the new API alluded to in the introduction. The server is the SQL 2005 server (the instance name is Mysorian) on the machine Hodentek. The database is Northwind as described earlier. The security is Windows integrated and the DataType Compatibility is set to 80; this is necessary so that the ADO data types map correctly to the new data types of the SQLNCI. Of course if you want to access Multiple Active Recordsets (MARS), you want this argument (MARS Connection) to be assigned a value true.
After providing this string you may open the connection, and this connection will support opening multiple record sets at the same time. The results from these can be used independently, or in any other fashion within the context of a single batch.
Next: Executing SQL Queries on the Open Connection >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
/ 6 
