How to use VBA to find out if all of the tables are connected?

[Seth Schrock]

Is there a way to find out if all of the tables in the BE are connected? I would like to have a form open when the database is first opened. In its On_Load event, I want to check if all the tables are connected. If they are, then the form will close and the main form will open for the user to use the database. If the tables aren't connected, then I will use the startup form to enter the BE name and file path to be passed to my ReLink function (which is being worked on in another thread). I just need to know how to check if the tables are connected. I have gone through all the TableDef properties on MSDN, but the information is very scarce. I couldn't find anything that said "Check Connection", so I'm guessing that there isn't anything explicitly for this, but I'm also guessing that there is a way of finding out if it is connected by checking something like record count or something like that. I just don't know how things will be returned so I don't know how to check for them. I did find out that the record count for a linked table will always be -1. If the BE is disconnected, would it still return a -1 because it knows that it is a linked table or would it return something else?

Just tested my last guess and it still returned -1. Is there anything else like that that could be tested and would return a different value if not connected?

[zmbd]

The only way I've found is to attempt to open a record set on the linked table, try a record move, and trap for error.
I do this on just one linked table in the backend and assume if it fails they all do and if it passes then they all pass; however, you could do this for each attached table in the tabledefs.

[NeoPa]

I suspect any action on a table linked to air would result in a failure Seth.

It seems you have two options :

1. Access the table somehow and trap the error.
2. Identify the file name from the .Connect property (Your other recent thread has an example of how to do this,) and check for that file using Dir(). Some example code, if required, is :
   

   'Exist() returns true if strFile exists.  By default ignores folders.
   '22/05/2003 Rewritten with better code.
   '20/05/2005 Added finding of R/O, System & Hidden files.
   Public Function Exist(strFile As String, _
                         Optional intAttrib As Integer = vbReadOnly Or _
                                                         vbHidden Or _
                                                         vbSystem) As Boolean
       Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
   End Function

Clearly, you would need to process through your TableDefs using a For Each Next loop.

[Seth Schrock]

So option 2 works based on the (logical) assumption that if the BE exists where the FE thinks it is, then there is a connection? That makes sense. And I would loop through the TableDefs because it is possible that there are multiple BE files (however unlikely that would be) so each table would pass its location to the function to check for true.

I thinking of the whole picture, I see two options. If a table Exist() comes back as false, then I could either immediately jump to where I would relink all the tables, or I could add the tables to a recordset of disconnected table. I would then loop through that recordset in the ReLink function. Which would you recommend?

[zmbd]

Seth,
I would look at a "both/and" instead of an "either/or."

I've had two occasions where the DIR() will show that the file indeed exists; however, once the user tried to do anything in the backend we were sunk.

What happened is that a new Employee had the default "List Folder Contents" access rights to the directory; thus could see that file, but did not have the R/W/M.

To get around this situation, I check for the backend file using the DIR() using the stored location in the local table or which you can get as Neopa states and then proceed as in my earlier post.

[Seth Schrock]

If you are going to open the recordset and do the error trapping like you said, why would you try to find the file as NeoPa said? I understand why you don't like relying on the file being where it is supposed to be, but I don't understand why you would use Dir() if you are going to use the other method anyway.

[NeoPa]

It is certainly safer and more thorough to avoid the assumption that just becasue a file exists it is necessarily available for access.

As ReLink() takes a BE file parameter though, rather than a TableDef, I would would use it for each BE linked to rather than all TableDefs you find.

Indeed, checking for the existence of a BE makes little sense to me if you are already aware that the link is not effective for one reason or another.

[zmbd]

Short:
Because I deal with backends that have thousands of records, there's a sense of logic involved, and because I have an aversion to error trapping as a programing method to handle things that can be done so within the schema.


A little longer:
- If the file is either not there or not accessible to the user, then logically it makes no sense to attempt the record open. Instead, I inform the user that the file doesn't appear to be in the normal location and ask them if they want to take a stab at locating the file. This being the first opportunity to do so; thus, the best logical point to branch the code for a file search.

