Easysoft Blog

Tue, 14 August 2018

Bulk Salesforce Inserts from Microsoft Access

Version 2+ of the Salesforce ODBC driver enables you to batch multiple SOQL Insert statements. This blog show you how to insert multiple Microsoft Access records into Salesforce.

To get started:

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

To run ODBC Administrator (which you use to create a data source), in the Windows Run dialog box, type this command if you are using a 64-bit version of Microsoft Office:

%windir%\system32\odbcad32.exe

–Or–

Type this command if you are using a 32-bit version of Microsoft Office:

%windir%\syswow64\odbcad32.exe

If you are not sure whether your version of Microsoft Office is 32-bit or 64-bit, start an Office application e.g. Microsoft Access, and then look for the application's process in Task Manager. If the process name is (for Microsoft Access) MSACCESS.EXE *32, Microsoft Office is 32-bit. If the process name is MSACCESS.EXE, Microsoft Office is 64-bit.

To create a Salesforce.com ODBC Driver data source:

  1. In ODBC Administrator, choose the System DSN tab, and then choose Add.
  2. In the Create New Data Source dialog box, choose Easysoft Salesforce ODBC SOQL Driver, and then choose Finish.
  3. Complete the Easysoft Salesforce SOQL ODBC Driver DSN Setup dialog box:
    Setting Value
    DSN SFSOQL
    User Name The name of your Salesforce.com user. For example, myuser@mydomain.com.
    Password The password for your Salesforce.com user.
    Token The security token for your Salesforce.com 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.com emails the security token to the email address associated with your Salesforce.com user account. If you have not received a security token, you can regenerate it. Salesforce.com will then email the new security token to you. To regenerate your security token, log in to Salesforce.com and then choose Setup from the user menu. Search for "security token" in the Quick Find box. Click 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.com.

Microsoft Access

Using the Salesforce ODBC driver to access Salesforce data as linked 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.
  6. 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
    
    
  7. On the Run menu, use Run Sub/UserForm to run the subroutines.