2002 -> 2007 dao ado argh!

[Babbage]

Hi all!

Ok today for the first time I get the chance to finally look at office 2007 and upgrading my access db from 2002 (our entire workplace is upgrading to 2007 and our db is going with it)

The access itself is used mainly as a front end to a back end ms sql 2005 database, with a smattering of local tables in the db too.

So conversion ran well, then it came to testing the code.

Up sprang forth the following error....
odbcdirect is no longer supported rewrite the code to use ado instead of dao

`Oh bugger` was my first thought, knowing the amount of code in our db. I noted that none of the `features no longer available in access 2007` guides id read mentioned this!

So I do some research to see the issues in hand, and from that what it takes to convert. The MS examples were a bit lacking, and other examples dotted around varied in response. So I thought id ask opinions here.

The first port of call when my access opens is to try to create a basic connection to the ms sql server without actually accessing any data, to check the database link itself is ok (we can trap for problems such as user has access denied or doesnt have permissions set up on the ms sql server to access the database) - at this point in time there may be no tables linked to our database.

I normally do this with a code snippet of

    On Error Resume Next
    Err.Clear
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("", dbDriverNoPrompt, False, getODBCConnectionString)
 
    If Err.number <> 0 Then
        ' handle error accordingly
    End If

I can't quite work out the equivalent code using an ADODB.Connection

I presume the default way of linking tables in 2007 is via ODBC link tables so I do need to check if the connection works. ado != ODBC is it, so checking an ado connection wouldnt give me what im after?

Another question, heres an example of my opening a recordset in current code...

    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim rs As Recordset
    Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("", , False, getODBCConnectionString)
    Set rs = conPubs.OpenRecordset("SELECT fields FROM mytable", dbOpenSnapshot)

Code im already using using ado connections (to execute stored procedures from access) goes something like

    Dim objConn As ADODB.Connection
    Dim objCmd As ADODB.Command
    Dim sDataConnect As String
    Dim lngErrorNum As Long
 
     ' Instantiate a database connection
    Set objConn = CreateObject("ADODB.Connection")
 
    ' Create an ADO connect string
    sDataConnect = getOLEConnectionString
 
    ' Open a database connection
    objConn.Open sDataConnect
 
    Set objCmd = CreateObject("ADODB.Command")
    With objCmd
        .ActiveConnection = objConn
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.mystoredproc"
         etc.etc.
        .Execute , , adExecuteNoRecords
        lngErrorNum = .Parameters.item("@err")
        etc.etc.
    End With
    Set objCmd = nothing
    objConn.close
    Set objConn = nothing

I see lots of examples using currentproject.connection, but I wager this doesnt mean I can share multiple seperate recordsets down the same connection at the same time - should I be creating my own adodb connection each time or only using currentproject.connection. I don't understand how that works - theoretically your access may be connected to several back end db's - what does currentproject.connection actually point to? Im guessing currentproject.connection = linked tables (which is what most of my code based querying accesses)

Hitting the server direct in code based queries or converting lots of things to pass through queries is not an option, given the volume of work, and dynamic nature of much of it.

So with the above I supose im asking - what kind of connection is required on the first example above (currentproject.connection - and can this share multiple live recordsets down it at the same time?), and is the ado stored procedure example still valid, or a little `long winded` compaired to current good working practise examples!

Many thanks in advance for any input,

Martin

 

[LPurvis]

Hi

Lots of points to address really - I'll just wander through them...

ado != ODBC
Not necessarily. ADO is a non platform specific library. It is a consumer of data and depends on a provider to supply the drivers to access the data source. It can use OLEDB or ODBC providers to connect to data sources. But most examples are for OLEDB and that is indeed not useful for linked tables.

what does currentproject.connection actually point to?
The currently running application. If you're using an MDB (ACCDB) then that is the MDB loaded in Access. Linked tables are exposed directly to that connection - so you're able to refer to them (pretty much) as you would a native table. But the source of those linked tables is not exposed directly to the connection object.

I wager this doesnt mean I can share multiple seperate recordsets down the same connection at the same time
You certainly can - if the scope of your connection permits it. The Currentproject.Connection object might not be the one you want for that though

should I be creating my own adodb connection each time
If you're wanting to execute server based objects (as in the example of the Stored Proc) then you'll need to establish your own connection yes. But you needn't create a different one each time. It's often a good choice to maintain a single connection to the server using it to execute statements and return recordsets as required.
(It depends on the application of course - sometimes it makes more sense to destroy the connection after each use).


What is your ultimate aim / problem?
The example you have for executing the stored proc is almost exactly implementable for returning a recordset instead.

 

[Babbage]

I supose the short answer there fore is, it doesnt look like there will be much of a problem - just the slog of going through all code and updating as relevant!

I just don't want to go about doing it one way, to find out that method is actually a couple of years past its sell by date on the current best practises.

How about the initial question re checking an odbc connection?

The other thing ive noticed, before when using
Dim wrkODBC As Workspace
Dim conPubs As Connection
defining my old DAO connections, I go on to create querydef's off it
.... = conPubs.CreateQueryDef()
Which is not offered as part of a adodb.connection object - is there an equivalent to this?

