473,324 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Opening a connection from Access 2007 to Sharepoint

Hi all.

I'm working on a database on Access 2007. Db is kept on a Sharepoint server, and each user has its FE .mdb file, with tables linked to Sharepoint.

Ok, everything's working fine. Each user has to login when file is opened (Access' security), and also when tries to open a table, a window prompts for authentication against Sharepoint.

Now, we want to change a little thing, and I need to bypass Sharepoint's authentication: I mean, I would like to write a code to open a connection against Sharepoint using CurrentUser(), checking for the pass on an encrypted table.

Please, any advice about how to open this connection?



PD: I know it is possible because I have tested it using two tables, T1 and T2. If an user has admin privileges on both tables, once he has logged on Sharepoint while trying to open T1, then he can open T2 without re-login. (There must be some kind of "open connection")
Jan 16 '12 #1
11 7270
sierra7
446 Expert 256MB
I have limited knowledge of this but my understanding was that to use Sharepoint you needed an .accdb file not .mdb, which is why I upgraded to 2010.

Also, my understanding is that Sharepoint is just a repository for storing (a) the data and (b) the compiled code for the App, its forms, macros etc. To do any changes you would change the original source .accdb then re-upload, so the revision can be downloaded to the users.

So I shall watch this to see the correct solution!
S7
Jan 16 '12 #2
NeoPa
32,556 Expert Mod 16PB
Mikeleroi:
and I need to bypass Sharepoint's authentication:
I expect this will throw a red flag for most of our experts. We don't help anyone to bypass security here, so unless you can convince that this is only a requirement to reuse a security connection that has already been successfully validated, I doubt you'll get much help (Without it, any such response will be pretty quickly removed anyway and the poster will need to explain themselves).

Please clarify.
Jan 16 '12 #3
Mikeleroi:
and I need to bypass Sharepoint's authentication

Sorry, my mystake, I didn´t choose properly the words as I don´t want to bypass this security procedure.

I just would like to try to establish a connection: the same way if an user opens a browser, Sharepoint prompts for user/pass; same happens when this user tries to open a linked table from Access, needs to write user/pass.

And if this user tries to open another table (and he has privileges), it will open without re-filling the prompt. On the other hand, if doesn´t have privileges, login prompt will open again.

Sharepoint's security will continue to work; I just would like to avoid the first login window, using actual user and pass (As I would be admin, I will set the passwords... anyway, they will be encrypted on a local table user -> pass). Do you know if is it possible?

Edit: I'm trying coding on VBA using .Connect of linked table, and adding it UID and PWD, but doesn´t work.

PD: I'm not the one who designed the solution, the one who did would like to do it this way.

And if it is not possible, I would like to know also, to start looking for alternatives. Please, if there's any other security method suitable, I would be glad if you could point me in the right direction.

Thank for your advice
Jan 17 '12 #4
NeoPa
32,556 Expert Mod 16PB
That's a good enough explanation for me.

I'm afraid I have no personal experience with SharePoint though, so I can't give much help myself (though I'm sure there are others who can). My best guess would be that this is almost certainly possible. Access has always had the option of opening connections as well as simple databases. The trick (I expect) would be first to open a connection (or similar object appropriate to SharePoint) and then to open the sub-items, like tables etc, specifically from within this connection (or similar) object.

Not definitive I know, but hopefully points you in the right direction. Best of luck :-)

PS. What is PD: indicating in some of your posts? I've read it as a PS but am I right to?
Jan 17 '12 #5
Thanks for your advice NeoPa :) Sorry about "PD", I used the spanish acronym.

I will continue searching, when I find the solution I'll post it on this question.
Jan 18 '12 #6
NeoPa
32,556 Expert Mod 16PB
Mikeleroi:
I will continue searching, when I find the solution I'll post it on this question.
That'll be great.

I'm curious as to why the Spanish version of the Latin term - Post Scriptum - would be in any way different from any other language?
Jan 18 '12 #7
Still looking :S In Spanish, it is said "Post Data", from "Post datum".

I've seen there's NetworkCredential class in VB.net that provides credentials... but I can´t find it on VB, does anybody know if there is something similiar?
Jan 19 '12 #8
Please, any hint? I'm trying to modify .Connect string, something like:

