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:
- Install and license the Salesforce.com ODBC Driver on the machine where Microsoft Access is installed.
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:
- In ODBC Administrator, choose the System DSN tab, and then choose Add.
- In the Create New Data Source dialog box, choose Easysoft Salesforce ODBC SOQL Driver, and then choose Finish.
- 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.
- Use the Test button to verify that you can successfully connect to Salesforce.com.
Microsoft Access
- Create a new Microsoft Access database.
- Create a table called Account with these columns:
Column Data Type ID AutoNumber AccName ShortText Property Description ShortText Address ShortText Town ShortText PostCode ShortText - 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
- Press ALT+F11 to start the Visual Basic Editor.
- 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.
- On the Run menu, use Run Sub/UserForm to run the subroutines.
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