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.OpenRecordset(strDBName) and Set rsTable = db.TableDefs(strTblName).OpenRecordset()? 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).

[NeoPa]

I guess that means you're limited to Help on the web. The old system on 2003 had a decent page where I got all the information from, that I've used in this thread. Certainly both formats are covered in the same page, but the parameters for each are explained and all available values discussed. I had a quick look on MSDN and what they have there now is truly chronic. Disgraceful really.

[zmbd]

[F1]
Once help opens
Upper left corner search box
Enter terms
Use the dropdown (VERY VERY small button next to the search button... they're trying to hide this I think)
Select offline developers

You get much better help than the current online crud.
Discovered by accident and then ran accross the same information in one of the other access help sites.

>>>EDIT<<<
This will sometimes default BACK to the online search... don't know why... it just does.

I also goto the bottom right corner in the help window and click on the globe wherein I change that to "show content only from this computer" If I need the web to find the answer then I tend to do a much better search using one of the other search engines than MSOffice has provided thru their cruddy cloud vision. ... OR .... I come here :)

[Seth Schrock]

I either can't find the place that you are talking about or I don't have that option. I have two groups: online and on this computer. Under On this computer I have Access Help and Developer Reference. I chose Developer Reference and that did come up with some stuff. However it doesn't seem to be any different than what is online.

For TableDef.OpenRecordset it says that it "Creates a new recordset object and appends it to the recordsets collection."

Database.OpenRecordset says the exact same thing.

[NeoPa]

Nice Z.

Seth:
For TableDef.OpenRecordset it says that it "Creates a new recordset object and appends it to the recordsets collection."

Database.OpenRecordset says the exact same thing.

That's no big surprise. It's true for both. Does it give you the parameters and what they do? That's the acid question.

[Seth Schrock]

I can't find a difference other than Database.OpenRecordset has sample code and the other doesn't. Each have the following parameters:

Name
Type
Options
LockEdit

Both describe the parameters exactly the same.

[NeoPa]

That's exactly what you need Seth, except the TableDef version including a Name parameter (which is simply incorrect). Do you have the link for that?

[Seth Schrock]

Well, let me put it this way. It says the syntax is
expression.OpenRecordset(Type, Options) but it then continues to describe the four parameters I listed. Here are the links:
TableDef.OpenRecordset Method
Database.OpenRecordset Method

[NeoPa]

Thank you for the links.

Not impressive I'll grant you Seth, but it does include the information you need. The Syntax described at the top is accurate, even if, confusingly, it explains parameters that aren't available in that syntax. Low score for MS on their Help pages, especially after coming from a system where the quality was considerably higher, but enough to get you going using the methods. It's particularly upsetting as this will clearly effect newbies more than experienced developers, thus restricting the uptake of Access in new developers.

[zmbd]

Seth:
This should work in V2007 as well as 2010.
As you can see from the picture.... there's just a few lines of information:

When I did this (as shown) the first thing I got was a list of about 20 or so items, I clicked on the first one just to see what was there and that is what is shown in the screen shot.

The other option is in the bottom corner of the help window... a little globe.

[Seth Schrock]

That is what I clicked on Z. However, in this case I didn't get any different results. I just posted the links for NeoPa since it was the same data and so that I didn't have to do screen shots and post them.

[NeoPa]

The point here is that the links you posted were web-based, while the pic Z posted is from an internal Help program. Generally speaking, the latter is a much more reliable resource. Given the choice, most of us would use the internal system. It's certainly what I'm recommending you use if you have that option available. The software needs to be included when Access/Office is installed.