Posts by PCFish

    Re: Access run-time error '3265'


    Hi,


    Thanks for the link you provided some time ago Gollem. It was of use as it allowed me to determine that in actual fact the problem was caused by an incorrectly typed field name by the client which I had completely missed (because I hadn't expected that to even be an issue - no changes should have been made to the database structure) - just shows that you have to check even the most basic things. I had to wait some time before the client got back to me (he was away for a few weeks) and tested the changes, but I have finally heard back that the problem has been solved.


    So, thanks for your help.


    Cheers,

    Hi all,


    I had to modify a small portion of code in an add-in (added an extra sub to extract data from a new column in an excel spreadsheet to an Access database) for an overseas client. The add-in worked fine before the modification, but it is now returning a Run-time error:'3265'. No alterations were made to the sub that appends the data to the table and where the ADODB connection and recordset is made, so I'm not sure why this error should now appear. There is no error when I run the add-in on my own PC, but it is obviously occurring on the client's machine.


    Any suggestions on how I can resolve this problem will be greatly appreciated.


    Thanks very much.

    :bsmile: :bouncing:
    Just heard that after updating the client's version of MDAC to v 2.7, the add-in now runs successfully. So, the direct cause of the problem was the old MDAC version that was installed on the client's machine.


    I will probably make the additional code changes that you suggested Dennis to avoid any future problems due to this issue, but for now the problem has been solved.


    Thanks everyone for your suggestions - I'd never have worked out the problem without your help, at least not in this century!


    Cheers :cheers:

    Thanks again Xl-Dennis,


    I'll look at amending my code further.


    Regarding the database connectivity that we are experiencing, our guy overseas tried the changed code (strConn statement changed & using adCmdTableDirect) but the routine still crashes in the same place. Would making the other suggested changes help resolve this situation?


    Also, the comments from our guy overseas regarding MDAC are as follows:


    "On my laptop I have version 2.70.7713.4 (from regedit) or 2.7 RPM (from a tool called component checker [CC], designed specifically to find out what version of MDAC you have without having to use regedit - from a MSDN site), while the client's machine has version 2.53 (from CC). The question is what do I do about it. There are MDAC downloads available from Microsoft, but none of them seem to have included the Microsoft Jet driver, nor any desktop DB drivers at all. How do I update the client's Machine?"


    He has downloaded the version 2.7 MDAC update, but does not want to install it until we clarify whether it is likely to work without the Access driver included.


    Any thoughts on the above,


    Cheers,

    Many thanks for those suggestions WillR and XL-Dennis.


    I will pass those on to our guy overseas to try.


    I have also made some edits to the code based on your comments XL-Dennis.


    If you think that it is worth making the last 2 amendments to my code, ie.


    no need to select the XL-objects the code will work with.


    I would also recommend to read all the data into an array of the variant-type and loop throught it when adding data to the table.


    then I'll have a go at that, but could you give me some pointers on how to achieve this please.


    Cheers,

    Thanks for your suggestion WillR.


    Unfortunately, your suggestion didn't fix the problem. The response I received back was:


    the suggested bug fix did not work. The PC has the same references that my install has, except for calendar control 9 instead of 8. It now has some additional references, including the Access 9 reference, but it still bombs in exactly the same way at the same line:


    conn.Open strConn


    As that in itself may not be very revealing, below is the sub routine where the problem occurs:


    Any other suggestions most appreciated.


    Cheers,

    Hi All,


    I have a problem with a routine that works on one computer running Office 2000, but not on another.


    I have an add-in that runs from Excel and reformats a spreadsheet. Once this is completed, then this data is appended to a table in an Access database. Everything runs fine on our laptop, but there is a problem getting it to work on the client's machine. The problem occurs when it tries to run the sub routine that connects to the database.


    I am acting as a middleman on this issue, as the problem is occuring overseas and is trying to be solved by someone else. This is their description of the problem:


    "No luck with the macro. It still fails, despite upgrading the installation to SP2 of Office 2000, which is the same as mine.

    It crashes when trying to connect to the specified database, wherever that is. The error message received is:

    Runtime error '-2147220999 (800401f9)'
    Method 'Open' of object '_Connection' failed

    The number in brackets is hex code for an error that says "Error in the DLL" according to Knowledgebase.

    We found one DLL that office setup was unable to repair, that was an earlier version than mine - VGX.DLL
    I replaced that but the problem continues."


    Does anyone have any experience with a similar sort of problem and solutions on how to solve it?


    All help much appreciated.


    Cheers,

    Thanks Dave,


    When I use the code you suggested, I get a type mismatch error.


    I presume that I have to pass the string to the ExtractNumber function using code such as:


    reverseFieldCode = ExtractNumber(x)


    replacing the code that I currently have:


    reverseFieldCode = EXTRACTELEMENT(x, 3, " ")


    However, I'm not sure how to pass the string x as a range, which appears to be what is required (or have I got the wrong end of the stick here?).


    The code I am using which will use the ExtractNumber function is:


    Sub FindFieldCode()


    Dim text2 As String
    Dim x As String
    Dim reverseFieldCode As String
    Dim fieldCode As String
    Dim fieldCodeTemp As String

    For Counter = startRow To numRows
    Sheets(origSheetName).Activate

    ' First, reverse text string

    text2 = Range("D" & Counter).Value
    x = REVERSETEXT(text2)


    ' Find third space and extract all characters before it
    reverseFieldCode = EXTRACTELEMENT(x, 3, " ")

    ' Check if value of 'reverseFieldCode' contains any non-numeric characters
    If IsNumeric(reverseFieldCode) Then

    ' If only contains numeric characters then re-reverse extracted number
    fieldCode = REVERSETEXT(reverseFieldCode)

    Else ' If 'reverseFieldCode' does contain non-numeric characters
    ' ie. will be in the format "NO.123"

    ' Find full stop and extract all characters before it
    fieldCodeTemp = EXTRACTELEMENT(reverseFieldCode, 1, ".")


    ' Re-reverse extracted number
    fieldCode = REVERSETEXT(fieldCodeTemp)

    End If

    Sheets(formatSheetName).Cells(Counter - (startRow - 2), 6).Value = fieldCode
    Next Counter


    End Sub


    I guess once I get the ExtractNumber functioning OK, I won't need to reverse the text anymore either.


    Cheers,

    Hi all.


    This is probably a basic question, but I can't find the exact info I'm looking for.


    I have strings of various lengths that I want to extract the numbers from eg:


    BIOTA FIELD 94 TYPE NTP
    BIOTA TISSUE 369 NH


    I was using the number of spaces to determine where to extract the numbers, as I thought the number of spaces was consistent. Now that I have found this isn't the case though, I need to extract the numbers without referring to the spaces. Presumably I should use something along the lines of a mid statement to work out where the first number is and keep extracting until a space (most likely) or non-numeric character is encountered.


    Any suggestions appreciated.


    Cheers,

    Hi,


    I have some code developed in XL2002 that when run in 2000 pops up a "Run-time error '9' - Subscript out of range" error message. This doesn't occur when run in 2002.


    The line it is having problems with is:


    origFileLocation = Workbooks(origFilename).Path


    where origFileLocation and origFilename are variables with values assigned in an earlier sub.


    Any suggestions as to what might be causing this error?


    Thanks,

    Hi Andy,


    Yeah, the code does work fine on its own, or after the rest of the project subs have run. The problem I have is that within the project, even though the detect duplicate form does appear on screen, the code doesn't pause here to allow the duplicates to be edited - it just continues running through the rest of the subs. So, I was wondering whether I have put some code in the wrong location, or if there is another problem.


    This code also needs to be compatible with XL 2000.


    Any suggestions?


    Cheers,

    Hi again Andy & Dave,


    I have made the changes as suggested previously, but with only limited success. When I run the module, the form does display, but the command button doesn't work so I can't edit any duplicates.


    I'm sure I've probably put something in the wrong place. If you can steer me in the right direction that would be great.


    The code in the userForm is:


    Private Sub ChkDupsButton_Click()
    m_DisplayDuplicateCells
    End Sub


    Private Sub frmDupCheck_Initialize()
    m_DisplayDuplicateCells
    End Sub


    The code in the main module is:


    Sub Test()

    Run "m_DisplayDuplicateCells"

    frmDupCheck.Show vbModeless

    End Sub


    Sub m_DisplayDuplicateCells()
    Dim intIndex As Integer
    Dim rngCheck As Range
    Dim rngTemp As Range
    Dim blnIsDup As Boolean
    Dim intArrayCount As Integer
    Dim vntArray() As Variant

    Set rngCheck = Range("D" & startRow & ":D" & numRows)
    frmDupCheck.DupListBox.Clear
    intArrayCount = 0
    For Each rngTemp In rngCheck
    ' clear highlight
    rngTemp.Interior.ColorIndex = xlNone
    blnIsDup = False
    For intIndex = 1 To intArrayCount
    If rngTemp.Value = Range(vntArray(0, intIndex)).Value Then
    blnIsDup = True
    ' Increment count of number of duplicates
    vntArray(1, intIndex) = vntArray(1, intIndex) + 1
    ' Add duplicate addresses
    vntArray(2, intIndex) = vntArray(2, intIndex) & "," & rngTemp.Address
    ' highlight current and original duplicate
    Range(vntArray(0, intIndex)).Interior.ColorIndex = 3
    rngTemp.Interior.ColorIndex = 3
    Exit For
    End If
    Next
    If Not blnIsDup Then
    intArrayCount = intArrayCount + 1
    ReDim Preserve vntArray(2, intArrayCount) As Variant
    ' Address of First for comparison
    vntArray(0, intArrayCount) = rngTemp.Address
    ' Begin count of number of duplicates
    vntArray(1, intArrayCount) = 1
    ' Begin list of duplicate addresses
    vntArray(2, intArrayCount) = rngTemp.Address
    End If
    Next
    For intIndex = 1 To intArrayCount
    If vntArray(1, intIndex) > 1 Then
    frmDupCheck.DupListBox.AddItem vntArray(2, intIndex)
    End If
    Next
    End Sub


    startRow and numRows are global variables with values assigned in another sub.


    Thanks,

    Andy,


    Thanks for your latest suggestion. That's an excellent solution and I've got it working quite well, at least when I test it on its own.


    However, when incorporated in a sub in module1 where it is called after a few other subs, it only runs the Test sub in module1 and skips showing the userform and running any of the code behind the userform (although the userform does appear when the spreadsheet is clicked - but the command button doesn't work) .


    In Sub m_DisplayDuplicateCells(), the range is set by the statement
    Set rngCheck = Range("D" & startRow & ":D" & numRows)
    where startRow and numRows are values set in another sub in module1. I don't think it is related to the range values not being passed to the userform, as it still skips the code when I hard code the cell values in as well.


    Any suggestions as to what the problem might be?


    Cheers,

    Thanks for that suggestion Andy :thumbup:. That's pretty close to what I'm after.


    However, I have a couple of other things that I need to sort out in order to customise the code for my requirements.


    1) I only need to check column D, but the row numbers to check vary. Elsewhere in the code I am returning startRow and numRows, which correspond to the column range that I want to check for duplicates. What is the best way to incorporate this in the range statement


    Set rngCheck = Range("D5:D189")


    so that the row value is taken from startRow and numRows?


    2) Can I trap the cell location of both duplicate cells, not just the second instance of each duplicate for display in the message box?


    3) Is it possible for the message box to persist on-screen after exiting a sub? What I want is if duplicates are detected, then the routine will be exited, after making the original worksheet the active worksheet. If the message box can remain on-screen until the duplicate cells have been edited, that will be very useful, or maybe these values can be saved in a notepad text file.


    As an afterthought, would it be possible for the following to be done?:


    When a duplicate cell is detected, the location in the original spreadsheet is highlighted for the user to edit while still in the code, and when that has been modified, then get the code to re-check the cell range again to ensure that the correction was changed successfully, and also to check for further duplicates within the rest of the range? If this can be done relatively easily, then I probably won't need to address points 2 & 3.


    Thanks in advance,


    Cheers,