Solved

VBA ODBC recordcount fails on some machines

Posted on 2011-09-27
9
539 Views
Last Modified: 2012-06-21
The recordsets.recordcount hangs my laptop but not my desktop.
- The version of Excel are identical, 11.8341.8341 (SP3)
- The References in Excel are identical
- The database is exactly the same (SQLite)
- Same release of TCL (although I don't think it has anything to do with it)
- ODBC version is same, but the desktop may be using the 64bit version (both 32bit and 64bit are installed on that machine)
- Both machines run lots of other SQL queries that works

Code is enclosed



Sub connectDB(pathname As String)
Dim sConn As String
    
sConn = "DRIVER=SQLite3 ODBC Driver;Database="
sConn = sConn & pathname
sConn = sConn & ";LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0"

Conn2.ConnectionString = sConn
Conn2.Open
End Sub


Function QueryThreeArray(pathname As String, myQuery As String) As Variant
Dim Rs2 As New ADODB.Recordset
Dim NSFArray() As String
Dim numHits As Integer
Dim k As Integer
    
ReDim NSFArray(1, 4)
       
' pass the query
Rs2.Open myQuery, Conn2, adOpenStatic, adLockOptimistic

If Rs2 Is Nothing Then
    MsgBox "Please specify a source file that actually contains data!"
    End
End If

'  THIS HANGS ON LAPTOP, but works on DESKTOP
numHits = Rs2.RecordCount
ReDim NSFArray(numHits, 4)
Do While Not Rs2.EOF
    NSFArray(k, 0) = Rs2.Fields(0).value
    NSFArray(k, 1) = Rs2.Fields(1).value
    NSFArray(k, 2) = Rs2.Fields(3).value
    Rs2.MoveNext
    k = k + 1
Loop

QueryThreeArray = NSFArray

' cleanup
Rs2.Close
Set Rs2 = Nothing

End Function

Open in new window

0
Comment
Question by:FKoutchouk
9 Comments
 
LVL 10

Expert Comment

by:ukerandi
ID: 36715073
Go to try to create new ODBC using Control Panel ->select ODBC create new ODBC connect
then you can find out why its not working
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 400 total points
ID: 36716764
FKoutchouk,

RecordCount can be unreliable; I would recommend executing a query such as "SELECT COUNT(1) FROM MyTable" or the like to determine the number of records.

Also, keep in mind that even if you use RecordCount, it starts at 1, while for your ReDim, unless you have a module level declaration of Option Base 1, your statement:

ReDim NSFArray(numHits, 4)

Open in new window


is setting the bounds of the array at 0 To numHits, NOT 1 To numHits.  I always recommend explicitly stating the bounds of your arrays:

ReDim NSFArray(1 To numHits, 1 To 4)

Open in new window


or, if you need 0 to be the lower bound:

ReDim NSFArray(0 To numHits, 0 To 4)

Open in new window


Finally, using Integer for numHits will lead to an overflow error if there are ever 32,768+ records returned.  I recommend never using Integer, and always using Long instead.  It's been 20 years or more since every byte mattered :)

Patrick
0
 
LVL 1

Author Comment

by:FKoutchouk
ID: 36719359
Excellent, MatthewsPatrick.  Now the problem shifted further down the code (attached)

On both machines rs.fields(1).value is correct (a string) but

On one machine rs.Fields(0).value returns the correct value (a string of a filename)
On the other machine (same laptop as above), the rs.Fields(0).value returns always the same string (first value).

In other words tmp1 is never updated!




Function QueryArray(pathname As String, myQuery As String, mytable As String) As Variant
Dim Rs2 As New ADODB.Recordset
Dim NSFArray As Variant
Dim numHits As Long
Dim k As Long
Dim strCounter As String
Dim tmp1 As String
Dim tmp2 As String

strCounter = "select count(1) from " & mytable
' count the values
Rs2.Open strCounter, Conn2, adOpenStatic, adLockOptimistic
numHits = Rs2.Fields(0).value
Rs2.Close
Set Rs2 = Nothing

ReDim NSFArray(0 To numHits, 0 To 4)
       
' pass the query
Rs2.Open myQuery, Conn2, adOpenStatic, adLockOptimistic

If Rs2 Is Nothing Then
    MsgBox "Please specify a source file that actually contains data!"
    End
End If

Do While Not Rs2.EOF
    tmp1 = Rs2.Fields(0).value  ' db_id
    NSFArray(k, 0) = tmp1
    tmp2 = Rs2.Fields(1).value  ' numelements
    NSFArray(k, 1) = tmp2
    Rs2.MoveNext
    k = k + 1
Loop

QueryArray = NSFArray

' cleanup
Rs2.Close
Set Rs2 = Nothing


End Function

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36719420
That suggests to me that either the two machines are not hitting the exact same database, or that they are, but that the SQL statements are not exactly the same.

Modify your procedures to use Debug.Print to print the connection string and the SQL statement to the Immediate window, and check the output for both machines.
0
 
LVL 1

Author Comment

by:FKoutchouk
ID: 36719466
Both machines are using the same database -- when I issue the SQL query by in my SQL front-end I get the correct result.  The bizarre part is that only the first column is not getting updated by the
rs.movenext
is there a way around that?  
Thanks
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36720282
Please post the Debug.Print results for both sConn and myQuery from both machines
0
 
LVL 1

Author Comment

by:FKoutchouk
ID: 36900544
All is the same on both machines.  Except the laptop generate a s.Error.count = 2 -- something rather cryptic.  Next step is to load on new machines.... next few days.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 37412268
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question