Rerferences issue?

[TallMan]

Hello-

I am a bit confused regarding the below code. I am working with another department within my company and they have been caring enough to share code that will import a text file into an access table. I am importing the same exact text file that they are as well trying to import into the same table name. If you are wondering why I do not link directly to the othre departments databse....it is complicated.

I have set up a form with a button, when I click the button I want the following code to execute and bring in the .ttx file from my share drive.

When I click the button I get the message "Compile Error: User Defined type not defined" The section of the code it highlights is in the declaration section "New ADODB.Recordset"

DOes this have something to do with the correct references not being checked? What is crazy is the EXACT code works on my colleagues PC.

Private Sub Command2_Click()
MsgBox "Command may take more than 1 minute to run.", vbInformation, "AMS"
Dim booFirstLineRead As Boolean
Dim intFile As Integer
Dim intLoop As Integer
Dim strBuffer As String
Dim strFile As String
Dim strsql As String
Dim strSQLHeader As String
Dim varFields As Variant
Dim varValues As Variant
Set cncurrent = CurrentProject.Connection
Set rsdiag = New ADODB.Recordset ''Error Message Highlights HERE
'Delete all records from Table
strsql = "DELETE * FROM tblTempAcctData"
rsdiag.Open strsql, cncurrent
  intFile = FreeFile()
  Open "\\ABCD$\C_Share$\Compliance\aiv accounts.ttx" For Input As #intFile
  strsql = "Select * from tblTempAcctData"
  rsdiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
  'Open strFile For Input As #intFile
  Line Input #intFile, strBuffer
  Do While Not EOF(intFile)
' Read in an entire line
    Line Input #intFile, strBuffer
    varValues = Split(strBuffer, vbTab)
    rsdiag.AddNew
    rsdiag!acct_fa_num = Replace(varValues(0), """", "")
    rsdiag!Acct_Num = Replace(varValues(1), """", "") & "0"
    rsdiag!acc_grp_grp_id = Replace(varValues(2), """", "")
    rsdiag!cusip_num = Replace(varValues(3), """", "")
    rsdiag!Type = Replace(varValues(4), """", "")
    rsdiag!product_name = Replace(varValues(5), """", "")
    'aiv_investment_value
        If Replace(Trim(varValues(6)), """", "") = "" Then
            rsdiag!aiv_investment_value = 0
        Else
            rsdiag!aiv_investment_value = Replace(varValues(6), """", "")
        End If
    'account_value
        If Replace(Trim(varValues(7)), """", "") = "" Then
            rsdiag!Account_Value = 0
        Else
            rsdiag!Account_Value = Replace(varValues(7), """", "")
        End If
    'aiv_account_value
        If Replace(Trim(varValues(8)), """", "") = "" Then
            rsdiag!aiv_account_value = 0
        Else
            rsdiag!aiv_account_value = Replace(varValues(8), """", "")
        End If
   'household_value
        If Replace(Trim(varValues(9)), """", "") = "" Then
            rsdiag!Household_Value = 0
        Else
            rsdiag!Household_Value = Replace(varValues(9), """", "")
        End If
   'aiv_household_value
        If Replace(Trim(varValues(10)), """", "") = "" Then
            rsdiag!aiv_household_value = 0
        Else
            rsdiag!aiv_household_value = Replace(varValues(10), """", "")
        End If
   'percent_of_account_in_this_product
        If Replace(Trim(varValues(11)), """", "") = "" Then
            rsdiag!percent_of_account_in_this_product = 0
        Else
            rsdiag!percent_of_account_in_this_product = Replace(varValues(11), """", "")
        End If
   'percent_of_account_in_AIG_products
        If Replace(Trim(varValues(12)), """", "") = "" Then
            rsdiag!percent_of_account_in_AIG_products = 0
        Else
            rsdiag!percent_of_account_in_AIG_products = Replace(varValues(12), """", "")
        End If
   'percent_of_household_in_this_product
        If Replace(Trim(varValues(13)), """", "") = "" Then
            rsdiag!percent_of_household_in_this_product = 0
        Else
            rsdiag!percent_of_household_in_this_product = Replace(varValues(13), """", "")
        End If
   'percent_of_household_in_AIG_products
        If Replace(Trim(varValues(14)), """", "") = "" Then
            rsdiag!percent_of_household_in_AIG_products = 0
        Else
            rsdiag!percent_of_household_in_AIG_products = Replace(varValues(14), """", "")
        End If
    rsdiag!Class = Replace(varValues(15), """", "")
    rsdiag!sec_type_code = Replace(varValues(16), """", "")
    rsdiag!td_quantity = Replace(varValues(17), """", "")
    rsdiag!Price = Replace(varValues(18), """", "")
 
    rsdiag.Update
    Loop
    ' Close the data file.
    Close #intFile
    rsdiag.Close
End Sub

 

[boblarson]

You need to set a reference to ADO in the VBA window.

Go to TOOLS > REFERENCES in the VBA window and select

Microsoft ActiveX Objects 2.x Library

where .x is the highest number you have listed.

 

[TallMan]

You are the man!!!

Thanks BOB!

 

[boblarson]

No problem. Glad to be able to help.