Browsing for Table to Import

[Eric_LA]

Hi

I have a database that I need to import a table from on a weekly basis.
Let's call it "ExtDB.mdb." The table is named according to the date it was
created, say "tblData_February_02." (I know the pitfalls of this naming
convention; it's not my design and outside my sphere of influence.). The
database itself is stored on a fixed location on our network.

I'd like to set up a form button that would make use of
DoCmd.TransferDatabase. How would I code it so that when the button is
clicked, the user is able to browse the list of tables in ExtDB and choose
the one to import? I would want to rename the imported table to a fixed
value, say "tblData_Current," and overwrite the table if it already exists.

Thanks,
Eric

 

[Allen Browne]

Here's the code you need to open the Windows FileOpen dialog so the user can
choose an MDB file:
http://www.mvps.org/access/api/api0001.htm

I think I'd be tempted to clear out the existing table rather than delete
it:
db.Execute "DELETE FROM tblData_Current;", dbFailOnError
At least you know the field types and properties and indexes are correct.

 

[Ken Sheridan]

Eric:

Once you know the path tot the external database, either by opening a common
dialogue using code such as that to which Allen referred you, or by storing
it as a value in the local database, e.g. in a column in a row table, then
you can list all of its tables in a list box on a form with a function along
these lines:

Public Function ListExternalTables(strDb As String, ctrl As Control)

Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set wks = DBEngine(0)
Set dbs = wks.OpenDatabase(strDb)

ctrl.RowSourceType = "Value List"
ctrl.RowSource = ""

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
ctrl.AddItem tdf.Name
End If
Next tdf

dbs.Close

End Function

In the form with the list box you'd call the function to fill the list box
with:

Dim ctrl As Control
Dim strExternalDb As String

Set ctrl = Me.Controls("lstExternalTables")

strExternalDb = <get the path from somewhere>

ListExternalTables strExternalDb, ctrl

where lstExternalTables is the name of the list box.

Ken Sheridan
Stafford, England

 

[Eric_LA]

Thanks, Ken. That was just what I was looking for.