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!!
Looping thru DB tables
- Thread starter zach
- Start date
- Local time
- Today, 06:29
- Joined
- Feb 28, 2001
- Messages
- 30,563
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
.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.
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
.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.
You read correctly
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
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???
this is a 97 access DB opening with 07, i have enabled all the content and converted to an .accdb file any ideas???
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
VB.NET Forum
Visual Basic Forum
VB.NET Forum
