Pass ADODB To Word

Jason Keats

2009-07-26 03:58:29 UTC

Post by Derek Hart
I am converting an Access application where I would pass an adodb recordset
over to Microsoft Word. Now in dotnet I am creating the recordset, and
passing it to Word, but it only stay in memory for a short time. As soon as
I used the recordset, it is gone. It is like Access just passed the
recordsets by value, but in dotnet it holds onto them and closes them. Here
is some code I use to create the recordset, and then I pass 4 recordsets as
parameters into MS Word. Better way to do this so the recordsets stay in
cn = New ADODB.Connection
cn.Open("Provider=SQLOLEDB.1;Data Source=delllaptop2;Initial
Catalog=igen;integrated security=SSPI")
rsFormName = New ADODB.Recordset
rsDataName = New ADODB.Recordset
rsDataItemDef = New ADODB.Recordset
rsDataItemDefDataPreparation = New ADODB.Recordset
sql = "exec dbo.spFormNameIDWithFieldsChooseSel " & GroupID
rsFormName.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic)
sql = "exec dbo.spDataPreparationDataNameSel " & GroupID & ",'"
& RepQuote(FormNameID) & "'"
rsDataName.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic)
sql = "exec dbo.spDataItemDefFromGroupIDSel " & GroupID
rsDataItemDef.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic,
ADODB.LockTypeEnum.adLockOptimistic)
sql = "exec dbo.spDataPreparationListFieldSel " & GroupID & ",'"
& RepQuote(FormNameID) & "'"
rsDataItemDefDataPreparation.Open(sql, cn,
ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
objWord.Run(macroname:="FillMergeFields", varg1:=rsFormName,
varg2:=rsDataName, varg3:=rsDataItemDef,
varg4:=rsDataItemDefDataPreparation)
Worked great loading this data into Word from Access, but dotnet wants to
hold onto and control the arguments. Any ideas would be appreciated as to
what the best way to do this might be...

I can't actually find a question in the above, but it sounds like you
probably need to try using disconnected recordsets.

A function like the following might help...

Public Function GetRS(ByVal sSQL As String) As ADODB.Recordset

Dim rs As New ADODB.Recordset = Nothing
Dim oCnn As New ADODB.Connection

With oCnn
.Mode = ADODB.ConnectModeEnum.adModeReadWrite
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.ConnectionTimeout = 15
.CommandTimeout = 90

.Open(msConnection) 'your connection string
End With

With rs
.ActiveConnection = oCnn

.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.CacheSize = 32

.let_Source(sSQL)

'.Properties("Update Criteria").Value =
ADODB.ADCPROP_UPDATECRITERIA_ENUM.adCriteriaAllCols
.Properties("Update Criteria").Value =
ADODB.ADCPROP_UPDATECRITERIA_ENUM.adCriteriaKey

.Open(, , , , ADODB.CommandTypeEnum.adCmdText)

.ActiveConnection = Nothing 'disconnect recordset
End With

If Not oCnn Is Nothing Then
If oCnn.State <> ADODB.ObjectStateEnum.adStateClosed Then
oCnn.Close()
End If
End If

Return rs
End Function

I've cobbled this together from various places, just to illustrate the
point - so it's untested. If you're only going to use the above for
reporting, then you could change adModeReadWrite and delete the "Update
Criteria".

HTH