Hello all, i am new so please bear with me. My question is: I have this access 97 database that opens with office 07 and I am TRYING to open the database and loop through it pulling data from columns in all tables that end with the word "Data". I am lost so your help is greatly appreciated!!

Clarification is in order.

If you mean "all records in a table where a particular fields ends with 'data' " then you need to use the LIKE operator. If this is what you meant then look up "LIKE" as an operator in SQL using Access Help.

If you stated your problem exactly correctly, however, you want records from every table that has a name ending with "data" (and it is up to you to specify the records.)

In the latter case, the "right" way is to write VBA code that goes through the TableDefs collection, looking at TableDefs(n).Name to see if "Data" ends the name. You can then go any of several ways about extracting records.

If this is what you meant, look up Access Help on these topics: TableDefs, Collections, Recordsets, and VBA topics on looping. You didn't say what you wanted to do with the data sets.

However, if this is only a one-time thing, it would be faster to do it by hand unless you have literally hundreds of candidate tables.

Yes, finally somebody understands what i am trying to do. I will be pulling only about 10 columns(if the column has data) and inserting them into one very large SQL server 2005 table.

I am pretty sure my coding will work once i get the actual looping to take place. I am using VS2005 to do the code, I use a DAO reference to use my tableDef.
(forgive my ignorance but i am not real sure on how to open the database file (there is no opendatabase like in vb6) but i am researching this)

keep ya posted

ok i have been looking at looping thru the MSysObjects system table to get my table names that end in "Data" but i am getting the error: Records cannot be read, no read permissions on MSysObjects.

this is a 97 access DB opening with 07, i have enabled all the content and converted to an .accdb file any ideas??? :)

Not sure how you would not have at least read permissions on MSysObjects, but did you try it how Doc originally stated, as in, loop through TableDefs?

yes i see Doc's reply is very helpful but how would i actually open the .mdb file so that i can set my variable db(datatype is database) to the database so that i can get the table names

well I am still not sure how to set my db variable to open a database I used a data grid view to loop thru table schema and get my table name that way.

Sorry not to help more, but you're asking a VS question in an Access forum. You probably would've received a better response in the VS forums.

Visual Basic Forum (http://www.access-programmers.co.uk/forums/forumdisplay.php?f=68)

VB.NET Forum (http://www.access-programmers.co.uk/forums/forumdisplay.php?f=73)