Solved

VBA ODBC recordcount fails on some machines

Posted on 2011-09-27
9
542 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:Francois Koutchouk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2

Author Comment

by:Francois Koutchouk
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 2

Author Comment

by:Francois Koutchouk
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 2

Author Comment

by:Francois Koutchouk
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
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

691 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