Hi
I have created a function that recturns a recordset. it works fine except that I can not get it to release the connection to the table. this is areal pain as I want to create new fields but I get Error3211 -
the function call is:
Set .RecADO = ASXDataSet(ASXIndex, ReturnDataFields, Period) 'returns filtered record set
.tbl.Fields.Append .tbl.CreateField("777 month STD", dbDouble)
'*********************************************************************************
the function is:
Public Function ASXDataSet(ASX_Index As String, ByRef DefineFields As clsDefineFields, Period As String) As ADODB.Recordset
'---------------------------------------------------------------------------------------
' Purpose : Return the entire row data set as an array for a particular table.
' the table must be linked to the open data base
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTableName: Name of the table that holds data
' ASX_Index: Index
' TableName: Source Table of data
' Period: Daily, weekly filte, => Weekly is taken on fridays
'---------------------------------------------------------------------------------------
Dim ASXDefineFields As New clsDefineFields
Dim strSQLCommand, strSQLFilter, strSQLOrder, strSQLFields, strSQLTable As String
Set DefineFields.RecADO = New ADODB.Recordset
With DefineFields.RecADO
strSQLFields = "Select * "
strSQLTable = "FROM [" & DefineFields.PrimaryTableName & "] "
If Period = "Daily" Then
strSQLFilter = " WHERE [ASX Index] = '" & ASX_Index & "'"
Else
strSQLFilter = " WHERE [ASX Index] = '" & ASX_Index & "' AND weekday([Date]) = 6 "
End If
strSQLOrder = "ORDER BY [Date] ASC"
strSQLCommand = strSQLFields & strSQLTable & strSQLFilter & strSQLOrder
DefineFields.RecADO.Open strSQLCommand, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set ASXDataSet = DefineFields.RecADO.Clone
End With
'ASXDefineFields.RecADO.Close
'Set ASXDefineFields.RecADO = Nothing
End Function
I have tried many ways to close the recordset but it always returns the error ": The database engine could not lock table because it is already in use by another person or process"
Any help would be much appreciated.
Ian
I have created a function that recturns a recordset. it works fine except that I can not get it to release the connection to the table. this is areal pain as I want to create new fields but I get Error3211 -
the function call is:
Set .RecADO = ASXDataSet(ASXIndex, ReturnDataFields, Period) 'returns filtered record set
.tbl.Fields.Append .tbl.CreateField("777 month STD", dbDouble)
'*********************************************************************************
the function is:
Public Function ASXDataSet(ASX_Index As String, ByRef DefineFields As clsDefineFields, Period As String) As ADODB.Recordset
'---------------------------------------------------------------------------------------
' Purpose : Return the entire row data set as an array for a particular table.
' the table must be linked to the open data base
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTableName: Name of the table that holds data
' ASX_Index: Index
' TableName: Source Table of data
' Period: Daily, weekly filte, => Weekly is taken on fridays
'---------------------------------------------------------------------------------------
Dim ASXDefineFields As New clsDefineFields
Dim strSQLCommand, strSQLFilter, strSQLOrder, strSQLFields, strSQLTable As String
Set DefineFields.RecADO = New ADODB.Recordset
With DefineFields.RecADO
strSQLFields = "Select * "
strSQLTable = "FROM [" & DefineFields.PrimaryTableName & "] "
If Period = "Daily" Then
strSQLFilter = " WHERE [ASX Index] = '" & ASX_Index & "'"
Else
strSQLFilter = " WHERE [ASX Index] = '" & ASX_Index & "' AND weekday([Date]) = 6 "
End If
strSQLOrder = "ORDER BY [Date] ASC"
strSQLCommand = strSQLFields & strSQLTable & strSQLFilter & strSQLOrder
DefineFields.RecADO.Open strSQLCommand, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set ASXDataSet = DefineFields.RecADO.Clone
End With
'ASXDefineFields.RecADO.Close
'Set ASXDefineFields.RecADO = Nothing
End Function
I have tried many ways to close the recordset but it always returns the error ": The database engine could not lock table because it is already in use by another person or process"
Any help would be much appreciated.
Ian