Solved

VBA ODBC recordcount fails on some machines

Posted on 2011-09-27
9
538 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

791 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