My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 What Is The Fastest Way To Insert Data Into Sharepoint Link Table From Access. Please Advice.
|
|
 Mar 3 2012, 05:53 AM
Post
#1
|
|
|
New Member Posts: 8  |
Currently i link the view from sharepoint and 'm using something like ths...
However it takes 0.78 to update the table when i ttried debug.print timer... Thanks for helping in advance Option Compare Database Option Explicit Public Sub InsertTest1() Dim dbC As DAO.Database Set dbC = CurrentDb dbC.Execute "INSERT INTO Table1 (Names) Values ('123123')", dbFailOnError Set dbC = Nothing End Sub |
 |
 
|
|
Mar 3 2012, 09:54 AM
Post
#2
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
Hi,
(IMG:style_emoticons/default/welcome2UA.gif) What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question. Linking to SharePoint probably means you're connecting to the table via the Internet. If so, then the delay is probably network traffic related. Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
|
Mar 5 2012, 02:09 AM
Post
#3
|
|
|
New Member Posts: 8 |
Thanks for quick reply, 'm using Office Access 2007 and it is connected through vba using import view and link feature....
|
|
|
|
|
Mar 5 2012, 10:55 AM
Post
#4
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
QUOTE (pedie @ Mar 5 2012, 12:09 AM)  Thanks for quick reply, 'm using Office Access 2007 and it is connected through vba using import view and link feature.... Thanks but, where is the SharePoint server located? Is it in-house, within a LAN? Or, are you connecting via WAN? Just curious... |
|
|
|
|
Mar 5 2012, 12:32 PM
Post
#5
|
|
|
New Member Posts: 8 |
My computer is connected through LAN to organization Intranet.
|
|
|
|
|
Mar 5 2012, 12:44 PM
Post
#6
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
Hi,
QUOTE (pedie @ Mar 5 2012, 10:32 AM) My computer is connected through LAN to organization Intranet. Thank you. But, I don't think that completely answers my question. If you are saying that the SharePoint server is in the same building as your computer then, I am not sure why there would be a "big" delay in writing the data back into the SharePoint List (of course, it's still subject to network traffic on your LAN). By the way, 0.78 (seconds?) doesn't seem to be a "big" delay. What am I not undestanding here? What numbers were you expecting? Or better yet, what numbers are you getting when doing it on a local table? Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
|
Mar 5 2012, 02:35 PM
Post
#7
|
|
|
New Member Posts: 8 |
I am expecting 0. Someone has done automation connecting to Sharepoint List and it is really fast...there is no delay at all. However when i use this method it is not as fast/quick in appending data as i thought...So I am wanting to know how to make that happen..
Thanks again |
|
|
|
|
Mar 5 2012, 02:55 PM
Post
#8
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
QUOTE (pedie @ Mar 5 2012, 12:35 PM) I am expecting 0. Someone has done automation connecting to Sharepoint List and it is really fast...there is no delay at all. However when i use this method it is not as fast/quick in appending data as i thought...So I am wanting to know how to make that happen.. Thanks again Wow! That seems impossible... Even with a local table, I just tried it now, I got 0.05. There has to be some time that will elapse between connecting to the table and then adding a record to it before it gets done with the job - let alone connecting to a SharePoint List (and possibly passing credentials to it). Could you ask this "someone" what technique they used? I'm just curious... Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
|
Mar 5 2012, 02:59 PM
Post
#9
|
|
|
UtterAccess VIP Posts: 1,776 From: Edmonton, Alberta Canada |
This is the nature of how a web connection works.
And a good portion of your time is of course setting up the connection, authentication of who you are, allowing you to log on. And THEN sending the one update. And keep in mind that SharePoint has to THEN send back the primary key it generated. SharePoint will also update who updated the column with last user/time ALSO comes down the network pipe etc. So a lot of things are occurring here compared to updating a local table. Access 2010 tends to be a lot better, and you can "force" kick everything into off line mode. You can then run your update, and then re-connect. However, I don't have the reconnect ability in code as of yet. So, I see simular times, and this is the case even on a LAN or local network. eg: CODE Dim rst As DAO.Recordset Dim i As Long Dim t As Single ' Call GoOffline t = Timer Set rst = CurrentDb.OpenRecordset("faxbook2") For i = 1 To 10 rst.AddNew rst!FirstName = "first & " & i rst!LastName = "Last " & i rst.Update Debug.Print i Next i rst.Close t = Timer - t Debug.Print "time = " & t The result I get is this: CODE 6 7 8 9 10 time = 0.9960938 What this means is updating of lots of records is a VERY VERY expensive operation here. You want to thus minimize such operations. And such update operations are more expensvie then even SQL server. Access 2010 is better in terms of caching the data here. So in a2010 you still find high cost in updates, the reading of data can be blistering fast and often much faster then SQL server since you working with a local data store that is being synced to sql server. To give an idea of off/line mode vs on-line mode, this code: CODE Dim rst As DAO.Recordset Dim i As Long Dim t As Single Call GoOffline t = Timer Set rst = CurrentDb.OpenRecordset("faxbook2") For i = 1 To 100 rst.AddNew rst!FirstName = "first & " & i rst!LastName = "Last " & i rst.Update Debug.Print i Next i rst.Close t = Timer - t Debug.Print "time = " & t Call GoOnline Output: [code] ..... 96 97 98 99 100 time = 0.140625 So you can see 100 records in off line mode is fast (it should be 10x slower then first example, but it not). So this 100x or more faster (because I am off line). So keep in mind that updating of data on SharePoint lists are expensive, and even more so than compared to sing sql server. However, with SQL server an update can be pass-through whereas with SharePoint no such option exists. This is really a tradeoff, since other queries and the local cache often runs circles around SQL server, and often it is the reverse – updates = costly. So desings that have LOTS of recordset processing don't work well with SharePoint. On the other hand, if the updates are on existing data for reporting then again using local tables can work really well in that kind of case. You have to learn the trade offs here and thus adjust your application development approaches. Albert D. Kallal (Access MVP) Edmonton, Alberta Canada kallal@msn.com |
|
|
|
|
Mar 5 2012, 03:11 PM
Post
#10
|
|
|
New Member Posts: 8 |
Thanks Albert! 0.50 and below should be okay (IMG:style_emoticons/default/smile.gif)
"Call GoOffline" is that an inbuild feature? I found few code from this forum taking table offline....etc which is really cool however i dont want pop be show when i sync with the Sharepoint which says "Updating and there is a Cancel button that User can actually click which i do not want to happen. Thanks again and please suggest. Regards Pedie |
|
|
|
|
Mar 5 2012, 03:22 PM
Post
#11
|
|
|
UtterAccess VIP Posts: 1,776 From: Edmonton, Alberta Canada |
QUOTE (pedie @ Mar 5 2012, 01:11 PM) Thanks Albert! 0.50 and below should be okay (IMG:style_emoticons/default/smile.gif) "Call GoOffline" is that an inbuild feature? Boy, if I had a choice of one new feature for Access? It would be program control of off-line vs on-line. The code from above is this: CODE Option Compare Database Option Explicit Private Declare Function _ InternetSetOption Lib "Wininet.dll" Alias "InternetSetOptionA" _ (ByVal hInternet As Long, _ ByVal dwOption As Long, _ ByRef lpBuffer As Any, ByVal dwBufferLength As Long) As Long Private Type INTERNET_CONNECTED_INFO dwConnectedState As Long dwFlags As Long End Type Const INTERNET_STATE_CONNECTED = &H1 Const INTERNET_STATE_DISCONNECTED_BY_USER = &H10 Const ISO_FORCE_DISCONNECTED = &H1 Const INTERNET_OPTION_CONNECTED_STATE = 50 Public Sub SetOnlineState(OnlineState As Boolean) Dim lngReturnValue As Long Dim iso As INTERNET_CONNECTED_INFO If OnlineState = False Then iso.dwConnectedState = INTERNET_STATE_DISCONNECTED_BY_USER iso.dwFlags = ISO_FORCE_DISCONNECTED Else iso.dwConnectedState = INTERNET_STATE_CONNECTED End If lngReturnValue = InternetSetOption(0&, INTERNET_OPTION_CONNECTED_STATE, iso, Len(iso)) End Sub Sub GoOffline() SetOnlineState False End Sub Sub GoOnline() SetOnlineState True End Sub I not tested this with 2007 - I don't recall that a2007 allows off line mode. However, calling the on-line mode does not start a sync in Access - I am still looking for a solution for this - so the above solution is not quite ready for prime time, but I looking into this ability.. Albert D. Kallal (Access MVP) Edmonton, Alberta Canada kallal@msn.com |
|
|
|
|
Mar 5 2012, 04:08 PM
Post
#12
|
|
|
New Member Posts: 8 |
Thanks again Albert!
I'll have to try that code tomorrow...that'll be after another 12hours....i'm at home and does not have Sharepoint Connection here to Practice....(IMG:style_emoticons/default/smile.gif) But anyways that looks like a good news....2007 does allow to go offline and Online [Toogle]..it is one of the Feature in database.... Thanks |
|
|
|
|
Mar 6 2012, 02:59 PM
Post
#13
|
|
|
New Member Posts: 8 |
Albert this actually did the trick!!!
Thanks...i appriciate your help here..... |
|
|
|
|
Mar 6 2012, 03:03 PM
Post
#14
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
Hi,
Glad to hear you got it to work! It just goes to show you, if you have realistic expectations, anything is possible. Good luck with your project. Thanks for the assist, Albert! (IMG:style_emoticons/default/thumbup.gif) Cheers (IMG:style_emoticons/default/cheers.gif) |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 03:24 PM |