problem returning a recordset from a function

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 !

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" wrote:

> 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 !

great !

thanks a lot Jim

"Jim Thomlinson" wrote:

> 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" wrote:
>
> > 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 !