DLOOKUP in an external Database gives TYPE Mismatch-Error

[Reiner Harmgardt]

Hi NG,

in the following code i'm trying do make a DLOOKUP in an external Database
(xTarget_MDB)
and i receive the error message Type Mismatch with DLOOKUP highlighted.

Can anybody tell me how i can do better that this works.

Thanks for helping!

Have a nice day!

Regards

Reiner

Sub Update_Freeze_mdb_Name_in_Alldef_Attachments(xTarget_MDB, xFreeze_Dir,
xFreeze_MDB_Name As String)

' 2004-07-21 RH
' -------------
Dim DB As Database
Set DB = DBEngine.Workspaces(0).OpenDatabase(xTarget_MDB)

Dim SQL As String

SQL = ""
SQL = SQL & " UPDATE [ALLDEF Attachments] as A"
SQL = SQL & " SET A.[DB] = '" & xFreeze_MDB_Name & "'"
SQL = SQL & " WHERE A.[P] = 'xFreeze'"

DoCmd.SetWarnings True
DB.Execute (SQL)
DoCmd.SetWarnings False

Dim xxFreeze As Variant
Dim strDaten As String
Dim i As Integer

strDaten = xFreeze_Dir & xFreeze_MDB_Name

For i = 0 To DB.TableDefs.Count - 1
If DB.TableDefs(i).Connect <> "" Then
If InStr(1, DB.TableDefs(i).Connect, "Freeze") > 0 Then

xxFreeze = DLookup("P", DB("ALLDEF Attachments"),
"[Original Name] = '" & DB.TableDefs(i).Name & "'")
' ***********************************
' messgage = Type mismatch on DLookup
' ***********************************

If xxFreeze = "xFreeze" Then
If Mid(DB.TableDefs(i).Connect, 11) <> strDaten
Then
DB.TableDefs(i).Connect = ";database=" &
strDaten
DB.TableDefs(i).RefreshLink
End If
End If

End If
End If
Next i


DB.Close
Set DB = Nothing


End Sub

 

[Albert D. Kallal]

DLookup always looks in the current database, so you can't use it. You can
simply just create a sql statement, and use that:

xxFreeze = DLookup("P", DB("ALLDEF Attachments"),
"[Original Name] = '" & DB.TableDefs(i).Name & "'")

You can use:

dim rstLookup as dao.recordset
dim strLookupSql as string

strLookUpSql = "select P from [ALLDEF attachments] where [Original Name] =
'" & DB.TableDefs(i).Name & "'"
set rstLookup = DB.OpenReocrdSet(strLookUpSql)
if rstLookUp.RecordCount > 0 then
xxFreeze = rstLookUp!P
else
xxFreeze = Null
end if
rstLookUp.Close
set rstLookup = nothing

You could also move the code to a function, and call it "mydlookup" or
somting like that....