9 Replies Latest reply on Mar 2, 2017 3:30 PM by Jiří Konečný

    MS Access Database conection (read)

    Jiří Konečný

      Zdravím, potřebuji poradit s nefunkčním makrem, které má do pole načíst data z databáze MS Access. Načte se pouze jeden záznam a pak skončí chybou. chyba je popsaná v programu.

       

      Hello, I need help with dysfunctional macro. I need read data from table to array, but it not works.  Macro read only one record, after that stop with error. Error is described on the code

       

      Sub NacteniPolotovary()

      Dim db As Object
      Dim swRecord As Recordset
      Dim intArraySize As Integer         'The size of your array
      Dim iCounter As Integer             'Index of the array
      Set db = OpenDatabase("C:\Data\camaxis.accdb")
      Set swRecord = db.OpenRecordset("SWx_Polotovary", dbReadOnly)
      If Not swRecord.EOF Then
          swRecord.MoveFirst
          intArraySize = swRecord.RecordCount - 1               'swRecord.RecordCount   This instruction has only 1 record, but table in MS Access has many records
          iCounter = 0
          ReDim PoleDatabaze_polotovaru(intArraySize, 6)
          Do Until swRecord.EOF
              PoleDatabaze_polotovaru(iCounter, 0) = swRecord.Fields("ProductID")
              PoleDatabaze_polotovaru(iCounter, 1) = swRecord.Fields("DruhPolotovaru")
              PoleDatabaze_polotovaru(iCounter, 2) = swRecord.Fields("Rozmer1")
              PoleDatabaze_polotovaru(iCounter, 3) = swRecord.Fields("Rozmer2")
              PoleDatabaze_polotovaru(iCounter, 4) = swRecord.Fields("Rozmer3")
              PoleDatabaze_polotovaru(iCounter, 5) = swRecord.Fields("Norma")
              PoleDatabaze_polotovaru(iCounter, 6) = swRecord.Fields("Material")
              iCounter = iCounter + 1
             swRecord.MoveNext                    'Run-time error 424:      Object required
          Loop
      End If
      swRecord.Close
      db.Close
      Set swRecord = Nothing
      Set db = Nothing

       

      Děkuji

      Thanks

        • Re: MS Access Database conection (read)
          Ivana Kolin

          zkuste pred tim swRecord.MoveFirst dat pridat swRecord.MoveLast.

           

          nebo teda :

          1. moveLast

          2. Recordcount

          3. moveFirst

            • Re: MS Access Database conection (read)
              Jiří Konečný

              Dík za rychlou odpověď.

              Pomohlo to vyřešit počet záznamů ale stále nechce skočit na další záznam jako předtím ('Run-time error 424: Object required)

              Zapsáno takto:

               

              If Not swRecord.EOF Then

                  swRecord.MoveLast

                  swRecord.MoveFirst

                  intArraySize = swRecord.RecordCount - 1

                  iCounter = 0

                  ReDim PoleDatabaze_polotovaru(intArraySize, 6)

                  Do Until swRecord.EOF

                      PoleDatabaze_polotovaru(iCounter, 0) = swRecord.Fields("ProductID")

                      PoleDatabaze_polotovaru(iCounter, 1) = swRecord.Fields("DruhPolotovaru")

                      PoleDatabaze_polotovaru(iCounter, 2) = swRecord.Fields("Rozmer1")

                      PoleDatabaze_polotovaru(iCounter, 3) = swRecord.Fields("Rozmer2")

                      PoleDatabaze_polotovaru(iCounter, 4) = swRecord.Fields("Rozmer3")

                      PoleDatabaze_polotovaru(iCounter, 5) = swRecord.Fields("Norma")

                      PoleDatabaze_polotovaru(iCounter, 6) = swRecord.Fields("Material")

                      iCounter = iCounter + 1

                      swRecord.MoveNext

                  Loop

              End If

               

              Jo a když zařadím MoveNext za MoveFirst tak tam funguje normálně.

                • Re: MS Access Database conection (read)
                  Ivana Kolin

                  recordcount je to same jako movelast

                    • Re: MS Access Database conection (read)
                      Jiří Konečný

                      Tak nakonec vyřešeno vyhozením cyklu. Děkuji za nasměrování

                       

                      Dim db As Object

                      Dim swRecord As Recordset

                      Dim intArraySize As Integer         'The size of your array

                      Dim iCounter As Integer             'Index of the array

                      Set db = OpenDatabase(AccessDB)

                      Set swRecord = db.OpenRecordset("SWx_Polotovary")

                          swRecord.MoveLast

                          swRecord.MoveNext

                          swRecord.MoveFirst

                          intArraySize = swRecord.RecordCount - 1

                          iCounter = 0

                          ReDim PoleDatabaze_polotovaru(intArraySize, 6)

                      Skok:

                          PoleDatabaze_polotovaru(iCounter, 0) = swRecord.Fields("ProductID")

                          PoleDatabaze_polotovaru(iCounter, 1) = swRecord.Fields("DruhPolotovaru")

                          PoleDatabaze_polotovaru(iCounter, 2) = swRecord.Fields("Rozmer1")

                          PoleDatabaze_polotovaru(iCounter, 3) = swRecord.Fields("Rozmer2")

                          PoleDatabaze_polotovaru(iCounter, 4) = swRecord.Fields("Rozmer3")

                          PoleDatabaze_polotovaru(iCounter, 5) = swRecord.Fields("Norma")

                          PoleDatabaze_polotovaru(iCounter, 6) = swRecord.Fields("Material")

                          iCounter = iCounter + 1

                          swRecord.MoveNext

                          If Not iCounter > intArraySize Then GoTo Skok

                      swRecord.Close

                      db.Close

                      Set swRecord = Nothing

                      Set db = Nothing

                      Exit Sub

                        • Re: MS Access Database conection (read)
                          Ivana Kolin

                          jejda, tak tohle uz je hodne osklive. Vcera jsem odpovidala jen z mobilu, takze nebyla moznost odzkouset ktere parametry jsou nejlepsi.

                          Pokud nema ta vase tabulka par desitek tisic radku tak by mohlo toto stacit.

                          Sub NacteniPolotovary()
                          Dim db As Object
                          Dim swRecord As Recordset
                          Dim intArraySize As Integer         'The size of your array
                          Dim iCounter As Integer             'Index of the array
                          Set db = CurrentDb
                          Set swRecord = db.OpenRecordset("SWx_Polotovary", dbOpenTable, dbReadOnly)
                          If Not swRecord.EOF Then
                             
                              intArraySize = swRecord.RecordCount - 1               'swRecord.RecordCount   This instruction has only 1 record, but table in MS Access has many records
                              iCounter = 0
                              ReDim PoleDatabaze_polotovaru(intArraySize, 6)
                              Do Until swRecord.EOF
                                  PoleDatabaze_polotovaru(iCounter, 0) = swRecord.Fields("ProductID")
                                  PoleDatabaze_polotovaru(iCounter, 1) = swRecord.Fields("DruhPolotovaru")
                                  PoleDatabaze_polotovaru(iCounter, 2) = swRecord.Fields("Rozmer1")
                                  PoleDatabaze_polotovaru(iCounter, 3) = swRecord.Fields("Rozmer2")
                                  PoleDatabaze_polotovaru(iCounter, 4) = swRecord.Fields("Rozmer3")
                                  PoleDatabaze_polotovaru(iCounter, 5) = swRecord.Fields("Norma")
                                  PoleDatabaze_polotovaru(iCounter, 6) = swRecord.Fields("Material")
                                  iCounter = iCounter + 1
                                 swRecord.MoveNext                    'Run-time error 424:      Object required
                              Loop
                          End If
                          
                          
                          On Error Resume Next
                          swRecord.Close
                          db.Close
                          Set swRecord = Nothing
                          Set db = Nothing
                          End Sub
                          
                            • Re: MS Access Database conection (read)
                              Jiří Konečný

                              Finální verze, sice je to téměř totožné co před tím, ale teď to funguje. Moc děkuji za radu.

                               

                              Public Const AccessDB As String = "C:\Data\camaxis.accdb"

                              Sub NacteniPolotovary()

                              Dim AccessDB As String = "C:\Data\camaxis.accdb"

                              Dim db As Object
                              Dim swRecord As Recordset
                              Dim intArraySize As Integer
                              Dim iCounter As Integer
                              Set db = OpenDatabase(AccessDB)
                              Set swRecord = db.OpenRecordset("SWx_Polotovary", dbOpenDynaset, dbReadOnly)
                              If Not swRecord.EOF Then
                                  swRecord.MoveLast
                                  swRecord.MoveFirst
                                  intArraySize = swRecord.RecordCount - 1
                                  iCounter = 0
                                  ReDim PoleDatabaze_polotovaru(intArraySize, 6)
                                  Do Until swRecord.EOF
                                      PoleDatabaze_polotovaru(iCounter, 0) = swRecord.Fields("ProductID")
                                      PoleDatabaze_polotovaru(iCounter, 1) = swRecord.Fields("DruhPolotovaru")
                                      PoleDatabaze_polotovaru(iCounter, 2) = swRecord.Fields("Rozmer1")
                                      PoleDatabaze_polotovaru(iCounter, 3) = swRecord.Fields("Rozmer2")
                                      PoleDatabaze_polotovaru(iCounter, 4) = swRecord.Fields("Rozmer3")
                                      PoleDatabaze_polotovaru(iCounter, 5) = swRecord.Fields("Norma")
                                      PoleDatabaze_polotovaru(iCounter, 6) = swRecord.Fields("Material")
                                      iCounter = iCounter + 1
                                      swRecord.MoveNext
                                  Loop
                              End If
                              On Error Resume Next
                              swRecord.Close
                              db.Close
                              Set swRecord = Nothing
                              Set db = Nothing
                              End Sub

                    • Re: MS Access Database conection (read)
                      Ivana Kolin

                      tady mate chybu:

                       

                      Set swRecord = db.OpenRecordset("SWx_Polotovary", dbReadOnly)

                       

                      syntax je:

                       

                      expression .OpenRecordset(Name, Type, Options, LockEdit)

                       

                      dbReadOnly (4) je Opton ale vy to mate jako parametr Type a to je dbOpenSnapshot

                       

                      cili spravne by melo byt

                       

                      Set swRecord = db.OpenRecordset("SWx_Polotovary",dbOpenDynaset, dbReadOnly)

                       

                       

                      RecordsetTypeEnum Enumeration (DAO)