Automate Link Process

[DM - NPS]

I have a table (tbl_Tree) that gets updated weekly using data collected from
a GPS unit. So what I want to do...... Create a command button that lets
the user designate where the Access database is located. Once the user does
this, it will automatically go into that database, find the table "tree" and
link to it, copy the records from the table tree and load them into my table
(tbl_Tree), delete the linked table, and then open the tbl_tree so the user
can verify that their records were loaded.

I used a macro with 5 steps: TransferDatabase (to link to the table in the
other access database), Open Query (append query that appends records from
the linked table to my table, deleteObject to delete the linked table,
OpenTable to open the table, and GoToRecord to go to the first record.

This works GREAT, the only problem in the TransferDatabase command I have to
enter the entire pathway and database name that contains the table I want to
link to. The problem is everytime we download from the GPS unit the name
changes so the user must go and rename the database before they click the
command button.

How do I do a TransferDatabase method that lets the user designate the
database that contains the table "tree" to link to?

Sorry for the long message but thanks for the help.

 

[Clifford Bass]

Hi,

This requires a call out to the file open/save common dialog. To do
that create a new regular module. Or use an existing regular module. Add
this code into it.

Public Function SelectAccessDatabase() As String

' Uses Microsoft Office nn.n Object Library

Dim fd As FileDialog

Set fd = FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Microsoft Office Access", _
"*.accdb; *.mdb; *.adp; *.accda; *.mde; *.accde; *.ade"
.Filters.Add "All Files", "*.*"
.FilterIndex = 1
.Title = "Select Access Database"
If .Show Then
SelectAccessDatabase = .SelectedItems(1)
Else
SelectAccessDatabase = vbNullString
End If
End With

End Function

While in the VBA Editor go to the Tools menu and choose References.
Add the "Microsoft Office nn.n Object Library", where nn.n is a version
number, by clicking on the check box.

In your macro add the following three lines to the top:

1) Action - SetTempVar
Name - strDatabaseName
Expression - SelectAccessDatabase()

2) Condition - [TempVars]![strDatabaseName]=""
Action - RemoveTempVar
Name - strDatabaseName

3) Condition - ...
Action = StopMacro

In your TransferDatabase line change the Database Name to:

=[TempVars]![strDatabaseName].

Add to the end of your steps this action (or after the TransferDatabase
step):

Action - RemoveTempVar
Name - strDatabaseName

That should do the trick.

Clifford Bass