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

No, because there is no concept of "current table". However, what you want is not uncommon and fairly easy to do. If you show us the code you use to import the tables we can show you how to modify it.

Answer 2

No, because there is no concept of "current table". However, what you want is not uncommon and fairly easy to do. If you show us the code you use to import the tables we can show you how to modify it.

Thanks for the reply!! By using the Access 2016's ribbon we are able to import all csv and Excel files manually into our database.  I believe you're saying Access does not have a built in "me.tablename" nor a "get_meTableName" type-of-function available to an update query?

Simply put, I trying to put the table name, no path info, into a record cells. Later normalization procedures follow.

The below sub routine list all tables of my database. Is there someway to may it only list the Me.Name??

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

Thanks!!

Answer 3

Looking at an older Access 2000 VBA book, The section on "The File Informantion Class" is missing in my Access 2016 reference library.

Would this library have information regarding an Access table name?

Thanks!

Answer 4

First, the Me. keyword is only available on Forms and Reports as a shortcut to refer to the currently active object. The only way to get a tablename is from the Recordsource property which doesn't help you. Second, if a table is open, you can check if it is but you will need to know the table name so that doesn't help you. Third, File Info refers to the Access container file, not tables.

So, again there is no concept of "current table"

Since you have already imported the data into tables, then my initial idea no longer applies. However, the code you show can help. Basically, you want to loop through all tables in the Tables Collection (which is what that code appears to do). With each pass through the loop you want to generate and execute a UPDATE statement to populate the tablename.

strSQL = "UPDATE " & vnt.Name & " SET Imported_Source = '" & vnt.Name & "';"

CurrentDB.Execute strSQL, dbFailOnerror

So if you replace your Debug.Print line with those 2 lines it should do what you want.

However this assumes that EVERY table in your database has the Imported Source field. So you may need to add some error trapping or a test that it is one of the tables you want to update.

A question though, are these text files all different structures? Is the reason why you want to fill in the Imported_Source field because you want to combine these tables into one? If so, then a UNION query may be a better option.