Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Add dummy record to all tables in access database

Status
Not open for further replies.

JeffNolan1900

Technical User
Mar 21, 2007
26
US
Hello,
I have a database populated with about 500 tables, all of which are blank (as far as data goes). The tables are linked properly, have fields (ADDRESS_ID for example), and are set up, but completly empty of data. The database is auto-created thru a filter process using another application and so every time I re-run the filter I will have to go into the database and add a dummy record into each table, which will be quite tedious.

The reason I need a dummy value in each table is because I am trying to map the Access Database info using FME Workbench to an Oracle 10g Database. The problem is that FME will not translate Access tables to Oracle when they are blank. Does anyone know of a coding method to auto-add dummy records to the first column in all tables.

I want to end up with this.
Table1:
ADDRESS_ID USER_NAME
1
Table2
LOCATION_ID ROOM_NAME
1

Basically, I want to add the value 1 into the first column of each table, despite the table name, or column name.

[small]note: any table that starts with d_ has to be ignored, since they are already populated. This is not a big deal and I can add a if d_ then else statement.[/small]

Thanks so much for your time in advance.
Jeff
 
I guess the easiest method would be to use a recordset:

Code:
For each tdf in currentdb.tabledefs
   set rs=currentdb.openrecordset(tdf.name)
   rs.addnew
   rs.fields(0)=1
   rs.update
Next

Or such like. However, it is probable that you will have to use a table of tables because linked tables will need to be filled in a specific order. You will also run into problems if any fields are required.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top