Access creating deleted record occassionally on one form

[Kev86]

Sorry CJ i am not very tech savvy with all this. So is it this piece of code to use to check whether there is a connection with the sever where the BE is stored?

Function DriveIsAvailable(MDrive As String) As Boolean

    On Error Resume Next

    ChDir MDrive & ":\"
    DriveIsAvailable = Err = 0
    
End Function

[Kev86]

Sorry CJ just read your code again and now see you have a function then the second part is calling upon that function. So i guess within the code below where you have ":" i will need to put the complete file path?

Function DriveIsAvailable(MDrive As String) As Boolean

    On Error Resume Next

    ChDir MDrive & ":\"
    DriveIsAvailable = Err = 0
    
End Function

Also then in your second piece of code calling that function where you have ("z") i assume i can just put the file name there rather than a Z?

Really sorry as i say my knowledge on code is basic and i originally built this database a couple of years ago with a lot of help from Google and little knowledge

[Kev86]

Sorry CJ just read your code again and now see you have a function then the second part is calling upon that function. So i guess within the code below where you have ":" i will need to put the complete file path?

Function DriveIsAvailable(MDrive As String) As Boolean

    On Error Resume Next

    ChDir MDrive & ":\"
    DriveIsAvailable = Err = 0
    
End Function

Also then in your second piece of code calling that function where you have ("z") i assume i can just put the file name there rather than a Z?

Really sorry as i say my knowledge on code is basic and i originally built this database a couple of years ago with a lot of help from Google and little knowledge

[CJ_London]

when you link to the BE what is the path?

It might be something like

Z:\public\databases\myBE.accdb

in which case you would just need the Z so you would use

if DriveIsAvailable("Z") then
...
...

if the path is X:\databases\myBE.accdb

you would just need the X


if DriveIsAvailable("X") then
...
...

[Kev86]

Hi CJ,

I use the full file path rather than a drive letter. Also some people might use different letter drives if they have saved a link themselves to the folder location. Can i just use the full file path which is...

\\maidat01\work\PowerConnect\Projects South\Project Design\CDS\WESTERN\Capital\New Database

[CJ_London]

I’m not at my computer to set up a test environment- only place I’ve needed to use it is with a client who used mapped drives (IT set the mapping so users have no choice) suggest try it and see - experiment for yourself, that way you will gain an understanding of what you are doing.

Would think you would only need to test for

\\maidat01\

so you won’t need the colon ( : ) in the function

[Kev86]

Ok thank you, I will have a look and test it.

[Kev86]

Hi CJ,

I have been playing about with the code and it's recognising it's there however every time I test it the message comes up. Even though i have put it on the BeforeUpdate event the job still updates and the message appears saying that the drive could not be reached.

I had to amend the code as the word DriveIsAvailable it did not like so i just changed that to Drive.

I now have a module called 'CheckServer' and the code is as follows...

Function Drive(MDrive As String) As Boolean


    On Error Resume Next
    
    ChDir MDrive & "C:\Users\lock01k\OneDrive - UK Power Networks\Desktop\Database test with Adam"
    Drive = Err = 0


End Function

Then in the BeforeUpdate event linked tot he form i have the following code...

If Drive("C:\Users\lock01k\OneDrive - UK Power Networks\Desktop\Database test with Adam") Then
    'carry on
Else
   MsgBox "Drive not available, there may be a temporary network failure so please try again later. If the problem persists please contact your IT dept", vbOKOnly
End If

I am probably doing something completely wrong but at the moment it's letting me make a change and go to the next line and it's not stopping the update however the message "Drive not available, there may be a temporary network failure so please try again later. If the problem persists please contact your IT dept" is then showing up.

[Welshgasman]

Is Drive a reserved word?
Put the whole path into a string variable and debug.print that. Then copy that and paste into Explorer, does that work?

[Kev86]

I changed it to DriveTest and that did the same, not sure if Drive is a reserved word or not. Sorry my knowledge is lacking so i am a little lost of what to do next.

[Welshgasman]

I changed it to DriveTest and that did the same, not sure if Drive is a reserved word or not. Sorry my knowledge is lacking so i am a little lost of what to do next.

Start walking your code with F8 and breakpoints then.

What is Mdrive supposed to be?

? drivetest( "W")
False

[Welshgasman]

? drivetest( "F")
err is 0
True

Function DriveTest(MDrive As String) As Boolean
Dim strPath As String




    On Error Resume Next
    strPath = MDrive & ":\Temp"
    ChDir strPath 'MDrive & "\Temp" '"C:\Users\lock01k\OneDrive - UK Power Networks\Desktop\Database test with Adam"
    Debug.Print "err is " & Err
    DriveTest = Err = 0




End Function

[Kev86]

Thank you Welshgasman for your responses however i am still struggling to get it to work. I tried adding your code to a new module but just got an error, when i click Debug the error shows as 'Compile error: Function call on lef-hand side of assignment must return Variant or Object'.

Function DriveTest(MDrive As String) As Boolean
Dim strPath As String




    On Error Resume Next
    strPath = MDrive & ":\Temp"
    ChDir strPath 'MDrive & "\Temp" '"C:\Users\lock01k\OneDrive - UK Power Networks\Desktop\Database test with Adam"
    Debug.Print "err is " & Err
    DriveTest = Err = 0




End Function

[Welshgasman]

In the immedfate window enter
? drivetest(<WhateverYou MDriveIs>) just like I used my F drive.

Show EXACTLY how you call it, just as I have done above.

NB, If you do not have a F:\Temp folder, it is going to return False.