Missing Records (VB6 / MS Access)

Topic

New Reply

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

Reply | Quote

Replies

[indent]

---

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!!

---

[/indent] 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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.NetworkDatabasePathName & “” & 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote