ADO

[CData]

greetings - hope all is well...
getting into an ADO environment
the question is on moving the ADO to a global mod and then retrieving data to the form object
i.e.
Phase 1: in the form event the ADO commands exists working:
Dim rs As ADODB.Recordset, cn, cmd, and other various needed to be brief
Set cn.ConnectionString to be brief
With cmd
.ActiveConnection = cn
.CommandText = "SELECT '...
End With
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
.Open cmd
End With
Me.Txt_RatingEx_PRO = rs.Fields("RatingEx").Value
--------------end Phase 1
Phase 1 is chopped down to be brief but let's say it works now....
in Phase 2 would like all the common ADO connection and query function to exist not at each form event
but in a global mod to be more efficient; so that at the form event is just:
Dim sqlString as String
sqlString = "SELECT ....
MOD_ADO.ExecuteSQL(sqlString)

the question is the method to return some data to the form event, the last line of Phase 1 in example....

as always - thanks for your time.....

[HansV]

Perhaps something like this in a standard module:

Code: Select all

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Function GetCN() As ADODB.Connection
    If cn Is Nothing Then
        Set cn = New ADODB.Connection
    End If
    If cn.State = adStateClosed Then
        cn.Open ConnectionString:="..."
    End If
    Set GetCN = cn
End Function

Function GetRS(strSQL As String) As ADODB.Recordset
    If rs Is Nothing Then
        Set rs = New ADODB.Recordset
    End If
    rs.Open Source:=strSQL, ActiveConnection:=GetCN, CursorType:=adOpenStatic, _
        LockType:=adLockReadOnly, Options:=adCmdText
    Set GetRS = rs
End Function

You can then use

Code: Select all

    sqlString = "..."
    Me.Txt_RatingEx_PRO = GetRS(sqlString).Fields("RatingEx").Value

[CData]

in T Sql what is the ? symbol do...
i.e.
ExecuteSQL "underwriting.dbo.ChangeName_EIB ?, ?"

[HansV]

The ? stand for two parameter prompts.

[CData]

huh...okay thanks...strangely difficult and ineffective attempting to google about a ? symbol....

so I did not follow your advice which is very rare indeed.... there was something about a 2nd opening of the string that made me pause and looking around more found an example where the local form rs is made available as part of the reference/call to the ADO global module, like this (in local form event):

Dim rs As ADODB.Recordset
Set rs = ModADO.ExecuteSQL("SELECT...

then you can
Me.Txt_RatingEx_PRO = rs.Fields("RatingEx").Value
………..seems to work so far...…...