VBA code to insert records into a Sharepoint database list

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