Use an ADODB connection to update records from Excel to Access stored on SharePoint

Question

I have some  VBA code in Excel 2007 that uses an ADODB connection to append an Access 2007 table stored on SharePoint.  All was working well until I updated my OS to Windows7 on my computer with Excel doc.  Now I am getting error: 

Run-Time error '-2147467259(800004005)':

Automation error

Unspecified error

My code:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn

 strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=//vzcentral.vzwcorp.com/sites/Scheduling/Haynes/x/testopsdoc.accdb;" _
& "Persist Security info = false;"

sSQL = "tech"
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open strConn
rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable         <---------Errors here

I'm told Win7 no longer doesn't pass authentication when running scripts and my SharePoint site isn't letting me access it, so I need to explicity include user authentication in it (I can get the current user..but how do I pass that to SharePoint?).  I have tested the code and it will update a DB stored locally, but will NOT update a  SharePoint file or even a file stored on a shared network drive...

Help!!

Answer 1

The credentials are on the connection open, not the SQL open

cnn.Open ConnectionString:=strConn, UserID:="abc",Password:="xyz"

---

jdweng