|
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
Hi Drk:
Reading your posts I must say Jim asks the question that’s been bothering me, in what way is your database being corrupted ! When split (even when shared) access works quite well with multiple users. We currently run with the db backend on a Win2K server with multiple users accessing and updating and haven’t seen a record corruption yet. Prior to splitting the backend we ran multiple users on peer to peer with a single db and again no problems. Is your database split? What version and SR are you using ? What is the nature of the corruption ? If you really want to restrict changes to a single user, you could turn on record locking but I’m not sure I really understand your predicament. 
Err…..
I would go in to make a simple change to a report, say, changing a text field from this to that. While attempting to save these changes, I would get an error message, stating that the database was in an unrecognized format. I would then get another error stating that Microsoft Access could not close the database, at which time, the unrecognized database error appeared again, then the couldn’t close error, round and round we go until program termination.
Upon re-accessing the database, all tables are gone, *le pouf* Vanished! When I clicked on the ‘Tables’ tab, I got more errors.
The [censored][censored][censored][censored] who programmed the database used only macros to accomplish the functionality. Macros record extra things into the code, making the code unstable. I believe I have fixed this problem by deleting the macros and hard-coding the objects in the database. (it’s much more stable now…)
I believe the cause of this corruption was not in the use of macros to code the database, but also in the multiple user environment. The entire database is one file, again, to minimize confusion if data changes of any kind occur.
We’re all using O97SR2.
Hope that breaks it down a bit.. 
Drk.
[indent]
Upon re-accessing the database, all tables are gone, *le pouf* Vanished!
[/indent] WHoaaa ! That is scary 
It’s no wonder you want to backup the tables, who wouldn’t. But I agree the problem likely doesn’t have anything to do with code Macro or otherwise. It almost sounds like prior to your recreating the .mdb the database was split and links to the backend were disappearing. That would certainly make the data appear to be lost when viewed from a frontend. I have seen the “Unrecognized database format” error occur when there are serious network problems or power failure while actually writing to a record (but for me at least that’s very rare). Sometimes running JETCOMP.EXE will recover otherwise copying the tables to a new database will generally work. Having said that, storing the data in a backend on a secure and stable server is definitely the preferred method for security and performance reasons.
Anyhow; sorry for the tangent. I do have this piece of code I got from somewhere (I think Dev Ashish but I’m not sure).[indent]
Option Compare Database
‘ Declare a record type to break down the user info
Private Type UserRec
bMach(1 To 32) As Byte ‘ 1st 32 bytes hold machine name
bUser(1 To 32) As Byte ‘ 2nd 32 bytes hold user name
End Type
Private Sub Form_Open(Cancel As Integer)
Me.LoggedOn.RowSource = WhosOn()
End Sub
Private Sub OKBtn_Click()
DoCmd.Close A_FORM, “frmLoggedOn”
End Sub
Private Sub UpdateBtn_Click()
Me.LoggedOn.RowSource = WhosOn()
End Sub
‘————————————————————————————-
‘ Subject : WhosOn()
‘ Purpose : Will read *.LDB file and read who’s currently
‘ logged on and their station name.
‘
‘ The LDB file has a 64 byte record.
‘
‘ The station name starts at byte 1 and is null
‘ terminated.
‘
‘ Log-in names start at the 33rd byte and are
‘ also null terminated.
‘
‘ I had to change the way the file was accessed
‘ because the Input() function did not return
‘ nulls, so there was no way to see where the
‘ names ended.
‘————————————————————————————-
Private Function WhosOn() As String
On Error GoTo Err_WhosOn
Dim iLDBFile As Integer, iStart As Integer
Dim iLOF As Integer, i As Integer
Dim sPath As String, x As String
Dim sLogStr As String, sLogins As String
Dim sMach As String, sUser As String
Dim rUser As UserRec ‘ Defined in General
Dim dbCurrent As Database
‘ Get Path of current database. Should substitute this code
‘ for an attached table path in a multi-user environment.
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
sPath = dbCurrent.Name
dbCurrent.Close
‘ Iterate thru dbCurrent.LDB file for login names.
sPath = Left(sPath, InStr(1, sPath, “.”)) + “LDB”
‘ Test for valid file, else Error
x = Dir(sPath)
iStart = 1
iLDBFile = FreeFile
Open sPath For Binary Access Read Shared As iLDBFile
iLOF = LOF(iLDBFile)
Do While Not EOF(iLDBFile)
Get iLDBFile, , rUser
With rUser
i = 1
sMach = “”
While .bMach(i) 0
sMach = sMach & Chr(.bMach(i))
i = i + 1
Wend
i = 1
sUser = “”
While .bUser(i) 0
sUser = sUser & Chr(.bUser(i))
i = i + 1
Wend
End With
sLogStr = sMach & ” — ” & sUser
If InStr(sLogins, sLogStr) = 0 Then
sLogins = sLogins & sLogStr & “;”
End If
iStart = iStart + 64 ‘increment to next record offset
Loop
Close iLDBFile
WhosOn = sLogins
Exit_WhosOn:
Exit Function
Err_WhosOn:
If Err = 68 Then
MsgBox “Couldn’t populate the list”, 48, “No LDB File”
Else
MsgBox “Error: ” & Err.Number & vbCrLf & Err.Description
Close iLDBFile
End If
Resume Exit_WhosOn
End Function
[/indent]
Actually looking at the code it probably wasn’t Dev Ashish. Anyway maybe it will point you in the right direction.
Have fun,
Hi Drk:
Well, it sounds like you have a network problem. Splitting a database should improve not degrade performance, theory being, the local ‘fe’ is handling all of the grunt work and only retrieving required record information from the server ‘be’. The persistent corruption you speak of also points to network problems. But, I agree, you need to solve the immediate stability problem first. Good luck. 
Though I’m guessing the corruption issue is seperate from multiple users accessing the .MDB at the same time, here is a function stolen wholesale from Susan Sales Harkins writing in the Nov. 2000 issue of Inside MS Access published by Element K. It looks to see what devices have a user lock in the .LDB file. It will return, in order, if called from the immediate window: the COMPUTER_NAME, the LOGIN_NAME, a CONNECTED flag (-1 = true), and a SUSPECTED_STATE flag (-1 = true, null = false).
When you call the function from your PC, your name will show up as having a lock record in the .LDB. If yours is the only name, nobody else is in the db. So, you could examine the resulting recordset’s count property, if it’s >1, somebody else is connected, and you can raise that big, red flag! Judging by your ability to create great icons, I’m guessing it’d be a handsome flag at that! 
HTH, and here’s the code from Ms. Harkins.
~Shane
~~~~~~~~~~~~~~~~~~~~~
Global Const JET_SCHEMA_USERROSTER = _
“{947bb102-5d43-11d1-bdbf-00c04fb92675}”
Sub ReturnUserRoster()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
cnn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=Your path and file name here;”
Set rst = cnn.OpenSchema(adSchemaProviderSpecific _
, , JET_SCHEMA_USERROSTER)
Debug.Print rst.GetString
Set rst = Nothing
Set cnn = Nothing
End Sub
[indent]
I’m guessing it’d be a handsome flag at that!
[/indent] I’ll bet … I definitely want to see that screen shot 
do you know if this will work in ’97SR2? 
)
Thanks!
Drk.
Drk:
Erp! 
It makes use of ADO’s schema recordsets, so I’d guess no in 0ffice 97. Unless perhaps you set a reference to the ADO 2.x library? Dunno..but I have an unsuspecting user with ’97SR2 that I’ll go pester and see if that does do the trick! 
~Shane
Great! Thanks for all your help Shane!
Drk.
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
