Sharepoint : Configuring Database Connection

[zephyrhex]

i am using Sharepoint Designer 2007
Access 2003
WSS site

i am trying to view data from my Access DB in the sharepoint site. when i go to the Data Source Library, i select Database Connections and try to establish a connection by using the following connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test1.mdb;User Id=admin;Password=;

i get an error about the web server not being able to connect to the database because the string is wrong, the DB is unavailable or the web site is behind a proxy or firewall.

the website is not behind a firewall or proxy. the DB file is available. is my connection string wrong?

am i doing this the right way?

 

[zephyrhex]

also, i have Sharepoint Designer installed on a computer other than the one hosting the WSS site. is this causing the problem?

 

[philhege]

Is the Access MDB on the Sharepoint server?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

no, the access database is on my local desktop. the sharepoint site is on the server.

sharepoint designer 2007 is also installed on my local computer.

thanks

 

[philhege]

It looks like the Web server needs the DSN. It needs to point across the network at your local MDB.



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

let me make sure i understand the structure correctly.

i have Sharepoint Designer installed on my desktop computer. that desktop computer is also where i have the Access database located.

in Sharepoint Designer, i am using the Database Connection. i use the Edit Connection String option and use the connection string i've posted above. when i try to run it, it gives me the error.

i'm not sure how having a DSN on the server for the Access database will work... i mean, Sharepoint is running from the desktop computer and not from the server. why would the server need a DSN?

i will go ahead and set up the DSN on the server and retry the connection to see if it works.

thanks

 

[philhege]

You have to ask yourself where the page is running. What computer must make the connection? The designer's on your computer, but where's the WSS instance?

Think of the designer like FrontPage. You edit the page on your machine, but it runs on the Web server. Even if it were running locally, you would eventually have to make a DSN on the production Web server.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

i tried this and i am still getting the same error.

here is what i did:
on my local desktop, i set up sharing (full control, everyone) on the folder in which the Access database resides.
i then verified that i can access the folder from the server.

on the server hosting WSS, i set up a system DSN pointing to the Access database.

and i used the connection string in sharepoint designer that i have posted above. i got the same error, so i tried the following connection string instead:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\qm_it\test\test1.mdb;User Id=admin;Password=;

(where qm_it is my local desktop) and i am still getting the error

 

[zephyrhex]

here are some other things i've tried:
move test1.mdb to the server hosting WSS.

try sharepoint designer from local desktop with path to the server:
tried :
c:\test\test1.mdb
\\servername\test\test1.mdb

tried adding a share with full control permissions for everyone to the folder on the server with the Access database
\\servername\test\test1.mdb
\\serverIP\test\test1.mdb

still getting the same errors...

here's a question... the DSN i set up on the server which pointed to the Access database on my local computer... how does WSS know to use that DSN? i mean, when i use sharepoint designer from my local desktop, there is no place to tell WSS to use that DSN... and you'd think you'd have to specify the DSN because i could have more than 1 and it would need to know which one to use....

 

[philhege]

I would imagine that the designer stores the connection info in a configuration file somewhere. Sorry this is conjecture; I'm still on SPS2003.

OK. What error are you receiving?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

server error: an error occurred while retrieving the list of Databases from c:\test\test1.mdb: the web server cannot connect to the specified data source because the query may contain errors, the data source may be unavailable or configured improperly, or your Web site is behind a proxy server or firewall.

 

[philhege]

an error occurred while retrieving the list of Databases from c:\test\test1.mdb"

This sounds like the connection is assuming a SQL connection (it may be issuing a statement like "
select catalog_name from master.information_schema.schemata" . Are there any properties in the Data Source Library that pertain to the type of database to connect to?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

no.
and according to this site:

http://www.connectionstrings.com/

i am using the right format for an Access database.

if it were a SQL database, i don't think i'd have to use the Edit String option. i think i could just fill in the server name, etc.

i have verified that the frontpage and sharepoint designer interfaces are nearly identical to each other for Data View. so if you have either product, you can see what i have to work with...

 

[philhege]

Well, I hope we're getting closer. Apparently the only OLEDB providers Sharepoint trusts by default are the SQL providers. See this thread for a possible solution (you'll have to find the proper representation for the Access provider):

http://groups.google.com/group/micr...the+list+of+Databases&rnum=6#48f73a2ecfd4f9ce

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

thanks for your help. i will look at that link and post back the results

 

[zephyrhex]

yes, that is the right path.

with a little more google work, i found this:

http://www.wssdemo.com:8080/Pages/mdb.aspx?menu=Articles

following those instructions, i was able to use the Data View web part with Frontpage and establish a custom connection to an MSAccess database.

thanks for your help.

details:

The MDB can't be on a network share.
Enable the Data Retrieval Services from the WSS Central Adminstration.

Run the following stsadm command to check the OLEDB providers that are enabled

stsadm -o getproperty -propertyname data-retrieval-services-oledb-providers

If you don't see Microsoft.Jet.OLEDB.4.0 in the list, append it to the existing list of providers and set the new list of providers similar to this

stsadm -o setproperty -propertyname data-retrieval-services-oledb-providers -propertyvalue DB2OLEDB;IBMDADB2;MSDAORA;OraOLEDB.Oracle;SQLOLEDB;Microsoft.Jet.OLEDB.4.0

In FrontPage, add a Data Source under Database Connection. You will have to select a Custom Connection string which should look like this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\

http://wwwroot\Drop\sample.mdb

 

[philhege]

The MDB can't be on a network share"

That stinks.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[zephyrhex]

well, through testing i discovered that the database has to be on the server hosting WSS, but you can share that folder.

i just cant get the permissions or sharing or connection string path right so that people who are not on the server can see the web part data.

it works fine when i view it from the server hosting WSS. any other machine, and i get:
An error occurred while processing the data for this request. Contact the server administrator for more information.

if i copy the database to the local machine, it works fine. so it must be something with the path in the connection string, or the sharing or permissions on the folder.

i have tried the following paths in my connection string:
c:\inetpub\

http://wwwroot\DB\test1.mdb

and
\\servername\DB\test1.mdb << sharing Everyone with Change/Read permissions and Security set for Everyone read/write/execute

what is really strange is that when i tried the second string above, i originally had it set to sharing Everyone read only. i got the error on my local machine, so i set the sharing to Change. i refreshed the page on my local computer and i could see the data. i then tried to view the data on another local computer, and got the error. so i refreshed on the first computer, and the data was replaced by the error. it was very weird because i'd made no changes in between the refreshes.

so, any ideas on how i can get local computers to see the data?

 

[zephyrhex]

i think i've figured out when it displays on the local computer and when it does not.

it will display on my local computer if i am also viewing the same page on the server at the same time. if i close the browser on the server, then i can no longer view the data from my local computer. if i try to refresh the page on my local computer, i will get the error unless i refresh the page on the server first.

this is weird.