How can use variable instead of table name?

[AbdullahGH]

how can use variable instead of table name?



For example..
if I have 20 tables ; Names1.. to Names20 And I want to compare all tables, then I have to repeat below code 20 times:

if Names1.fields(0)=Names2.fields(0) then ..

instead to that, I want to use table "tablesNames" contain all tables names And use variable ..
Table=TablesNames.item("Name").value

then write below code..

While TablesNames .EOF=False
if Names1.fields(0)=Table.fields(0) then
.. .
wend

the issue.
VBA not Accept variable: table.fields(0)
What is the correct statement?

[Micron]

What you have posted as examples doesn't make much sense.
First, you cannot simply refer to a table by its name, and thus to any of its fields and get a value from a record in that field. Not only am I fairly certain it's not possible, even if it was, there's no way to know which record you want to get the value from. Second, you cannot loop through a collection of such tables using the EOF property of a record set.

So be more exact and explicit about what you have and the desired outcome, and don't make up stuff that doesn't apply.
If you're not dealing with tables, but are using recordsets, say so.
If the tables are not really basically the same name except that a number is just appended, then don't imply that they are, otherwise some suggestions would be a waste of time.
As for being more clear about the situation and goal, giving one comparison example out of 20 doesn't help much. What is the next comparison? Names1 to Names3? Or is it Names3 to Names4?

[AbdullahGH]

First Thanks for your comments. May it is as you said: “we can’t refer to a table by its name, and to any of its fields and get a value from a record in that field”

However, May I need to explain by other way to be understand.

What I want to do is compare old DataBase with new DataBase (Both in MS Access). Each has many tables (>20), and each table has many fields like “Number”, “ Name”, etc, and put the differences in “Output” Table.

Then to make code short as possible, I want to use Loop and use Variable refer to Tables names. I put all Tables Names in below table:

TablesNames
Old_DB_Tables_Names	New_DB_Tables_Names
Items_List	Items_List
Items_Price	Items_Price
Items_Specs	Items_Specs
Items_Size	Items_Size
Items_Wearhouse	Items_Wearhouse
Items_Spare	Items_Spare
Items_Quantity	Items_Quantity
: :	: :

Code:

Dim TablesNames As ADODB.Recordset
Dim Items_List As ADODB.Recordset
Dim Items_Price As ADODB.Recordset
Dim Items_Specs As ADODB.Recordset
Dim Items_Size As ADODB.Recordset
Dim Items_Wearhouse As ADODB.Recordset
Dim Items_Spare As ADODB.Recordset
Dim Items_Quantity As ADODB.Recordset
:
:
Dim I as Integer
I=0

Table1=TablesNames.item("Old_DB_Tables_Names").val ue
Table2=TablesNames.item("New_DB_Tables_Names").val ue

While TablesNames .EOF=False
if Table1.fields(I) <> Table2.fields(I) then

Output.Fields.Item("Number").Value Table2.Fields.Item("Number ").Value
Output.Fields.Item("Name").Value Table2.Fields.Item("Name ").Value
:
:
End If
I=I+1

Wend


The issue VBA not accept statement [Table1.fields(I) <> Table2.fields(I)].

If you have anther way, Please let me know.

[June7]

Do you want to compare data? Look at Find Unmatched query wizard.

Do you want to compare structure? Will likely need TableDefs collection.

None of the code you show makes any sense for either purpose.

[Micron]

Agree that you code doesn't make much sense. If you've declared Table1 and Table2 as an ADODB fields collection it sure isn't obvious. On the other hand, you appear to be assigning a recordset field value to a variable (Table1=TablesNames.item("Old_DB_Tables_Names").value,

then trying to get at field(I) of that variable

Table1.fields(I) <> Table2.fields(I). The variable doesn't have any fields, so it will never work.

Either so much of your code is missing that we cannot "connect the dots" or it's not very well written. If you must stay with this approach (instead of using the unmatched query) then you might want to post the entire procedure, but please do use code tags around your code (# button on forum posting menu). I'm not a big fan/user of ADO, so I can't promise too much at this point.