Splitting a dabase across different Network drives? 1

[SeeWard]

Hi,
I have successfully split a database so that all users can use it at the same time without too much hassle....I had it the FE loaded on all pcs except one. When I try to open it on the pc, it gives an error message of '<path> bad path...' ..the only reason I can think is that her pc is not networked to the same letter drive...is to say that rather than L// she has Z// and it's possible that it's an absolute path...How can one rememdy this?

 

[JerryKlmns]

An ADOX version

Sub ReLinkThem(ByVal myServer As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim varA

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
    If tbl.Properties("Jet OLEDB:Link Datasource") <> "" Then
        varA = Split(tbl.Properties("Jet OLEDB:Link Datasource"), "\")
        varA(0) = "\\" & myServer
        tbl.Properties("Jet OLEDB:Link Datasource") = Join(varA, "\")
    End If
Next tbl
cat.Tables.Refresh
Set cat = Nothing
End Sub

an DAO version

Sub ReLinkThem2(ByVal myServer As String)
Dim tbl As New TableDef
Dim varA

For Each tbl In CurrentDb.TableDefs
    If tbl.Connect <> "" Then
        varA = Split(tbl.Connect, "\")
        varA(0) = ";DATABASE=\\" & myServer 
        tbl.Connect = Join(varA, "\")
        tbl.RefreshLink
    End If
Next tbl
End Sub

Both versions
call ("eh007rpg9"),
relinks to \\eh007rpg9\restofthepath
from f:\restofthepath, or g:\restofthepath or ...

On Tools-->Options check
For the 1st
Microsoft ADO Ext. 2.x for DDL and Security
For the 2nd
Microsoft DAO 3.6 Object Library

 

[SeeWard]

First off, THANK YOU for responding to my post. I really appreciate it...I do wonder though where I need to implement this code...is it within the module or within other code?
Thanks and sorry for the silly question!

 

[JerryKlmns]

SeeWard,

No silly at all.

Place them in a module. Don't forget do check the references :
On Tools-->References check
For the 1st
Microsoft ADO Ext. 2.x for DDL and Security
For the 2nd
Microsoft DAO 3.6 Object Library

 

[SeeWard]

Thank you so much for walking me through this...
I have done as you've stated...I have checked to be sure that the DAO 3.6 Object Library is checked and it is....I have placed the code in a module but it still will not open properly on her pc. It keeps coming up with an error message that L\...Call Center_be.mdb is an invalid path...?? Do I need to alter the code?

 

[JerryKlmns]

You could check the path. Open table MSysObjects and filter excluding blank on column Database. Is there an L:\.... Could it be something else looking for that mapped drive letter?

You may re-run ReLinkThem2 more than once.

 

[SeeWard]

No luck with that ...I'm so sorry. I don't know what I'm doing wrong...though truth be told I'm not sure where MSysObjects table resides as I do not have it and my module won't run. Run is greyed out and ..well....I'm not in my proper depths with this at all I'm afraid...

 

[JerryKlmns]

On Tools-->Options-->View tab, select System Objects + Ok.
Now System tables are visible (but not editable like others)
If Run is greyed out probably there is some code running. I hope your are not messing in someone else db...

 

[SeeWard]

No, I'm not messing in someone else's db. This is mine. I am new to Access 2003 and this is the first time I've split a database let alnoe have a stragler on the network yet mapped through a different letter. That seemed odd to me and I inquired about it. They told me that she couldn't map to the L drive so she had to use the Z drive instead. It's throwing me terribly and I apologise. I did as you suggested and thank you for helping me with this. I did check the SysObjects table and everything seems ok. It's all mapped into the L:\ drive with no trouble. I tried to get around the problem of the mapped drive by having the woman use a form that is actually in the _be.mdb...I know that that defeats the purpose of splitting the database, but it was the only way I could get it to work..Thank you so much...your help taught me a few things that I didn't know before...I am a mainframe person by trade

 

[SeeWard]

Ok...so now I've went ahead and reapped the drives so that we all have access to the same lettered drive...Now the problem is that there is an error that occurrs which states that a record can't be updated because it's locked....

 

[JerryKlmns]

SeeWard,

Locked record should be a different post as it is a different problem. I've seen posts here about it but to give you a start

On Tools-->Options-->Advanced tab, check
Default open mode SHARED
Default record locking EDITED RECORD
Open databases using record-level locking

On every form Properties-->Data tab
Record Locks EDITED RECORDS

And thanx for the *

 

[SeeWard]

JerryKlims,
You did it!!! I wish I could give you more stars in fact! You're more than welcome for the one I gave you. I got it to work finally (fingers crossed)...I shouldn't have taken their word for it that the other pc who was mapped to another drive couldn't be mapped to the proper one (L). I remapped her drive, relinked the tables in the BE and it ran smoothly for everyone except the person who was locked out. The reason she was locked out was due to the fact that a Module referenced a missing DAO (which you had previously warned me about)...I checked it out and and made sure all was well with it. The reason she got the error of it being Locked rather than a missing or damaged file was due to the fact that she was forcing data into form...she tried over-riding the original error message which I wasn't aware of...oh what a mess! BUT your patience and know-how really helped me out. Thanks so much!

 

[JerryKlmns]

Glad to be of help.