Prompt User to Select Table?

[Endlessknight]

Greetings,

Is there a way, in Access VBA, to prompt the user to select a table in the database and then return the table name as a string? Thanks in advance for any help or input. It's greatly appreciated.

 

[Joe4]

I don't think you can do that, at least not in that manner. You could probably create a query to return all table names, and use that to populate a drop-down box that the user can click on in a form to select a table. That could use some code like seen here: Queries: Return database object names

I have the feeling that this is a small part of something bigger that you are trying to accomplish. Can you tell us what that is, and we may have some other suggestions for you?

 

[Endlessknight]

Hi Joe,

I wrote a function that first formats and then imports a folder of Excel sheets to a given table. Code below. Right now the user types in the target table's name via an Inputbox (mytable). What I would like for a box, similar to the one that pops up when you make a query, that prompts the user to select the target table.

Thanks in a advance for any help and apologies if my question is silly, I just started with VBA last month.

Function Impo_allExcel()
Dim StartTime As Double
Dim SecondsElapsed As Double
Dim myfile As String
Dim mypath As Object
Dim mypath2 As String
Dim mymacro As Object
Dim mymacro2 As String
Dim mytable As String
Dim xl As Object
StartTime = Timer
mytable = InputBox("Please enter target table")
MsgBox ("Please Select Formatting Macro")
Set mymacro = Application.FileDialog(3)
 mymacro.Show
mymacro2 = mymacro.SelectedItems(1)
MsgBox ("Please Select Source Data Folder")
Set mypath = Application.FileDialog(4)
 mypath.Show
mypath2 = mypath.SelectedItems(1) & "\"
MsgBox ("Please Select Folder of Data to be Formated")
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open (mymacro2)
xl.Visible = False
xl.Run "Format_All_Workbooks"
xl.ActiveWorkbook.Close (True)
xl.Quit
Set xl = Nothing
ChDir (mypath2)
myfile = Dir(mypath2)
Do While myfile <> ""
  If myfile Like "*.xls" Then
    DoCmd.TransferSpreadsheet acImport, 8, mytable, mypath2 & myfile, True
  End If
  myfile = Dir()
Loop
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
End Function

 

[xenou]

IF you go with a form you could load a listbox with table names and then the user can select from the list.

 

[Endlessknight]

IF you go with a form you could load a listbox with table names and then the user can select from the list.

Sorry for being so dense but I've never worked with forms in access, can you elaborate a little?

 

[xenou]

Try this site to understand listboxes:
Access Tips: Making Sense of List Boxes

as far as creating forms, you might want to google a little on that.

 

[Joe4]

I've never worked with forms in access

I recommend trying to further your knowledge in Access a bit if you are going to be working in it in this manner. Access isn't quite as intuitive as Excel, and if you try to "muddle" your way through it, you will experience a lot of frustration, and may end up programming yourself into a corner at times (I know, I started out in Access that way myself!).

A good introductory book to Access will cover things like Tables, Queries, Forms, Reports, and Macros (Macros are different than VBA in Access). If you are going to be designing your own databases, it will also be essential to understand Relational Database Theory, especially the Rules of Normalization (see: https://support.office.com/en-us/article/Database-design-basics-1eade2bf-e3a0-41b5-aee6-d2331f158280).