No DLookUp

[Douglas J. Steele]

Well, there's definitely no reason for Me.TxtPath: you can simply put the
path in a variable:

Dim Test2SQL As String
Dim strPath As String

strPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "")
If Len(Dir$(strPath)) > 1 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPath & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
End If

What's your objection to the DLookup?

I suppose you could use CreateProperty to create a new database property and
store the path there, rather than in a table:

Sub SetBackendPath(strPath)
On Error GoTo Err_SetBackendPath

Dim dbCurr As DAO.Database
Dim prpBackend As DAO.Property

dbCurr = CurrentDb()
dbCurr.Properties("BackendPath") = strPath

End_SetBackendPath:
Set dbCurr = Nothing
Exit Sub

Err_SetBackendPath:
Select Case Err.Number
Case 3270 ' "Property Not Found"
Set prpBackend = dbCurr.CreateProperty( _
"BackendPath", dbText, strPath)
dbCurr.Properties.Append prpBackend
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & _
Err.Description
Resume End_SetBackendPath
End Select

End Sub

You could then have a GetBackendPath function to retrieve that value:

Function GetBackendPath() As String
On Error GoTo Err_GetBackendPath

GetBackendPath = CurrentDb.Properties("BackendPath") = strPath

End_GetBackendPath:
Exit Function

Err_GetBackendPath:
Select Case Err.Number
Case 3270 ' "Property Not Found"
GetBackendPath = "Backend path property not created yet."
Case Else
MsgBox "Error " & Err.Number & ": " & _
Err.Description
End Select
Resume End_GetBackendPath

End Function

 

[DS]

Is there a way of doing this without DLookUP or Me.TxtPath?
I have the Path stored in a table which I need to do incase I have to
swith paths. This way I don't have to change all of my code.

Thanks
DS


Me.TxtPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "")
If Len(Dir$(Forms!Form1!TxtPath)) > 1 Then
Dim Test2SQL As String
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & Forms!Form1!TxtPath & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
Else:
End If

 

[Douglas J. Steele]

If you unshare the directory, it makes sense that it wouldn't work.

Access needs to be able to, well, access the file in order to work...

 

[DS]

Wow! A lot of info, Douglas.
I just thought DLookUp would be slow.
I'm going to play with the code you posted.
In the meantime....
The code I posted works but....
If I unshare the directory I get Error 52
so maybe it doesn't work. How can I fix this?
Thanks
DS

 

[Douglas J. Steele]

OK Understood, but how do I get it not to run if it's unshared?
I want the code to work the same if it's shared or not.
If it's good then Update...
if it's not end if

Do I need 2 routines here one to check if th efolder is shared. And
another to check the path?

Why would you be unsharing it? That sounds like an odd thing to do...

Put in error trapping to catch the error 52, and just don't run the code if
that error occurs.

If you're not familiar with error trapping, Allen Browne has a good
introduction at http://www.allenbrowne.com/ser-23a.html

 

[DS]

If you unshare the directory, it makes sense that it wouldn't work.

Access needs to be able to, well, access the file in order to work...

OK Understood, but how do I get it not to run if it's unshared?
I want the code to work the same if it's shared or not.
If it's good then Update...
if it's not end if

Do I need 2 routines here one to check if th efolder is shared. And
another to check the path?
Thanks
DS

 

[Douglas J. Steele]

OnError GoTo Err_Routine

Dim booContinue As Boolean

' other code

booContinue = True
strPath = ....

If Len(Dir$(strPath)) > 0 Then
If booContinue = True Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPath & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
End If
End If

End_Routine:
Exit Sub

Err_Routine:
Select Case Err.Number
Case 52
booContinue = False
Resume Next
Case Else
Msgbox ....
Resume End_Routine
End Select

End Sub

 

[DS]

Why would you be unsharing it? That sounds like an odd thing to do...

Put in error trapping to catch the error 52, and just don't run the code if
that error occurs.

If you're not familiar with error trapping, Allen Browne has a good
introduction at http://www.allenbrowne.com/ser-23a.html

Thanks Douglas,
I wouldn't unshare the folder...but you never know!

I see the OnError stuff....

OnError GoTo Continue:

Continue:
Resume

But I don't think the resume thing is right, I just want to ignore and
go to the next line of code, not exit the sub....

Thanks
DS

 

[DS]

Thanks Douglas,
After playing around with this thing all day and using some of your
suggestions I came up with this. It seems to work, It Updates when the
Path is valid and does nothing when it is not...


Dim Test2SQL As String
Dim strPath As String

strPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "")
On Error Resume Next
If Err.Number <> 0 Then
End If
If Len(Dir$(strPath)) > 1 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPath & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
End If
DoCmd.SetWarnings True

If you see any problems or potential bombs about to happen please let me
know....I value your opinion.
Thanks
DS

 

[DS]

Here it is Cleaned Up...
Once again any input is welcomed.

Dim TestSQL As String
Dim Test2SQL As String
Dim strPath As String

DoCmd.SetWarnings False
TestSQL = "UPDATE table1 " & _
"SET table1.IDName = ""New"" " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (TestSQL)

strPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "")
On Error Resume Next
If Err.Number = 0 Then
If Len(Dir$(strPath)) > 1 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPath & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
End If
ElseIf Err.Number <> 0 Then
End If

Thanks
DS

 

[Rick Brandt]

Wow! A lot of info, Douglas.
I just thought DLookUp would be slow.
I'm going to play with the code you posted.
In the meantime....
The code I posted works but....
If I unshare the directory I get Error 52
so maybe it doesn't work. How can I fix this?
Thanks
DS

The idea that DLookup() is slower than similar mechanisms to retrieve a value is
a myth.
Domain functions impose a certain overhead Per Call and that makes them poorly
suited for queries and looping operations. Other than that their performance is
fine.

 

[DS]

Another Urban Myth!
Glad to hear it!
Thanks Rick
DS

 

[Douglas J. Steele]

That's not what I suggested and, in fact, the changes you made means it will
not work as you intend. The error 52 you're trying to avoid will occur by
trying to use strPath when the file's not accessible. That means you must
try to use the file BEFORE you check for the error. If you're determined not
to put in error handling such as I had (it's a mistake not to have error
handling in every routine in my opinion), change what you've got to

Dim TestSQL As String
Dim Test2SQL As String
Dim strPath As String

DoCmd.SetWarnings False
TestSQL = "UPDATE table1 " & _
"SET table1.IDName = ""New"" " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (TestSQL)

strPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "")
On Error Resume Next
If Len(Dir$(strPath)) > 1 Then
If Err.Number = 0 Then
DoCmd.SetWarnings False
Test2SQL = "UPDATE table1 IN '" & strPath & "' " & _
"SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _
"WHERE table1.IDNumber = 1 ;"
DoCmd.RunSQL (Test2SQL)
DoCmd.SetWarnings True
End If
End If

 

[DS]

Thanks Douglas I see your point. This is still fairly new to me so I
appreciate your help and patience. The new code is being used and
working fine. Once again thank you very much!
DS

 

[DS]

Actually I have to congratulate you even further. My code was hanging a
bit, yours workd lickity split fast! Once again.
Thank You
DS