Refer to a table using VBA

[AndyS48UK]

Anyone know how I refer to a table using VBA?

There doesn't seem to be a Tables collection and OpenRecordset creates a recordset I believe.

What I'm actually doing is trying to refer to a table so that I can write records to it from a from. The form records are partially generated from another query.

Thanks

 

[The_Doc_Man]

Your problem is partly conceptual.

Yes, OpenRecordset opens a recordset. What you don't realize is that recordsets have "flavors" - including a Table-type recordset.

When you open a table (say in datasheet view), you don't really open the table. You open a table-type recordset that feeds a default form generated for that table based on a datasheet template. The reason a query in datasheet view looks so similar is because Access is doing exactly the same thing, except it is choosing fields from the table based on the query's rules rather than the table's default rules. But it is still based on a recordset.

So your distinction between tables and recordsets is really just a mental image thing. Just like in "The Matrix" ("there is no spoon...."), the table isn't really there. Access is just feeding you something that you think is a table. And the illusion is so good that you believe it.

Read the help files on recordsets. Concentrate on the flavors of recordsets. You want to open a table-type recordset, not a snapshot or dynamic something-or-other. Once you do THAT, you can add content to your tables as you wish.

 

[AndyS48UK]

Thanks for the explanation Doc. I did read it this morning but was set on the idea that it was Creating rather than Opening so just assumed the "flavours" were non-relevant (for what I wanted) additions.

Many thanks...esp for The Matrix idea...the scene in the restaurant...I may not really be tasting it but it still tastes good!

Andy

 

[AndyS48UK]

Doc

Don't know if you will get to read this but can anyone help?

Doc gave me an explanation re referring to a table using VBA...tat helped to clarify things a little more.

I tried out the example using Northwinds.mdb, came up with a "funtion undefined" error, read up on that and then loaded in a Data Access Objects (so THAT's what a DAO is!!) library.

Great - VBA is talking to me about recordsets so I can refer to them.

BUT

Apparently the recordsets collection is a member of the database class so, logically, shouldn't I be able to refer to the Database that holds the recordset? Sure I can use OpenDatabase but my Database is already open so I get an error message.

I thought I had Objects, Methos and Properties figured out after a lot of self teaching but I'm baffled...if you look at the help file then the recordsets collection is held in the database. I can declare both a database and recordset but I can't do this;

sub test()
dim fred AS Database
dim fred2 AS Recordset

fred="OO1.mdb"

with fred
set fred2=.OpenRecordset("tblTable".dbOpenTable)
end with
end sub

...and I get an error message highlighting "fred=" saying invalid use of property.

but...if "fred" is declared as a database then shouldn't I be able to set it's value as "OO1.mdb" is a genuine database?

A

 

[AndyS48UK]

Thanks...Rich & Doc

Searching back on "recordset" I found an example that Rich wrote in Feb '02 (thanks!). This use Currentdb()...I'm a little puzzled that I couldn't find it referenced within the recordset help in access but, hey, glad it's done. Thanks for your time...the posts are really useful and I've found a good few answers when searching...and examples can be even more enlightening than the books sometimes.


Just thought that it was time to put this one to bed and say thanks.

...mind you...I didn't actually SEE the recordset open...but the code didn't kick me out so it must be there somewhere!!

A

 

[The_Doc_Man]

Andy, one thing that might be a little confusing to you and that relates to the error message about properties and such... consider the following snippets:

' presume a definition
Dim dbCur as DAO.Database
' statement 1
dbCur = {something}
' statement 2
Set dbCur = {something}

are two radically different statements in VBA.

In the former case, there is a default property assumed for the value-assign operator. Not sure what the default is for a database object, but if it happens to be a read-only property then you would get an error message for sure.

The latter case is an example of either dynamic instantiation or address referencing. (Most often, the former.) If you are familiar with Ada or C++ where you can have address variables, the Set statement is the moral equivalent of loading the address of the something into the variable. And the data type of a variable that points to an object includes the expected type of object, so you can't make a database variable point to a recordset object. (But you CAN make a variant point to nearly any kind of object at all.)

Therefore, if your

fred = "OO1.MDB"

statement is examined, you would see that VBA is probably confused about the property you were trying to deal with, because fred is declared as a dynamically definable object and VBA expects that syntax to refer to properties and methods, not objects. Does this make sense?

 

[AndyS48UK]

Hi Doc

Yeah it does make sense...although I've just read it about 8 times, referred to the code above and printed it out! I THINK I see what you are saying...I don't code in other languages I'm afraid (good grief I'm only just getting through this one!) but I can see what you're saying and, more importantly, what Access thinks I'm saying!

I'm going to go and hunt out a few more references and examples just to clarify it.

Thanks again. Really appreciate the time and effort that one must have taken!

A