Access 2007 Recordsets?

[PMHCSLtd]

This code, working from an Outlook project was fine until I updated the
database to Access 2007!!


Dim objConn As ADODB.Connection
Dim rstConn As ADODB.Recordset
Dim strSQL As String

Set objConn = OpenAccessDB(strDBPath)
Set rstConn = CreateObject("ADODB.Recordset")
strSQL = "SELECT strDomain, strActive FROM tblData ORDER BY strDomain"
rstConn.Open strSQL, objConn


Function OpenAccessDB(strDBPath As String, Optional UID = "admin", Optional
PWD = "") As ADODB.Connection
Dim objADOConn As ADODB.Connection
Dim strConn As String

On Error Resume Next

Set objADOConn = CreateObject("ADODB.Connection")
objADOConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" &
strDBPath & "; " 'strConn

If (Err = 0) And (objADOConn.State = adStateOpen) Then
Set OpenAccessDB = objADOConn
Else
Set OpenAccessDB = Nothing
End If

Set objADOConn = Nothing

End Function


I now get a Run Time Error 3709, can someone either show me how 2007 opens
database and a recordset or point me to an article that will explain! Thnak
you.

Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags

[CFDM]

Your connection code needs to look like this


PUBLIC FUNCTION OpenConnection()
Set cnn1 = New ADODB.Connection
cnn1.Open CurrentProject.Connection
End Function

Then refer to it on load. I think your error is in the connection.

Edited 24-Jun-06 by geekgirlau. Reason: insert vba tags

[PMHCSLtd]

Thank you, having down some more checking it does appear to be teh connection but I am nbot sure how to implement your code. Where dows the value for CurrentProject.Connection come from?

[CFDM]

look up current project connection in the help file

[PMHCSLtd]

I understand it within Access but my code is part of an Outlook Project and I am assuming that CurrentProject.Connection would only apply within an Access Project?