topic Re: Help with MS Access API in Integration and Testing

Help with MS Access API

dark11star — Wed, 17 Mar 2010 14:17:20 GMT

Hi,

I'm new to this and need a way to connect to authorize.net from MS Access. Can anyone point me to some informaion or sample code for VBA?

Thanks,

Jason

Re: Help with MS Access API

stanjones0net — Wed, 22 Sep 2010 18:39:48 GMT

Have you solved your problem yet. I am also using Access and the solution is fairly simple (after trying to do it the hard way).

Re: Help with MS Access API

grippy — Thu, 21 Oct 2010 04:23:17 GMT

I would like some more information about this. We cant find any MS Access sample code to connect to authorize.net anywhere. Is it even possible?

Re: Help with MS Access API

stanjones0net — Thu, 21 Oct 2010 16:56:05 GMT

Yes it is possible. Look at the sample code page.

Under the first heading Advanced Integration Method (AIM) download the AIM - ASP Classic code.

This is actually vb script code (almost VBA). Copy and paste into a Access module, delete or comment out the HTML, and make the vb script a public subroutine called "test."

I removed the reference to "Scripting.Dictionary" and built post_values (as a string) manually, just for testing purposes.

Also set a reference to "Microsoft XML, v6.0" then remove the line:

Set objRequest = Server.CreateObject("Microsoft.XMLHTTP")

and add the lines:

Dim objRequest As MSXML2.XMLHTTP

Set objRequest = New MSXML2.XMLHTTP

At the end of the code replace the Response.Write routine with Debug.Print in a loop.

It been a while since I did this so you will have to clean up some of the other code also.

Add your testing credentials and run the code. Keep me posted with your results.

Re: Help with MS Access API

YK — Sun, 01 May 2011 19:49:28 GMT

Thanks for this i was looking for such help for a while.

1 thing i canot figure out is the line below:

I removed the reference to "Scripting.Dictionary" and built post_values (as a string) manually, just for testing purposes.

how to built the post_values, if you can please give some detail instrutions.

Thanks so much

Re: Help with MS Access API

stanjones0net — Mon, 02 May 2011 16:38:03 GMT

You are building the post_string something like:

Public Sub test()
    Dim Post_Url
    Post_Url = "https://test.authorize.net/gateway/transact.dll"
    'post_url = "https://secure.authorize.net/gateway/transact.dll"
    Dim post_string         As String
    Dim post_response       As String
    'the API Login ID and Transaction Key must be replaced with valid values
    post_string = ""
    post_string = post_string & "x_login=" & URLEncode("API_LOGIN_ID") & "&"
    post_string = post_string & "x_tran_key=" & URLEncode("TRANSACTION_KEY") & "&"
    post_string = post_string & "x_delim_data=" & URLEncode("TRUE") & "&"
    post_string = post_string & "x_delim_char=" & URLEncode("|") & "&"
    post_string = post_string & "x_relay_response=" & URLEncode("FALSE") & "&"
    post_string = post_string & "x_type=" & URLEncode("AUTH_CAPTURE") & "&"
    post_string = post_string & "x_method=" & URLEncode("CC") & "&"
    post_string = post_string & "x_card_num=" & URLEncode("4111111111111111") & "&"
    post_string = post_string & "x_exp_date=" & URLEncode("0115") & "&"
    post_string = post_string & "x_amount=" & URLEncode("19.98") & "&"
    post_string = post_string & "x_description=" & URLEncode("Sample Transaction") & "&"
    post_string = post_string & "x_first_name=" & URLEncode("John") & "&"
    post_string = post_string & "x_last_name=" & URLEncode("Doe") & "&"
    post_string = post_string & "x_address=" & URLEncode("1234 Street") & "&"
    post_string = post_string & "x_state=" & URLEncode("WA") & "&"
    post_string = post_string & "x_zip=" & URLEncode("98004") & "&"
    ' Additional fields can be added here as outlined in the AIM integration
    ' guide at: http://developer.authorize.net
    post_string = Left(post_string, Len(post_string) - 1)
    ' We use xmlHTTP to submit the input values and record the response
    Dim objRequest          As New MSXML2.XMLHTTP
    objRequest.Open "POST", Post_Url, False
    objRequest.send post_string
    post_response = objRequest.responseText
    Debug.Print post_response
    Set objRequest = Nothing
    ' the response string is broken into an array using the specified delimiting character
    Dim response_array '(100) As String
    response_array = Split(post_response, "|", -1)
