3-Tier MS Access Connection Issue

[JaC74]

I am in the process of creating a quick prototype from a MS Access database stored in a network folder. Here is what I have done so far.

I haved made sure that the logon used by the MSTR machine has access to the folder and the MSTR server instances is started with the same login. From the MSTR box, I am able to map the drive to the MS Access data base and run queries against it using the DSN created and Test ODBC.

The DSN has been defined by locating the DB by a mapped drive and I have also tried absolute path. Both results in the same error below.

I can also connect via 2-Tier using the same DSN.

The server has been rebooted and when I try to connect via 3-Tier using the same DSN, I get an error that states:

"... SQLDriverConnect.[S1009:-1023n HDBC][MicroSoft][ODBC MicroSoft Access Driver]'(unknown is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides...."

Thanks in advance...

 

[teccum]

Hi JaC74,

I guess, I had the same type of issue sometime back(Thread posted on Jan 27 2005 on the thread (thread395-994057) with the subject "Mstr Tutorial Project in 3 - tier".

I was not able to figure out the exact issue. Hence in that particular case, I copied the database into my server machine and configured the dsn and had to do a workaround like this.

thanks
teccum.

 

[JaC74]

Teccum,

Thanks for your reply. That is our current work around also, but copying over the db on a daily basis is causing us too much overhead.

 

[FLB]

I am not sure I understand how you connect in 3-tier.....
Are you saying that your I-Server is running and accessing the metadata while you are also trying to access it from your Desktop? If this is the case, then you _will_ corrupt the metadata if you can connect, most likely you won't be able to access the MD as the access database will be locked by the Intelligence Server.

If this is not what you are doing, are you trying to connect in 2-tier then?
I found that using the long names (i.e. program files\...) does not work well, there seem to be a limit somewhere and just on my computer I had to move the files to a folder "closer" to the root _and_ use the short naming convention to access it (i.e. c:\progra~1\....).

HTH,
FLB

 

[petperson]

A couple of issues I've run into before with mapped drives:

1) must map either directly from server console or something like PCAnywhere. Do not use a virtual connection like Terminal services.
2) IServer must remain 'logged on'. MSTR could not access the mapped drives when it wasn't logged on, even though shares were set up correctly. Workaround was, whenever server was rebooted, a script logged on the server and then locked the screen.

HTH..

pp