473,558 Members | 2,923 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem returning a recordset from a Sub in vba

Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query works fine, but
passing the resulting recordset back to the sub's caller is not working

out.
Things I tried:
I attempted creating a Function that returns the recordset.
I tried passing (ByRef) a recordset into a sub, and having the sub
populate the record set.
The problem is that the recordset IS correctly created and populated in

the Function or Sub but it's empty when passed back to the caller!! I'm

guessing this is a scoping problem, but I just don't see it.
I'm using DAO.DBEngine to make the database connection.
Here is an abridged version of the program, showing only the portions
that would be of interest in solving my problem
Public QueryResult As Recordset 'Global recordset declaration
Const Dim SQLQUERYCPFB as String = "<some known valid sql query here>"
Const Dim USER as String = "<the usernanme>"
Const Dim PWD as String = "<the password>"
Const Dim TODBCDSN As String = "LCCM TEST Database" 'Data Source name
Const Dim TQDATABASE As String = "martt2" 'test database name
Const Dim SQLQUERYFIRSTPA RT As String = "<a piece of a query>"
Const Dim SQLQUERYNODATE As String = "<a piece of a query>"
'Dummy starting point
Public Sub Main
MakeOLR xxx, yyy
End Sub
'Do a bunch of stuff with Excel spreadsheets (not shown here) then make

a query via a subroutine call
Public Sub MakeOLR(WkbPath AndName As String, AccountID As Integer)
' -----------------------------S*NIP OUT LOTS OF EXCEL
CODE--------------------------*---
'Query the Database requesting data on today's loans for the
account the user selected
Dim Account as String
Account = "<some partial query>" 'gotten from excel sheets
QueryLoans Account, QueryResult 'call sub that does the query
passing a piece of a query and the recordset to be populated
QueryResult.Mov eFirst
Dim cNr As Integer, cusip As String ' test variables to see if the

recordset has something in it
cNr = QueryResult.Fie lds.Count ' what I find is 0. should be 11
cusip = QueryResult.Fie lds(0) ' what I find is an empty string
should be a 10 character string
End Sub
Public Sub QueryLoans(Acco unt As String, QueryResult as Recordset)
Dim ws As Workspace
Dim conn As Connection
'ODBC Direct workspace which will host a connection to the
database using USER, PWD
Set ws = DAO.DBEngine.Cr eateWorkspace(" *", USER, PWD, dbUseODBC)
'Create a database connection.
Dim str as String
str = "ODBC;DSN=" & TODBCDSN 'for testing
Set conn = ws.OpenConnecti on(TQDATABASE, dbDriverNoPromp t, True,
str)
'Compose the query for today's loans
Dim DatabaseDate As String
DatabaseDate = "2005-03-07" 'TESTING ONLY
'SQLQUERYFIRSTP ART and QLQUERYNODATE are fixed strings defined in
the Const / Global section
Query = SQLQUERYFIRSTPA RT & Account & SQLQUERYNODATE & "'" &
DatabaseDate & "'"
'Now make the query
Set QueryResult = conn.OpenRecord set(Query, dbOpenDynamic) 'obtain a

result in passed in recordset
'Here we test to see if ther is something interesting in the
recordset. The result is that there is a reasonable value for cNr (11)
and for cusip (a short string representing a real database value). I do

it twice to see if the read is somehow destructive. It isn't
Dim cNr As Long, cusip As String
QueryResult.Mov eFirst
cNr = QueryResult.Fie lds.Count
cusip = QueryResult.Fie lds(0)
cNr = QueryResult.Fie lds.Count
cusip = QueryResult.Fie lds(0)
End Sub
So to reiterate:
The connection to the database inside Sub "QueryLoans " goes fine.
Inside Sub "QueryLoans " the recordset QueryResult (which is both Global

and is passed as a parameter)yield s reasonable results. But inside Sub
MakeOLR, the recordset results in a .Count of 0 and a .Fields(o) of "",

both of which are incorrect.
The QueryResult recordset (I presume) is passed in ByRef, which is the
default way VBA passes parameters. On top of that The QueryResult
recordset is declared Global. But somehow, it gets reset when Sub
QueryLoans returns!!!!
I get similar behavior when I rewrite QueryLoans(...) as a function
that returns a recordset.
As of now, I'm stumped.
Thanks in advance for any advice!
Lenny Wintfeld
ps- the database I'm connecting to is an Oracle database (using
ODBCDirect). I post the problem here because the connection to the
database goes smoothly; it's the handling of the returned recordset
that's the problem. And it seems here's the place where Database VBA
and ODBC experts hang out.... -L.

Nov 13 '05 #1
6 6521
For starters, it looks like you're mixing up Functions and Subs. Subs
are UNABLE to return a value. So trying to return a recordset from a
sub is never going to work. You could create a function that takes
whatever arguments you need, and then connect to the Oracle DB (using
ADO if you want) and then returning a recordset.

Public Function OracleRecordset (arglist) As Recordset
dim rs as adodb.recordset
....
OracleRecordset = rs
rs.close
set rs = nothing
End Function

Start there and see what happens.

Nov 13 '05 #2
lenny wrote:
Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query works fine, but
passing the resulting recordset back to the sub's caller is not working
out.
Things I tried:
I attempted creating a Function that returns the recordset.
I tried passing (ByRef) a recordset into a sub, and having the sub
populate the record set.


I'm not an expert yet, but I looked at a sample Subroutine where I pass
a recordset (different use though) and noticed that I also passed the
name of the database object variable used to create the recordset.

Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenDynaset)
....
Call FillForm(cbxInd ex.Text, MyRS, MyDB)

In my case it allowed me to preserve the recordset. Note: I didn't
need to use a ByRef. Maybe just the database object going out of scope
caused your problem. I hope this helps.