' now use Debug.Print to write out response_array
End Sub

Public Function URLEncode(xxx As String) As String
    URLEncode = Replace(xxx, " ", "%20")
End Function


This really old code I found laying around but it still works.

Re: Help with MS Access API

hotdryice — Tue, 21 Feb 2012 07:07:00 GMT

Thank you. This code works perfect. I did not put the code in a module. Instead i put it directly behind a form. Easier I think. :smileyhappy:

Re: Help with MS Access API

stanjones0net — Tue, 20 Mar 2012 20:15:42 GMT

Actually I built a complete set of Class modules which are referenced from my application. This way I can fill out the request to Authorize.Net with regular looking code and not a lot of string manipulation in the Form itself. The class also includes any encryption and base 64 coding necessary.

Re: Help with MS Access API

karl3200 — Fri, 04 May 2012 15:14:48 GMT

Do you have an sample VBA code for the Transaction Details API (downloading the settled batch list , transacion list for speicified batches, unsettled transactions list etc)

Thanis

Re: Help with MS Access API

stanjones0net — Tue, 08 May 2012 15:25:21 GMT

I don't have any code for Transactions. I briefly looked at the sample code but didn't find anything appropriate. My code is directed at charging cards. You could try working with MSXML2 methods and properties, e.g. responseXML. If you have any success please let me know.

Re: Help with MS Access API

thenelson — Thu, 26 Jul 2012 14:09:47 GMT

Have you come up with any VBA code?

Strong can you share the code you have for charging cards?

Thanks

Re: Help with MS Access API

Michelle — Fri, 27 Jul 2012 19:56:38 GMT

Hey thenelson,

It's been a while since this was first posted. I would recommend subscribing to this topic so that you'll be alerted via email if anyone from the community is able to respond with any comments. To subscribe, click Topic Options at the top of this thread and then select Subscribe. You'll then receive an email once anyone replies.

Thanks,

Michelle
Developer Community Manager

Re: Help with MS Access API

busyfritz — Tue, 10 Feb 2015 22:23:26 GMT


'===============================================================================================
'Card Transaction Class for Authorize.Net
'Written in 2014 by Freeman Helmuth
'Use this code any way you would like to with the exception of the portions
'that are copyright by others.
'
'Reference Requirements:
'       Microsoft XML, v6.0
'       Microsoft Scripting Runtime
'===============================================================================================

Option Compare Database
Option Explicit

'===============================================================================================
'                                    Private Variables
Private PostValues As Scripting.Dictionary
Private ResponseArray As Variant
'                                  End Private Variables
'===============================================================================================


'===============================================================================================
'                                 Property Let's
'
'Additional fields can be added here by adding a property let and a call to the
'AddPostField function with field names as outlined in the
'AIM integration guide at: http://developer.authorize.net

Property Let FirstName(Value As String)
    AddPostField "x_first_name", Value
End Property

Property Let LastName(Value As String)
    AddPostField "x_last_name", Value
End Property

Property Let AddressLine1(Value As String)
    AddPostField "x_address", Value
End Property

Property Let State(Value As String)
    If Len(Value) = 2 And Not IsNumeric(left(Value, 1)) And Not IsNumeric(left(Value, 2)) Then
        AddPostField "x_state", Value
    Else
        MsgBox "The state must be a two digit non numeric value"
    End If
End Property

Property Let ZipCode(Value As Integer)
    If Len(Value) = 5 And IsNumeric(Value) Then
        AddPostField "x_zip", Value
    Else
        MsgBox "The zip must be a 5 digit numeric value"
    End If
