Generate report with data from another Access Database

[Eichenbaum]

Hello,

I have a database divided in Back-end and Front-end. I'm trying to generate a report in front-end using the data from Back-end as below:

Set db = DAO.OpenDatabase(DatabasePath)
Set rst = db.OpenRecordset("tblEmployee", dbOpenDynaset)

Me.RecordSource = rst.Name

When I try to open the report, access warns that it is not possible to find the table tblEmployee. I tried the same using a test table in front-end and works fine. How can I connect with a external record source (in this case, the back end?).

Many thanks in anticipation!

 

[sxschech]

Couldn't you link the table? If it an access table then you don't actually have to link it, you could try:

select * from tblemployees in 'c:\tables\backend.mdb'
[CODE]

If the table is on the network, you can also use UNC format:
[CODE]
select * from tblemployees in '\\netwrkdisk\tables\backend.mdb'

 

[Eichenbaum]

Hi sxschech,

Thanks for the tip. It works fine but I have now a problem. This database is protected with password. How can I inform the password in the SQL Statement?

Many Thanks!

 

[sxschech]

Since I haven't worked on a password protected database with the In Clause, I did a search and found this. Hopefully it will work for you. I wanted to give the link to the actual posting, but wasn't accepting the link due to less than 10 posts, so had to add extra spaces to the URL for it to show in this posting. Remember to remove the spaces when using in your browser.

http : // thedailyreviewer.com /office/view/what-is-the-in-clause-for-an-access-database-password-107342311

Here is the example from the posting on how the password can be part of the sql statement:

SELECT id from table1
IN '' [MS Access;PWD=myword;DATABASE=C:\dbprotected.mdb]