Yes, I put this in the Access forum, but it was recommended I put this in the Excel forum too.
First the real-world scenario: I have a system that involves many Access databases feeding data to an Excel front end, and all those databases need to be secure: if some user zips and Emails any database outside, the database should be inaccessible. My problem is, when I encrypt any database, Excel can't get the data, even if I know the password and splice it into the connection string of the query getting the data.
I use Office 2010 all the way, and here's how to create the problem most simply:
• Make a new Access database.
• Make a new table and populate it, save it as Table1, and close the database.
• Make a new Excel workbook.
• Do Data / From Access, navigate to the new database, choose the table, etc., and get a new table reflecting the data.
• Close the workbook.
• Open the database, open Table1, change the data, and close the database.
• Open the workbook, refresh the table, and you will see the changes.
• Close the workbook.
• Open the database exclusively, encrypt the database (File / Info) with a password, and close it.
• Open the workbook, and refresh the table.
It repeatedly asks for the password, even if you supply the right password!
I've tried everything. I've tried making a connection first with Data / Connections / Add and then Data / Existing Connections. I've tried Data / Other / Data Connection Wizard and Data / Other / MS Query with every Access provider there is, including first making an ODBC entry with every Access driver there is for the database which includes the password. I've checked the Save Password checkbox and spliced the password into every connection string. I've saved every DSN, ODC and DQY file, I've inserted the password into all of them, and I've read them all in.
I've also tried making a linker database with a link to the source database that includes the password, and that works -- that is, I open the linker database and double-click the link, and I see the data without a password prompt. But when I try to get the data into Excel from the linker database, in all the ways above, it still repeatedly asks for a password, even if I give it the right one -- which I shouldn't have to do, because the linker already has it.
I even sent all this to a well-known Excel expert, he sent it back saying "got it working", but it doesn't work for me. He used MS Query and picked the MS Access Database ODBC entry, which of course is one of the many things I tried.
There is simply no way to get that data into Excel once I encrypt the source database. And I absolutely do know the password, because I can open the source and linker databases any time.
What in hell, Microsoft? Have you never heard of such a scenario??
First the real-world scenario: I have a system that involves many Access databases feeding data to an Excel front end, and all those databases need to be secure: if some user zips and Emails any database outside, the database should be inaccessible. My problem is, when I encrypt any database, Excel can't get the data, even if I know the password and splice it into the connection string of the query getting the data.
I use Office 2010 all the way, and here's how to create the problem most simply:
• Make a new Access database.
• Make a new table and populate it, save it as Table1, and close the database.
• Make a new Excel workbook.
• Do Data / From Access, navigate to the new database, choose the table, etc., and get a new table reflecting the data.
• Close the workbook.
• Open the database, open Table1, change the data, and close the database.
• Open the workbook, refresh the table, and you will see the changes.
• Close the workbook.
• Open the database exclusively, encrypt the database (File / Info) with a password, and close it.
• Open the workbook, and refresh the table.
It repeatedly asks for the password, even if you supply the right password!
I've tried everything. I've tried making a connection first with Data / Connections / Add and then Data / Existing Connections. I've tried Data / Other / Data Connection Wizard and Data / Other / MS Query with every Access provider there is, including first making an ODBC entry with every Access driver there is for the database which includes the password. I've checked the Save Password checkbox and spliced the password into every connection string. I've saved every DSN, ODC and DQY file, I've inserted the password into all of them, and I've read them all in.
I've also tried making a linker database with a link to the source database that includes the password, and that works -- that is, I open the linker database and double-click the link, and I see the data without a password prompt. But when I try to get the data into Excel from the linker database, in all the ways above, it still repeatedly asks for a password, even if I give it the right one -- which I shouldn't have to do, because the linker already has it.
I even sent all this to a well-known Excel expert, he sent it back saying "got it working", but it doesn't work for me. He used MS Query and picked the MS Access Database ODBC entry, which of course is one of the many things I tried.
There is simply no way to get that data into Excel once I encrypt the source database. And I absolutely do know the password, because I can open the source and linker databases any time.
What in hell, Microsoft? Have you never heard of such a scenario??
Last edited:
