Changing data source (table) for a query

[adrian.stock22]

Hi,

I have two Tables TA and TB. I have a set of queries based on TA. I want to substitute TB for TA (i.e. TB will stand in place of TA). I want the old TA queries remain unchanged but now be based on TB.

If I delete TA and then rename TB into TA, I will lose the queries. Therefore I want to 'point' the queries (in the most efficient way) to TB instead of to TA.

Then I can delete TA. Then I can rename TB into TA and the queries will follow along, I expect.

I have been trying in vain to find a way of pointing the queries to TA instead of TB? How do I do it?

I have done it with Forms (Design Views, Properties), where I can select a datasource. But where is the corresponding thing enabling me to select a datasource for Queries?

Thanks.

Adrian

 

[KeithG]

You could change the SQL statement with VBA but I don't see why you don't just make more queries. What is the big picture here?

 

[adrian.stock22]

You ask: Why not just create fresh queries for the new table:

It is too time consuming

1 There are many queries attached to the table
2 Some of them are complicated, that makes re-making them time-consuming and, worse, error-prone
3 The substitution operation for this table is a regular event, which makes it even more 'wasteful' to redo all the queries.
4 There are other databases in which the repeated task arises.

So, does anyone know of a way in which I can change the data source for a query, as simply as the way in which I can change the data source for a Form?

I am NOT a VB programmer, so I am trying to do it in the Access interface.

Thanks in advance.

Adrian

 

[wardster]

The only way i could think of would be to add TB to the Query as well as TA - then change the 'Table' that is referenced using the drop-downs, Then remove TA - but this is time consuming, as i have just had to do the excercise myself. But it does save re-creating them all

Ian

 

[adrian.stock22]

Hi, thank you both for trying to help me.

Having exhausted you as a source of information, I think I WILL re-do the queries. I had one tip from a friend for the avoidance of error and of some labour:

In Design View of the Query, I display the query in SQL view (View), then copy and paste it into Notepad or into a Word file (I found the latter better because search/replace utilities are available there, e.g. to change the name of the underlying Table). All Queries can be kept in the same Word file.

Then when re-creating the queries, I copy the SQL versions into the empty queries of the new table, rather than going through ticking and selection routine.

So that's what I am trying now. I succeeded once, then failed once, but hope to fix the problem in due course.

thanks all.

Adrian

 

[PeterF]

For what I know it should be possible to delete the original table and rename the new table if you have the "automatic name correction" disabled.

 

[adrian.stock22]

Hi PeterF,

Thank you. I have come accross this feature before but cannot find it now. Could you please tell me where to change this setting, and its exact name, so that I don't mess up something else?

Thanks.

Adrian

 

[PeterF]

In the Options under the general tab (I'm not sure it's called that way in the English version) uncheck the top box for automatic corrections.
For more info about what the 3 options do, read the help.

 

[KeithG]

You could actually create a function to change the from clause in your query. I can make an example for you. Are your queries based off of one table or due they contain multiple tables? Can you post a copy of your db? Or an example SQL statement?

 

[adrian.stock22]

Hi Keith,

Thank you. This is a single-table db, i.e. no relationship.

I have uploaded the db for you. It is now at the following URL:
www.tudo.co.uk/testing/demo-db.zip


I want to replace ValidMainTable by ProtectionTable01 (i.e. ProtectionTable01 will become the new ValidMainTable) and I want the old queries to point at the new table.

Because I deleted so many records there is now presumably no difference between the two tables. But I have written the words 'valid main table' and 'protection table' into the third field of the first record so that you can see at a glance where each table comes from.

Prior to zipping and uploading, I deleted ca 4000 records in each of the two tables, leaving only four valid records. All the six queries point at ValidMainTable.

The zip file is 7,582 kb. If that is too long for you, I have to do something about it.

I had a problem with reducing the size of the mdb file (a problem which I may have to post as a separate thread). When the db contained several tables of 4000 records each, its file size was 134,788 kb. Then I deleted all but two tables (backups etc) and took out ca 4000 records in each of the remaining two tables, which now contain only four records each. Nevertheless the file size of the mdb file did not go done even by one bit, to say nothing of 1 byte.

What can I do to get rid of that bloated empty space?

Thanks,

Adrian

 

[PeterF]

To get rid of the space occupied by deleted objects and records use the compact and repair option, you can even set it to compact and repair on exit under the options in the menu Extra.

I tested my sugestion to disable the Auto name correction function (Again as I don't have a English Access version I can't tell if I my translated is correct)and IMHO this is the easiest way to do wat you want, simply rename the old table to a backup name and give the new table the old name, after that you can enable this functionality again.

 

[KeithG]

Here you go! I create a procedure called UpdateQuery to update the source table of the your query. There are 3 parameter for the procedure QueryName, CurrentSourceTable, and NewSourceTable. If you look in module 2 I created to procedure. The first procedure will change all the queries source table to ProductionTable01 and the second will change the source table back to MainValidTable. Let me know if you have any questions.

I also compacted and repaired the database because when you delete and object in access you do not regain any of the space it was taking up untill you compact the database

 

[adrian.stock22]

Hi KeithG, thank you for creating that code for me. It is somewhat beyond me and I have never worked with Modules in Access before. Since I cannot expect you to teach me the basics in this Forum, I have sent your solution to a VB programmer friend (extraordinarily patient - smiles) and asked him to show me how to implement it. - If I get stuck with him, I'll come back to you. So, thanks again for this code and your other advice.

Adrian

 

[KeithG]

I through the function together quickly so there is no error handling and it is a bit slopy but you or your friend can customize it to fit your needs. If you need any help just let me know.