Persistent connection to backend, 'Dim dbsAlwaysOpen As DAO.Database' causing error

[mattscott]

Hi,

[Preface: I'm using Access 2002 and have relatively limited knowledge of the whole thing, have been learning as I've pieced together this project]

I've been having major speed issues with a form loading records and have been led to believe that opening a persistent connection to the backend oracle database should help. This code I found looks like it should do the job:

Dim dbsAlwaysOpen As DAO.Database

Private Sub Form_Close()
    Set dbsAlwaysOpen = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set dbsAlwaysOpen = OpenDatabase("MY BACKEND DATABASE", False)
End Sub

Whenever I run it though, the line: 'Dim dbsAlwaysOpen As DAO.Database' always brings up the following error:

"The expression On Open you entered as the event property setting produced the following error: User-defined type not defined."

Can anyone tell me what's wrong with this specifically, or if not, if there's a better way to accomplish this out there?

Thanks

 

[TJPoorman]

instead of Dim try Private

 

[ChrisO]

From memory, Access 2002 did not have a reference set to DAO.

Chris.

 

[mattscott]

Thanks Chris,

In checking what you said I found out that a) You're right, but b) it can be activated very easily (in VB Editor, Tools>References and tick 'Microsoft DAO 3.6 Object Library').

Still not fully working, but at least this part of it is sorted now.

The new problem is it won't recognise my DSN though for some reason, it's definitely there in my Machine Data Sources under the right name.

If anyone can help, I have it written as:
Set dbsAlwaysOpen = OpenDatabase("DSN=backenddatabase;", False)