Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Reconnect (A2k)

  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reconnect (A2k)

    Quite recently I have had problems with a database on one particular pc.

    "Disk or network error"

    I've scoured multiple search engines and web sites to no avail and numerous remedies, which again, never found the correct solution.

    Anyway.

    I want to set the following module to be called from perhaps a timer event, which I have managed to do so successfully, but have a couple of questions.

    1. The code cannot see linked tables.
    2. The error is 3078. When ok-ing thru the errors, the form loads ok with the linked tables intact as record source's for the forms.
    3. Some of the code is a little complicated for me to understand.

    Can any-one help with these questions ??

    Option Compare Database
    Option Explicit
    Function Reconnect()
    On Error GoTo er

    '************************************************* *************
    '* START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
    '* AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
    '* WHEN THE 'DATA.MDB' AND THE 'PRG.MDB'
    '* ARE IN THE SAME DIRECTORY!!!
    '* PROGRAMMING BY PETER VUKOVIC, Germany
    '* 100700.1262@compuserve.com
    '* ************************************************** **********
    Dim Source As String, path As String
    Dim dbsource As String, I As Integer, J As Integer
    Dim db As DAO.Database

    Set db = DBEngine.Workspaces(0).Databases(0)
    '************************************************* ************
    '* RECOGNIZE THE PATH *
    '************************************************* ************
    For I = Len(db.NAME) To 1 Step -1
    If Mid(db.NAME, I, 1) = Chr(92) Then
    path = Mid(db.NAME, 1, I)
    ''MsgBox (path)
    Exit For
    End If
    Next
    '************************************************* ************
    '* CHANGE THE PATH AND CONNECT AGAIN *
    '************************************************* ************

    For I = 0 To db.TableDefs.Count - 1
    If db.TableDefs(I).Connect <> " " Then
    Source = Mid(db.TableDefs(I).Connect, 11)
    For J = Len(Source) To 1 Step -1
    If Mid(Source, J, 1) = Chr(92) Then
    dbsource = Mid(Source, J + 1, Len(Source))
    Source = Mid(Source, 1, J)
    If Source <> path Then
    db.TableDefs(I).Connect = ";Database=" + path + dbsource ' if you have a password in your backend then add this -> + ";PWD=yourcodehere"

    db.TableDefs(I).RefreshLink
    End If
    Exit For
    End If
    Next
    End If
    Next

    Exit Function
    er:
    MsgBox Err.DESCRIPTION & "," & Err.Number
    Resume Next
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reconnect (A2k)

    Dave,

    The code you posted can be used in the following situation:
    <UL><LI>You have two databases in the same folder
    <LI>One of these contains links to tables in the other.
    <LI>You have moved both databases to another folder (the same for both databases)[/list]Since the paths of linked tables are hard-coded, the links won't work any more. The code checks if the hard-coded path is equal to the current location, and if not, updates the connection. Is that what you want to do?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconnect (A2k)

    Hans, Well, not really !

    I have the FE C:Bodyshop.mdb
    And the BE L:Bodyshop.mdb

    The L: drive contains the tables, the C: drive holds the forms, queries etc.

    My only aim is to refresh the links to the L: drive every 5 Minutes or so, hoping to cut out the Disk or Network Error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reconnect (A2k)

    Dave,

    I don't think that will work. If a user has a form or report open based on a linked table, its design is locked, so you can't refresh the link. And I doubt whether it would solve your problem. After all, you're not moving your databases around.
    Others have more experience with frontend/backend setups, let's see what they have to say before you continue working on this.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconnect (A2k)

    Thanks for the advice, Hans.

    I am now Paused............................................ .................................................. .................................................

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Reconnect (A2k)

    Change the PC or is that not an option? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reconnect (A2k)

    It is unlikely to work. Access is extremely sensitive to failures in network links and once the link has failed, it can't be refreshed or relinked, you have to get out of Access and reopen the connection. Access is like a miner's canary for network problems. The error can be caused by such diverse things as a chattering network card on any machine on the network (not necessarily the one that gets this error), a hub problem, a bad router, a bad segment of wire in the walls, or a bunch of other things including the use of the IPX/SPX protocol to connect to the server or exporting to a Paradox or dBase format over the top of an existing corrupted file. There are other reasons too, but without more information on the kind of server you're connecting to (i.e., WinNT, WIn2k, etc.), the version of Windows you're running, and whether you are using something like Terminal Server, it's impossible to make any specific suggestions. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconnect (A2k)

    Thanks all for your wisdom.

    I've had this problem for so long now, I'm beginning to live with it. (Obviously thats not a solution).

    I've changed network cards, Router, all but the cabling.

    We're on Win NT4, and have read several articles saying Access doesn't like NT but, It's only one machine with the problem.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reconnect (A2k)

    Actually I've worked on a number of machines running NT 4 with several versions of Access and didn't experience any instances of Access not "liking" it. I did find some odd behavior if the NT service pack was below 4, but I doubt that is the case at this late date.
    Charlotte

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconnect (A2k)

    Just checked, Version 4.0.1381 !!

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reconnect (A2k)

    I agree with Charlotte - refreshing your links isn't likely to do any good. The problem you describe sounds very much like a network problem, and it sounds like you've eliminated nearly everything but the wiring. Is it configured such that you could run a new cable or test the PC by moving it to a different location where a user isn't having any problems? We fought this kind of thing for nearly two years where a client was running a system using a radio link that was noisy - they finally agreed to let us reconfigure things and suddenly the problem went away.
    Wendell

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconnect (A2k)

    Yes I think a dummy cable may be the answer, I'll try it.

    Wendell,Is the offer to re-configure still optional ??

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reconnect (A2k)

    NT 4 version 4.0.1381 is SP6, I believe...

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconnect (A2k)

    Hans
    Does this mean I have to check the Jet version for the problem machine ??, or the server.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reconnect (A2k)

    If you select Help | About... in Access, then System Information, finally System DLL's, you'll see a list of what's installed. Compare to the info in <!mskb=239114>Microsoft Knowledge Base Article 239114<!/mskb> to find out which SP level you have for Jet 4. You should have SP6.

Page 1 of 2 12 LastLast

Posting Permissions

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