- If the file is there, then I check the links. I no-longer assume that the user will have read/write/modify rights. Nor, have I at this point needed to load any of the libraries etc... because we haven't needed to thus, smaller overhead in the pc. I do assume that if the small table is good, the big table will be good to... in the one case where I do not have a small single row table, the select record set I open is for a "TOP 1" if that fails then I assume the rest of the connections fail too. (and when that employee leaves the plant... there will be some changes made to that database - as in, it'll go away we already have that same information in the main datatables. sigh)

- Hold over from my professors. They taught that although error trapping was a good practice, it was not good practice to use it for anything that can be properly handled within the built-in structure of the engine. In this case, there is not other means of testing the actual connection within the MSA/MSO-VBA that I have found.

[NeoPa]

I certainly wouldn't argue with that approach Z, however :

1. There seems little benefit (to me) to add an element that doesn't use error trapping, if error trapping will be required at some point in the logic anyway. Access and VBA don't seem to share your POV, and there are logic holes in the system where the only viable way (I've found at least) to determine something is to smash it one and see if it breaks. Having lost that battle, there's very little point in introducing partial solutions that are purer in themselves, when they are simply part of a logic whole which has been corrupted by the error handling approach.
2. Your approach does seem a more polished approach, but seems to diverge somewhat from the subject of the question. IE. A better answer, but for the reason behind the question rather than the question itself (I would say).

That said, as long as that is understood, what you say makes sense. Personally, I'd prefer to focus on understanding the fundamental issue before fixing it up with what probably constitutes a more thorough approach. I expect though, that now Seth has that context to what you said earlier, that he can fit it into his overall understanding quite neatly.

[Seth Schrock]

Finally getting to test my knowledge of what everyone has told me. I was able to test this at work when the tables were really connected but got called away before I could move the BE to another location to break the link. I am now working on it from home which automatically put the BE in another place and now I'm getting a run-time error #52 that says "Bad file name or number". Here is my code:

Public Function Exist(Optional intAttrib As Integer = vbReadOnly Or _
                                                      vbHidden Or _
                                                      vbSystem) As Boolean
Dim strFile As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim intParam As Integer
Dim varLinkAry As Variant
 
Set db = CurrentDb()
 
For Each tdf In db.TableDefs
    With tdf
        If .RecordCount = -1 Then
           varLinkAry = Split(.Connect, ";")
           For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
           Next intParam
 
           strFile = Mid(varLinkAry(intParam), 10)
           Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
           If Exist = False Then Exit Function
        End If
    End With
Next tdf
MsgBox ("All tables are connected")
DoCmd.Close acForm, "frmSplash"
DoCmd.OpenForm "frmTest"
 
End Function
 

The error highlights line 21. I checked the value of strFile and it came up with
\\Ftcbank1\docs\sschrock\My Documents\Database Stuff\BackendTesting\Location1\BackendTesting_be.a ccdb which is correct for the location at work. What I don't get is why is this failing when the whole purpose of that line is to see if it exists?

I do plan on opening the recordset and do the error trapping, but since I have never done work on either part, I decided to tackle one step at a time and decided to start with the step that came first in the logic.

[NeoPa]

Interesting. I get the same issue, which means the code I suggested isn't reliable in all circumstances. Due to the delay in responding (Network time-out), I'm guessing that it is happy to report on the status of a file, but expects the reference to be to a valid and reachable device. As the share referred to is on a server that is not only unreachable, but also indeterminable (it can't even work out where to look for it), I'm guessing that's why it crashes rather than returns an empty string.

I would recommend handling this in the Exist() procedure. A very good reason why this procedure should be left as an autonomous unit. Mixing it up with the rest of your logic is certainly not recommended. It goes against all the rules of programming.

'Exist() returns true if strFile exists.  By default ignores folders.
'22/05/2003 Rewritten with better code.
'20/05/2005 Added finding of R/O, System & Hidden files.
'11/12/2012 Added handling of inaccessible drives.
Public Function Exist(strFile As String, _
                      Optional intAttrib As Integer = vbReadOnly Or _
                                                      vbHidden Or _
                                                      vbSystem) As Boolean
    On Error Resume Next
    Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
End Function

[Seth Schrock]

Unfortunately, I am very illiterate when it comes to rules of programming. I really wish that I didn't have to self teach myself everything as I feel that I have many gapping holes in my knowledge foundation :(

So your suggestion is to make line 21 of my code its own function? Should I put the other part of the code in the form's code or leave it as a to be called from the form which will in turn call Exist(strFile)?

I also have a question about how accurately I copied your code. Am I supposed to include lines 6, 7, and 8 or just choose one of the options?

In thinking of the whole picture, I see two options. If a table Exist() comes back as false, then I could either immediately jump to where I would relink all the tables, or I could add the tables to a recordset of disconnected tables. I would then loop through that recordset in the ReLink function. Which would you recommend?

[NeoPa]

Seth:
Unfortunately, I am very illiterate when it comes to rules of programming. I really wish that I didn't have to self teach myself everything as I feel that I have many gapping holes in my knowledge foundation :(

Maybe you could browse through Allen Browne's site (Tips for Programmers). There's a lot there to get you going.

Seth:
So your suggestion is to make line 21 of my code its own function? Should I put the other part of the code in the form's code or leave it as a to be called from the form which will in turn call Exist(strFile)?

I would suggest calling the Exist() function at that point, but your whole code seems to be within an Exist() function. It really shouldn't be in one called that or even declared that way.

Seth:
I also have a question about how accurately I copied your code. Am I supposed to include lines 6, 7, and 8 or just choose one of the options?

You would copy the whole procedure exactly as is. Those lines ensure that the default value of intAttrib is set to recognise normal files as well as any files with any of those attributes set. It normally works fine as a default. One would rarely need to specify that value themselves.

Seth:
In thinking of the whole picture, I see two options. If a table Exist() comes back as false, then I could either immediately jump to where I would relink all the tables, or I could add the tables to a recordset of disconnected tables. I would then loop through that recordset in the ReLink function. Which would you recommend?

It really depends on the structure of your database, and was handled specifically in a separate thread (where this question is more relevant). The procedure found in the other thread was designed to relink all tables found in the database that currently linked to a BE A, to a BE B. In that scenario, it makes good sense to save a list, not of linked tables, but of BEs linked to, along with the replacements for each. As I say, it all depends on the structure of your database.

[Seth Schrock]

The last part of my question was more asking what to in the part of the code that checks if e file exists: stop on the first table that comes back as does not exist or just add it to a list of unlinked tables and continue checking the other tables? My current code stops as soon as it is discovered that the table's BE file doesn't exist. I just felt this question had more to do this this side than the rekindling side. If the way that I just explained it still falls under your original understanding, I will post it in my other question.

I will try splitting the exist function out like you have it and put the other code back in the form's On_Load event.

[Seth Schrock]

Okay, I have split the Exist() function to just test if the file exists and I get the same error message on the same code. So here is what I've got:

Public Function Exist(strFile As String, _
                      Optional intAttrib As Integer = vbReadOnly Or _
                                                      vbHidden Or _
                                                      vbSystem) As Boolean
 
 
Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
 
 
End Function

Private Sub Form_Load()
Dim strFile As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim intParam As Integer
Dim varLinkAry As Variant
 
Set db = CurrentDb()
 
For Each tdf In db.TableDefs
    With tdf
        If .RecordCount = -1 Then
            varLinkAry = Split(.Connect, ";")
            For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
            Next intParam
 
            strFile = Mid(varLinkAry(intParam), 10)
            If Exist(strFile) = False Then Exit Sub
        End If
    End With
Next tdf
MsgBox ("All tables are connected")
DoCmd.Close acForm, "frmSplash"
DoCmd.OpenForm "frmTest"
 
End Sub
 

[NeoPa]

Seth:
If the way that I just explained it still falls under your original understanding, I will post it in my other question.

It's already answered in your other question, as well as here if you read what I posted. The answer isn't a simple yes/no, so maybe you're not seeing it easily.

Seth:
Okay, I have split the Exist() function to just test if the file exists and I get the same error message on the same code. So here is what I've got:

The code you have there for the Exist() function is not the fixed code I (just) posted in #11. This code was updated to handle exactly the error you've fallen over, so will certainly fall over if you use this older version.

[Seth Schrock]

Just when I think I'm learning, I miss two answers in one post. Great way to learn.

Okay, as you probably already knew, that extra line (which I missed on first reading) made the whole thing work beautifully.

Now, on to the error trapping portion. Would I be correct that I should pass the table name from the code that I already have in the form's on_load event to a separate Connected() function and do the error trapping in that?

[NeoPa]

Seth:
Just when I think I'm learning, I miss two answers in one post. Great way to learn.

There's really no need to be so hard on yourself Seth. Certainly, try to proceed carefully and attentively, but there's much here and it's quite easy to make mistakes. A gentle nudge in the right direction is all you need. You're already making visible progress. Sometimes, seeing that in onesself is the hardest thing to perceive.

Seth:
Now, on to the error trapping portion. Would I be correct that I should pass the table name from the code that I already have in the form's on_load event to a separate Connected() function and do the error trapping in that?

I'm afraid I'm not really sure which part you're talking about here. Generally error handling is done within each procedure where it's required. What errors are you interested in trapping? And which code/procedure are we considering?

[Seth Schrock]

Sorry, I was referring to Z's idea of trying to do a record move and trapping for an error. I was planning on checking for the existence of the file and then if the file existed, doing the move first/move last and trap for an error as post #5 suggested. I believe this fits what you said in post #9, but I could be wrong.

So to be clear, should I make a separate function to do the record move and trapping for an error in the move. My idea was to replace line 19 in the form's On_Load event (second code block of post #15) with something like the following:

If Exist(strFile) = True Then
     If Connected(.Name) = False Then Exit Sub
Else
     Exit Sub
End If

Connected(strTableName) would then be the separate function that tries to do the record move.

[NeoPa]

The test need only be to open the table Seth. It does make sense that the logic for all of that be encapsulated within a procedure that has all the error-handling within.

If you were to call this new procedure Connected(), as it seems is your plan, then the code you posted to illustrate is somewhat strange, however. Getting rid of unnecessary and confusing parts, this would boil down to :

If Exist(strFile) Then Call Connected(.Name)
Exit Sub

If that's what you're looking for then fine.

I would guess, now that you seem to be happy with the answers in this thread to the original question, that it would make most sense to post this part separately.

[Seth Schrock]

Opening the recordset was part of the answer to my question as given by Z in post 5. I'm just working that portion of the answer now. I am very happy with the part that tests if the database is there, I just need to finish the test of being able to open the recordset to know if I am truly connected.

[Seth Schrock]

Well, my stab at opening the recordset and trapping the error didn't work too well. I tried copying what MSDN had here, but I'm getting a Run-time error 3219: Invalid Operation on line 8 for some reason. Here is what I have:

Public Function Connected(strDBName As String) As Boolean
Dim db As DAO.Database
Dim rsTable As DAO.Recordset
 
'On Error GoTo Err_SetValue
 
Set db = CurrentDb
Set rsTable = db.OpenRecordset(strDBName, dbOpenTable)
 
Connected = True
 
Err_SetValue:
    Connected = False
 
End Function

[NeoPa]

Nothing obviously wrong with the code except line #5 is a comment and rsTable is never closed. In the circumstances, we need to know the exact value of strDBName at the time the error occurs, as well as whether or not this value (which should reflect a table of course, and not a database as the name implies) is the name of a table you expect to be valid or not. If you are testing a remote database then you need a table linked to that database in order for the test to be of use to you. This begs the question - Is it really sensible to attempt to set up a dummy link, just to test the values of a link you already have?

This all seems a little confused, if I'm honest. While technically you can continue to progress this in this thread, it is only making a long and complicated thread longer and more complicated. My original advice still holds. From experience I can say this is not making the thread any more helpful or easy to work within. Nevertheless, I'll leave it in your hands. At least for now.

[Seth Schrock]

Okay. I will split it out and put a link in here for future reference.

Edit: Error opening a recordset

Here is the link to the new thread.