Macro for Access Db

[Guest]

Hi!

I am trying to get a Macro to work for a program at work. The only parts I
am having trouble with, for now, is making it work automatically, three times
a day, on a day to day basis. I thought that I could do this with the Windows
Task Scheduler thing, but I can not figure out how to make the Macro
Activate, only Access itself. If someone could either tell me how to make the
Macro run with the Task Manager or how to automate it within the Macro
itself, that would help me out a lot.

Also if anyone knows how to make the Macro append information from different
tables to a Main table without it asking you which Table to do this at (I am
just using the Copy/Paste Function, I can not get Copy/PasteAppend to work
and just figured out a way to make it work with RunCommands) that would be
awesome also.

Thanks!

 

[Tom C.]

Hi!

I am trying to get a Macro to work for a program at work. The only parts I
am having trouble with, for now, is making it work automatically, three times
a day, on a day to day basis. I thought that I could do this with the Windows
Task Scheduler thing, but I can not figure out how to make the Macro
Activate, only Access itself. If someone could either tell me how to make the
Macro run with the Task Manager or how to automate it within the Macro
itself, that would help me out a lot.

Also if anyone knows how to make the Macro append information from different
tables to a Main table without it asking you which Table to do this at (I am
just using the Copy/Paste Function, I can not get Copy/PasteAppend to work
and just figured out a way to make it work with RunCommands) that would be
awesome also.

Thanks!

Have you tried the "command line" options to start MS Access. One of
these is "/cmd". You could start Access from a DOS prompt with the /
cmd parameter. The parameter could start a custom function. You could
use the function to do the rest.

 

[Steve Schapel]

Ohaern,

The Commmand line in the Taks Manager will look something like this:
"C:\Program Files\Microsoft Office\Msaccess.exe"
"C:\YourFolder\YourDatabase.mdb" /x NameOfYourMacro

You should set up an Append Query to transfer your data to the main
table, and then in your macro use an OpenQuery action to run the append.

 

[Guest]

Thanks for the Info Steve, and you also Tom, this should help me be able to
finish this Macro. If I have any problems with making the AppendQuery is
there somewhere I can look up info on it at? Or is it just very straight
forward?

Thanks!

 

[Guest]

OK, Was messing around with the AppendQuery and found out that if you need to
drop data the Query wont work. For my Macro part of the function is to
compare what is pulled at different times to what has already come across.
This is not possible if it will not append data that it has already seen. I
make one of the numbers the Primary key and give everything an auto time
stamp. Then when it is all put together only the "unique" records are kept
and the report spits out the asked for time and date. This is the goal of the
Database. Is there maybe a better way to do al of this, or to do the append
so that it automatically drops the duplicate information?

Thanks!

 

[Steve Schapel]

Ohaern,

Are you able to provide some specific examples of records where the
process is not working for you. I am struggling to really understand
your meaning.

It is the nature of Append Queries that they can not add a record to a
table that would violate data integrity rules. This would include
records where a Primary Key value (or other field with a Unique Index
set) already exists in the table. If what you are really trying to do
is change the data in some fields of an already existing record, then
you either need to delete that record first from the destination table,
before doing the Append, or else an Update Query may be applicable instead.

 

[dallin]

This helped me out Great! Within my macro I have a query that uses a ODBC
connect to bring data into the db -- that requires an additional login and
password. How do I code my ODBC login and password into my macro -- so I
don't have to key board it in each time?

 

[dallin]

No worries found it in Macro Actions -- Sendkeys -- works great.