CODEWITHSUNDEEP
sqlms-access
RBSt
RBSt

Reputation: 143

Linked Query in Access 2010 to a database that is password protected.

I am trying to make a linked query, very similar to a linked table, in Access 2010. I have built a query in one database where the source database is another database. This allows me to select all from the query in the other database (SQL shown below.) The only problem is the other database is encrypted with a password, so when I run the query it displays the error message, "Not a valid password." 

SELECT qry_Report.*
FROM qry_Report IN 'Location of Database.Database'

The reason I don't move everything over to the new database is because the query is somewhat complex and has 12 tables in it. I could link those 12 tables and copy the query over, but I would rather not. The database I am working on is front end and more for reporting purpopses.

Please let me know if there is a quick and easy fix for this, or if there is a better way to do this.

Thank you is advance!

Upvotes: 2

Views: 3104

Answers (2)

Aung Ko
Aung Ko

Reputation: 1

I think It can help you .

exec sp_addlinkedserver 
    @server = 'TestLinkServer4', 
    @provider = 'Microsoft.ACE.OLEDB.12.0', 
    @srvproduct = 'Access4',
    @datasrc = 'F:\FPDB\db2.0.4\Pm2014.mdb', 
    @provstr = ';PWD=yourpassword'

exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'TestLinkServer4',
    @useself = 'FALSE',
    @locallogin = null, 
    @rmtuser = 'Admin', 
    @rmtpassword = null

Upvotes: 0

Scotch
Scotch

Reputation: 3226

Given your password protected database is in C:\Database\myDatabase.accdb

    SELECT * FROM yourTable IN '' [MS Access;PWD=yourPassword;DATABASE=C:\Database\myDatabase.accdb]

Tested with two .accdbs (Access 2010) and it surprisingly runs kind of fast.

Upvotes: 3

Related Questions