Check if Database is Open

[motpid]

Hi!
I have been writing a VB desktop user program and website that are using an Access XP database. I recently ran in to problems with the database not being written to as it was already opened by someone else. My problem is the following; I need to be able to check to see if the database is opened by a different portion of the program before it attempts to write any changes. There are many functions/subroutines that use the database in both the website and desktop portions. Does anyone have any suggestions for how to check this status before the connection is opened? Thanks!

This is what I have so far, the problem is, I cannot access the function that called this code. (If this makes any sense, if not please let me know.)

    Private Sub TestIfOpen()
        If Len(Trim(Dir("Z:\prntaudit\tcsss\tcsss.ldb"))) > 0 Then
            ' Yup file is there
            MessageBox.Show("Database is locked")
            dbChkTmr.Enabled = True
            intTmrChk = 0
        Else
            ' No file
            MessageBox.Show("Database is open")
            If dbChkTmr.Enabled = True Then
                dbChkTmr.Enabled = False
            End If
        End If
    End Sub
 

Thanks!
Thomas

[Dököll]

This is what I have so far, the problem is, I cannot access the function that called this code. (If this makes any sense, if not please let me know.)

    Private Sub TestIfOpen()
        If Len(Trim(Dir("Z:\prntaudit\tcsss\tcsss.ldb"))) > 0 Then
            ' Yup file is there
            MessageBox.Show("Database is locked")
            dbChkTmr.Enabled = True
            intTmrChk = 0
        Else
            ' No file
            MessageBox.Show("Database is open")
            If dbChkTmr.Enabled = True Then
                dbChkTmr.Enabled = False
            End If
        End If
    End Sub
 

Thanks!
Thomas

Hello, motpid!

I am not sure how this can be handled, just letting you know to stay tuned for added support:-)

In a bit!

Dököll

[NeoPa]

Thomas,
Your code checks the LDB file. The presence of an LDB file simply shows that the database is open. It doesn't imply it's locked. That info may be held inside the LDB file but I don't know the layout so can't help much there I'm afraid.
I'm also sorry that I didn't really understand what your question meant. I can see the sort of thing you're trying to do (from your code) but the reference to a calling function and your inability to access it made no sense to me. Sorry.

[motpid]

Let me see if I can explain my problem a little more clearly. I have created a staff management system. The manager portion allows the manager to add/delete/update student information, create schedules, print reports, etc. There is a staff website and a program to report issues as well. The website allows the staff member to view a variety of schedules and submit hours they are available to work.
The problem that I am running into is that all 3 of these separate components use a single database and all write and read from this database as well. I need to find a way to check and see if the database is locked by another program/person before it attempts to write the new changes to the database. I have had issues with 2 staff members attempting to submit times they are available at the same from the website and only one persons records are saved.
I am using VB 2005 code in all 3 programs with db connections and commands to write/read to the database. Is there a simple way to embed an IF...ELSE statement before attempting to write to the database? Or maybe there is a better, easier way?
Thanks!
Thomas

[NeoPa]

Some issues to consider then :

1. This is the Access / VBA forum. I suspect most of what you're dealing with is VB related. I would suggest that forum may be of more help. I will throw in what I can though (On your request, I can move the thread to the VBA forum for you).
2. Your code needs to ensure that the open type is always correct. Opening the database exclusively, for instance, when simply updating some data would be inappropriate. It may be that the return-code from one of the opens (database or recordset) may indicate the current state of the database or recordset. It should at least fail if it is locked and your open is correctly formatted.
3. In VBA I would do a check of the status (if the previous item doesn't help) by using On Error ... before you try to access the table. If the command fails at least then you know that it has and you can report it to the user - or maybe even prompt to try again.

I suggest that the current code you're using (including the open commands) would be helpful before continuing. I suspect this would have to be passed over to VB in that case of course.
Let's hope you can make some progress with what I've already said though.
Let us know how you get on :)