Import Object Window

[Andrew Smith via AccessMonster.com]

Alright here it is,

I need to open an object window that shows all the tables in another
database based off the client number a user supplies. For instance,
if they supply 1233c then it will go to the folder P:\Clients\12\1233c\
client.mdb Then it should open a dialog box with all available tables to
import. Then the user selects the talbe they want to import. I have tried
using a file dialog box but cannot open base of the file they choose an
object browser. If anyone has a better idea please provide. Here is what I
have:

Private Sub cmd_conversion_Click()
On Error GoTo Err_cmd_conversion_Click

Dim MyDB As DAO.Database
Dim fDlg As Office.FileDialog
Dim strFormat As String
Dim strFileName As String
Dim varFile As Variant

Set MyDB = CurrentDb()
' asks for client number
clientnum = InputBox("Please enter the client number:", "Air Report
Information")
If clientnum > 1000 Then
digits = Left(clientnum, 2)

Set fDlg = Application.FileDialog(msoFileDialogFilePicker)
With fDlg

.Title = "Select Database"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
.InitialFileName = "P:\Clients\" & digits & _
"\" & clientnum & "\client.mdb"

If .Show = True Then
'here would be nice to have something to open the file with
what i do not know
End If
End With
Else
MsgBox "Incorrect client number detected." & _
" Please restart the process and provide a correct client
number.", vbExclamation, "Status"
End If

Exit_cmd_conversion_Click:
Exit Sub

Err_cmd_conversion_Click:
Select Case Err.Number
Case 2501
'Do nothing as cancel selected by user
Resume Exit_cmd_conversion_Click
Case Else
MsgBox "An Error as occured while trying to complete the task.
" _
& "Please report the following " & "error to your IT
department: " _
& vbCrLf & Err.Description, vbCritical, "Error"
Resume Exit_cmd_conversion_Click
End Select

End Sub

 

[John Nurick]

Hi Andrew,

I'd use the code at http://www.mvps.org/access/api/api0001.htm to get
the path to the .mdb file from the user, and then use it in a query like
this to get the list of tables into a combobox:

SELECT Name
FROM MsysObjects IN 'C:\Temp1\BoxWithinBox_Backup.mdb'
WHERE Type=1
ORDER BY NAME;