If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLOOKUP in an external Database gives TYPE Mismatch-Error
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(xTarg et_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 |
#2
|
|||
|
|||
DLOOKUP in an external Database gives TYPE Mismatch-Error
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.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Too few parameters error when Get External Access Data | aaronkuok | General Discussion | 2 | July 9th, 2004 11:41 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
Error trying to insert database for use in a merge | jeff | Mailmerge | 1 | April 27th, 2004 12:48 PM |
vba - need to find the type mismatch in this line of code | Bob Phillips | Worksheet Functions | 1 | January 30th, 2004 08:22 PM |