Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects

M

mdlueck

Sr. Application Developer
Local time
Yesterday, 22:12
Joined
Jun 23, 2011
Messages
2,651
There are very few examples to be found on the Internet of using an ADODB.Command with ADODB.Parameters objects to issue a SQL INSERT statement to an Access table. Here is a working example! :D

Code: 
Public Function Insert() As Boolean
On Error GoTo Err_Insert

  Dim adoCMD As Object
  Dim adoRS As Object
  Dim strSQL As String

  'Define a query to INSERT a new record into the FE temp table
  strSQL = "INSERT INTO [" & Me.FETempTableName & "] ([partnumber],[title],[qtyper],[oldqtyper],[addpartrecordflg],[doneflg])" & vbCrLf & _
           "VALUES (p1,p2,p3,p4,p5,p6);"

  'Define attachment to database table specifics
  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
    .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
    .Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
    .Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)
    .Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, True)
    .Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, False)
    .CommandText = strSQL
    Set adoRS = .Execute
  End With

  'Return a good return code
  Insert = True

Exit_Insert:
  'Clean up the connection to the database
  Set adoRS = Nothing
  Set adoCMD = Nothing

  Exit Function

Err_Insert:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Insert()")
  Insert = False
  Resume Exit_Insert

End Function
I found lists of Parameters Types here:

"ADO » Command » CreateParameter"
http://www.devguru.com/technologies/ado/8528.asp

"ADO CreateParameter Method"
http://www.w3schools.com/ado/met_comm_createparameter.asp

And I finally found the list of Type Sizes here:

"Data Type Mapping"
http://www.carlprothman.net/Default.aspx?tabid=97

The example which finally worked, just was a bit more elaborate than I needed was found here:

"Problems using a SQL INSERT command with ADODB"
http://www.tek-tips.com/viewthread.cfm?qid=1042219
Posting by: "fredericofonseca (IS/IT--Management) 25 Apr 05 12:27"

Update Note: I have updated this post to use Late-Binding syntax to create the ADO object(s). You will also need to use this post to obtain all of the constants ADO utilizes in order to have complete success with Late-Binding.

ADO Constants for use with Late Binding ActiveX Data Objects 2.8 Library
http://www.access-programmers.co.uk/forums/showthread.php?t=243088
 
Last edited:
Re: Example of SQL INSERT using ADODB.Command and ADODB.Parameters objects

And posting an example of how to issue a SQL UPDATE using the same technology:

Code: 
Public Function Update() As Boolean
On Error GoTo Err_Update

  Dim adoCMD As Object
  Dim adoRS As Object
  Dim strSQL As String
  Dim lRecordsAffected As Long

  'Define a query to Update a new record into the FE temp table
  strSQL = "UPDATE [" & Me.FETempTableName & "]" & vbCrLf & _
           "SET [partnumber] = p1," & vbCrLf & _
           "[title] = p2," & vbCrLf & _
           "[qtyper] = p3," & vbCrLf & _
           "[oldqtyper] = p4," & vbCrLf & _
           "[addpartrecordflg] = p5," & vbCrLf & _
           "[doneflg] = p6" & vbCrLf & _
           "WHERE [aid] = p7;"

  'Define attachment to database table specifics
  Set adoCMD = CreateObject("ADODB.Command")
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
    .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
    .Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
    .Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)
    .Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, Me.addpartrecordflg)
    .Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, Me.doneflg)
    .Parameters.Append .CreateParameter("p7", adInteger, adParamInput, 4, Me.aid)
    .CommandText = strSQL
    Set adoRS = .Execute(lRecordsAffected)
  End With

  If lRecordsAffected = 0 Then
    Update = False
  Else
    'Return a good return code
    Update = True
  End If

Exit_Update:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_Update:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Update()")
  Update = False
  Resume Exit_Update

End Function
For getting the Update method to work, I found this posting helpful: (Specifically the warning)

"Using Parameters with an ADO Command"
http://www.xtremevbtalk.com/showthread.php?t=309329#post1337389

