Selecting Tables from VBA code

[J Bhujanga]

I have some VBA code in which I am asking the user to tell it which table has the data that is to be converted according to a process that comes afterward.
As it is, I use an input box and the user types in the table name and everything proceeds fine and dandily. However, what would be slicker is if the user could click on a table name that exists in the database instead of having to type its name. Can this be accomplished? If so, how is it done?

If the answer is yes, then can it also allow the user to go to a file window to find a different database from which to specify a table, similar to the way that an Excel sheet lets you import data from an Access by letting you pick a table from within a selected database when you click on the "From Access" icon in the "Data" menu.

Thanks.

[ssanfu]

I have some VBA code in which I am asking the user to tell it which table has the data that is to be converted according to a process that comes afterward.
As it is, I use an input box and the user types in the table name and everything proceeds fine and dandily. However, what would be slicker is if the user could click on a table name that exists in the database instead of having to type its name. Can this be accomplished? If so, how is it done?

This is what I use
Use a combo box or a list box and set the row source to:

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4)<>"Msys")=True) AND ((MSysObjects.Flags)=0));

Returns only local tables- no linked tables.

Also see http://access.mvps.org/Access/queries/qry0002.htm


Still thinking about the 2nd question...

[orange]

As for your second question ----- getting/selecting a table from a different database ---- you certainly
could get databases listed, but how exactly would you know what tables exit in any given database.

You could create a utility that would go through each of your directories, find the databases, then open the database and save the db and tablenames to some known location. Then use that list to resolve the question.
It would necessitate keeping the list current. So each time you add/delete a table in any database you would have to ensure you run your utility to update the "list".

Some people create a database as a "dictionary/repository" where each database, each table, each query.... is identified and described. It serves as a "master/authoritative" source for all of your database artifacts.

Steve Bishop has a new a video (Nov 2016) on importing csv and other delimited files into Access. It goes from simple use of Access tools to the creation of class modules to build a custom dialog for importing files.

Just some thoughts for consideration.
Good luck.

[J Bhujanga]

Steve,
Thanks for this reply. It works great and it makes it real easy to filter out tables that they shouldn't be picking by putting a where clause on the 'name' field.