Bulk Salesforce inserts from Microsoft Access

The Salesforce ODBC driver enables you to batch multiple SOQL insert statements. This blog shows you how to insert multiple Microsoft Access records into Salesforce.

To get started:

• Install and license the Salesforce ODBC driver on the machine where Microsoft Access is installed.

Before you can use the Salesforce ODBC driver to connect your application to Salesforce, you need to configure an ODBC data source. An ODBC data source stores the connection details for the target database (in this case, Salesforce) and the ODBC driver that is required to connect to it (in this case, the Salesforce ODBC driver).

To run ODBC Data Source Administrator (which you use to create a data source), in the Windows Run dialog box, enter this command if you're using a 64-bit version of Microsoft Access:

%windir%\system32\odbcad32.exe

–Or–

Enter this command if you're using a 32-bit version of Microsoft Access:

%windir%\syswow64\odbcad32.exe

If you're not sure whether your version of Microsoft Access is 32-bit or 64-bit, start Access, and then search for the application's process in Task Manager. If the process name is MSACCESS.EXE *32, Microsoft Office is 32-bit. If the process name is MSACCESS.EXE, Microsoft Office is 64-bit.

To create a Salesforce ODBC driver data source:

1. In ODBC Data Source Administrator, choose the System DSN tab, and then choose Add.
2. In the Create New Data Source dialog box, choose Easysoft Salesforce SOQL ODBC driver, and then choose Finish.
3. Complete the DSN Setup dialog box:

   Setting	Value
   DSN	Salesforce
   User Name	The name of your Salesforce user. For example, myuser@mydomain.com.
   Password	The password for your Salesforce user.
   Token	The security token for your Salesforce user, if required. To find out whether you need to supply a security token, choose the Test button. If the connection attempt fails with an error which contains LOGIN_MUST_USE_SECURITY_TOKEN, you need to supply one. Salesforce emails the security token to the email address associated with your Salesforce user account. If you have not received a security token, you can regenerate it. Salesforce will then email the new security token to you. To regenerate your security token, log in to Salesforce and then choose Setup from the user menu. Search for "security token" in the Quick Find box. Choose Reset Security Token in the Reset Security Token page. When you receive the token in your email client, copy it and then paste it into the Token field.

4. Use the Test button to verify that you can successfully connect to Salesforce.

Inserting the Microsoft Access data

1. Create a new Microsoft Access database.
2. Create a table called Account with these columns:

   Column	Data Type
   ID	AutoNumber
   AccName	ShortText
   Property Description	ShortText
   Address	ShortText
   Town	ShortText
   PostCode	ShortText

3. Enter some sample data into the table. For example:

   AccName	Property Description	Address		Town	PostCode
   MyCo	Head Office		1 MyStreet	MyTown	AB1 DEF
   AcmeLtd	Workshop		1 MyRoad	MyTown	AB1 XYZ

4. Press ALT+F11 to start the Visual Basic Editor.
5. Insert a new module and add the following code. There are two subroutines and a helper function. Both subroutines insert the Access records into Salesforce in bulk. The second subroutine shows how to use a parameterised SOQL insert statement.

Option Compare Database

