Run append query from another database

[Jim L.]

I have "Database 1" that automatically collects transactions into a table.
Each night, this database is backed up, and pasted in a folder. Also in this
folder is "Database 2" that users can easily access information and reports
based off of the information in "Database 1". What I would like to do is
have an "Update" button on the switchboard of "Database 2" that will run the
append query in "Database 1" to update the transactions table in "2". I
don't want to link the tables, because I don't want "1" to hold the 50,000
transactions that are in "2", and I don't want anyone going inside "1" to run
the append query.
Is this possible to do simply without codes? Hopefully this makes sense,
and someone can help me out.
Thanks, as always!

 

[Steve Schapel]

Jim,

In Database 1 you can maka a macro using the OpenQuery action to run the
append query.

And in Database 2 you can use a macro using the RunApp action, with Command
Line something like this:
"C:\Program Files\...\MSAccess.exe" "C:\YourFolder\Database1.mdb" /x
NameOfMacro

--
Steve Schapel, Microsoft Access MVP

I have "Database 1" that automatically collects transactions into a table.
Each night, this database is backed up, and pasted in a folder. Also in
this
folder is "Database 2" that users can easily access information and
reports
based off of the information in "Database 1". What I would like to do is
have an "Update" button on the switchboard of "Database 2" that will run
the
append query in "Database 1" to update the transactions table in "2". I
don't want to link the tables, because I don't want "1" to hold the 50,000
transactions that are in "2", and I don't want anyone going inside "1" to
run
the append query.
Is this possible to do simply without codes? Hopefully this makes sense,
and someone can help me out.
Thanks, as always!

__________ Information from ESET Smart Security, version of virus signature database 4186 (20090624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jim L.]

Steve,
This works good, but now I get a message that pops up after clicking "Yes"
to run the query, and "Yes" to append the rows. It says:
"Microsoft Office Access can't append all the records in the append query.
Microsoft Office Access set 0 field(s) to null due to a type conversion
failure, and it didn't add 1811 record(s) to the table due to key violations,
0 record(s) due to lock violations, and 0 record(s) due to validation rule
violations. Do you want to run the macro anyway?"
If I click "Yes" to run the macro, it seems to work fine. Is this because I
have many more fields in db1 that I don't have in db2? How can I get rid of
this error, and is there something I can add to the macro to click all of the
"OK" and "Yes" buttons for me? I would like as little input by the user as
possible.
Thanks again.

 

[Steve Schapel]

Jim,

In order to suppress the display of the action query confirmation messages,
you can put a SetWarnings/No action in the macro, prior to the OpenQuery
action.

However, first of all I would try to sort out why you are getting that
conflict with the 1811 records. Does this meant that there are 1811 records
in the source table that are already in the destination table for the
append? If so, then it may be ok, it just means that the existing records
won't be overwritten, but any records that don't exist in the destination
will be added. However, if there might be destination records that are no
longer in the source table, then they will still be there, in which case it
may be appropriate to use a delete query to clean out all the records from
the destination table first.

--
Steve Schapel, Microsoft Access MVP

Steve,
This works good, but now I get a message that pops up after clicking "Yes"
to run the query, and "Yes" to append the rows. It says:
"Microsoft Office Access can't append all the records in the append query.
Microsoft Office Access set 0 field(s) to null due to a type conversion
failure, and it didn't add 1811 record(s) to the table due to key
violations,
0 record(s) due to lock violations, and 0 record(s) due to validation rule
violations. Do you want to run the macro anyway?"
If I click "Yes" to run the macro, it seems to work fine. Is this because
I
have many more fields in db1 that I don't have in db2? How can I get rid
of
this error, and is there something I can add to the macro to click all of
the
"OK" and "Yes" buttons for me? I would like as little input by the user
as
possible.
Thanks again.

__________ Information from ESET Smart Security, version of virus signature database 4190 (20090626) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jim L.]

Steve,
I set the warnings to "No", and things are working great now. The only
prompts I am getting is to click "Open" when Access starts up db1 (A minor
annoyance I can live with). I am just a beginner with Access, but my
databases have come a long way with the help from you guys. Much
appreciated, and I will be writing back again.

 

[Steve Schapel]

Jim,

I don't know what this click 'Open' when the db1 starts up, but I don't
think it's "normal"... something must be causing it. If you want to explore
this aspect further, please let us know any further details about this
prompt, and when it occurs.

--
Steve Schapel, Microsoft Access MVP

Steve,
I set the warnings to "No", and things are working great now. The only
prompts I am getting is to click "Open" when Access starts up db1 (A minor
annoyance I can live with). I am just a beginner with Access, but my
databases have come a long way with the help from you guys. Much
appreciated, and I will be writing back again.

__________ Information from ESET Smart Security, version of virus signature database 4190 (20090626) __________

The message was checked by ESET Smart Security.

http://www.eset.com