Here's the code I use to get the table location which is currently selected in catalog window of arcmap:
Dim tableName As String
tableName = PermitID.Value ' permitID is the name of the table which I get from a text box within a userform
Dim pGxApp As IGxApplication
Set pGxApp = Application
Dim pSelObj As IGxObject
Set pSelObj = pGxApp.SelectedObject
Dim Location As String
Location = pSelObj.FullName & "\" & PermitID.Value 'read the full path to workspace
Dim pFeatureWorkspace As IFeatureWorkspace
Dim pAccessFactory As IWorkspaceFactory
Set pAccessFactory = New AccessWorkspaceFactory
Dim pAccessWorkspace As IWorkspace
Set pAccessWorkspace = pAccessFactory.OpenFromFile(pSelObj.FullName, 0)
Set pFeatureWorkspace = pAccessWorkspace
Kill (Location) ' trying to delete the table - I don't know how