Sub InsertAccounts()

    Dim con As New ADODB.Connection
    Dim comm As New ADODB.Command
    Dim PrmName As New ADODB.Parameter
    Dim PrmAddress As New ADODB.Parameter
    Dim PrmTown As New ADODB.Parameter
    Dim PrmPostCode As New ADODB.Parameter
    Dim PrmDescription As New ADODB.Parameter
    Dim RowCount As Long
    Dim i As Integer
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim BlockCount As String
    Dim isPosted As Boolean
    
    RowCount = 0
    
    ' Open the connection to the Easysoft Salesforce SOQL ODBC Driver data source
    con.Open "SFSOQL"
    
    comm.ActiveConnection = con
    
    ' Set up the initial insert statement using ? for each column I am going to pass in
    comm.CommandText = "insert into Account (Name, BillingStreet, BillingCity, BillingPostalCode, Description ) values ( ?, ?, ?, ?, ? )"
    
    ' Bind all the columns to the statement
    Set PrmName = comm.CreateParameter("P1", adVarWChar, adParamInput, 255, Null)
    Set PrmAddress = comm.CreateParameter("P2", adVarWChar, adParamInput, 255, Null)
    Set PrmTown = comm.CreateParameter("P3", adVarWChar, adParamInput, 120, Null)
    Set PrmPostCode = comm.CreateParameter("P4", adVarWChar, adParamInput, 60, Null)
    Set PrmDescription = comm.CreateParameter("P5", adLongVarWChar, adParamInput, 255, Null)
    comm.Parameters.Append PrmName
    comm.Parameters.Append PrmAddress
    comm.Parameters.Append PrmTown
    comm.Parameters.Append PrmPostCode
    comm.Parameters.Append PrmDescription
    
    ' Create a connection to the local database and start working through the rows
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Account order by Id")
    BlockCount = 0
    Do While Not rs.EOF
    
        RowCount = RowCount + 1
    
        If BlockCount = 0 Then
            ' Start a new transaction
            con.BeginTrans
            isPosted = False
        End If
        BlockCount = BlockCount + 1
        
        Debug.Print RowCount & " : " & rs.Fields("AccName")
        DoEvents
        
        ' Prepare to transfer the data to the ODBC driver
        PrmName.Value = rs.Fields("AccName")
        
        If Not IsNull(rs.Fields("Address")) Then
            PrmAddress.Value = Replace(rs.Fields("Address"), ",", vbCrLf)
        Else
            PrmAddress.Value = Null
        End If

        If Not IsNull(rs.Fields("Town")) Then
            PrmTown.Value = rs.Fields("Town")
        Else
            PrmTown.Value = Null
        End If
        
        If Not IsNull(rs.Fields("Town")) Then
            PrmPostCode.Value = rs.Fields("PostCode")
        Else
            PrmPostCode.Value = Null
        End If
        
        If Not IsNull(rs.Fields("Property Description")) Then
            PrmDescription.Value = rs.Fields("Property Description")
        Else
            PrmDescription.Value = Null
        End If
        
        comm.Execute
    
        ' When 200 rows have been sent to the driver, commit
        If BlockCount = 200 Then
            Debug.Print "Block posted"
            con.CommitTrans
            isPosted = True
            BlockCount = 0
        End If
        
        ' Loop through the block until the end is reached
        rs.MoveNext
    Loop
    rs.Close
    db.Close
    
    ' Finally, if there are any rows left to commit, send them
    If Not isPosted Then con.CommitTrans
    
    con.Close
    
End Sub

Sub InsertAccountsParameterisedSOQL()

    Dim con As New ADODB.Connection
    
    Dim SQL As String
    Dim SQLBase As String
    Dim BlockCount As Long
    Dim isPosted As Boolean
    
    Dim RowCount As Long
    Dim i As Integer
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    RowCount = 0
    
    ' Open the connection to the Easysoft Salesforce SOQL ODBC Driver data source
    con.Open "SFSOQL"
    
    SQLBase = "insert into Account (Name, BillingStreet, BillingCity, BillingPostalCode, Description ) values ( "

    ' Create a connection to the local database and start working through the rows
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Account order by Id")
    BlockCount = 0
    Do While Not rs.EOF
    
        RowCount = RowCount + 1
    
        If BlockCount = 0 Then
      
            ' Start a new transaction
            con.BeginTrans
            isPosted = False
        End If
        BlockCount = BlockCount + 1
        
        Debug.Print RowCount & " : " & rs.Fields("AccName")
        DoEvents
        
        ' Prepare to transfer the data to the ODBC driver
        SQL = SQLBase
        If IsNull(rs.Fields("AccName")) Then
            SQL = SQL & "NULL, "
        Else
            SQL = SQL & "'" & EscQuotes(rs.Fields("AccName")) & "', "
        End If
        
        If IsNull(rs.Fields("Address")) Then
            SQL = SQL & "NULL, "
        Else
            SQL = SQL & "'" & EscQuotes(Replace(rs.Fields("Address"), ",", vbCrLf)) & "', "
        End If

        If Not IsNull(rs.Fields("Town")) Then
            SQL = SQL & "NULL, "
        Else
            SQL = SQL & "'" & EscQuotes(rs.Fields("Town")) & "', "
        End If
        
        If IsNull(rs.Fields("PostCode")) Then
            SQL = SQL & "NULL, "
        Else
            SQL = SQL & "'" & EscQuotes(rs.Fields("PostCode")) & "', "
        End If
        
        If IsNull(rs.Fields("Property Description")) Then
            SQL = SQL & "NULL) "
        Else
            SQL = SQL & "'" & EscQuotes(rs.Fields("Property Description")) & "')"
        End If
        
        con.Execute SQL
    
        ' When 200 rows have been sent to the driver then commit
        If BlockCount = 200 Then
            Debug.Print "Block posted"
            con.CommitTrans
            isPosted = True
            BlockCount = 0
        End If
        
        ' Loop through the block until the end is reached
        rs.MoveNext
    Loop
    rs.Close
    db.Close
    
    ' Finally, if there are any rows left to commit, send them
    If Not isPosted Then con.CommitTrans
    
    con.Close
    
End Sub

Function EscQuotes(inpStr As String) As String

    EscQuotes = Replace(inpStr, "'", "''")

End Function

6. On the Run menu, use Run Sub/UserForm to run the subroutines.