Setup screen for Access DB path

[linmatt]

I'm setting up an Access2000 database usinf 2 mdb's - one for data and one client app. It will need to be distributed to various locations - the data ideally run from 1 pc at each loc'n with the local client apps connecting to it. Can I store the path in a table and if so how do I access it via VBA? I want to be able to give them a 'Setup' screen where they type in the path to the Data mbd and then connect to it. It'll be different for each site.

 

[LLudden]

Use the common dialog controls to let the user browse for the database location, then once you have it, call this function with the path:

Function ChangeData(strPath)
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim strName As String
Dim strName2 As String
Dim intFlag As Integer
Dim intX As Integer
Dim strFilter As String
intFlag = 0
Dim strConnect As String
Dim strTest As String

ChangeData = -1
Set db = CurrentDb()
For intX = 0 To db.TableDefs.Count - 1
strConnect = Mid(db.TableDefs(intX).Connect, 11)
If Len(strConnect) Then
db.TableDefs(intX).Connect = ";DATABASE=" & strPath
db.TableDefs(intX).RefreshLink
strTest = db.TableDefs(intX).Fields(0).NAME
Select Case Err
Case 3024
MsgBox "Couldn't find the file " & vbCrLf & strPath
ChangeData = False
GoTo Exit_ChangeData
Case 3044
MsgBox "The path is not valid"
ChangeData = False
GoTo Exit_ChangeData
Case 3045
MsgBox "The file with the data is currently being used in a mode that doesn't allow it to be shared with you. It is opened exclusively"
ChangeData = False
GoTo Exit_ChangeData
Case 3051
MsgBox "You currently do not have permission to use the data file. Please contact your administrator."
ChangeData = False
GoTo Exit_ChangeData
End Select

Do While Err <> 0
strName2 = strGetFileName(strPath)
strFilter = strName2 & Chr(0) & strName2 & Chr(0) & Chr(0)
strName = OpenCommDlg(strFilter, &quot;Could not find Database '&quot; & strName2 & &quot;'. Please select correct path.&quot
strName2 = strName
If strName = &quot;&quot; Then 'User hits cancel
ChangeData = 0
MsgBox &quot;Sorry, I can't find the correct link to the data. Please see your Adminstrator or try again.&quot; & Chr(13) & &quot;Good Bye&quot;
Exit Function
End If
Forms![frmAttach]![Sub].Form![Path] = strName2
db.TableDefs(intX).Connect = &quot;;DATABASE=&quot; & strName
db.TableDefs(intX).RefreshLink
Loop
End If
Next intX
Exit_ChangeData:
Exit Function
End Function