ADO code: what is it? 3

[GSMike]

I recently posted a thread asking the difference between using linked tables and pass-through queries. JimmytheGeek gave me a great answer (thanks, jimmy).
It was commented that using pass-through queries or ADO code to connect to SQL data sources both produced better results than using linked tables.
Can anyone give me an example of ADO code? How do I know it when I see it?
Thanks for your help.
Mike Kemp

 

[jimmythegeek]

ADO (ActiveX Data Objects) is the multiple technology equivalent to DAO. You can open data through code and manipulate it even though it is in different database formats.

First, in any code window, go to Tools | References, and make sure you have checked the Microsoft ActiveX Data Objects 2.0 (or higher) Library reference.

Secondly, create a connection:

Here I am declaring a public connection object, and setting a public connection string: (you will need to change the red to connect to your SQL Server appropriate db)(The following is an example using SQL Server, you would use different connections strings to other databases such as Oracle, or DB2, or even Text Files).

Public adoCN As ADODB.Connection
Public Const ADOConnect As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASENAMEHERE;Data Source=SERVERNAMEHERE"

If you do this publically, when you open the database, you will be able to refer to the same connection throughout the application.

Here are a few sample functions using the above sample:
(Obviously, field names and the such would need to be changed, but this is mainly for example)
====================
Private Function GetCount(TableName As String, WhereStmt As String) As Integer
Dim sCount As String
Dim rsCount As ADODB.Recordset

Set adoCN = New ADODB.Connection ' Normally on initial open of application
adoCN.Open (ADOConnect) ' Normally on initial open of application

sCount = "SELECT Count(*) AS Total FROM " & TableName & " WHERE " & WhereStmt

Set rsCount = New ADODB.Recordset
rsCount.Open sCount, adoCN, adOpenForwardOnly, adLockReadOnly, adCmdText

GetCount = rsCount!Total

rsCount.Close
Set rsCount = Nothing
End Function
=====================

This sample uses the connection.Execute method for action queries:
=====================
Private Sub CmdInvoice_Click()
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim varNumber As Variant

Set adoCN = New ADODB.Connection ' Normally on initial open of application
adoCN.Open (ADOConnect) ' Normally on initial open of application

If Me.lstJobsNotInvoiced.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one job to update.", vbOKOnly + vbInformation, "No Jobs Selected"
Exit Sub
End If

For Each varNumber In Me.lstJobsNotInvoiced.ItemsSelected
strSQL = "UPDATE tblJobs SET strInvoiced = - 1 WHERE FieldName = " & Me.lstJobsNotInvoiced.ItemData(varNumber)
adoCN.Execute strSQL
Next
Me.lstJobsNotInvoiced.Requery

rst.Close
Set rst = Nothing
End Sub
====================

Another Execute example:
====================
Public Sub Aging()
On Error GoTo Aging_Err
Dim AgeSQL As String
Dim varReturn
If AgeMe Then
DoCmd.Hourglass True
varReturn = SysCmd(acSysCmdSetStatus, "Aging In Process&quot
AgeSQL = "Update tblProvBase Set CurrAct = 0 "
AgeSQL = AgeSQL & &quot;WHERE (((CurrAct) <> 0) AND ((IPAteff) Is Not Null) AND ((IPAteff) < '&quot; & DATE & &quot;'))&quot;

adoCN.Execute AgeSQL

Call ChangeAge
varReturn = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
End If
Aging_Err:
If Err.Number = 3021 Then
DoCmd.Hourglass False
varReturn = SysCmd(acSysCmdClearStatus)
Exit Sub
End If
End Sub
====================

And here is an example of getting a stored procedure:
====================
Private Sub GetStoredProcedure()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim adoCN As ADODB.Connection

Set adoCN = New ADODB.Connection ' Normally on initial open of application
adoCN.Open (ADOConnect) ' Normally on initial open of application
Set cmd = New ADODB.Command

With cmd
Set .ActiveConnection = adoCN
.CommandType = adCmdStoredProc
.CommandText = &quot;sp_SPNameHere&quot;
' Use the following two lines if you have a parameter to pass
'Set paritem = .CreateParameter(&quot;Keyword&quot;, adVarChar, adParamInput, 255, Me.txtValue)
'.Parameters.Append paritem
End With

Set rs = New ADODB.Recordset
rs.Open cmd, , adOpenStatic

' Do whatever you want here .......

Set rs = Nothing
Set cmd = Nothing
Set adoCN = Nothing
End Sub
====================

It is very important that you use the right syntax for the .Open method. You need to make sure you are opening the right kind of recordset, and using the proper locking and options to go with it. I have a good document on the .Open method that I would be happy to e-mail you if you want to contact me.

This just scratches the surface of what is capable with ADO. Get books and find examples to work from, this is the best way. Hope this helps some. Jim Lunde
compugeeks@hotmail.com
Custom Application Development