By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Opening a connection from Access 2007 to Sharepoint

P: 7
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
Share this Question
Share on Google+
11 Replies


Expert 100+
P: 446
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
Expert Mod 15k+
P: 31,492
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

P: 7
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
Expert Mod 15k+
P: 31,492
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

P: 7
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
Expert Mod 15k+
P: 31,492
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

P: 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

P: 7
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

Expert 100+
P: 446
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

P: 7
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

P: 7
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

Post your reply

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