Make it work for me please!
April 3, 2012 10:54 AM Subscribe
MS Access Filter: Magic button wanted! How do I code it? I want a one-click button on my form which will pop open a table (or query --I may determine that a query works better) housed in a separate database. Can you help?
Assume for this purpose that linking the target table into my database is not a useful solution. At a minimum, I want the target database to open (it is an Access DB on another drive. We call the drive "T" if that is helpful). Having it open to a specific database object would be even more helpful. I'm imaginging it as VBA code stuck to a click button, but there's no reason a Macro wouldn't work, if it can get the job done. Currently using Access 2003. Thanks!
Assume for this purpose that linking the target table into my database is not a useful solution. At a minimum, I want the target database to open (it is an Access DB on another drive. We call the drive "T" if that is helpful). Having it open to a specific database object would be even more helpful. I'm imaginging it as VBA code stuck to a click button, but there's no reason a Macro wouldn't work, if it can get the job done. Currently using Access 2003. Thanks!
There are a bunch of DoCmd methods for opening objects but they only work against the current database.
What you can do is - open the database on drive T:
Select the table (or query or form) of interest in the Access window and drag it straight onto your desktop, that will create a shortcut.
If that works, then to run the shortcut from a button on your form should be doable with VBA and the Shell() function.
posted by Lanark at 11:52 AM on April 3, 2012
What you can do is - open the database on drive T:
Select the table (or query or form) of interest in the Access window and drag it straight onto your desktop, that will create a shortcut.
If that works, then to run the shortcut from a button on your form should be doable with VBA and the Shell() function.
posted by Lanark at 11:52 AM on April 3, 2012
Response by poster: Soelo: it really boils down to "because boss says don't do it that way"
Lanark: could you scribble me some sample code? I literally do not know vba, so if I can't spot it in the assists I'm lost, but I have gotten pretty good at tweaking code based on samples ("linguistics majors do it backwards" & all that) to suit my needs.
posted by Ys at 12:20 PM on April 3, 2012
Lanark: could you scribble me some sample code? I literally do not know vba, so if I can't spot it in the assists I'm lost, but I have gotten pretty good at tweaking code based on samples ("linguistics majors do it backwards" & all that) to suit my needs.
posted by Ys at 12:20 PM on April 3, 2012
Okay, if the boss says no to linking, what about importing?
A macro would only require these 3 commands
SetWarnings - it will be No by default, keep it set as no
TransferDatabase - Transfer Type = Import, Database Name = full path of the db on the T: drive, Object Type = "Table", Source - name of Table in the other db, Destination = same name, Structure Only = No
Open Table - Table Name = name of table you just imported, View = Datasheet, Data Mode = Edit
Hopefully it doesn't need to be a button if it already a macro as double clicking the macro would be the same as clicking the button.
posted by soelo at 2:43 PM on April 4, 2012
A macro would only require these 3 commands
SetWarnings - it will be No by default, keep it set as no
TransferDatabase - Transfer Type = Import, Database Name = full path of the db on the T: drive, Object Type = "Table", Source - name of Table in the other db, Destination = same name, Structure Only = No
Open Table - Table Name = name of table you just imported, View = Datasheet, Data Mode = Edit
Hopefully it doesn't need to be a button if it already a macro as double clicking the macro would be the same as clicking the button.
posted by soelo at 2:43 PM on April 4, 2012
Response by poster: Thanks Soleo! That sounds like a good way to get the desired end without having to argue about means.
posted by Ys at 4:36 PM on April 5, 2012
posted by Ys at 4:36 PM on April 5, 2012
This thread is closed to new comments.
posted by soelo at 11:52 AM on April 3, 2012