Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count number of record of an MDB (2000 sr 1)

    In this macro is present a command to count a record, but is possible to cont the value in a signle fileds?
    For example the value present in fileds CICS:

    Sub ADO_DATE_TAB()
    Dim num As Integer
    Sheets("DB_AGENZIE").Visible = True
    Sheets("DB_AGENZIE").Select

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, n As Long
    Set cn = New ADODB.Connection
    ' **** Substitute the correct path ************
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    '**********************************************
    Set rs = New ADODB.Recordset
    rs.Open "DATE_TAB", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.Index = "CICS"
    r = 2 ' the start row in the worksheet
    Do While Len(Range("K" & r).Formula) > 0
    With rs
    If Not rs.BOF Then
    rs.MoveFirst
    End If
    rs.Seek Array(Range("K" & r)), adSeekFirstEQ
    If rs.EOF = True Then
    .AddNew ' create a new record
    'add values to each field in the record
    .Fields("CICS") = Range("K" & r).Value
    .Fields("OK") = Range("L" & r).Value

    .Update ' stores the new record
    End If
    End With
    r = r + 1 ' next row
    Loop

    Debug.Print n & " records added."

    num = rs.RecordCount
    Range("J2") = num

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    Sheets("DB_AGENZIE").Visible = False

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: count number of record of an MDB (2000 sr 1)

    Are you referring to these lines?

    num = rs.RecordCount
    Range("J2") = num

    If that is correct, what exactly would you like instead?
    1) The number of records for which CICS is not blank (empty), or
    2) The number of unique values in the CICS field, or
    3) Something else - if so, what?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count number of record of an MDB (2000 sr 1)

    1) The number of records for which CICS is not blank (empty)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: count number of record of an MDB (2000 sr 1)

    Try this:

    ...
    Debug.Print n & " records added."
    ' close the recordset
    rs.Close
    ' open another recordset
    rs.Open "SELECT Count(CICS) As Cnt FROM DATE_TAB", cn, adOpenKeyset, adLockOptimistic, adCmdText
    ' get the count
    Range("J2") = rs!Cnt
    ' close this recordset
    rs.Close
    Set rs = Nothing
    ...

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count number of record of an MDB (2000 sr 1)

    ONLY A WORD...
    YOU KNOW;-)

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count number of record of an MDB (2000 sr 1)

    Guru!

    But is possible to use this piece of your new code to count the record in the field SERVIZIO?

    Sub IMPORTA_CDI_50()
    ThisWorkbook.Activate
    Set ELENCO = Worksheets("L0785_CDI_50")
    CONT = FirstFree("L0785_CDI_50", "A", 6)
    Dim NomeDB As String

    NomeDB = gPROVADatabasePath

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
    Dim OggettoConnessione As Object, OggettoRecordset As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = CreateObject("ADODB.Recordset")
    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from CDI_50")

    Do While Not OggettoRecordset.EOF

    ID = OggettoRecordset("SERVIZIO")
    Set Found_ID = Sheets("L0785_CDI_50").Columns("S:S").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then
    ELENCO.Range("A" & Trim(Str(CONT))).Value = OggettoRecordset("DATA_CONT")
    ELENCO.Range("B" & Trim(Str(CONT))).Value = OggettoRecordset("DIP")
    ELENCO.Range("C" & Trim(Str(CONT))).Value = OggettoRecordset("COD_BATCH")
    ELENCO.Range("D" & Trim(Str(CONT))).Value = OggettoRecordset("C_C")
    ELENCO.Range("E" & Trim(Str(CONT))).Value = OggettoRecordset("NOMINATIVO")
    ELENCO.Range("F" & Trim(Str(CONT))).Value = OggettoRecordset("CAUS")
    ELENCO.Range("G" & Trim(Str(CONT))).Value = OggettoRecordset("DARE")
    ELENCO.Range("H" & Trim(Str(CONT))).Value = OggettoRecordset("AVERE")
    ELENCO.Range("I" & Trim(Str(CONT))).Value = OggettoRecordset("VAL")
    ELENCO.Range("J" & Trim(Str(CONT))).Value = OggettoRecordset("SPORT_MIT")
    ELENCO.Range("K" & Trim(Str(CONT))).Value = OggettoRecordset("ANOM")
    ELENCO.Range("L" & Trim(Str(CONT))).Value = OggettoRecordset("DESCR")
    ELENCO.Range("M" & Trim(Str(CONT))).Value = OggettoRecordset("CRO")
    ELENCO.Range("N" & Trim(Str(CONT))).Value = OggettoRecordset("ABI")
    ELENCO.Range("O" & Trim(Str(CONT))).Value = OggettoRecordset("CAB")
    ELENCO.Range("P" & Trim(Str(CONT))).Value = OggettoRecordset("PAG_IMP")
    ELENCO.Range("Q" & Trim(Str(CONT))).Value = OggettoRecordset("NR_ASS") * 1
    ELENCO.Range("R" & Trim(Str(CONT))).Value = OggettoRecordset("MT")
    ELENCO.Range("S" & Trim(Str(CONT))).Value = OggettoRecordset("SERVIZIO")
    ELENCO.Range("T" & Trim(Str(CONT))).Value = OggettoRecordset("NOTE_BOU")
    ELENCO.Range("U" & Trim(Str(CONT))).Value = OggettoRecordset("SPESE")
    ELENCO.Range("V" & Trim(Str(CONT))).Value = OggettoRecordset("DATA_ATT")
    ELENCO.Range("W" & Trim(Str(CONT))).Value = OggettoRecordset("COD")
    ELENCO.Range("X" & Trim(Str(CONT))).Value = OggettoRecordset("NOTA_LIB")
    CONT = CONT + 1
    End If
    OggettoRecordset.MoveNext

    Loop

    Call ORD_ASS_CDI_50

    Range("A7").Select

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: count number of record of an MDB (2000 sr 1)

    You should be able to figure that out yourself now. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count number of record of an MDB (2000 sr 1)

    Solved in a minute...
    Sorry...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •