Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: .LDB Bummer

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    .LDB Bummer

    Those of you using Access in a network environment feel my pain... Is there any way to alert a user that the database is already open if the matching .ldb file exists? I want BIG RED FLAGS!!! I've rebuilt this particular database several times due to data corruption...

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    Just because it is networked is no reason for concern. What is of concern is how everyone access it. Is the db split into front-end/back-end or is everyone using the SAME .mdb? Exactly what is being corrupted?

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    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. <img src=/S/shrug.gif border=0 width=39 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    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! <img src=/S/grin.gif border=0 width=15 height=15>

    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
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    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.. <img src=/S/smile.gif border=0 width=15 height=15> <img src=/S/sarcasm.gif border=0 width=15 height=15>

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    <hr>I'm guessing it'd be a handsome flag at that! <hr>
    I'll bet ... I definitely want to see that screen shot <img src=/S/laugh.gif border=0 width=15 height=15>

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    do you know if this will work in '97SR2? [img]/forums/images/smilies/smile.gif[/img])

    Thanks!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    Drk:

    Erp! <img src=/S/blush.gif border=0 width=15 height=15> 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! <img src=/S/devil.gif border=0 width=15 height=15>

    ~Shane
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    <hr>Upon re-accessing the database, all tables are gone, *le pouf* Vanished! <hr>
    WHoaaa ! That is scary <img src=/S/alien.gif border=0 width=14 height=15> 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).
    <hr>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
    <hr>
    Actually looking at the code it probably wasn't Dev Ashish. Anyway maybe it will point you in the right direction.
    Have fun,
    <img src=/S/smile.gif border=0 width=15 height=15>

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    Dev Ashish is my hero!! Almost..

    For those less familiar, his site may be found at <A target="_blank" HREF=http://www.mvps.org>www.mvps.org</A> Definitely a treasure-trove of Access Information!!

    I'll check this out Brian, Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    Yup, it works; Win98 machine with Office 97 SR2 with references set to the ADO 2.0, 2.1, or 2.5 libraries - take yer pick!

    ~Shane
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    Great! [img]/forums/images/smilies/smile.gif[/img] Thanks for all your help Shane!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    New Lounger
    Join Date
    Mar 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    What I experienced under Windows 95 and Access 97 was that networked databses started getting corrupt if size exceded 100 megabytes. Maybe due to network limitations of 95 or just our own local network limitations. Splitting the databases into sizes less than 100 megabytes seemed to solve this problem. Maybe you've already solved it, but someone else might benefit from this.

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    You mention we (as in many users) and that the database is one file. Do you have one database with all of the tables, queries, forms etc or is it split into two databases? One for the tables. The other for the interface. If the answer is no then I would recommend you splitting this for the simple matter of backups.
    Second point. If there is one centralised database for the forms etc. then on which database do you make changes. If, as I suspect, you do it on that one copy then if anyone else that opens the database could be the cause of the corruption.
    If you want to only allow one person to open this centralised database at one time then why not open it in exclusive mode? No need for all that checking, let Access do it for you.

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .LDB Bummer

    All very good suggestions.. but there's a problem!

    If I split the database into two, front end, back end, The performance is severely degraded. Everything becomes a long, drawn-out process. The combination of network traffic, relational structure, and sheer number of records contribute to this.. I'm working on a backup system which will automatically backup the data as time/date each time the database is closed.. that should satisfy any problems which may occur in the future, as we'll have a few backups leading up to the problem.

    Thanks again!

    Drk.
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Page 1 of 2 12 LastLast

Posting Permissions

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