HandsOn Executing a Pass Through Query Saved in Access

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Execute_PassThroughQuery procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Execute_PassThroughQuery() Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Dim strConnect As String strConnect = "Provider=SQLOLEDB;" & _

"Data Source=Julitta733\JKDesktop1;" & _ "Initial Catalog=Northwind;" & _ "User Id=sa;" & _ "Password="

Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection Set cmd = New ADODB.Command

Set cmd = cat.Procedures("French Customers").Command Set rst = cmd.Execute

Debug.Print "--French Customers Only--" & vbCrLf _ & rst.GetString

Creating and Manipulating Databases with ADO

Set rst = Nothing Set cmd = Nothing Set cat = Nothing End Sub

The procedure begins by building a connection string to the SQL Server database. This is a standard connection that uses the native OLE DB SQL Server Provider (SQLOLEDB). This connection requires that you also provide the name of the SQL Server (Data Source), the name of the database from which to retrieve records (Initial Catalog), and the security context with which to log in (UserId, Password). If you connect to your SQL Server database using the NT integrated security, your connection string will look like this:

strConnect = "Provider=SQLOLEDB;" & _ "Data Source=yourServerName;" & _ "Integrated Security=SSPI;" & _ "Initial Catalog=Northwind"

Because the pass-through query you want to execute has been saved in the Access database, you need to open the ADOX Catalog object to access its Procedures collection. The following line of code specifies the name of the query you want to execute and assigns it to the Command object:

Set cmd = cat.Procedures("French Customers").Command

To execute a pass-through query that returns records, you need to use the Recordset object in addition to the Command object. The following statement executes the pass-through query:

Set rst = cmd.Execute

The pass-through query executes on the server. To quickly view data on the client machine, we retrieve the contents of the recordset by using the GetString method:

Debug.Print "--French Customers Only--" & vbCrLf _ & rst.GetString

0 0

Post a comment