ACCESS Database on remore W2k Server 1

[Turkbear]

Hi,
I'm using CE 8.5 Professional and I am having problems getting a report based on an Access database to run once it is published..
I have tried both with ODBC and File system access when setting the database thru CR Pro and the drive mappings and ODBC DSN configurations are the same on the designer PC and the CE server - I am running the pageserver service as a user with the same rights to that Server/directory
as I have on the designer's PC but, when run from CE the report either:
1: if ODBC, it prompts for a user/pass - but none work so I suspect it is not finding the table.
OR
2: If using file system mapping ( the 'Database Files' option in CR) to the MDB file it gives an 'incorrect file location' error

Is it even possible to run a report against a remote Access database? If so, what am I missing?

Side Note: All of our other reports are using various Oracle databases so I have never had a problem like this occur..This is a new client and, at present, they cannot migrate their data to Oracle ( or even SqlServer)

Thanks for any pointers you can give...

 

[jedownsjr]

We have run into this problem before with our CE implementation. Don't quote me on this, but I believe that CE support will tell you that using remote Access databases is not actually supported. However, we did find a workaround by doing the following:
1. On the server where CE is installed, you should be able to create a new Access database, then create new tables by linking to the "real" database's tables though ODBC. This new database basically just acts as a conduit to the data, but it lives on your CE server.
2. Set the location for your report to the new database. You should be able to use these linked tables in your report.
Hope that helps!

 

[Turkbear]

Hi, Thanks..I'll give it a try and post the results...

 

[Turkbear]

Hi,
Oh Well...( I really thought this would work jedownsjr But..

Could not link using ODBC ( Microsoft Error said something like cannot link Jet or ISAM databases thru ODBC ) so use File Ssytem to 'point ' the new Access DB ( local) to the remote one which was on a mapped drive - Created a link to the table within the database I wanted and there it was)
Could browse it in my new db , create queries on it, etc...BUT, when a report built on it was published, I now get one of those annoying generic
'Error in Database DLL detected by cacheserver/Pageserver'

Back to the drawing board.

Thanks for the effort...

 

[Turkbear]

Hi..
Success !!!!

CE support led me through the steps need to make the Windows ODBC configuation utility use UNC instead of a drive letter mapped to that location - Windows services cannot use drive letters so this is the only way to specify the location of the data if the CE services are to be able to find it. The services also need to be running as a user with rights to the directory ( I had that part)..

Thanx all for your ideas..

 

[bdreed35]

We use the Access native driver for our reports that are Access based. We were able to get them working using the Convert to UNC button in the Database->Set Location dialog box. You can also use drive mappings if the account that created the mapping is the same account that the Page/Job Server is running under.

~Brian

 

[Turkbear]

Hi,
The UNC part works fine - Drive mappings are a problem ( They were created by the same user as running the services) - my understanding is that Windows services do not 'know' about mapped drives unless the user that created them is actually logged in on te server, not just being used to run a service.

Thanks for the info about the native Access driver..I did not install it since I did not want to encourage use of Access as a 'shared' information source...

 

[pelajhia]

Can some one explain this a little more:

'create new tables by linking to the "real" database's tables though ODBC'
Not sure how to do this, otherwise the solution sounds perfect for what I am trying!

 

[pelajhia]

Nevermind - I actually found instructions for this in access' help (go figure!)

 

[pelajhia]

"CE support led me through the steps need to make the Windows ODBC configuation utility use UNC instead of a drive letter mapped to that location"

Turkbear, Can you provide some details on this ?

Thanks!