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
- 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
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
- =DLookUp("DIRECTORY_NM","q_CompanyStatus","Comp_ID = " & [Company_ID])
Sandra