Update Note: I have updated this post to use Late-Binding syntax to create the ADO object(s). You will also need to use this post to obtain all of the constants ADO utilizes in order to have complete success with Late-Binding.

ADO Constants for use with Late Binding ActiveX Data Objects 2.8 Library
http://www.access-programmers.co.uk/forums/showthread.php?t=243088
 
Last edited:
Re: Example of SQL INSERT using ADODB.Command and ADODB.Parameters objects

mdlueck said:
And posting an example of how to issue a SQL UPDATE using the same technology:

Code: 
Public Function Update() As Boolean
On Error GoTo Err_Update

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim strSQL As String
  Dim lRecordsAffected As Long

  'Define a query to Update a new record into the FE temp table
  strSQL = "UPDATE [" & Me.FETempTableName & "]" & vbCrLf & _
           "SET [partnumber] = ?," & vbCrLf & _
           "[title] = ?," & vbCrLf & _
           "[qtyper] = ?," & vbCrLf & _
           "[oldqtyper] = ?," & vbCrLf & _
           "[addpartrecordflg] = ?," & vbCrLf & _
           "[doneflg] = ?" & vbCrLf & _
           "WHERE [aid] = ?;"

  'Define attachment to database table specifics
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
    .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, 50, Me.title)
    .Parameters.Append .CreateParameter("p3", adSmallInt, adParamInput, 2, Me.qtyper)
    .Parameters.Append .CreateParameter("p4", adSmallInt, adParamInput, 2, Me.oldqtyper)
    .Parameters.Append .CreateParameter("p5", adBoolean, adParamInput, 2, Me.addpartrecordflg)
    .Parameters.Append .CreateParameter("p6", adBoolean, adParamInput, 2, Me.doneflg)
    .Parameters.Append .CreateParameter("p7", adInteger, adParamInput, 4, Me.aid)
    Set adoRS = .Execute(lRecordsAffected)
  End With

  If lRecordsAffected = 0 Then
    Update = False
  Else
    'Return a good return code
    Update = True
  End If

Exit_Update:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_Update:
  Call errorhandler_MsgBox("Class: clsObjPartsImportWizardTbl, Function: Update()")
  Update = False
  Resume Exit_Update

End Function
For getting the Update method to work, I found this posting helpful: (Specifically the warning)

"Using Parameters with an ADO Command"
http://www.xtremevbtalk.com/showthread.php?t=309329#post1337389
Click to expand...

I believe my SQL statement is correct now.
Code: 
strSQL = "INSERT INTO Temp ([CarrierName], [InsuranceCompanyA]) VALUES ('" & CarrierName & "', '" & InsuranceCompanyA & "');"

But I am having issues getting the information to appear in the proper fields. I think it has to do with my End Ifs being in the wrong spots.
 
Re: Example of SQL INSERT using ADODB.Command and ADODB.Parameters objects

frustrating said:
I believe my SQL statement is correct now.
Code: 
strSQL = "INSERT INTO Temp ([CarrierName], [InsuranceCompanyA]) VALUES ('" & CarrierName & "', '" & InsuranceCompanyA & "');"
But I am having issues getting the information to appear in the proper fields. I think it has to do with my End Ifs being in the wrong spots.
Click to expand...

frustrating, from the looks of your code I do not believe you are using ADODB.Command and ADODB.Parameters type objects, nor do I see any "End Ifs" in the small code sample you provided.

Perhaps come to the normal Modules & VBA section to receive assistance.
 
You must log in or register to reply here.

Similar threads

P
Save and Retrieve From Access Backend as BLOB
Replies
5
Views
3,091
kentgorrell
K
K
ADO Reference
Replies
10
Views
1,600
khurram7x
K
H
Advantage/Disadvantage of using a Direct Update VS ADO connection
Replies
8
Views
1,416
static
S
M
Retrieve Autonumber ID after adoCMD INSERT via adoRS query
Replies
0
Views
4,925
mdlueck
M
Z
StoreProcedure not working for Excel to Access Update
Replies
3
Views
1,305
JHB
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom