Accessing SharePoint List via Access through Excel

[Gustavo Miller]

Finally! Iwas able to figure out how to post!
Ok, the post title might be hard to understand. I got it.

I have a Microsoft Access database with a bunch of linked tables from SharePoint - Table Lists.
Now, here comes the tricky part. I have created this utility in Excel (which connect to the Access DB -mentioned above, using ADO). The database opens, but crashes when reading tables (any table).
Here is the code, it is pretty straight forward, Hope you can understand...

Set mCnn = CreateObject("ADODB.Connection")
With mCnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=C:\NSDS\Excel Templates\Network Tracking Sheets\data\test.mdb;"
.Open
End With

Set mCmd = New ADODB.Command '<!-- Instantiate new Command object - Recordset object
mCmd.ActiveConnection = mCnn '<!-- Use active connection
mCmd.CommandType = adCmdText '<!-- Command type enumerator
mCmd.CommandText = "SELECT * FROM lkpProvinces"

Set mRS = New ADODB.Recordset '<!-- Instantiate new recordset
'mRS.CursorLocation = adUseClient

'<!-- Here is where it crashes... Excel locks and I have to kill the process
mRS.Open source:=mCmd ', CursorType:=adOpenDynamic

ConnectWBDatabase = True

I have tried different Providers but with no avail.

Work-around: this is not the optimal solution.
If I create an object, of type Access Application [CreateObject("Microsoft.Access")] I can access the tables using DAO, which is fine, I it runs. But, it requires that users have Access installed on their machines. Microsoft Access is a blocked application at the Government of Canada -I am a contractor with them. The application has 150 users, I would have to justify the installation of Access on their terminals. Not happening.

 

[theDBguy]

Hi. Just curious, can you access the SharePoint lists directly from Excel?

Sent from phone...

 

[Gustavo Miller]

As a single SP List yes. Even execute some queries with multiple List tables, but I have some complicated UNION queries and nested queries that cannot be done directly from Excel. I was relying in ADODB.

I have another application (National Defense) in where the GUI interface is Microsoft Access (linked to SP); it works slow but does work. In this application also, I have super complicated queries (including using XML fields) that work like a charm.

I am kind of losing hope in this, so I am starting to look into PHP and MySQL, but will also continue pressing for SQL Server -this is my preference. But I am just curios to see if we can resolve the issue

 

[Pat Hartman]

Why is Excel accessing SP via Access? You can't link to a linked table so that makes no sense.

If Access is the "owner" of the data, then you can export whatever you want from SP to Excel from some form button in Access.

Why are you starting with Excel? Why are you not starting with Access?

 

[Gustavo Miller]

Why is Excel accessing SP via Access? You can't link to a linked table so that makes no sense.

If Access is the "owner" of the data, then you can export whatever you want from SP to Excel from some form button in Access.

Why are you starting with Excel? Why are you not starting with Access?

MS Access is not the owner; but you can connect via linked tables. So, I was connecting the access DB to excel and attempt to access SP.
Why are you starting with Excel? Why are you not starting with Access? well, my post mentions - "Microsoft Access is a blocked application at the Government of Canada" maybe you missed that part.

 

[Pat Hartman]

The blocked part was pretty far down the list.

Can you install the Access Runtime? That is a free download and all of the users could use that.

If you can't use Access at all, then you can't use the application so I'm not sure how we can help you. I suppose you could use Excel as a front end to Share Point.

The problem is you started out with "I have an Access application" Do you or do you not have an Access application. If Canada says you can't use it, then you don't have an Access application.

You MUST have either the full version of Access or at least the Access runtime if you want to automate Access and use any of its objects. You can't get around this silly edict by trying to use OLE automation.

 

[Gustavo Miller]

No worries! I found some other guy that was trying the same, but never got around it.
We are moving into SQL Server; I have converted the database without a problem, all I have to do now, is convert the Query statements into Stored Procedures. Should be no problem.

Thanks for your help; it's much appreciated.

 

[theDBguy]

Good luck!

 

[Pat Hartman]

We are moving into SQL Serve

Not sure how that solves your "Access" problem but whatever.