469,271 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Using a Recordset in DLookup

Hello -

I have an Access 2007 database that has an ODBC connection to several very large, non-updateable tables in our CRM system.

I would like to speed up my app by using a recordset as the source for some DLookup fields in my forms.

I found and have adapted the following code that runs when I load my Dashboard, the first form that loads whent the app is opened.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. Dim conn As New ADODB.Connection
  4. Dim rst As New ADODB.Recordset
  5. Dim strg As String
  6. strg = "Provider=Microsoft.ACE.OLEDB.12.0;" _
  7.         & " Data Source=\\Network\Folder\subfolder\susubfolder\MyDatabase.accdb;" _
  8.         & " Password=MyPassword;Persist Security Info=False"
  9. conn.Open strg
  10. Dim strsql As String
  11. strsql = "SELECT dbo_INT_AUX_DIR_LIST.LISTING_ID, q_FolderList.DIRECTORY_NM" _
  12.         & " FROM (dbo_INT_AUX_LISTING LEFT JOIN dbo_INT_AUX_DIR_LIST " _
  13.         & " ON dbo_INT_AUX_LISTING.LISTING_ID = dbo_INT_AUX_DIR_LIST.LISTING_ID) " _
  14.         & " LEFT JOIN q_FolderList " _
  15.         & " ON dbo_INT_AUX_DIR_LIST.CONTAIN_DIR_ID = q_FolderList.DIRECTORY_ID" _
  16.         & " WHERE (((q_FolderList.DIRECTORY_NM) Not LIKE '*" & "firm lawyers" & "*' " _
  17.         & " And (q_FolderList.DIRECTORY_NM) Not LIKE '*" & "Companies (Public)" & "*') " _
  18.         & " AND ((dbo_INT_AUX_LISTING.LISTING_TYP_IND)=1))" _
  19.         & " ORDER BY dbo_INT_AUX_DIR_LIST.LISTING_ID "
  20.  
  21. rst.ActiveConnection = conn
  22.  
  23. 'to open a recordset, a source (strsql) and an ActiveConnection(conn) are provided.
  24.  
  25. rst.Open strsql, conn
  26.  
  27. MsgBox ("open")
  28.  
  29. Set rst = Nothing
  30. Set conn = Nothing
  31.  
  32. End Sub
Question 1 - errors
I am getting a
Compile error: User-defined type not defined
at line 3.

I had this code in a test database and it worked fine until line 9, where I got the error:

Run-time error '-2147217843 (80040e4d)':
Cannot start your application. The workgroup information file is missing or opened exclusively bby another user.


Both databases are in the same network directory.

Question 2 - Connecting
How do I reference this recordset in my DLookup where q_CompanyStatus has been replaced by the RecordSet? I am not sure where the RecordSet is named.
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("DIRECTORY_NM","q_CompanyStatus","Comp_ID = " & [Company_ID])
Many thanks!
Sandra
Jan 30 '12 #1

✓ answered by Rabbit

You can't reference a VBA recordset object using DLookup.

5 9371
Rabbit
12,516 Expert Mod 8TB
You can't reference a VBA recordset object using DLookup.
Jan 30 '12 #2
Well that solves that!!

Thanks :-)
Jan 30 '12 #3
ADezii
8,800 Expert 8TB
You ca use the 'Find' Method of the Recordset Object if you so desire. The General Idea would be:
Expand|Select|Wrap|Line Numbers
  1. 'Must have a Reference to the Microsoft ActiveX Data Objects X.X Object Library
  2. Dim rst As ADODB.Recordset
  3. Dim strSearchString As String
  4. Dim strSQL As String
  5.  
  6. strSQL = "Select * FROM tblEmployees;"
  7. strSearchString = "Flintstone"
  8.  
  9. Set rst = New ADODB.Recordset
  10.  
  11. With rst
  12.   .Source = strSQL
  13.   .ActiveConnection = CurrentProject.Connection
  14.   .CursorType = adOpenKeyset
  15.   .LockType = adLockReadOnly
  16.     .Open
  17.  
  18.   .Find "[Last] = '" & strSearchString & "'"
  19.  
  20.     'Record found matching Criteria
  21.     If Not .EOF Then
  22.       MsgBox ![First] & " " & ![Last] & " | Record# " & .AbsolutePosition
  23.     Else
  24.       MsgBox strSearchString & " not found!"
  25.     End If
  26. End With
  27.  
  28. rst.Close
  29. Set rst = Nothing
Jan 30 '12 #4
Thanks, AD - I will give that a try :-)
Jan 30 '12 #5
NeoPa
32,171 Expert Mod 16PB
This question should have been reported and deleted as it's an attempt to deal with multiple issues in the same thread.

It has a valid answer posted so I won't delete it now, but to avoid the temptation for anyone of continuing with other aspects of the Opening Post I will close it now. I am however, open to any questions or alternative suggestions on this via PM.

@Sandra.
Please check out how we expect threads to be posted here. There are two threads at the very top of the forum that deal with this and, in this particular case which includes posted code, you need to read When Posting (VBA or SQL) Code before doing so again.
Jan 30 '12 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Markus | last post: by
4 posts views Thread by jimgardener | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.