Results 1 to 13 of 13
  1. #1
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7

    Database seems to need time before recently added rows are recognized

    Good morning. First off, this is my first post here, so please excuse (help) me if I didn't do things the best way. Second, I'm definitely NOT a novice at Office/VBA (including MS Access of course). I've been using it for over 20 years, which doesn't make me anything more than experienced. I'm well-aware that I do NOT know everything. Please note that I HAVE tried to work around this in several different ways. Fundamentally, I don't believe I'm doing anything wrong (technically that is; of course we all have our own way of going about things).

    I've recently come across a situation that has me seriously scratching my head. Using standard stuff (AccDB back-end for DATA and AccDB front-end for everything else), I have a fairly simple procedure that writes records to an ADO recordset (table in the back-end). Specifically, it's doing a "Dir" command to load file-system entries into a table for subsequent processing. Now here's the problem: Immediately after writing those records to the database, if I use "DCount" to get the number of records, I'll get 0 returned even though there ARE actually records present. Furthermore, WAITING a few seconds MAY clear it up. I actually noticed what turns out to be this issue MONTHS (maybe YEARS) ago, but only recently decided to dig into it (which I believe reduces the chances of "Office Repair" or "SFC" etc clearing it up). My first thought was that I'd need to also use ADO to get the record-count from the back-end table (since DCount is relying on the front-end linked table), but that did NOT clear the issue up.

    In order to NOT have to provide too much code, I stripped things down and combined them in order to be able to demonstrate the issue. Basically, DCount("*","Directory_Files") SHOULD return the number of records in the table, but it doesn't do so reliably. Rarely, but sometimes, it WILL be correct immediately. Most of the time it will be correct after a little bit of time (sometimes a second or 2 of waiting is enough but other times waiting 60 seconds isn't enough). I'm pretty sure bringing the table LOCAL (to the FRONT-end AccDB) DOES fix the issue, but fundamentally I'd rather it be in the back-end with the rest of the data, plus that doesn't resolve the underlying issue (so it could pop-up elsewhere in a place where moving the table isn't an option).

    Remember, this was originally written quite awhile back ... I've dropped some of the extraneous/unrelated stuff from the code and inserted some things to help troubleshoot, but HAVE confirmed that this still behaves the same (works, fails, etc).

    Thanks in advance for your thoughts, ideas, questions, etc ... Mark

    Code:
    Sub zzzTemp3_Directory(Path, _
                           Optional Pattern As String = "*.*", _
                           Optional UseLocalConnection As Boolean)
    On Error GoTo ErrorHandler
    
    
    'Do the equivalent of a DOS Dir command and place the results in a table.
    
    
    Const kThisProcedure = "zzzTemp3_Directory"
    
    
    Dim cn                                 As New ADODB.Connection
    Dim rs                                 As New ADODB.Recordset
    
    
    Dim WRK_Counter
    Dim WRK_Counter_Files
    Dim WRK_Counter_SubDirectories
    Dim WRK_FileDateTime
    Dim WRK_FileLength
    Dim WRK_Name
    Dim WRK_Type
    
    
       If UseLocalConnection Then
          cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=<Fully-qualified-back-end.AccDB>"
          cn.Execute "Delete From Directory_Files"
          rs.Open "Directory_Files", cn, adOpenForwardOnly, adLockPessimistic
       Else
          CheckConnection p_cn '(p_cn is a global/public ADODB.Connection; CheckConnection just makes sure it is valid; using the local vs public connection did not isolate anything for me)
          p_cn.Execute "Delete From Directory_Files"
          rs.Open "Directory_Files", p_cn, adOpenForwardOnly, adLockPessimistic
       End If
          
       WRK_Counter = 0
       
       WRK_Name = Dir(Path & "\" & Pattern, vbDirectory)
       
          Do While WRK_Name <> ""
       
          If Left(WRK_Name, 1) <> "." Then
          
             WRK_Counter = WRK_Counter + 1
          
             WRK_FileDateTime = FileDateTime(Path & "\" & WRK_Name)
             WRK_FileLength = FileLen(Path & "\" & WRK_Name)
             WRK_Type = GetAttr(Path & "\" & WRK_Name) And vbDirectory
          
             'The entry returned was a DIRECTORY.
             If WRK_Type Then
             
             'The entry returned was a FILE.
             Else
             
                WRK_Counter_Files = WRK_Counter_Files + 1
          
                rs.AddNew
                rs("Path") = IIf(Len(Path) > 255, Left(Path, 252) & "...", Path)
                rs("FileName") = IIf(Len(WRK_Name) > 255, Left(WRK_Name, 252) & "...", WRK_Name)
                rs("Modified") = IIf(WRK_FileDateTime < #1/1/1900#, #1/1/1900#, WRK_FileDateTime)
                rs("Length") = WRK_FileLength
                rs("Current") = Now()
                rs.Update
                   
             End If
           
          End If
          
          WRK_Name = Dir
          
          DoEvents
           
       Loop
          
       Debug.Print kThisProcedure & " DCount(*,Directory_Files) = " & DCount("*", "Directory_Files")
       Debug.Print kThisProcedure & " RecordCount(Directory_Files) = " & RecordCount("Directory_Files") '(RecordCount uses ADO to get the count; neither method worked or failed 100% of the time)
       
       GoTo CleanUp
    
    
    ErrorHandler:
    
    
       Select Case Err.Number
          Case Else
             Dim vErrorReply
             vErrorReply = MsgBox(Err.Description, vbAbortRetryIgnore, "Error " & Err.Number & " (Abort = Stop)")
             If vErrorReply = vbAbort Then Stop
             If vErrorReply = vbIgnore Then Resume Next
             If vErrorReply = vbRetry Then Resume
       End Select
    
    
    CleanUp:
    
    
       rs.Close
       If Not rs Is Nothing Then Set rs = Nothing
       If Not cn.State = adStateClosed Then cn.Close
       If Not cn Is Nothing Then Set cn = Nothing
       
    End Sub


  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Any particular reason why you are using an ADO recordset and not DAO? Assuming the Directory_Files table is linked (based on the attempt to use dCount on it), here is how I would have tried to do it:
    Code:
    Sub zzzTemp3_Directory(Path, _
                           Optional Pattern As String = "*.*", _
                           Optional UseLocalConnection As Boolean)
    On Error GoTo ErrorHandler
    
    
    
    
    'Do the equivalent of a DOS Dir command and place the results in a table.
    
    
    
    
    Const kThisProcedure = "zzzTemp3_Directory"
    
    
    
    
    'Dim cn As New ADODB.Connection
    Dim rs As DAO.Recordset 'New ADODB.Recordset
    Dim db as DAO.Database
    
    
    Dim WRK_Counter
    Dim WRK_Counter_Files
    Dim WRK_Counter_SubDirectories
    Dim WRK_FileDateTime
    Dim WRK_FileLength
    Dim WRK_Name
    Dim WRK_Type
    
    
    Set db=CurrentDb
    'clear table
    db.Execute "Delete * From Directory_Files;",dbFailOnError
    Set rs=db.OpenRecordset("Directory_Files",dbOpenDynaset)
    
    
    
    
    
    
    '   If UseLocalConnection Then
    '      cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=<Fully-qualified-back-end.AccDB>"
    '      cn.Execute "Delete From Directory_Files"
    '      rs.Open "Directory_Files", cn, adOpenForwardOnly, adLockPessimistic
    '   Else
    '      CheckConnection p_cn '(p_cn is a global/public ADODB.Connection; CheckConnection just makes sure it is valid; using the local vs public connection did not isolate anything for me)
    '      p_cn.Execute "Delete From Directory_Files"
    '      rs.Open "Directory_Files", p_cn, adOpenForwardOnly, adLockPessimistic
    '   End If
          
       WRK_Counter = 0
       
       WRK_Name = Dir(Path & "\" & Pattern, vbDirectory)
       
          Do While WRK_Name <> ""
       
          If Left(WRK_Name, 1) <> "." Then
          
             WRK_Counter = WRK_Counter + 1
          
             WRK_FileDateTime = FileDateTime(Path & "\" & WRK_Name)
             WRK_FileLength = FileLen(Path & "\" & WRK_Name)
             WRK_Type = GetAttr(Path & "\" & WRK_Name) And vbDirectory
          
             'The entry returned was a DIRECTORY.
             If WRK_Type Then
             
             'The entry returned was a FILE.
             Else
             
                WRK_Counter_Files = WRK_Counter_Files + 1
          
                rs.AddNew
                rs("Path") = IIf(Len(Path) > 255, Left(Path, 252) & "...", Path)
                rs("FileName") = IIf(Len(WRK_Name) > 255, Left(WRK_Name, 252) & "...", WRK_Name)
                rs("Modified") = IIf(WRK_FileDateTime < #1/1/1900#, #1/1/1900#, WRK_FileDateTime)
                rs("Length") = WRK_FileLength
                rs("Current") = Now()
                rs.Update
                   
             End If
           
          End If
          
          WRK_Name = Dir
          
          DoEvents
           
       Loop
          
       Debug.Print kThisProcedure & " DCount(*,Directory_Files) = " & DCount("*", "Directory_Files")
    '   Debug.Print kThisProcedure & " RecordCount(Directory_Files) = " & RecordCount("Directory_Files") '(RecordCount uses ADO to get the count; neither method worked or failed 100% of the time)
       
       GoTo CleanUp
    
    
    
    
    ErrorHandler:
    
    
    
    
       Select Case Err.Number
          Case Else
             Dim vErrorReply
             vErrorReply = MsgBox(Err.Description, vbAbortRetryIgnore, "Error " & Err.Number & " (Abort = Stop)")
             If vErrorReply = vbAbort Then Stop
             If vErrorReply = vbIgnore Then Resume Next
             If vErrorReply = vbRetry Then Resume
       End Select
    
    
    CleanUp:
    
    
       rs.Close
       If Not rs Is Nothing Then Set rs = Nothing
    '   If Not cn.State = adStateClosed Then cn.Close
    '   If Not cn Is Nothing Then Set cn = Nothing
       set db=Nothing
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7
    The main reason I generally use ADO is for consistency. I'm retired now, but while I was working, connected to SQL Server a lot. ADO (as I recall) was either preferred or required (can't immediately recall). Ultimately, is there any reason why the code as it was written should fail?

  4. #4
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7
    I also recall seeing something along these lines ...

    https://answers.microsoft.com/en-us/...a-cc2f766179ef

    Honestly, I bet DAO will be around as long as MS Access, so I might have to reconsider ... But that ultimately will require me to analyze a lot of stuff (to make sure this same issue hasn't crept in elsewhere), which is why I'm still curious as to what's going on or where I went wrong. Thanks, Mark

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I have not used ADO much but I would think this line
    Code:
    cn.Execute "Delete From Directory_Files"
    would fail as it doesn't include the *.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    @Gicu - That syntax would work in SQL Server.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Mark, I've been using DAO with Access front-ends for over 20 years with MySQL, Oracle, SQL Server and Access back-ends with no problems; I did use ADO in few instances (mainly when using SaveToFile\LoadFromFile to save files in BLOB fields), but for most cases I found DAO easier to use.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7
    "*" is optional. Works either way.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Then I would say the cursor type you used is the culprit.
    You're using adOpenForwardOnly and according to MS it does not support bookmarks:
    https://learn.microsoft.com/en-us/sq...l-server-ver16

    After you call the AddNew method, the new record becomes the current record and remains current after you call the Update method. Since the new record is appended to the Recordset, a call to MoveNext following the Update will move past the end of the Recordset, making EOF True. If the Recordset object does not support bookmarks, you may not be able to access the new record once you move to another record. Depending on your cursor type, you may need to call the Requery method to make the new record accessible.
    https://learn.microsoft.com/en-us/sq...l-server-ver16

    So try changing the cursor type to adOpenKeyset or maybe issue a Requery on the recordset object and close the connection before using the DCount.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7
    Thanks for the input Vlad. Using adOpenKeyset, it acts the same (hit and miss). I'm still looking into a couple other items and will report back once I have additional info. Thanks for your time. Mark

  11. #11
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7
    All I've got to say is "hmmmm" .. I ran another series of tests, and unfortunately the waters got a little murkier. Using a table in a "back-end" AccDB that's linked to the front-end, both of the following should have the same result:

    --> ADODB Connection: cn.Execute "Delete From <TheTable> Where <TheClause>"
    --> DoCmd.RunSQL "Delete From <TheTable> Where <TheClause>"

    And they do .. SOMETIMES! If I put DCount("*", "<TheTable>", "<TheClause>") immediately after the "delete" statement, the result isn't always 0.

    This leads me to believe I have something odd going on within my own configuration, but I'm not having any (good) luck locating it. I putzed a little with the ADODB reference, but changing that (2.8 --> 6.1) didn't make a difference (that I was able to notice). The fact that I can't find anyone complaining about the same type of thing is what makes me feel like I'm on an island. And since the code DOES work, even if only some of the time, I don't have an obvious oversight relative to a missing tick in the Where clause, etc.

    For now it seems to be stable when NOT using the ADODB syntax above and I've put some alerts in place if/when things change or something odd happens, but I'm still pretty well baffled since I cannot find anything to indicate that what I've done shouldn't work ... It's almost as if there's a delay between when the code tells the DB to do something and when the DB engine actually completes it, but that's a total grasp ...

    If anyone has any ideas or thoughts, etc, please share them. Thanks, Mark

  12. #12
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If you're working with ADODB, better use the command object. It works fine, is flexible and also returns the number of affected recs .

    Code:
    Dim lngRecsAffected As Long
    Dim cnn As New ADODB.Connection
    Dim MyCmd As New ADODB.Command
    
    
    Set cnn = CurrentProject.Connection
    Set MyCmd.ActiveConnection = cnn
    MyCmd.CommandType = adCmdText
    MyCmd.CommandText = "delete from MyTable"
    MyCmd.Execute lngRecsAffected

  13. #13
    MStepan is offline Novice
    Windows 11 Office 365
    Join Date
    Mar 2023
    Posts
    7
    Thanks for the note Noella. 2 things: First, the Execute method of the Connection object can also return the number of rows affected (according to the documentation; I didn't try it because it isn't needed for my purposes). Second, using Cmd.Execute didn't change my results. Rats. But thanks for your time and consideration. I think I'm going to use one of my other computers to see if the issues follow or stop. At least then I'll have more info to go on. Thanks again, Mark

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2020, 10:50 AM
  2. Replies: 2
    Last Post: 03-28-2019, 08:18 AM
  3. Replies: 7
    Last Post: 10-10-2017, 12:06 PM
  4. Adding the Date & Time record added
    By jo15765 in forum Access
    Replies: 2
    Last Post: 11-26-2010, 11:31 PM
  5. Replies: 4
    Last Post: 01-29-2009, 02:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums