Solved

VBA ODBC recordcount fails on some machines

Posted on 2011-09-27
9
528 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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:teylyn
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now