I have a function that works ALMOST always works

[OhioSteve]

How can I bring the results of a query into the vba world??

For years I have used a connection string. So I tell the .mdb to connect to itself. This ALMOST always works. But about one out of every 400 times, problems happen.

Is there another way to get the results of a query?? Here is a code sample:

Public Function test()

'Create some objects and primitives.
Dim rs As New ADODB.Recordset
Dim localConn As New ADODB.Connection
Dim counter As Integer
counter = 0
Dim allComments As String
allComments = ""

'Open the LOCAL connection.
localConn.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\acmeSales\sales1.mdb; Jet OLEDB:System database=C:\acmeSales\acme.mdw; User ID=Admin;Password=j@w123"
   
'Configure the recordset object, and fill it with records.
With rs
    .ActiveConnection = localConn
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    .Open "Select comments from salesTbl"
End With

'Iterate through the recordset building a string.
Do While counter < rs.RecordCount
    allComments = allComments & ", " & rs(0).value
    rs.Move 1
    counter = counter + 1
Loop

'Display the string
MsgBox allComments
                
'Clean up
localConn.Close

End Function

 

[JoeAtWork]

So I tell the .mdb to connect to itself.

You don't have to create a new connection object, there's one already available for the currently open MDB file:

CurrentProject.Connection

 

[JoeAtWork]

Also just noticed how you are doing that loop. I recommend not using the unpredictable RecordCount property, rather do something like:

If Not (rs.BOF And rs.EOF) Then
  Do While Not rs.EOF
    allComments = allComments & ", " & rs(0).value
    rs.MoveNext
  Loop
End If

 

[OhioSteve]

That's an intresting comments. How is it unpredictable?

 

[HarleyQuinn]

If you don't go to the last record in the RS before you use recordcount (to force it to 'see' all of the returned records) it can often return 0 even though there are records in the RS.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JoeAtWork]

I have found that the accuracy of RecordCount depends on several factors: database provider, cursor location, cursor type. Most of the time doing a MoveLast will populate the recordset so that RecordCount is accurate, but I'm not sure that even that is 100% accurate in all cases.

Anyways, checking BOF and EOF is more efficient than doing a MoveLast and then a MoveFirst to start at the top again.

 

[HarleyQuinn]

Joe - Totally agree, even MoveLast (while better) isn't 100% (and was used only as an example to illustrate your point about inaccuracy of .RecordCount). As you say, EOF and BOF are infinitely better ways to achieve the loop.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.