End Property

Property Let CardData(Value As String)
'Complete Card Data or track 1 or 2 values can be passed to this property

    Select Case left(Value, 1)
    Case "%"
        'Track 1 data
        AddPostField "x_track1", Mid(Value, 2, InStr(Value, "?") - 2)
    Case ";"
        'Track 2 data
        AddPostField "x_track2", Mid(Value, 2, InStr(Value, "?") - 2)
    Case Else
        MsgBox "Invalid Track Data", , "Track Data Error"
    End Select
End Property

Property Let CardNumber(Number As String)
'==============================================================
'This copyright notice applies to some parts of the code below
'==============================================================

' ------------------------------------------------------------------------
' Credit Card Validation Solution, version 3.7        Visual Basic Edition
' December 20, 2002
'
' ------------------------------------------------------------------------
' DESCRIPTION:
' Credit Card Validation Solution uses a four step process to ensure
' credit card numbers are keyed in correctly.  This procedure accurately
' checks cards from Discover, JCB, MasterCard and Visa.
'
' ------------------------------------------------------------------------
' CAUTION:
' CCVS uses exact number ranges as part of the validation process. These
' ranges are current as of 10 Nov 2014.  If presently undefined ranges
' come into use in the future, this program will improperly reject card
' numbers in such ranges, rendering an error message entitled "Potential
' Card Type Discrepancy."  If this happens while entering a card & type
' you KNOW are valid, please contact us so we can update the ranges.
'
' ------------------------------------------------------------------------
' POTENTIAL CUSTOMIZATIONS:
' *  If you don't accept some of these card types, just comment out that
' section of the code by putting a single quote mark "'" at the beginning
' of the "Case" and "ShouldLength" lines in question.
' *  Additional card types can be added by inserting new "Case," and
' "ShouldLength" lines and adding the cardtype to the cardtype function
' *  The three functions here can be called from elsewhere in your databse
' to check any number.
'
' ------------------------------------------------------------------------
' CREDITS:
' We learned of the Mod 10 Algorithm in some Perl code, entitled
' "The Validator," available on Matt's Script Archive,
' http://worldwidemart.com/scripts/readme/ccver.shtml.  That code was
' written by David Paris, who based it on material Melvyn Myers reposted
' from an unknown author.  Paris credits Aries Solis for tracking down the
' data underlying the algorithm.  At the same time, our code bears no
' resemblance to its predecessors.  My thanks to Allen Browne and
' Rico Zschau for feedback and further refinements.
'
' ------------------------------------------------------------------------
' COPYRIGHT NOTICE:
' a) This code is property of The Analysis and Solutions Company.
' b) It is being distributed free of charge and on an "as is" basis.
' c) Use of this code, or any part thereof, is contingent upon leaving
'     this copyright notice, name and address information in tact.
' d) Written permission must be obtained from us before this code, or any
'     part thereof, is sold or used in a product which is sold.
' e) By using this code, you accept full responsibility for it's use
'     and will not hold the Analysis and Solutions Company, its employees
'     or officers liable for damages of any sort.
' f) This code is not to be used for illegal purposes.
' g) Please email us any revisions made to this code.
' h) This code can not be reposted.  Sites such as code repositories
'     need to provide a link directly to our URI, below.
'
' Copyright 2002      http://www.AnalysisAndSolutions.com/code/ccvs-vb.htm
' The Analysis and Solutions Company         info@AnalysisAndSolutions.com
' ------------------------------------------------------------------------

    Dim NumberLength As Integer
    Dim CardType As String
    Dim ShouldLength As Integer
    Dim Missing As Integer

    'Get rid of spaces and non-numeric characters.
    Number = OnlyNumeric(Number)
    'Get the card type
    CardType = GetCardType(Number)
    'Get the length of the card number
    NumberLength = Len(Number)

    'Get which length the card number should be for the card type
    Select Case CardType
    Case "American Express"
        ShouldLength = 15
    Case "Visa"
        Select Case NumberLength
        Case Is > 14
            ShouldLength = 16
        Case Is < 14
            ShouldLength = 13
        Case Else
            MsgBox "The Visa number you typed in is 14 digits long." & Chr(13) & "Visa cards usually have 16 digits, though some have 13." & Chr(13) & Chr(13) _
                   & "Please check the number and try again.", vbExclamation, "Invalid Length:"
            Exit Property
        End Select
    Case "MasterCard"
        ShouldLength = 16
    Case "Discover"
        ShouldLength = 16
    Case "JCB"
        ShouldLength = 16
    Case Else
        MsgBox "The first four digits of the number entered are " & left(Number, 4) & "." & Chr(13) _
               & "If that's correct, we don't accept that type of credit card." & Chr(13) _
               & "If it's wrong, please try again.", vbExclamation, "Potential Card Type Discrepancy:"
        Exit Property
    End Select

    'Check that the number is the right length
    If NumberLength <> ShouldLength Then
        Missing = NumberLength - ShouldLength
        If Missing < 0 Then
            MsgBox "The " & CardType & " number entered, " & Number & ", is missing " & Abs(Missing) & " digit(s)." & Chr(13) & Chr(13) _
                   & "Please check the number and try again.", vbExclamation, "Invalid Length:"
        Else
            MsgBox "The " & CardType & " number entered, " & Number & ", has " & Missing & " too many digit(s)." & Chr(13) & Chr(13) _
                   & "Please check the number and try again.", vbExclamation, "Invalid Length:"
        End If
        Exit Property
    End If

    'Does the number pass the Mod 10 Algorithm Checksum?
    If Mod10Solution(Number) = False Then
        MsgBox "The algorithm of the " & CardType & " number entered, " & Number & ", is invalid." & Chr(13) & Chr(13) _
               & "Please check the number and try again.", vbExclamation, "Invalid Algorithm"
        Exit Property
    End If

    AddPostField "x_card_num", Number