Expand|Select|Wrap|Line Numbers
  1. Dim tdef As DAO.TableDef
  2. Dim cOld As String
  3. Set daoDB = CurrentDb
  4. Set tdef = daoDB.TableDefs(strTableName)
  5.  
  6. cOld = tdef.Connect
  7. MsgBox cOld
  8. Dim c As String
  9.  
  10. c = Left(cOld, InStr(cOld, "}"))
  11.         c = c & ",false," & strUserName & "," & strPassword & Right(cOld, Len(cOld) - Len(c))
but no results :(
Jan 25 '12 #9
sierra7
446 Expert 256MB
This works for me, i.e. get messagebox saying the connection string for table named "Batch"
Expand|Select|Wrap|Line Numbers
  1. Dim tdef As DAO.TableDef
  2. Dim daoDB As DAO.Database
  3. Dim cOld As String
  4.  
  5. Set daoDB = CurrentDb
  6. Set tdef = daoDB.TableDefs("Batch")
  7.  
  8. cOld = tdef.Connect
  9. MsgBox cOld
Where I stopped. The main change is I explicitly defined daoDB

I don't know where you other string variables (strTableName, strUserName, strPassword) are defined or populated, but two points. The first is the the seperators in a connection string are usually semi-colons (;) but commas (,) might work.

Secondly, I would expect strPassword = "PASSWORD=Sierra007" or similar i.e not just the password. Same for strUserName.

Finally, I can't remember seeing a curly bracket (}) in a connection string but then I have never connected to Sharepoint only SQL Server
S7
Jan 25 '12 #10
Well, as it wasn´t working, finally I have chosen another option: auto-login with current Windows user.

Just in case anyone finds it useful, IE -> Tools -> Internet options -> Security -> Custom -> User authentication -> 2º option (automatic login with current user and pass) (maybe some tab has other name, it has been my quick translation from Spanish :)
Jan 25 '12 #11
Solved! =) Solution:

Add a reference to Microsoft HTML Object Library and MS Scripting Runtime

Expand|Select|Wrap|Line Numbers
  1. Dim xmlhttp
  2. Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")
  3. xmlhttp.Open "GET", " URL ", False, " USER ", " PASS "
Jan 26 '12 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Bob Alston | last post by:
Anyone know of any features of the new Access 2007 that will improve on the ability of Access to work in a web environment??? Bob
5
by: bobdydd | last post by:
Hi Everybody I am currently updating a 2000.mdb to a 2007.accdb and I am trying to use the 3.6 DAO Object Library in the tools>references in the vb editor. This produces an error "Name conflict...
16
by: Neil | last post by:
I posted a few days ago that it seems to me that the Access 2007 rich text feature does not support: a) full text justification; b) programmatic manipulation. I was hoping that someone might...
7
by: ARC | last post by:
This is taken from the "What's New in Access 2007" page. However, I've looked through all the properties of a text field memo box, and cannot find the append only option. Does anyone know how to...
2
by: ARC | last post by:
Just curious if anyone is having issues with Acc 2007 once the number of objects and complexity increases? I have a fairly large app, with many linked tables, 100's of forms, queries, reports, and...
0
by: jxt1303 | last post by:
I've created an Access 2007 application that has linked tables in a SharePoint 2007 site. Normally, after I open the app and access a form that uses one of these linked tables, you are prompted for...
7
by: Icarus | last post by:
I have a Access 2000 database that I am opening with Access 2007. When I open the datbase in Access 2000 or Access 2003, the database runs as expected. In Access 2007 my main form opens, a...
1
by: trixxnixon | last post by:
When a database is loaded into sharepoint, would an end user also need to have access 2007 loaded on their machine?
5
by: wassimdaccache | last post by:
Dear Experts, I got an opportunity to implement a project for a market. I have been reading the proposal for this market it seems it is a huge application whereby we will have 6 cashiers 2...
0
by: alinagoo | last post by:
Hello all Could any body help me to know how i could integrate Access 2007 database with Sharepoint server and that Sharepoint server's configurations because I'm not familiar with Sharepoint....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.