D
DinosRose
So it seems I'm always trying to think of every angle.
I am building a utility database that creates an instance of an encrypted
database (which I will call sourceDB) in order to run a make table query,
placing the resulting table into a third database (which I will call
destinationDB).
sourceDB has tables linked directly to SharePoint lists, so we have encryped
that database to remove the users ability alter live data through any
interface other than SharePoint. The utility database is our solution to
giving the users a snapshot of the data from the SharePoint lists which they
can use to run reports. By giving them a "local" table, any accidently
changes to data cannot be synchronized with SharePoint.
destinationDB comes into play because the business users have requested the
ability to design their own reporting process for the data; however, we need
to keep them out of the code which identifies the connection string to the
encrypted sourceDB. The utility database allows the user to select which
database to use for the destinationDB.
Where my concern comes in is that if the business users decide to build a
fe/be solution for the reporting process and encrypt the be database as part
of the process, the make table query will not be able to execute Select
<fields> INTO xTable IN 'destinationDB.accdb'...
I've attempted setting up a test Make Table query and it prompted me for the
password when I selected and encrypted database. It's been added to the sql
statement as 'destinationDB.accdb'[;PWD=strMyPW] but I still get a Not a
Valid Password error.
The password works, because I can open my encrypted database and I've passed
it to a routine that uses OpenDatabase to cycle through the destinationDB
tabledef collection to delete xTable if it exists.
What am I doing wrong? Is there another way to make a table into
destinationDB from tables in sourceDB using my utility database which only
contains a form, 1 xSettings table, and a couple of modules?
Thanks!
I am building a utility database that creates an instance of an encrypted
database (which I will call sourceDB) in order to run a make table query,
placing the resulting table into a third database (which I will call
destinationDB).
sourceDB has tables linked directly to SharePoint lists, so we have encryped
that database to remove the users ability alter live data through any
interface other than SharePoint. The utility database is our solution to
giving the users a snapshot of the data from the SharePoint lists which they
can use to run reports. By giving them a "local" table, any accidently
changes to data cannot be synchronized with SharePoint.
destinationDB comes into play because the business users have requested the
ability to design their own reporting process for the data; however, we need
to keep them out of the code which identifies the connection string to the
encrypted sourceDB. The utility database allows the user to select which
database to use for the destinationDB.
Where my concern comes in is that if the business users decide to build a
fe/be solution for the reporting process and encrypt the be database as part
of the process, the make table query will not be able to execute Select
<fields> INTO xTable IN 'destinationDB.accdb'...
I've attempted setting up a test Make Table query and it prompted me for the
password when I selected and encrypted database. It's been added to the sql
statement as 'destinationDB.accdb'[;PWD=strMyPW] but I still get a Not a
Valid Password error.
The password works, because I can open my encrypted database and I've passed
it to a routine that uses OpenDatabase to cycle through the destinationDB
tabledef collection to delete xTable if it exists.
What am I doing wrong? Is there another way to make a table into
destinationDB from tables in sourceDB using my utility database which only
contains a form, 1 xSettings table, and a couple of modules?
Thanks!