Overview:
We have a client that wanted to insert records into a Sharepoint calendar from one of their existing SQL Server databases that was using a Microsoft Access project as a front end. We did some research, and found 3 approaches to inserting records into a Sharepoint site/database using code:
1) Calling the web services that are installed with Sharepoint.
2) Calling some of the SQL Server stored procedures that come with Sharepoint (for example, you can try calling the procedure proc_addlistitem to insert an item into a Sharepoint list.
3) Writing your own SQL code to directly insert data into the tables (this is tough and not recommended).
We couldn't get approaches 1 and 2 to work, and we didn't want to try approach 3. Ultimately, we came up with an easy workaround using just plain old ADO and VBA code.
Solution:
Starting with Access 2003, Microsoft Access has a built-in tool to link an Access database to a Sharepoint site. So we just decided to create an Access database (which we called Sharepoint.mdb) with a linked table to our Sharepoint site's calendar list (when you link an Access database to a Sharepoint calendar, the linked table in Access by default is given the name "Calendar". We then wrote the following VBA code to insert the records from our other SQL Server database (Access project front end) into the Sharepoint calendar (using Access as an intermediary). Once you insert the records into the linked table in Access, Access does the rest automatically. We found that this simple approach worked pretty well.
CODE:
'If new event, add the event to Sharepoint
Dim Cn As ADODB.Connection
Dim oCm As ADODB.Command
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=microsoft.jet.oledb.4.0;Data Source=\\server\Sharepoint.mdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn
Dim strSQL As String
strSQL = "Insert into calendar ([Job Title], Phase, [Start Time], [End Time], PDFCheckingID, Foreman, Subdivision, [Plan number], Comments) values ('" & strAddress & "', '" & strPhase & "', '" & dtBeginDate & "' , '" & dtEndDate & "', " & intPDFCheckingID & ", '" & strForeman & "', '" & strSubdivision & "' ,'" & strPlanNumber & "' , '" & strComments & "')"
oCm.CommandText = strSQL
oCm.Execute
If Cn.State <> adStateClosed Then
Cn.Close
End If
If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing