How use DLookup within VBA
I seem to have gotten what I wanted from earlier question, I have created below a table where I want to store two strings:
Tbllinkstring, see attached table
Now I want to use DLookup to be pick the string from the table, how do I use the IF and THEN and ELSE from the code below so that it evaluate and use the correct string, do not worry about the rest, I will take care of the rest, see the shaded area in the attached table:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DA
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
For me this will be the best solution because before deploying the application I will be required only to change one of the links above in the table called Tbllinkstring to the correct host name, this means that I will still maintain my existing laptop links which allow me to compile and make an ACCDE copy without an error. Once the application is deployed to a new host name again it will simply drop my existing links after evaluating and use the new one. The advantage is that the users will not be required to select the correct link at all, the system will handle that for them.
On the shaded areas I want to use the Dlookup and IF THEN & ELSE, any Idea here????
Regards
Chris
Table-links.docx
Tbllinkstring, see attached table
Now I want to use DLookup to be pick the string from the table, how do I use the IF and THEN and ELSE from the code below so that it evaluate and use the correct string, do not worry about the rest, I will take care of the rest, see the shaded area in the attached table:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DA
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function
For me this will be the best solution because before deploying the application I will be required only to change one of the links above in the table called Tbllinkstring to the correct host name, this means that I will still maintain my existing laptop links which allow me to compile and make an ACCDE copy without an error. Once the application is deployed to a new host name again it will simply drop my existing links after evaluating and use the new one. The advantage is that the users will not be required to select the correct link at all, the system will handle that for them.
On the shaded areas I want to use the Dlookup and IF THEN & ELSE, any Idea here????
Regards
Chris
Table-links.docx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just so you have an idea, the function below currently is checking for a disk file, but I could include additional checks. Also, it accepts an optional argument so I can force debug mode if I want to.
Jim.
Jim.
Function DebugMode(Optional varMode As Variant) As Integer
' Determines if app is running in debug mode.
' Debug mode is set by placing a .txt file
' in the database directory with a name of
' <database name>_Debug.txt
' Or by calling this routine with the optional argument.
Dim strFileName As String
Dim intLen As Integer
10 On Error GoTo DebugMode_Error
20 If Nz(gvarDebugMode, "") = "" Then
30 On Error GoTo 0
40 Err = 0
50 strFileName = left(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "_Debug.txt"
60 intLen = Len(Dir(strFileName))
70 If (Not Err And intLen > 0) Then
80 gvarDebugMode = True
90 Else
100 gvarDebugMode = False
110 End If
120 On Error GoTo DebugMode_Error
130 End If
140 If Not IsMissing(varMode) Then
150 gvarDebugMode = varMode
160 End If
DebugMode_Exit:
170 On Error Resume Next
180 DebugMode = Nz(gvarDebugMode, False)
190 Exit Function
DebugMode_Error:
200 gvarDebugMode = Null
210 Resume DebugMode_Exit
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In my opinion, it is better to use a conditional compilation directive,, so you can write different behavior fo your functionalities without touching too much on the project, neither test too many variables or objects.
Bit too inflexible for me. I like to be able to jump into debug mode on my apps in a number of ways.
Jim.
I'm also left wondering where that would leave you with the compile state as your actually altering which lines of your code are getting compiled.
Jim.
Jim.
I'm also left wondering where that would leave you with the compile state as your actually altering which lines of your code are getting compiled.The compile state reset whenever you change the directive's state (exactly like when you touch anything on your project).
I'm not sure to see what you have in mind here.
The compile state reset
Just that; every time you change the constant, your actually altering the lines of code that get executed and breaking the compile state.
I don't see any upside to doing a debug flag this way.
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That or call a function, which can remain un-changed when you call it, but the checks it makes to see if your in debug mode can take many forms:
1. A database property
2. A flag in a table
3. A file on disk
4. A registry entry
and may be one or more of them.
Jim.