Access DSN Provider

S

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
 
R

RoyVidar

Scott said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Max Record with ADO 2
Recordset Error Handling 3
ado issue 0
DoCmd.RunSQL Syntax Help 6
Problem with ADO Code 2
getting subform information 2
Help writing SQL in VBA 3
needing to convert? 1

Top