Solved

VBA ODBC recordcount fails on some machines

Posted on 2011-09-27
9
535 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

810 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