End Property

Property Let ExpirationDate(Value As String)

'Check if the correct number of digits have been given
    If Len(Value) <> 4 Then
        MsgBox "Invalid Expiration Date"
        Exit Property
    Else
        'Check if the card has expired
        If left(Value, 2) < Month(Date) And Right(Value, 2) <= Year(Date) Then
            MsgBox "This card expired on " & left(Value, 2) & "/" & Right(Value, 2)
            Exit Property
        End If
    End If

    AddPostField "x_exp_date", Value
End Property

Property Let VerificationCode(Value As String)
    AddPostField "x_card_code", Value
End Property

Property Let Amount(Value As String)

    If Not left(Right(Value, 3), 1) = "." Then
        'Make sure that we have two decimal points
        MsgBox "You must provide exactly two decimal places"
        Exit Property
    End If

    AddPostField "x_amount", Value
End Property

Property Let TransactionType(Value As String)

'Transaction Type | DESCRIPTION
'=========================================================================================================
'AUTH_CAPTURE       Transactions of this type are sent for authorization. The transaction is automatically
'                   picked up for settlement if approved. This is the default transaction type in the
'                   gateway. If no type is indicated when submitting transactions to the gateway, the
'                   gateway will assume that the transaction is of the type AUTH_CAPTURE.
'                   If the approved amount is less than the requested amount and the transaction
'                   qualifies as a partial authorization transaction, see the special conditions described
'                   in "Credit Card Transaction Processing," page 16 of the PDF.

'AUTH_ONLY          Transactions of this type are submitted if the merchant wishes to validate the credit
'                   card for the amount of the goods sold. If the merchant does not have goods in stock
'                   or wishes to review orders before shipping the goods, this transaction type should
'                   be submitted. The gateway will send this type of transaction to the financial
'                   institution for approval. However this transaction will not be sent for settlement. If the
'                   merchant does not act on the transaction within 30 days, the transaction will no
'                   longer be available for capture.

