Changing the link address of several tables

[Yochiver]

Hello, I have made a lot of queries on several tables. The data is coming from pdf files . When there is a new pdf file there will be created a new table with the name of the pdf file. . The tables are all in the same access db (2016). To use the queries I would like to link the new table to the old one . So I don't have to copy and modify the queries. Sure I can delete the old table and rename the new one. Or I can delete the records of the old one and insert the data from the new one in the old one. I am wondering if it is not easier to change the link address with a sub in vba that is called in a form ?
Thanks for any suggestion.

 

[Gasman]

I would have one table, with an additional field to indicate which file, then just query on the file required.
Possibly use a TempVar and set that for the table name each time?

 

[theDBguy]

Another approach is to create a new query based on your table and then modify your other queries to point to this new query. When you get a new table, just modify that new query to point to the new table, and all the original queries should still work without any changes.

 

[Pat Hartman]

Creating a new table for each new file is not the correct method. @Gasman gave you the correct solution. I actually do this slightly differently. I have a log table where I add a row for each new file I import and I save the autonumber that is generated. Then when I append the rows from the new table, I add the ID as the FK. It is a little more efficient than saving a long string path name in each record. It also gives me some flexibility in that I can "back out" an entire import and do it again if I discover there is a problem with the data. And finally, in the case of imports such as bank statements, I can ensure that I only import Sept 2024 ONCE for each bank account and also ensure that I don't import September before I import August.

 

[arnelgp]

I am wondering if it is not easier to change the link address with a sub in vba that is called in a form ?

yes it is easy to replace the link (hyperlink field) using vba.

dim hyp as string
dim parts as variant
'get the hyperlink from the table
hyp=dlookup("hyperlinkFieldName", "TableNameHere", "AutoNumberField = " & thePassedAutonumber)
parts=split(hyp, "#")
'change the link part
parts(1) = "c:\new.pdf"
'assemble them again:
hyp=join(parts,"#")
'replace the content of the table
currentdb.execute "update TableNameHere set hyperlinkfieldname = '" & hyp & "';"

 

[Yochiver]

Thank you all very much for the valuable responses. it is clear to me that there are several good solutions to my problem. I will test them all and see which one works best for me. At first glance, creating a basic query, using the new table, so that all the other queries and reports refer to it, seems like the easiest solution to me. However, I would then want to adapt that basic querie in the module that creates the new tables.

I also think the solution via the adaptation of the link is very useful and was at the origin of my question.

The solution using the ID or Name of the PDF is certainly workable and I think perhaps even the most simple one . However, for me it is less useful . The table would become incredibly large after a few years and would probably require a lot of work from access to create the reports with all kinds of Joins (also non-related /double records and join with several other large tables. Thanks thanks

 

[Pat Hartman]

The solution using the ID or Name of the PDF is certainly workable and I think perhaps even the most simple one . However, for me it is less useful . The table would become incredibly large after a few years and would probably require a lot of work from access to create the reports with all kinds of Joins (also non-related /double records and join with several other large tables. Thanks thanks

If all the imported data ends up in a single able, nothing changes. You can import data from a thousand files. You still have a single join. If you are not actually importing the data but intend to maintain it as separate files, that adds a complication. In that case, you will need to create the SQL on the fly rather than by using a saved querydef. Your code simply gets the file name and path from the log table I described and uses that information to create the query you need.

Of course, analyzing data from multiple detached files is a poor solution. Unless this is a one-time only event, I would import the data.

 

[Yochiver]

If all the imported data ends up in a single able, nothing changes. You can import data from a thousand files. You still have a single join. If you are not actually importing the data but intend to maintain it as separate files, that adds a complication. In that case, you will need to create the SQL on the fly rather than by using a saved querydef. Your code simply gets the file name and path from the log table I described and uses that information to create the query you need.

Of course, analyzing data from multiple detached files is a poor solution. Unless this is a one-time only event, I would import the data.

I am convinced your solution will be the best one in other cases. But for me , after a while (lot of testing and verifying reports by users) I don't need the bad files anymore . At the end of a period I will only keep the good one by that period.

 

[Pat Hartman]

Whether you import the data into the same table or you keep it external and just log the files, I gave you the solution for how to not have to modify your query manually to choose the file you want to link to.

 

[Yochiver]

Whether you import the data into the same table or you keep it external and just log the files, I gave you the solution for how to not have to modify your query manually to choose the file you want to link to.

Thanks again. When I create the tables in a module I also adapt the basic queries in that module . All other queries / reports / forms depend on those basic queries and stays unchanged.