Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Posts
    48

    Unanswered: VBA code does not run on another PC

    I have run a successful Access VBA program that I inherited on my office PC, and then transferred it to my laptop, both using Access 2003.

    When I open the Access file on my laptop, I get the following message from one of the functions in the program ( see code below):

    Run-time Error '-2147024769 (8007007f):
    Method 'Connection ' of object '_CurrentProject' failed
    I have checked that both computers have the same References selected.

    I would welcome any suggestions and clues as to what is going wrong.

    The office PC runs with Access 2003 SP3, and the laptop runs with SP2

    with thanks,

    Jill


    Code:
    'Opens or Destroys ADO Connections as necessary.
    Public Function ThisDb(Optional strDistroy As String = "NORMAL") As ADODB.Connection
        
        Static objThisDB As ADODB.Connection
        
            Select Case strDistroy
           
                Case "KILL"
                
                    If Not objThisDB Is Nothing Then
                        If objThisDB.State = adStateOpen Then objThisDB.Close
                    End If
                    
                    Set objThisDB = Nothing
                
                
                Case Else
            
                    If objThisDB Is Nothing Then
                      
                      Set objThisDB = CurrentProject.Connection
                    
                    End If
                
                    Set ThisDb = objThisDB
        
            End Select
    
    End Function
    Last edited by JillB; 01-18-08 at 02:20.

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Check Permissions

    here's a good refference

    http://classicasp.aspfaq.com/compone...7f-errors.html

    check if tour app, has the right permissions on your laptop

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've got a similar error. It only happens on my computer with Vista installed and all my programs worked fine when I had XP installed but I'm now getting the same error as you.

    I believe it has something to do with the new kind of permissions Vista uses or most likely as Marvels pointed out with the link, something to do with registering the dll but I'll let you know what I find.

    Are you noticing this error on a computer with Vista installed also or is this on an XP box?
    Last edited by pkstormy; 01-18-08 at 04:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2007
    Posts
    48
    Both are XP operarting systems, but my laptop is only Home edition, the others are Professional. I took the Access file into the Company office, and ran it on a laptop there, and it worked OK - again, a laptop with XP Pro. I have updated my own laptop from Access 2003 SP2 to SP3, reloaded the Access file from a San memory disk, same problem.

    Good to know that I am in good company! I felt silly posting it, but had no where to turn.

    kind regards,
    Jill ( in the hot, v. humid tropics of Australia)

  5. #5
    Join Date
    Dec 2007
    Posts
    48
    Marvels, from the code supplied above, would you have any idea which .dll file I should be registering for this connection problem, please?

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    re dll's

    Could thake a quite good guess. odbc32.dll
    But why not try the newest mdac version

    Not jet have i heard of a forward-compatibility program or system
    So,
    Next time do it the way around program it on a low level machine and then install it on a higher level one. better change of backwords compatibility.

    And that last thing you mentioned was a good pice off info (why you have a prob.) Home eddition Vs. Prof.Eddition

    G.luck -^.^-

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I ran a repair on my office installation which fixed the problem I was having.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If you open a module, and enter ?Date() in the immediate window do you get an error? What about ?Left("???",1)? If so, I would say it is related to references.

    Keep in mind, even though the references are the same, and you don't have 'Missing' anywhere, you could still have a problem. If there slight differences in the versions then it will not work. To make sure this is not the problem, uncheck a reference and exit references. Then go back in and check the reference you unchecked. This will 'refresh' you references and may fix the problem.

  9. #9
    Join Date
    Dec 2007
    Posts
    48

    Cool Success !!!

    Well , following all your advice I repaired the DB, no joy. I then tried the Date() tests, no problems there. I then did the regsvr32 test, and found odbc.dll did not exist!! Big clue here

    I went on the Internet and downloaded odbc.dll and odbc32.dll, put them in the respective folders in Windows, but still the DB came up with the same error as originally.

    At that stage I looked into MDAC ( Never heard of it before), downloaded it, installed it, rebooted, and .... viola!! the DB runs and loads forms.

    Looks like the Home edition of XP does not have any DB connection files, unlike the Pro edition.

    Many thanks to all for your help and suggestions

    from one very happy Jill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •