Sanity check...
I need to create a new table with only those records which have two fields not equal.
But I want to deal directly with the table using VBA. Because, occasionally I may change the data in the table (with all the information) and I want the new table to change automatically. With query in between, it won't be quite automated.
But there are two issues to be considered here.
First, you can only do a maketable once unless you delete the table completely before you try it again. If the fields are the same, don't do a maketable. Manually make the table instead, but make it empty. Then, if you need to start over again, you can do an ERASE query (DELETE * FROM TABLE X
. Then you could populate it with append queries.
Second, VBA is no more or less automatic than a query. I am smelling a misconception. If the table gets edited manually through datasheet view, there is no more guarantee that your query would run than that your VBA would run. If you are talking about a triggering event that does this automatically, you cannot guarantee this any way other than through a form supporting the change. But under the change, there is an equal chance to just trigger a query as to run some complicated VBA recordset scan.
But now, the kicker of the sanity check. You don't need a new table at all if there is a way inside a query to identify rows that should be excluded. If you could build a table from the query written as an APPEND query, you could just as well use the query directly as a SELECT query. You see, a query just defines a dynamic recordset. A table is just a static recordset. Anything you could have done with a table, you could do with a query - and it would (1) take up less room ('cause there is no separate storage) and (2) be dynamic. In fact, based on normalization concerns, you really don't want a separate table that holds duplicated data from your primary table anyway. Just not good programming practice.
Since you don't say why you particularly need it to be in a table, I'm going to suggest that you DON'T need it in a table in the first place. Yeah, I know it is arrogant of me, but from what I read in your replies, I am inferring that you are confused about something. If that is incorrect, I'm sorry. (But I stand by the comment that you still might not need a separate table, and that the separate table is bad news for normalization.)
