Showing results for 
Search instead for 
Did you mean: 

Help with MS Access API



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





Have you come up with any VBA code?


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





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.


Developer Community Manager

'=============================================================================================== '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: 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, ' 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 ' The Analysis and Solutions Company ' ------------------------------------------------------------------------ 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 = "" 'Live Post URL 'PostUrl = "" '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

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

Simply add "Property Let"s to add more fields