relink code not working (1 Viewer)

[Rachael]

Could someone please have a look at this code and identify why it might not work in some situations. I have a client who doesn't have a C drive but has an N drive in a network situation. This code which is in my start up form isn't prompting for a relink......works fine for me.....

On Error GoTo Err_Form_Open

Dim strTest As String, DB As DAO.Database
Dim td As DAO.TableDef
Set DB = CurrentDb

For Each td In DB.TableDefs
If Len(td.Connect) > 0 Then
On Error Resume Next
strTest = Dir(Mid(td.Connect, 11))
On Error GoTo Err_Form_Open
If Len(strTest) = 0 Then
If MsgBox("Couldn't find the data file " & Mid(td.Connect, 11) & ". Please choose the location of the data file.", vbExclamation + vbOKCancel + vbDefaultButton1, "Can't find data file") = vbOK Then
DoCmd.OpenForm "frmNewDataFile"
DoCmd.Close acForm, Me.Name
Exit Sub
Else
MsgBox "This application will now be shutdown.", vbExclamation, "Procedure Aborted"
DoCmd.Quit
End If
End If
End If
Next
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frm Password"

Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox Err.Number & ":" & Error.Description
Resume Exit_Form_Open


Thankyou kindly to those that have a squiz!

Rachael

 

[WayneRyan]

Rachael,

On Error GoTo Err_Form_Open

Dim strTest As String, DB As DAO.Database
Dim td As DAO.TableDef
Set DB = CurrentDb

For Each td In DB.TableDefs
   If Len(td.Connect) > 0 Then
      On Error Resume Next
      strTest = Dir(Mid(td.Connect, 11)) <-- If its > 0, does "11" REALLY have to work !!!
      On Error GoTo Err_Form_Open        <-- Now, I'm not so sure, but the "11" is bad news
                                         <-- 
      If Len(strTest) = 0 Then           <-- With the Resume Next, we're here no matter what
                                         <-- I'm going out on a limb here:
                                         <--
                                         <--    1) The table is linked,
                                         <--
                                         <--    2) From the 11th character on of tdf.connect,
                                         <--       it's not a valid file spec.
                                         <--
                                         <--    What does tdf.Connect look like as a file
                                         <--    spec from the problem computer?
                                         <--
                                         <-- I would guess they got the "Couldn't find ..."
                                         <--
         If MsgBox("Couldn't find the data file " & Mid(td.Connect, 11) & ". Please choose the location of the data file.", vbExclamation + vbOKCancel + vbDefaultButton1, "Can't find data file") = vbOK Then
            DoCmd.OpenForm "frmNewDataFile"
            DoCmd.Close acForm, Me.Name
            Exit Sub
         Else
            MsgBox "This application will now be shutdown.", vbExclamation, "Procedure Aborted"
            DoCmd.Quit
         End If
      End If
   End If
   Next

DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frm Password"

Exit_Form_Open:
   Exit Sub
Err_Form_Open:
   MsgBox Err.Number & ":" & Error.Description
   Resume Exit_Form_Open

Wayne

 

[Rachael]

Hi Wayne,

Thank you so much for having a look at this for me, its driving me bonkers.

The problem computer isn't even getting the "Can't find......", its like its skipping the whole startup checking for links (the code I posted and going straight to the logon screen, so on the problem computer - becuase the frontend needs to know where the backend is before it can let the user logon with there password etc its saying it can't find the passwords table for obvious reason (cos we haven't relinked) SO......why is it getting past the startup check for links part.....do you think the 11 has something to do with it? Not fully expert in writing code so not fully sure what the 11 does.

thanks again Wayne your help is much appreciated

Rachael

 

[Rachael]

Hello again,

Wayne has given me a few clues so have dicovered some more stuff.

I now know what the 11 does, the td.connect returns DATABASE=C:\Program Files\VinRec so it just starts at the C so....if the problem computer doesn't have a C drive and the above path is where the front end is linked....would it skip the start up? and if so how can I write the code to make it still prompt for relinking?

thanks a million especially you Wayne

Rachael

 

[WayneRyan]

Rachael,

Glad to help, actually there a lot of folks here that deal with this, but since
I have your attention:

If the database has links like "C:\ ..." then it obviously expects to find
the target database on its own C: drive.

Why aren't the links something like "\\SomeComputer\SomeFolder"?

The code is just looking for the Database in the same folder as itself?

Need more info,
Wayne

 

[Rachael]

Hi Wayne,

Yeah, probably do need more info!

The frontend is linked on my computer to the backend using the Linked table manager......which so happens to be C:\Program Files\VinRec

This code works as I have plenty of other clients working in a networked environment so when they get an update it prompts for the new path and refreshes the links and job is done.....but for some reason this one particular client, it wont prompt for the relink it seems to skip it and go straight to the logon screen with no errors....this client hasn't got a C drive he has an N drive....can't understand why this is doing this!

the database is Access2000 would newer version of Access not like the code and the way its written?

any help much appreciated

Kind regards,

Rachael

 

[WayneRyan]

Rachael,

So each computer has a front-end/back-end setup in C:\Program Files\vinrec?

If you made the front-end on your machine, and established the link on your
machine, then the database has to exist on C:\Program Files\vinrec\.

The Mid("C:\Program Files\vinrec", 11) returns " Files\vinrec\ThatDatabaseName.mdb"
That doesn't seem right at all.

If you browse for the file, (and probably link to it through the other form),
the next time you run the code, you'll be right back where you started from!

Why not just do a DCount on a table, if it fails, either relink to a static
database, or remember the one you browsed for.

I guess I need more info...
Wayne

 

[Rachael]

Hi Wayne,

The code I posted in the first message runs on my start up form - most clients install the frontend and backend in the default folder called VinRec so there's no drama - but....some clients want to network there software so they shift the backend to a network drive....so....the code I posted earlier says "Ok data file not in default location so now I'll ask for a new location and refresh the links" this has worked for me for a number of years...until now...with only this one client who doesn't have a C drive. Hope that makes sense...its nothing special thats going on.

DCount - thats a good idea - I will try this - it just might be alot easier -

any other ideas on why the code I posted doesn't work I'd be most interested.

Cheers,

Rachael

 

[bat1799]

strTest = Dir(Mid(td.Connect, 11)) <-- If its > 0, does "11" REALLY have to work !!!

Yes it does
Checking with a DB linked to my c drive I get
td. connect = ";DATABASE=C:\12345\bencal.mdb"
Mid(td.Connect, 11) ="C:\12345\bencal.mdb"
Dir(Mid(td.Connect, 11)) = "bencal.mdb"
The dir function proves the file/path exists.

I think you need to add some debugging code to check just what the db is returning here.

On Error Resume Next
MsgBox td.Connect
MsgBox = Mid(td.Connect, 11)
MsgBox = Dir(Mid(td.Connect, 11))
strTest = Dir(Mid(td.Connect, 11))

you also need to test that at least one connected table was found, you could do this with a simple counter after If Len(td.Connect) > 0 Then

HTH

Peter