'PRIOR_AUTH_CAPTURE This transaction is used to request settlement for a transaction that was previously
'                   submitted as an AUTH_ONLY. The gateway will accept this transaction and initiate
'                   settlement if the following conditions are met:
'                   * The transaction is submitted with the ID of the original authorization-only
'                   transaction which needs to be settled.
'                   * The transaction ID is valid and the system has a record of the original
'                   authorization-only transaction being submitted.
'                   * The original transaction referred to is not already settled or expired or errored.
'                   * The amount being requested for settlement in this transaction is less than or
'                   equal to the original authorized amount.
'                   If no amount is submitted in this transaction, the gateway will initiate settlement for
'                   the amount of the originally authorized transaction.
'                   Note If extended line item, tax, freight, and/or duty information was submitted with
'                   the original transaction, adjusted information can be submitted in the event that the
'                   transaction amount changed. If no adjusted line item, tax, freight, and/or duty
'                   information is submitted, the information submitted with the original transaction will
'                   apply.

'CREDIT             This transaction is also referred to as a Refund, and indicates to the gateway that
'                   money should flow from the merchant to the customer. The gateway will accept a
'                   credit or a refund request if the transaction submitted meets the following conditions:
'                   The transaction is submitted with the ID of the original transaction against which the
'                   credit is being issued (x_ref_trans_id).
'                   * The gateway has a record of the original transaction.
'                   * The original transaction has been settled.
'                   * The sum of the amount submitted in the Credit transaction and all credits
'                   submitted against the original transaction is less than the original transaction
'                   amount.
'                   * The full or last four digits of the credit card number submitted with the credit
'                   transaction match the full or last four digits of the credit card number used in the
'                   original transaction.
'                   * The transaction is submitted within 120 days of the settlement date of the original
'                   transaction.
'                   A transaction key is required to submit a credit to the system (i.e., x_tran_key
'                   should have a valid value when a CREDIT transaction is submitted).
'                   For details about how to submit CREDIT transactions to the Payment Gateway,
'                   please see the Issuing Credits Guide.

    AddPostField "x_type", Value
End Property


'                                        End Property Let's
'===============================================================================================
'                                       AddPostField  Function
'This function sets the PostValues variable if it is null,
'and adds the given field to the post values.

Private Function AddPostField(Field As Variant, FieldValue As Variant)

    If PostValues Is Nothing Then
        Set PostValues = New Scripting.Dictionary
    End If

    PostValues.Add Field, FieldValue
End Function

'===============================================================================================
'                                       Process Sub
'Processes the card transaction
Sub Process()

    Dim PostString As String
    Dim PostUrl As String
    Dim PostResponse As String
    Dim PostField As String

    Dim objRequest As MSXML2.XMLHTTP
    Dim ApprovalStatus As Variant
    Dim Delimiter As String
    Dim Key As Variant

    Set objRequest = New MSXML2.XMLHTTP

    'Delimiter to use for delimited data
    Delimiter = "|"

    'Test Post URL
    PostUrl = "https://test.authorize.net/gateway/transact.dll"

    'Live Post URL
    'PostUrl = "https://secure.authorize.net/gateway/transact.dll"

    'The API Login ID and Transaction Key must have valid values
    '==============================================================
    'Sandbox Values
    PostValues.Add "x_login", "XXXXXXXXXXXXXXXXXX"
    PostValues.Add "x_tran_key", "XXXXXXXXXXXXXXXXX"

    'Live values for YOUR COMPANY NAME
