Access VBA: Is there an internal VBA function that returns the current table name?

Question

Using an update query to store "current table name" to a new field.

I'm importing several text files into a Microsoft Access 2016 database. Before I starting appending all 25 tables into one common table, I'm needing to store the original text file's "name" into a newly created "Imported_Source" field. Doing a "F2", copy source file name, then placing argument into update query is way too time consuming. It would be nice if Access had a library function perhaps  <me.filename> or a function like <Get_MeTableName>. Then  I could place that function into the update argument of an Update Query.

Thanks!

Answer 1

I don't know what your issue is. Or what your problem with looping. Looping is an integral part of any programming automation.

You are trying to make what is a n easy and simple task into something harder by looking for something that doesn't exist. No matter how you cut it, the way to do this is to loop through your tables and for each table, generate and execute an query. Either a Query that updates a field in the table or appends the data to a master table, setting the value of a field. I gave you coding examples you can use.

But if you want to waste time looking for something that doesn't exist or trying to make a a simple task harder, that's up to you.

Answer 2

Thank you sir for your valued time. In my situation, I could not get your logic to work. Perhaps the two dozen imports made matters worse. Focusing on a single UPDATE query, returning it record source name to a new field, should have been my starting point. Have a nice day!

Answer 3

So show me the code you used and we can get it to work.

Answer 4

THANKS for your fast response. I'm still perplexed. The below code, shown before, was hand copied from an Access 2000 text book. In the immediate window, I'm able to see the result of tables plus many unknown system tables.  I will study your notes and get back to you. If your able, work with this code.  Thanks again.

Sub iterateAllTabes()

Dim vnt As Variant

    With CurrentData  ' comment CurrentData object

        For Each vnt In .AllTables

        Debug.Print vnt.Name

        Next vnt

    End With       

End Sub