many thanks

martin

 

[LPurvis]

Hi

How do you mean "checking an ODBC connection".
Checking it for what? In DAO or ADO?

Were you previously creating querydef objects on the server on the fly?
(Not necessarily persisted ones though).
Command objects (as you've already used in the earlier SP example) are very much ADO's equivalent to DAO's QueryDefs.
But they offer more than QueryDefs do - at the price of perhaps a bit more work at times. (i.e. your ADO code and parameter creation is perhaps a tad lengthier than what you used before?)

 

[Babbage]

On Error Resume Next
Err.Clear
Dim wrkODBC As Workspace
Dim conPubs As Connection
Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("", dbDriverNoPrompt, False, getODBCConnectionString)

If Err.number <> 0 Then
' handle error accordingly
End If

That bit there - checks to see if a connection can be established without actually pulling anything down. Let's me pre-check things like if a user has permission to connect to a database and ODBC link is working (and handle any failure if theres a problem) before we even think about linking tables or snagging data!

I tried re-creating using ado but didnt have any luck - though that could have been because I just got it a bit wrong!

Martin

 

[LPurvis]

Well while you can establish an ODBC connection using ADO it isn't going to be as consistent a test as with your previous ODBC direct (though it is more analogous in other ways - regarding SQL syntax and execution location etc).

You might be just as well opening a database object against the server db - with essentially the same connection string you'll be using in your linked tables thereafter.
e.g.

Dim db As DAO.Database
Dim strConnect As String
 
strConnect = "ODBC;Driver=SQL Server;Server=SvrNa;Database=DbNa;Trusted_Connection=Yes"
Set db = DBEngine.OpenDatabase("DbNa", dbDriverCompleteRequired, False, strConnect)

Just to ensure it's available as you say. (Perhaps before attempting to create the linked tables).

That said... that's if you're planning on using the linked tables or other DAO based data requests.
If you're planning on working with ADO - then IMO attempting to open an ADO connection is the appropriate way to proceed.

 

[LPurvis]

And just to offer it - to enable simple connection creation - there's this class below.
It's designed for connections to either SQL Server or Jet. And for a choice of the providers. (For example you can use it to create an ODBC ADO connection just as easily as you would one using the OLEDB provider).

It's also designed so that it can be used to ensure that if the object goes out of scope then the connection is closed rather than just letting the variable be destroyed by VBA garbage collection.

Example use in in the header comments.

Cheers.

Option Compare Database
Option Explicit
'Class Module: clsADOConnection
'Accepts properties to build an Jet or SQL Server OLEDB ADO connection
'Connection closed on object destruction
'Written by Leigh Purvis 2006
'Updated October 2007 - added ExecOption, CloseFirst and Shape options
'Updated February 2008 - added DSN and Trusted options
'Example Usage: Jet MDB
'
'    Dim objADO As New clsADOConnection
'    Dim cnn As ADODB.connection
'
'    With objADO
'        .ProviderType = ddJetOLE
'        .Database = "C:\db1.mdb"
'        .OpenConnection
'        Set cnn = .connection
'    End With
'Example Usage: SQL Server DB
'    With objADO
'        .ProviderType = ddSQLOLE
'        .Server = "ServerName"
'        .Database = "DBName"
'        .UserName = "UN"
'        .Password = "PW"
'        .AutoClose = True 'We want the connection to close when class object goes out of scope.
'        .OpenConnection
'        Set cnn = .connection
'    End With
Private Const cModName = "clsADOConnection"
Public Enum ddProviderType
    ddJetOLE
    ddSQLOLE
    ddSQLNative
    ddJetOLEShape
    ddSQLOLEShape
    ddSQLOLEODBC
End Enum
Private mcnn As ADODB.Connection
Private mstrConn As String
Private mstrUser As String
Private mstrPass As String
Private mstrServer As String
Private mstrDB As String
Private mstrProvider As String
Private mstrDataProvider As String
Private mstrDSN As String
Private mblnTrusted As Boolean
Private mddProvider As ddProviderType
Private mExecuteOption As ADODB.ExecuteOptionEnum
Private mblnRelease As Boolean
Private mblnCloseIfOpen As Boolean
Public Property Let ConnectionString(ConnectString As String)
    mstrConn = ConnectString
End Property
Public Property Get ConnectionString() As String
    ConnectionString = mstrConn
End Property
Public Property Let UserName(UN As String)
    mstrUser = UN
End Property
Public Property Get UserName() As String
    UserName = mstrUser
End Property
Public Property Let Password(PW As String)
    mstrPass = PW
End Property
Public Property Let Trusted(UseTrusted As Boolean)
    mblnTrusted = UseTrusted
End Property
Public Property Get Trusted() As Boolean
    Trusted = mblnTrusted
End Property
Public Property Let Server(ServerName As String)
    mstrServer = ServerName
End Property
Public Property Get Server() As String
    Server = mstrServer
End Property
Public Property Let Database(DBName As String)
    mstrDB = DBName
End Property
Public Property Get Database() As String
    Database = mstrDB
End Property
Public Property Let DSN(DSN_Name As String)
    mstrDSN = DSN_Name
End Property
Public Property Get DSN() As String
    DSN = mstrDSN
End Property
Public Property Let ExecOption(ConOption As ADODB.ExecuteOptionEnum)
    mExecuteOption = ConOption
End Property
Public Property Get ExecOption() As ADODB.ExecuteOptionEnum
    ExecOption = mExecuteOption
End Property
Public Property Let CloseFirst(CloseIfOpen As Boolean)
    mblnCloseIfOpen = CloseIfOpen
End Property
Public Property Get CloseFirst() As Boolean
    CloseFirst = mblnCloseIfOpen
End Property
Public Property Let ProviderType(ConType As ddProviderType)
    
    mddProvider = ConType
    Select Case ConType
        Case ddJetOLE
            mstrProvider = "Microsoft.Jet.OLEDB.4.0"
        Case ddSQLOLE
            mstrProvider = "SQLOLEDB.1"
        Case ddSQLNative
            mstrProvider = "SQLNCLI"
        Case ddJetOLEShape
            mstrProvider = "MSDataShape"
            mstrDataProvider = "Microsoft.Jet.OLEDB.4.0"
        Case ddSQLOLEShape
            mstrProvider = "MSDataShape"
            mstrDataProvider = "SQLOLEDB.1"
        Case ddSQLOLEODBC
            mstrProvider = "MSDASQL"
    End Select
    
End Property
Public Property Get ProviderType() As ddProviderType
    ProviderType = mddProvider
End Property
Public Property Let AutoClose(CloseConnection As Boolean)
    mblnRelease = CloseConnection
End Property
Public Property Get AutoClose() As Boolean
    AutoClose = mblnRelease
End Property
Public Property Set Connection(PassedConnection As ADODB.Connection)
    Set mcnn = PassedConnection
End Property
Public Property Get Connection() As ADODB.Connection
    Set Connection = mcnn
End Property
Public Property Get Connected() As Boolean
    If Not mcnn Is Nothing Then
        Connected = mcnn.State <> 0
    End If
End Property
Public Function OpenConnection() As Boolean
On Error GoTo HandleErr
    With mcnn
        'Check state - close to re-open if so requested
        If .State = adStateOpen Then
            If mblnCloseIfOpen Then
                .Close
            Else
                Exit Function
            End If
        End If
        
        BuildString
        .ConnectionString = mstrConn
        If mExecuteOption > 0 Then
            .Open , , , mExecuteOption
        Else
            .Open
        End If
        
        'Return state as success of function
        OpenConnection = .State = adStateOpen
    End With

ExitHere:
    Exit Function
HandleErr:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in " & cModName & ".OpenConnection", vbExclamation
    Resume ExitHere
    
End Function
Private Sub BuildString()
'If connection string not already set then build it up from passed values
'Differs for Jet and SQL Server
    If Len(mstrConn) = 0 Then
        'Doesn't exist yet - build it up
        mstrConn = "Provider=" & mstrProvider & ";"
        AddConnectionPart "Data Provider", mstrDataProvider
        If Me.Trusted Then
            'Trusted part depends on connection provider
            If Me.ProviderType = ddSQLOLEODBC Then
                AddConnectionPart "Trusted_Connection", "Yes"
            Else
                AddConnectionPart "Integrated Security", "SSPI"
            End If
        Else
            AddConnectionPart "User ID", Me.UserName
            AddConnectionPart "Password", mstrPass
        End If
        If Me.ProviderType = ddJetOLE Then
            AddConnectionPart "Data Source", mstrDB
        Else
            AddConnectionPart "DSN", mstrDSN
            AddConnectionPart "Data Source", mstrServer
            AddConnectionPart "Initial Catalog", mstrDB
        End If
        
    End If
    Debug.Print mstrConn
    
End Sub
Private Sub AddConnectionPart(ConnectionPart As String, PartValue As String)
    If Len(PartValue) > 0 Then
        mstrConn = mstrConn & ConnectionPart & "=" & PartValue & ";"
    End If
End Sub
Private Sub Class_Initialize()
    Set mcnn = New ADODB.Connection
End Sub
Private Sub Class_Terminate()
'As class is destroyed close the connection if so required
On Error Resume Next
    If mcnn.State <> 0 And mblnRelease Then
        mcnn.Close
    End If
    Set mcnn = Nothing
End Sub

 

[Babbage]

Many thanks for all that LPurvis - your a star!

Will read/digest and try

Martin

 

[LPurvis]

No Problemo.

 

[LPurvis]

Does a fundamental part of your application use a "direct" DAO connection to your server for data access - or is it primarily just for that initial check and then you fall back on linked tables (which become linked in the mean time)?

Even though linked tables are MS' official preferred connection method with Access 2007 - they do still recommend switching to ADO if ODBCDirect has been a feature of your application. Of course the ease of the move depends entirely upon the extent (and duplication) of your existing code.
(i.e. if you had a single procedure for establishing such access then replacing that is simpler than if you have the same connection method repeated in every procedure).

Cheers.