problem returning a recordset from a function

[Laurent M]

Hello, I have a little VBA function which execute a SQL
query. The function should return the relevant recordset.

But i get an error when i try to get the recordset from a
query :

My function :

***************************************
Function ImportFromdb(Query As String)

Dim DBPath As String
Dim cnt As New ADODB.Connection
Dim Rst As New ADODB.Recordset

' Database path
DBPath = "C:\Documents and
Settings\Administrateur\Bureau\MyPFE\financesoftWithData.mdb"

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & DBPath & ";"

Rst.Open Query, cnt, adOpenStatic

ImportFromdb = Rst

Rst.Close: cnt.Close
Set Rst = Nothing: Set cnt = Nothing
Set Rg = Nothing
End Function
***************************************

here is the part of the sub which calls the function

Dim Rst As New ADODB.Recordset
Set Rst = ImportFromdb("SELECT *FROM table")


So my question is : What should my function return precisely?

Thanks !

 

[Jim Thomlinson]

Unless specified your function will return a Variant which can cause some
problems... Here is my version of a function to return a Recordset. The
recordset is either connected (updateable) or disconnected (not updateable)
depending on the sate of the final argument... m_cDBLocation is a constat
which is the location of the database.

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

HTH...

 

[Laurent M]

great !

thanks a lot Jim

Unless specified your function will return a Variant which can cause some
problems... Here is my version of a function to return a Recordset. The
recordset is either connected (updateable) or disconnected (not updateable)
depending on the sate of the final argument... m_cDBLocation is a constat
which is the location of the database.

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

HTH...