Overflow error with ODBC link in large table

[bobmac-]

I have an ODBC connection from an ACCESS database to a MYSQL database. One of the tables contains just over 5 million records. If I try to count the records using the code below, I get a message box with 'Overflow' text and an OK button.

If I try to display the records by double clicking the table name, I get some records displayed but no count in the record selector at the bottom of the display. If I move the right hand scroll down I get more records. If I navigate to the last record I do end up with the total number of records. It looks like the dataset view can handle looking at some of the records.
Is there some environmental setting or parameter I can change or can you suggest an alternative approach?

Private Sub cmbOverallUsage_Click()
On Error GoTo Err_cmdOverallUsage_Click
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim counter As Integer
Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM variableLog", cnn, adOpenKeyset, adLockOptimistic
counter = rst.RecordCount
MsgBox "Count= '" & counter & "'"


Exit_cmdOverallUsage_Click:
Exit Sub
Err_cmdOverallUsage_Click:
MsgBox Err.Description
Resume Exit_cmdOverallUsage_Click

End Sub

 

[pbaldy]

If you look in help for data types, you'll find that Integer can only handle numbers to 32,767. Try Long.

 

[boblarson]

This is your problem:

Dim counter As Integer

In case you didn't know, an Integer can only handle slightly over 32,000. You will need to use a LONG.

 

[boblarson]

If you look in help for data types, you'll find that Integer can only handle numbers to 32,767. Try Long.

Darn! Beat me to the punch

 

[bobmac-]

Thanks fellas. The count works now.

 

[boblarson]