Append records from table in unsecured db to table in user level s

[Deirdre]

I need to append records from a table in a db with no security to a table in
a db with user level security. All users access the secured db with a
shortcut - meaning I think I need to be referring to the workgroup in my
code.
I would prefer to set this up to run at the end of each day automatically if
I could, and then delete all records from the original table once appended to
new table.
I know how to do an append query when security is not an issue but I can't
seem to get the destconnectstr right - I keep getting a permissions error.
Any help on this would be greatly appreciated.

 

[John W. Vinson]

I need to append records from a table in a db with no security to a table in
a db with user level security. All users access the secured db with a
shortcut - meaning I think I need to be referring to the workgroup in my
code.
I would prefer to set this up to run at the end of each day automatically if
I could, and then delete all records from the original table once appended to
new table.
I know how to do an append query when security is not an issue but I can't
seem to get the destconnectstr right - I keep getting a permissions error.
Any help on this would be greatly appreciated.

Are you trying to run the query from the unsecured source database? Try
instead running it from the destination database, using TransferDatabase.

John W. Vinson [MVP]

 

[Deirdre]

I would if I could write the code to automate the task so it runs daily. If
you can help with that it would be great. Otherwise I was looking at having
to use the windows scheduler to run a macro for this and I couldn't get the
schduler to open the secured database...

 

[John W. Vinson]

I would if I could write the code to automate the task so it runs daily. If
you can help with that it would be great. Otherwise I was looking at having
to use the windows scheduler to run a macro for this and I couldn't get the
schduler to open the secured database...

You'll need the userID and password, whichever database you use. You can pass
them from the scheduler using a command line like

"%programfiles%\Microsoft Office\Office11\msaccess.exe"
"K:\SomePath\securedatabase.mdb" /excl /uid BatchJob /pwd XYZ /x macroname

You will need of course to define a user named BatchJob with password XYZ, and
give that user *only* the minimum permissions needed to run the job, since the
user's id and password are exposed in the scheduler file.

John W. Vinson [MVP]