Question

Locked

How can i make a sistemnatic operation on several tables.

By camoes ·
I have an access database and have several tables with an equal structure, ex:

NAME as text
CODE as number
RESULT as number
DATE as date
.......

and they are named

TBL_PT
TBL_US
TBL_UK
......

the table names are listed on a table:

TABLES

LIST as text

how can i do a repeated operation for each one of the TBL's listed in LIST
ex:

copy all the names where CODE = 1
to a destiny table BRACKET

NAME as text
CODE as number.


the objective is to use the table names as parameter in several operations or queries without changing them, just by adding or deleting the table LIST

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

Collapse -

In access that would have to be some vba

by Tony Hopkinson In reply to How can i make a sistemna ...

e.g
select the table names. for ach entry bild a sql statement and then execute it.

From SQL you'd use a cursor and exec, but access doesn't do either of them.

Course it's dead easy if you use one table instead of the three you have now.

Collapse -

Reply

by camoes In reply to In access that would have ...

The data must be divided in several tables. i's a lot of information and represents different files and groups of costumers.

The actions that a need to run are maintenance ones.

The idea was to do the VBA code so the operators could change the table "TABLES", adding new tables to the list or deleting old ones without messing whith my code.

being like that i could make several actions, and with a simple change in the index table, keeping them all up to date.

if you now a better solution I'd be also pleased

Collapse -

Nope no better idea, with that schema

by Tony Hopkinson In reply to Reply

If by a lot of data you mean too much for access, have you thought about using sqlserver and access as the front end instead.

Only maintaing say UK and US through use of a configuration table would still be possible and the update statement would be trivial.
and you could run the entire thing as part of a maintenance plan on the server...
That's whether you keep the tables split or not.

Still if you are stuck with access as a backend, like it, your options are limited and fundamentally inefficient.

Collapse -

a realy have to use access

by camoes In reply to Nope no better idea, with ...

I don't now how to work in SQL.

And it's not to much data for access. But if i put all of them in the same table it would be difficult for the other guys to run their one queries.

The idea was to make several operations whit the same index.

If I could use any index they could add or remove tables and the operations still rum on all of them.

Like remove clients with no phone. or old records.


if i could read the Index table "Tables" or so, and by the names stored there read all the tables and there records(or deleting them)it would be great.

i was trying to do it in VBA but i cant get the code

Collapse -

VBA isn't my thing, nlr is access, but

by Tony Hopkinson In reply to a realy have to use acces ...

look up the DoCmd.OpenRecordSet method that would give you a your list of tables.
For each one you'd build an sql statement
using the table name from above, then
use something like DoCmd.RunSQL to execute it.


This might be a bit of help.

http://www.blueclaw-db.com/docmd_runsql_example.htm

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums