Using a Recordset in DLookup

[Sandra Walsh]

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.

Private Sub Form_Load()
 
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & " Data Source=\\Network\Folder\subfolder\susubfolder\MyDatabase.accdb;" _
        & " Password=MyPassword;Persist Security Info=False"
conn.Open strg
Dim strsql As String
strsql = "SELECT dbo_INT_AUX_DIR_LIST.LISTING_ID, q_FolderList.DIRECTORY_NM" _
        & " FROM (dbo_INT_AUX_LISTING LEFT JOIN dbo_INT_AUX_DIR_LIST " _
        & " ON dbo_INT_AUX_LISTING.LISTING_ID = dbo_INT_AUX_DIR_LIST.LISTING_ID) " _
        & " LEFT JOIN q_FolderList " _
        & " ON dbo_INT_AUX_DIR_LIST.CONTAIN_DIR_ID = q_FolderList.DIRECTORY_ID" _
        & " WHERE (((q_FolderList.DIRECTORY_NM) Not LIKE '*" & "firm lawyers" & "*' " _
        & " And (q_FolderList.DIRECTORY_NM) Not LIKE '*" & "Companies (Public)" & "*') " _
        & " AND ((dbo_INT_AUX_LISTING.LISTING_TYP_IND)=1))" _
        & " ORDER BY dbo_INT_AUX_DIR_LIST.LISTING_ID "
 
rst.ActiveConnection = conn
 
'to open a recordset, a source (strsql) and an ActiveConnection(conn) are provided.
 
rst.Open strsql, conn
 
MsgBox ("open")
 
Set rst = Nothing
Set conn = Nothing
 
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.

=DLookUp("DIRECTORY_NM","q_CompanyStatus","Comp_ID = " & [Company_ID])

Many thanks!
Sandra

[Rabbit]

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

[Sandra Walsh]

Well that solves that!!

Thanks :-)

[ADezii]

You ca use the 'Find' Method of the Recordset Object if you so desire. The General Idea would be:

'Must have a Reference to the Microsoft ActiveX Data Objects X.X Object Library
Dim rst As ADODB.Recordset
Dim strSearchString As String
Dim strSQL As String
 
strSQL = "Select * FROM tblEmployees;"
strSearchString = "Flintstone"
 
Set rst = New ADODB.Recordset
 
With rst
  .Source = strSQL
  .ActiveConnection = CurrentProject.Connection
  .CursorType = adOpenKeyset
  .LockType = adLockReadOnly
    .Open
 
  .Find "[Last] = '" & strSearchString & "'"
 
    'Record found matching Criteria
    If Not .EOF Then
      MsgBox ![First] & " " & ![Last] & " | Record# " & .AbsolutePosition
    Else
      MsgBox strSearchString & " not found!"
    End If
End With
 
rst.Close
Set rst = Nothing

[Sandra Walsh]

Thanks, AD - I will give that a try :-)

[NeoPa]

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.