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

Thanks again for your professional response. All csv and Excel files have same file structure. At this time, maual  import via ribbon is okay. Once each record , of each table, has  it's unique file table name, all imported fIles are appended into one file for data processing .

Answer 2

OK, So you have a couple of different options here. Instead of the code I gave you, you could use something like this:

strSQL = "INSERT INTO maintable (field list) " & _

               "SELECT field list, '" & vnt.Name & "' AS Imported_Source " & _

                "FROM " & vnt.Name & ";"

CurrentDB.Execute strSQL, dbFailOnerror

where the first field list is the list of fields that are in the main table. And the second is the list of fields in each of the imported tables. The result should be each table being appended to the main table with the tablename in the Imported_source field. Again, the same caveats about error trapping and testing for other tables.

The other option is a UNION query. Start with:

SELECT field1, field2, field3, ..., tablename AS Imported_Source

FROM tablename

UNION SELECT field1, field2, field3, ..., tablename AS Imported_Source

FROM tablename

You would then copy an paste the second part as many times as you need, replacing tablename with each table. You can then use the UNION query to append to your main table.

The problem here was the way you asked your question. Had you posted a question like; 

I imported 25 tables from text files into my database. These tables all have the same structure so I want to append them into one table. But I want to identify which imported file each set of records came from. what's the best way to do that?

I probably would have answered with the a solution similar to the above. I'm just mentioning this to help you use this forum in the future. Don't ask for something that you think is what you need, but describe what you need done and ask how to do it.

Answer 3

I'm hoping to avoid cut copy paste . sent by my android .

Answer 4

Then you can use code I gave you within a loop.

Answer 5

Query by example returns the table name in an UPDATE Query. What class library did it use to find the '"name property" of the table? Example: UPDATE "tableName" SET "new field"= "I wish to have table.property.name here"

From my understanding, an update query would eliminate FOR and Next looping. My question, is there a way VBA returns the "tableName" of an UPDATE Query? The "tableName" is one of the properties of a table.

Thanks again.