'    PostValues.Add "x_login", "XXXXXXXXXXXXXXXXX"
'    PostValues.Add "x_tran_key", "XXXXXXXXXXXXXXXXX"
    '==============================================================

    'Use the test request only when connecting to the live post URL
    'PostValues.Add "x_test_request", "TRUE"

    'Various Transaction settings
    PostValues.Add "x_market_type", "2"    'Market type 2 is retail
    PostValues.Add "x_delim_data", "TRUE"
    PostValues.Add "x_delim_char", Delimiter
    PostValues.Add "x_relay_response", "FALSE"

    'Build the Post String
    PostString = ""
    For Each Key In PostValues.Keys
        PostString = PostString & Key & "=" & UrlEncode(PostValues(Key)) & "&"
    Next

    'Trim the last &
    PostString = left(PostString, Len(PostString) - 1)

    ' We use xmlHTTP to submit the input values and record the response
    objRequest.Open "POST", PostUrl, False
    objRequest.send PostString
    PostResponse = objRequest.responseText

    Set objRequest = Nothing

    'The response string is broken into an array using the specified delimiting character

    ResponseArray = Split(PostResponse, Delimiter, -1)

End Sub

'                                   End Process Sub
'===============================================================================================
'                                   Property Get's(For Results)

Property Get Result()
    Result = ResponseArray(3)
End Property

Property Get AuthCode()
    AuthCode = ResponseArray(4)
End Property

'                                   End Property Get's
'===============================================================================================
'                                   Supporting Functions

Private Function UrlEncode(ByVal StringToEncode As String) As String
    UrlEncode = Replace(StringToEncode, " ", "%20")
End Function

Private Function OnlyNumeric(Number As String)
    Dim Location As Integer
    Dim NumberLength As Integer
    Dim CurrentOutput As String
    Dim CurrentCharacter As String * 1

    NumberLength = Len(Number)
    If NumberLength > 50 Then
        ' Avoids system overload from hacking via super long input.
        NumberLength = 50
    End If


    ' Go through each number in the string.
    For Location = 1 To NumberLength
        CurrentCharacter = Mid(Number, Location, 1)
        Select Case Asc(CurrentCharacter)
        Case 48 To 57
            ' This character is a number,
            ' append it to the variable we're going to output.
            CurrentOutput = CurrentOutput & CurrentCharacter
        End Select
    Next

End Function

Property Get GetCardType(Optional Number As String)

    Dim CardNumber As String

    If Number = "" Then
        CardNumber = PostValues("x_card_num")
    Else
        CardNumber = Number
    End If

    Select Case left(CardNumber, 4)
    Case 3400 To 3527, 3590 To 3799
        GetCardType = "American Express"
    Case 4000 To 4999
        GetCardType = "Visa"
    Case 5100 To 5599
        GetCardType = "MasterCard"
    Case 6011, 6221 To 6229, 6440 To 6499, 6500 To 6599
        GetCardType = "Discover"
    Case 3528 To 3589
        GetCardType = "JCB"
    Case Else
        GetCardType = "Invalid"
    End Select
End Property

Private Function Mod10Solution(Number As String) As Boolean
'This works for numbers up to 255 characters long.
'For longer numbers, increase variable data types as needed.
    On Error GoTo ErrHandle
    Dim NumberLength As Byte    'Up to 255 digits.
    Dim Location As Byte     'Up to 255 digits.
    Dim Checksum As Integer  'Up to 3,640 digits.
    Dim Digit As Byte

    NumberLength = Len(Number)

    'Add even digits in even length strings
    'or odd digits in odd length strings.
    For Location = 2 - (NumberLength Mod 2) To NumberLength Step 2
        Checksum = Mid(Number, Location, 1) + Checksum
    Next Location

    'Analyze odd digits in even length strings
    'or even digits in odd length strings.
    For Location = (NumberLength Mod 2) + 1 To NumberLength Step 2
        Digit = Mid(Number, Location, 1) * 2
        If Digit < 10 Then
            Checksum = Digit + Checksum
        Else
            Checksum = Digit - 9 + Checksum
        End If
    Next Location

    'Is the checksum divisible by ten?
    Mod10Solution = (Checksum Mod 10 = 0)
    Exit Function

ErrHandle:
    MsgBox Error, vbExclamation, "Mod 10 Solution Had Error:"
    Mod10Solution = False

End Function

Here is a complete class for processing transactions with authorize.net

It should be very flexible to get whatever data you need.

Simply add "Property Let"s to add more fields