Problems accessing a split database

[tobydobo]

Ok so I have successfully split my database (frontend/backend) and have the backend stored on a sharepoint for my team to use. One thing I noticed we all had to do is map the same drive letter to the network path where the backend is stored. Since I initally split the db on my pc using drive W, the db now requires everyone else to use the same drive letter.
So everyone maps a W drive for the path where the backend resides, and has a local copy of the front end. 3 of us can pull it up fine and we can have multiple instances running with no problems. One user however, gets the error: "W:\<path> is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides." I have verified that this user does indeed have drive W mapped to the correct location, but he still gets the error. Im lost...any takers?

[June7]

I use UNC (Universal Naming Convention) instead of drive pathing. Must use VBA code to set the table links. Sample code at http://forums.aspfree.com/microsoft-...ue-323364.html

I don't know if applies to Sharepoint.

[tobydobo]

Sweet! So I just add this code at the bottom of the existing code? I guess what I need to ask, do I just open the coding editor and just start this at the bottom?

[tobydobo]

and man oh man! i love your idea at the bottom of that post about auto-updating with latest version. I have been publishing the front end to the sharepoint and telling everyone to go and manually download it.

[June7]

I put this code in a public module I call Utilities. Then I run it manually whenever needed, which is not often anymore. I used it when we got a new server and they assigned a new name. I also use it when I want to run the program on a computer not on the network.

[tobydobo]

would it also work if i add it to the bottom of my existing code for the db? If not, can you explain in detail in how to create a seperate module like that?

[tobydobo]

How would I go about creating a new public module from scratch? This part is sort of new to me. I see a modules folder under contacts project in the VB editor, do I just right click modules and select insert > module? then add the script there?

[tobydobo]

nevermind I figured it out and it worked like a charm! : ) Next I think Im going to try your version update solution.

[June7]

Here is thread on another user's experience implementing my version update code. If you encounter some issues, might find resolution in that discussion.

http://forums.aspfree.com/microsoft-...tc-324677.html

[tobydobo]

oh yea! Thanks June7. i will try this out when I get a chance.

[tobydobo]

is there a way to run a split database offline? Currently it pulls the data tables from a networked location, but in the case where we need access to the data offline, there a way to cache a snap shot of the data within the db?

[June7]

I have no experience with that but I am inclined to say no.

[tobydobo]

Hi June7, having problems with the UNC script on a new copy of my db. I imported the script:

Public Sub NormalizeTableLinks()
'Relink tables with UNC pathing
'Use the variables provided to do the manipulations desired
Dim td As TableDef
Dim db As DAO.Database
Dim strOld As String
Dim strNew As String
'replace the following strings as needed
strOld = "change"
strNew = "change"
Set db = CurrentDb
For Each td In db.TableDefs
If InStr(td.Connect, strOld) > 0 Then
td.Connect = Replace(td.Connect, strOld, strNew)
td.RefreshLink
End If
Next td
db.TableDefs.Refresh
End Sub


and modified the links...but now when I run it, I get the error: Compile Error: User-defined type not defined. ....and it highlights the section that says: DIM td As TableDef.

Any ideas what could be causing this?

[tobydobo]

Also one of your earlier comments stated that you run this script for pc's that are not on the network. How would that work if your backend db is networked?

[June7]

That probably means a Reference is not selected. Not sure which one is needed. Check the attached image from my project.

Did you use 'change' in the pathing just for this post, not really in your code?

Sorry, I meant that I took the front and back ends home and modified to run there. I uploaded the modified file to http://www.box.net/shared/r8nea07sng. It is old now, I have of course changed quite a few things.