James A. Fortune

Nov 13 '05 #3
I found the problem and fixed it. I'm working in an environment where
the DAO and ADO libraries are both available. If I qualify the
declaration of the Recordset variable (which is an object that's
available in both libraries) the problem goes away. For instance Dim
QueryResult As DAO.Recordset or Dim QueryResult As ADODB.Recordset . In
my case, I completely rewrote the problematic code to use ADO, and
qualified all ADO objects with ADODB and class objects as "Set
<classobjectvar iable> = New ADODB.<classObj ectName>.

Nov 13 '05 #4
On 30 Mar 2005 07:05:27 -0800, "lenny" <lw*******@libe rtycorner.net> wrote:
I found the problem and fixed it. I'm working in an environment where
the DAO and ADO libraries are both available. If I qualify the
declaration of the Recordset variable (which is an object that's
available in both libraries) the problem goes away. For instance Dim
QueryResult As DAO.Recordset or Dim QueryResult As ADODB.Recordset . In
my case, I completely rewrote the problematic code to use ADO, and
qualified all ADO objects with ADODB and class objects as "Set
<classobjectva riable> = New ADODB.<classObj ectName>.


Well, you may have made it work, but I'll bet it still has problems. In the
code you presented, you created DAO objects, but did not properly tear them
down, then you tried to use the recordset in a procedure after its connection
object went out of scope.

If you are doing something similar in ADO, you can also expect strange
results. The only time you should ever try to use a recordset after its
database or connection instance is no longer being maintained is if you make
it a disconnected ADO recordset.
Nov 13 '05 #5
I agree with Steve. I used to use this function all the time to get a
table-type recordset based on a table regardless of whether it was
local or a linked table:

Function ExtRecordset(st rTableName As String) As Recordset
'
''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' ''
Dim db As Database
Dim strConnect As String

Set db = CurrentDb()

strConnect = db.TableDefs(st rTableName).Con nect
If Len(strConnect) = 0 Then ' this is _not_ an
attached table
Set ExtRecordset = db.OpenRecordse t(strTableName, dbOpenTable)
Else ' this is a linked
table

Set db = DBEngine.Worksp aces(0).OpenDat abase(Mid(strCo nnect,
InStr(1, strConnect, "=") + 1))
Set ExtRecordset = db.OpenRecordse t(strTableName, dbOpenTable)
End If

End Function

While this works, the database object that the recordset is a child of
should go out of scope when the function exits. Logically this should
foul up the returning recordset object but it doesn't. I stopped using
it because it made me very afraid...

Bruce

Nov 13 '05 #6
Thanks for your replies and critiques. In my ADO version I declared the
connection object global; though I I could have narrowed the scope some
and declared it in the caller.

Another alternative I guess would have been to invent a new class to do
the all the database nitty gritty work and then declare an instance of
the class in the caller....

But the current formulation seems ok for a small program.
-Lenny

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1721
by: david | last post by:
Hi I have written code in ASP for paging records from the database (SQL Server 2000). The real problem I have around 10,000 records and it tries to fetch all the records everytime (I'm saying because its take a lot time to display it). Even though, it displays all the data correctly and you can also navigate through links. Is it possible...
4
5310
by: Eli Sidwell | last post by:
Trying to return a Recordset to an ASP and the Recordset is empty. The StorredProc works in the query analyzer and it even works from a quick VB app that I wrote to test it. The storedproc that I am using is fairly complex (creates some temporary tables and populates them with 'Insert Into Select ...', but the during testing the only Select...
4
1899
by: Ola Tuvesson | last post by:
I'm having a really weird problem. When running the SP below in query analyzer the calculated column "Subscribed" is returned as expected: ------------- CREATE PROCEDURE get_mailinglists( @intCustomerID AS int ) AS
5
3626
by: msprygada | last post by:
I am having a problem with getting a recordset to fill with data in an Access Data Project from a SQL Server database. Here is the code example that is in the Access help files that I can get to work just fine: Dim Cnxn As ADODB.Connection Dim rstEmployees As ADODB.Recordset Dim strCnxn As String Dim strSQLEmployees As String
6
4975
by: Karl Stankiewicz | last post by:
Does any know how to return a recordset object from COM+ component written in C# so that it can be used as a Recordset in classic ASP? I have the frame component written i'm just not sure what object/class i should return. I tried returning SqlDataReader and ASP 3.0 found it to be ADO type data but i'm not sure how to use this return type...
4
1422
by: Win | last post by:
Dear All, I am going to change the coding from ASP & VB6 to ASP.Net and VB.Net. However, there is no data retrieved after I changed the coding. Is there anything wrong? Thanks ================================================ VB6 coding
22
12434
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source=" & msDbFilename moConn.Properties("Persist Security Info") = False moConn.ConnectionString = msConnString moConn.CursorLocation = adUseClient...
1
3556
by: vinodkus | last post by:
I M BEGINNER IN ASP I WANT TO RETURN TOTAL RECORDS FROM A TABLE. THERE ARE TWO FORMS CLASS1.ASP AND CLASS2.ASP THROUGH FIRST FORM I JUST POST THE NAME OF TABLE SO I M WRITING THE CODE OF CLASS2.ASP <!--#Include File = "Include/iecon.inc"--> <html> <head> <meta http-equiv="Content-Language" content="en-us">
3
6638
rsmccli
by: rsmccli | last post by:
Access 2002 Hi. I have a command button that will "approve" all records currently being looked at by an "approver". For some reason, even though there are multiple records that exist in the recordsetclone, EOF is returning true. I think this may have something to do with the sort order of the underlying query, but I'm not sure; at any rate, I...
0
7629
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7593
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6183
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5455
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3602
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1164
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
869
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.