Error opening a recordset

[Seth Schrock]

My goal is to see if I can open a table that is linked to another Access database so that I can test to make sure that the connection does exist and doesn't need reconnected to a different BE or just another location. It was suggested to first check if the file existed where I thought it should be and then see if I can open the recordset. If both come back true, then I'm good. Otherwise if one comes back false, then I need to reconnect to the BE. I have the code to test if the file exists and I now just need to figure out how to see if I can open the recordset.

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

Line #5 is commented out so that I can see what the error is since otherwise it just skips to line #12. strDBName = tblTest which is the only table that I currently have in this test database (the name is correct).

[Seth Schrock]

Never mind, I just fixed it.

Public Function Connected(strDBName As String) As Boolean
Dim db As DAO.Database
Dim rsTable As DAO.Recordset
 
On Error GoTo Connected_Err
 
Set db = CurrentDb
Set rsTable = db.OpenRecordset(strDBName)
rsTable.Close
 
Connected = True
 
Connected_Exit:
    Set rsTable = Nothing
    Set db = Nothing
    Exit Function
 
Connected_Err:
    Connected = False
    Resume Connected_Exit
 
End Function
 

I removed the , dbOpenTable from line 8 and fixed my terrible attempt at handling errors.

[NeoPa]

I still want to know why the parameter is named strDBName? OpenRecordset() doesn't deal with databases. If you're dealing with a table then dbOpenTable shouldn't be causing any issues. If it is, then you have something else wrong that needs looking at.

In the circumstances, we need to know the exact value of strDBName if we're to determine what is still wrong with the code that removing dbOpenTable hides away. Also, is this value the name of a table you expect to be valid or not?

[Seth Schrock]

I can't say what I was thinking naming it strDBName. Probably because of working with the Exist function so much. I will rename it strTblName so that it makes more sense.

Currently strDBName resolves to tblTest (as stated in the first post below the code block) and it is the name of the linked table that I'm trying to test.

[NeoPa]

Seth:
Currently strDBName resolves to tblTest (as stated in the first post below the code block) and it is the name of the linked table that I'm trying to test.

I do try not to make obvious mistakes like that Seth. It was a well-posted question and I missed some of the important information :-(

I assume that when using this value, your original code (with dbOpenTable) failed every time, even when the DB it was linked to should have been valid? This surprises me as that's not how it should behave according to my understanding.

[Seth Schrock]

No problem. I had missed that information in the other thread and this question was almost identical to it. I just didn't want you to think that I was ignoring your request from the other thread.

And you are correct, it errors out even if the link is fine and I can manually open the TableDef. I'm guessing from the error message that the problem isn't the link but my syntax. However I can't figure out what is wrong with it (not surprising) and you would know more about that than me. The reason I removed the dbopentable was that I read in my book how to open a recordset and that part was missing so I tried it without it and it worked. That is also how I figured out the problem with my error trapping.

[NeoPa]

There was absolutely nothing wrong with your error trapping Seth. You now just have a more verbose version of the same thing (essentially). Many would recommend that such things are done visibly and explicitly in order to be clear what is going on, but the previous version should work fine. The only missing element from the version in the other thread was closing the newly opened recordset on success, which is now handled fine.

As for the table opening code, I would consider using :

Set rsTable = db.TableDefs(strTblName).OpenRecordset()

This is a variant specifically for opening tables from the table object itself.

[Seth Schrock]

The problem with my error trapping in the OP was that the Err_SetValue code was processed even if there was no error. The biggest difference is that I now have the Exit Function prior to the Connected_Err: code so if there is no error the function will end prior to reaching the Connected = False.

I also just realized that since the error that I was receiving before was a run-time error, it had to be value oriented not syntax oriented. Otherwise it would have been a compile error. Is my understanding of this correct?

And your code work perfectly. I am new to working with Recordsets in VBA (at least with having an understanding of it). What is the difference between Set rsTable = db.OpenRecordse t(strDBName) and Set rsTable = db.TableDefs(st rTblName).OpenR ecordset()? I know there has to be one. You said the later "is a variant specifically for opening tables from the table object itself." What is the other for? I assume yours is more what I need in this case?

[NeoPa]

My best advice is to check through the Help page Seth (Type OpenRecordset then press F1). This can be run from a Database or Connection (where, clearly, some type of table or query still needs to be specified) or a TableDef or QueryDef object where this is already specified so needn't be included as a parameter.

BTW You're quite right about the Error trapping code. I overlooked that aspect. The Exit Sub would indeed have been necessary for it to work correctly.

Seth:
I also just realized that since the error that I was receiving before was a run-time error, it had to be value oriented not syntax oriented. Otherwise it would have been a compile error. Is my understanding of this correct?

That makes sense to me. I cannot state categorically that it is always so, but I would certainly consider it to be a good indication at least.

[Seth Schrock]

F1 didn't tell me much as the .Recordset() came up with the same page on both commands and TableDefs didn't tell me much either. Oh well, maybe I will come across it reading my Programmer's Reference Guide (if I ever get to it).