Access DSN Provider

[Scott]

I get an "object required" error when I call my function below as
GetMaxDataDate("C:\temp\myData.mdb")

The error is on the "Set Conn" line. I've checked my syntax, but Access
doesn't seem to like my DSN. Can someone help me with the "Set Conn" line
below?


CODE *******************

Public Function GetMaxDataDate(sDBPath)

Dim objRS As ADODB.Recordset, Conn As ADODB.Connection, sSQL As String
Dim sTable As String, sField As String
Set objRS = New ADODB.Recordset
objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic

Set Conn = "driver=Microsoft Access Driver (*.mdb);" & _
"dbq=" & sDBPath

sSQL = "SELECT MAX(recoverydate) FROM tblRecovery"
objRS.Open sSQL, Conn 'CurrentProject.Connection

GetMaxDataDate = objRS.Fields(0).Value

objRS.Close
Set objRS = Nothing

End Function

 

[RoyVidar]

I get an "object required" error when I call my function below as
GetMaxDataDate("C:\temp\myData.mdb")

The error is on the "Set Conn" line. I've checked my syntax, but
Access doesn't seem to like my DSN. Can someone help me with the "Set
Conn" line below?


CODE *******************

Public Function GetMaxDataDate(sDBPath)

Dim objRS As ADODB.Recordset, Conn As ADODB.Connection, sSQL As
String
Dim sTable As String, sField As String
Set objRS = New ADODB.Recordset
objRS.CursorType = adOpenDynamic
objRS.LockType = adLockOptimistic

Set Conn = "driver=Microsoft Access Driver (*.mdb);" & _
"dbq=" & sDBPath

sSQL = "SELECT MAX(recoverydate) FROM tblRecovery"
objRS.Open sSQL, Conn 'CurrentProject.Connection

GetMaxDataDate = objRS.Fields(0).Value

objRS.Close
Set objRS = Nothing

End Function

You need to instantiate before you open - something like this

Set Conn = New ADODB.Connection
Conn.Open "driver=Microsoft Access Driver (*.mdb);" & _
"dbq=" & sDBPath

or

Set Conn = New ADODB.Connection
Conn.ConnectionString = "driver=Microsoft Access Driver (*.mdb);" & _
"dbq=" & sDBPath
Conn.Open


Would you need some curly braces too? I wouldn't know, as I don't
use this technique, but check out
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForAccess