Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Records (VB6 / MS Access)

    This is a really strange one that I haven't encountered before, so if anyone has any ideas they'd be appreciated!

    I have a VB6 front end that is using an MS Access 97 database for it's data storage. Within the app, there is a search screen that allows you to find saved data. You can then click the search results pane which takes you to the 'Order details' page.

    While monitoring the database, I noticed that a record I had just added in the system had not appeared in the database. The record appeared in the search screen and I could click on it and view the data in the Order Details screen, but it was not in the database!! First question - how could I view the data if it isn't in the database?? The app is simply querying the database tables to display it's info!

    In the app, the record I had just added was given an ID of 5029 (An autonumber field from the Access db) which was displayed in the search screen. When I shut the app and reloaded it, the record did not appear in the search screen so I created another order and the next available ID was 5030. Even though record 5029 was not added to the database, the ID had been used?

    So, how is my app displaying and allowing the user to interact with data that has not actually been added to the database and which disappears when the app is closed & reloaded? I am using DAO transactions to control the addition of records to the main details tables - could this affect the data in the above ways?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing Records (VB6 / MS Access)

    <hr>The record appeared in the search screen and I could click on it and view the data in the Order Details screen, but it was not in the database!! <hr>
    What exactly do you mean by "not in the database"? Aren't the search screen and the orders details screen pulling data from the database? It sounds like the record was created but wasn't saved, and you will need to examine your code to see what went wrong.

    As for the autonumber, if a record is started but not saved, the autonumber is still incremented and the next record gets the next number, not the one that had been assigned to the discarded record.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records (VB6 / MS Access)

    Charlotte,

    This is the thing that's driving me crazy. The search screen just builds some SQL from user input and opens a recordset over the 'Order_Details' table. The results of the SQL are then loaded into the results pane.

    Record 5029 appeared in the results pane so it has been returned from the SQL run over the database, indicating to me that the record has been saved. But, when I went into Access and looked at the table, the record was not there! How is it possible for the SQL to return a record in the app that's not there in table view? If the record has not been saved, surely the SQL should not pick it up?

    I used a single transaction to control the update routine (BeginTrans, Update the recordset, CommitTrans, nothing fancy). Could the workspace have this effect?

    The WS object is released when the app is closed, this is the only thing I can think of that is being destroyed at this point, perhaps explaining why the data vanishes between app loads. Have you ever encountered any strange behaviour when using Workspaces?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing Records (VB6 / MS Access)

    Actually, in Access there are two copies of the database, the one on the drive and the one in RAM at the moment. The two copies aren't always in agreement since the local version updates the hard drive based on the refresh rate or when a refresh is forced on the DBEngine. I wonder if the data is getting written to the RAM copy but not written back to the actual database. What code are you using to instantiate the workspace and db objects?
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records (VB6 / MS Access)

    That sounds good & makes sense, I didn't realise it could occur like that! I'm creating global objects when the app is loaded, I use a single routine to create the objects...

    Private Function OpenDatabase(DB_Database As Database, sDatabaseName As String) As Boolean

    On Error GoTo MainOpenError

    ' // Create workspace if one does not exist
    If (g_WSControl Is Nothing) Then
    Set g_WSControl = DBEngine.Workspaces(0)
    End If

    ' // Set the database
    Set DB_Database = g_WSControl.OpenDatabase(DataLocations.NetworkData basePathName & "" & sDatabaseName, False, False)
    OpenDatabase = True

    Exit Function

    MainOpenError:
    If Len(sDatabaseName) = 0 Then
    MsgBox "Error opening database - no database name supplied. Please check INI File", vbInformation, "Error Occurred"
    End
    ElseIf (Err.Number = 3045) And (sDatabaseName = DataLocations.NetworkDatabaseName) Then
    MsgBox "The MACC database is being compacted and is not currently available." & Chr$(13) & "Please try again in 5 minutes", vbExclamation, "Database compacting"
    End
    Else
    MsgBox "Error occurred opening database(" & sDatabaseName & ")" & Chr$(13) & Err.Description, vbCritical, "Could not access " & sDatabaseName
    End If

    End Function

    So, the g_WSControl is just the [DEFAULT_WORKSPACE] for the client PC.

    Oh, and just to make things more interesting - this isn't a persistent error. It only seems to happen every now & then, but it's too serious an error for me to roll the app out!

    Thanks again

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Missing Records (VB6 / MS Access)

    It sounds like your network might have an occasional hiccup, and Access is extremely sensitive to that. Are you testing the object to make sure it's still valid anywhere in your code? It might not be a bad idea, and you might also want to force a refresh on the cache using DBEngine.Idle dbRefreshCache. Try a g_WSControl.CommitTrans dbForceOSFlush as well to refresh the lazy-write cache.

    I would recommend you take a look at MSKB article Q186278, since you're working with Access 97. It was aimed at A97 and VB5, but since you're working with the older DBEngine, it probably applies in your case as well.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records (VB6 / MS Access)

    Our network can be flaky at times & we are in the middle of migrating from 3xNovell 5.1 to IP4700 so this could well explain the hiccups.

    I think I'll give the dbForceOSFlush and dbRefreshCache flags a go, they sound like they could solve my issues.

    Thanks again for your help

Posting Permissions

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