COUNT RECORD IN MDB...

[sal21]

HOW,
I have an .mdb with a table TOTALE i would want to count the numer of the record and put the result in A2 of a sheet1
this is the path
e:\prova.mdb

 

[WillR]

Look Sal,

I don't want to try to follow you around the internet from one board to another trying to find where you buggered off to next.

I posted a solution that works

here

http://www.ozgrid.com/forum/showthread.php?t=25917

and here

http://www.vbaexpress.com/forum/showthread.php?t=1250

Can you please cross-reference your cross-posts so that people don't waste their time on an issue already solved elsewhere. Thanks. It is only common courtesy.... a bit like ringing 5 taxi companies & pissing off in the first one that gets there.

Here's the code

Option Explicit 
 
Sub GetRecordCount() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stDB As String, stSQL As String 
    Dim stConn As String 
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
    Dim i As Integer 
     
     'Instantiate the ADO-objects.
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = wbBook.Worksheets(1) 
     
     'Path to the database.
    stDB = "C:\Will\Prova.mdb" 
     
     'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
    & "Data Source=" & stDB & ";" 
     
     'The raw SQL-statement to be executed.
    stSQL = "SELECT DATA_CONT FROM TOTALE" 
     
    With cnt 
        .Open (stConn) 'Open the connection.
        .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
    End With 
     
    With rst 
        .Open stSQL, cnt 'Create the recordset.
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
        i = rst.RecordCount 
    End With 
     
    With wsSheet 
        .Cells(2, 1).Value = i 
    End With 
     'clean up
     
    rst.Close 
    Set rst = Nothing 
    cnt.Close 
    Set cnt = Nothing 
    Set wbBook = Nothing 
    Set wsSheet = Nothing 
     
End Sub

 

[sal21]

Look Sal,

I don't want to try to follow you around the internet from one board to another trying to find where you buggered off to next.

I posted a solution that works

here

http://www.ozgrid.com/forum/showthread.php?t=25917

and here

http://www.vbaexpress.com/forum/showthread.php?t=1250

Can you please cross-reference your cross-posts so that people don't waste their time on an issue already solved elsewhere. Thanks. It is only common courtesy.... a bit like ringing 5 taxi companies & pissing off in the first one that gets there.

Here's the code

Option Explicit 
 
Sub GetRecordCount() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stDB As String, stSQL As String 
    Dim stConn As String 
    Dim wbBook As Workbook 
    Dim wsSheet As Worksheet 
    Dim i As Integer 
     
     'Instantiate the ADO-objects.
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = wbBook.Worksheets(1) 
     
     'Path to the database.
    stDB = "C:\Will\Prova.mdb" 
     
     'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 
    & "Data Source=" & stDB & ";" 
     
     'The raw SQL-statement to be executed.
    stSQL = "SELECT DATA_CONT FROM TOTALE" 
     
    With cnt 
        .Open (stConn) 'Open the connection.
        .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
    End With 
     
    With rst 
        .Open stSQL, cnt 'Create the recordset.
        Set .ActiveConnection = Nothing 'Disconnect the recordset.
        i = rst.RecordCount 
    End With 
     
    With wsSheet 
        .Cells(2, 1).Value = i 
    End With 
     'clean up
     
    rst.Close 
    Set rst = Nothing 
    cnt.Close 
    Set cnt = Nothing 
    Set wbBook = Nothing 
    Set wsSheet = Nothing 
     
End Sub

... i am sorry but in this case i would want a quicly result ... Only for this.
For the pizza, you know, 4 pizza for you, for your patience

 

[WillR]

Wanting a quick result is fair enough. That does not have to stop you from being courteous to people who offer you free help - it's really easy to x-reference your posts.