access 2016 limitation

Question

hey  

 i have access 2016 , an DB holds A  4 million records , i try to export it to SharePoint 2013 list... only export 226000 items , try more and more  it stopped on this item 

first i think the problem with SharePoint but i try to export to excel 2016  also it stop the export process at 226000 too 

how i export all records to SharePoint or to excel??,  is there any setting should i do on access to set its export limitation ??

this is the second time share the same issue as i get no support in the first time..

Answer 1

To export to Excel, use the one-liner: DoCmd.TransferSpreadsheet. See help file.

As Albert said, this is no job for SharePoint. Maybe not for Excel either. Why not use a real database like Access or SQL Server? You have not given us enough of the raison d'etre to really be able to be of any use.

Answer 2

Hum, for SharePoint lists, I don’t recommend the size get must larger then say 10,000 rows.

I have pushed one table to 80,000 rows, but for all intentions, I would not suggest anything beyond 10 or 15,000 rows tops.

After that, things just don’t work well.

I am “surprised” that your row count of one table in access is 4 million rows, but given that “size”, I would suggest you send the data to a sql server table, and not to SharePoint.

While it is true that SharePoint does not really state a limit for number of rows in a SharePoint system, such large row counts into a single list don’t work well, and that is especially the case with access linking to such  large lists.

About the only suggestion here is to up-load say 5000 rows at one time. So I would create + setup the SharePoint table. Link to this “empty” table from Access.

Now, you can use append quires from the real local table to the “linked” tables (you can select by PK row ranges – 5000 at a time).

This type of append approach likely will work, but I would estimate this will take more than one day – since uploading and appending lots of rows to linked SharePoint tables is a slow process – think of Mr. Turtle here!

So your row counts are already WAY WAY beyond what is going to function well at all. Even if you adopt my above append only a set of rows at one time, the results will be so utterly un-usable that I fear I am wasting your time suggesting a workaround an issue what will not at the end of the day result in a useable setup.

That many rows of data needs to be sent up to sql server – SharePoint lists simply are not going to yield any real word usability – that number of rows is simply too large for the given technology.

So even if my “kluge” of sending up 5000 rows at one time, the resulting large table will simply not work at all, be it access or any other program attempting to make use of that many rows in a SharePoint list.

A SharePoint list is the wrong technology here – it not going to work well at all.

This well explains why no one answered your question before (because no one would have attempted to use SharePoint in this way. This is the same idea as asking for help on how to drive your car across the ocean. Since no one has attempted or driving their car across the ocean, then asking for advice on how to drive your car across the ocean will not yield any answers or results. Unfortunately cars don't float, and they don't travel on the water. And unfortunately, SharePoint lists don't handle or work with such large row counts. So, your chances of finding someone loading such high row counts to sharePoint is about the same as finding someone who attempted to drive their car across the ocean (but cars don't float, and it not practical to drive your car on water).

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada