Automate a Menu item

[Donna]

Hello group,
I will be exporting a table from one database to another on a daily basis.
The destination database will run an Append query using the imported table.
(Its a mobile operation, transferring data from a laptop in the field to a
desktop pc later in the day, no internet, using flash drive)

I would like to automate the process with VBA or a macro attached to a
button that will be on a switchboard.

I am using Access 2003 and the db has been saved in the Access 2000 format.
(same for the destination db)

The only logic I can think of using is to use the DoCmd and acMenu. I don't
know how to locate the numerical value of the menu items. I will be using
the File, Export, Export to {path}. The File menu is the first menu. The
Table is named "InitialDataEntry_tbl". I will most likely attach the code to
a switchboard item's OnClick event.

Can someone help me with this? I have seen the menu values somewhere, years
ago, but haven't been able to find it again. I will keep digging around, but
in the meantime, if someone would be so kind as to tell me how to write it
out I sure would appreciate it. Thank-you very much.

Donna

 

[Marshall Barton]

Hello group,
I will be exporting a table from one database to another on a daily basis.
The destination database will run an Append query using the imported table.
(Its a mobile operation, transferring data from a laptop in the field to a
desktop pc later in the day, no internet, using flash drive)

I would like to automate the process with VBA or a macro attached to a
button that will be on a switchboard.

I am using Access 2003 and the db has been saved in the Access 2000 format.
(same for the destination db)

The only logic I can think of using is to use the DoCmd and acMenu. I don't
know how to locate the numerical value of the menu items. I will be using
the File, Export, Export to {path}. The File menu is the first menu. The
Table is named "InitialDataEntry_tbl". I will most likely attach the code to
a switchboard item's OnClick event.

Can someone help me with this? I have seen the menu values somewhere, years
ago, but haven't been able to find it again. I will keep digging around, but
in the meantime, if someone would be so kind as to tell me how to write it
out I sure would appreciate it.

You do not want to use menu items for anything. There are
DoCmd methods to perform those kinds of operations instead.

In this case, it sounds like you want to use the
TransferDatabase method. Check it in VBA Help to see if
that's what you're looking for.

 

[Donna]

Hi Marshall,
I found the RunCommand method and acCmdExport to run with the DoCmd. I
haven't tested it yet, but I believe this is what I was looking for. I will
look into transfer database, but right off the top of my head, I only want
to export a table, not the entire database. Thanks..

 

[Marshall Barton]

RunCommand is only the modern version of the menu item
stuff. I believe you really should use TransferDatabase, it
can import/export any object in you mdb file.

 

[Donna]

If you don't mind me asking, what is wrong with using the menu item stuff?

I looked at the TransferDatabase after I wrote, and it looked like it could
move the table. I will look into in more closely. Very interesting....
thanks!

RunCommand is only the modern version of the menu item
stuff. I believe you really should use TransferDatabase, it
can import/export any object in you mdb file.
--
Marsh
MVP [MS Access]

I found the RunCommand method and acCmdExport to run with the DoCmd. I
haven't tested it yet, but I believe this is what I was looking for. I
will
look into transfer database, but right off the top of my head, I only want
to export a table, not the entire database. Thanks..

 

[Marshall Barton]

The menu items themselves assume you are clicking with the
mouse and can follow the prompts. RunCommand doesn't really
provide a decent way to specfy the things that you might
have clicked on in the menu, or it just open the menu item's
dialog and you have to click manually, which usually defeats
the purpose of automating it.

RunCommand is fine if it doesn't require additional
information to complete its operation.

 

[Donna]

Hi Marshall,
After working with the TransferDatabase today, I am so grateful for your
suggestion. The transfer of the table is completely automated and works
wonderfully. After trying the Run Command, and as you said, it was only
partly automated, defeating my intentions. Thank you so very much for the
perfect solution and for answering my questions. Have a great week! Donna

The menu items themselves assume you are clicking with the
mouse and can follow the prompts. RunCommand doesn't really
provide a decent way to specfy the things that you might
have clicked on in the menu, or it just open the menu item's
dialog and you have to click manually, which usually defeats
the purpose of automating it.

RunCommand is fine if it doesn't require additional
information to complete its operation.
--
Marsh
MVP [MS Access]

If you don't mind me asking, what is wrong with using the menu item stuff?

I looked at the TransferDatabase after I wrote, and it looked like it
could
move the table. I will look into in more closely. Very interesting....
thanks!

 

[Marshall Barton]

It's nice to hear that you're off and running with a
solution you're happy with. I appreciate your gracious
"thank you" and you have a great week too.