A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DLOOKUP in an external Database gives TYPE Mismatch-Error



 
 
Thread Tools Display Modes
  #1  
Old July 22nd, 2004, 08:18 AM
Reiner Harmgardt
external usenet poster
 
Posts: n/a
Default 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  
Old July 22nd, 2004, 09:00 AM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 10:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.