Different/right ways to communicate with Access Db from Excel?

[TomPC]

My dear Forum

Access 2007 / XP

I am becoming more confident communicating with an Access Db from Excel. I have a process that works but I want to understand the differences between the different methods of connection.

I have four modules that all seem to connect in different ways. What's the difference and why might one be better/more appropriate than another?

I have five questions, all of which are at the bottom.

1. "OpenDatabase" - with Workspaces(0)

Sub ExecuteSQL(strSQL As String, StatusMsg As String)
    Dim Db As Database
    Dim strAccessDBPath As String
    With Application
        strAccessDBPath = .[MasterPath].Value & .[DbPath].Value & .[DBFilename].Value
        Set Db = Workspaces(0).OpenDatabase(strAccessDBPath, ReadOnly:=True)
        Db.Execute (strSQL)
        Db.Close
        .StatusBar = False
    End With
End Sub

2. OpenDatabase - without Workspaces(0)

Sub ExecuteQuery(strQry As String, StatusMsg As String)
      Dim dba As Database
      With Application
        .StatusBar = StatusMsg
        Set dba = OpenDatabase(.[MasterPath].Value & .[DbPath].Value & .[DBFilename].Value)
        dba.Execute strQry
        dba.Close
        .StatusBar = False
    End With
End Sub

3. New Access.Application.OpenCurrentDatabase

Sub InsertInAccess(strType As String, strFilePath As String, strTableName As String, strSpec As String, PnlDate As Date)
    Dim strAccessDBPath As String
    Dim acApp As Access.Application
    Dim MasterPath As String
    With Application
        MasterPath = .[MasterPath].Value
        strAccessDBPath = MasterPath & .[DbPath].Value & .[DBFilename].Value
        Set acApp = New Access.Application
        With acApp
            acApp.OpenCurrentDatabase (strAccessDBPath)
            If strType = "csv" Then
                acApp.DoCmd.TransferText acImportDelim, strSpec, strTableName, strFilePath, False
            Else
                acApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strTableName, strFilePath, True
            End If
        End With
    End With
End Sub

4. ADODB connection/Microsoft Jet

Sub GetSQLResult(strSQL As String, sht As Worksheet, rng As Range)
    Dim i As Integer, strAccessDBPath As String, dealRS As ADODB.Recordset, dealConn As ADODB.Connection
    Set dealRS = New ADODB.Recordset
    Set dealConn = New ADODB.Connection
    With Application
        strAccessDBPath = .[MasterPath].Value & .[DbPath].Value & .[DBFilename].Value
    End With
    With dealConn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessDBPath & ";User Id=admin;Password=;"
        .Open
    End With
    'Get the field headings out of the Db
    dealRS.Open strSQL, dealConn, adOpenForwardOnly, adLockReadOnly
    For i = 0 To dealRS.Fields.Count - 1
        sht.rng.Offset(0, i).Value = dealRS.Fields(i).Name
    Next
    'Get the data recordset from the Db
    If Not dealRS.EOF Then
        sht.rng.Offset(1, 0).CopyFromRecordset dealRS
    End If
    Set dealRS = Nothing
    If dealConn.State = adStateOpen Then
        dealConn.Close
    End If
    Set dealConn = Nothing
End Sub

Questions

A. What do we more officially call the different kind of connections I used in each example?

B. What does Workspaces(0) do? Why is it apparently optional (difference between method 1 & 2).

C. Are all of these opening and closing cleanly (I haven't omitted any closes or 'nothings' have I?)

D. Are any of these better at doing a particular type of task than others?

E. If I picked only one methodology to be consistent which should I use and why.

As ever, dear Forum, I have the honour to remain your most humble servant and thank you for your kind consideration!

Tom

 

[Kyle123]

Hi Tom,

I have used Excel as a front end to numerous Access and SQL Server databases.

Generally speaking if you are only using access and have no plans to upgrade to another database DAO (options 1 & 2) requires less code and you have more control over the database than ADO would offer. For example you can do pessimistic access to a table for all users, ADO will only access pessimisticly for the current user.

ADO (option 4) would be a better choice if you are planning for the future where SQL server etc may be used, ADO is much better choice for remote or enterprise level databases. So if this may be in the pipeline, I'd be tempted to use ADO, you wouldn't need to rewrite any code then and usually only the SQL syntax would need changing.

Option 3 is not a good idea, automating access will be the slowest, it also requires that access be installed on any machine that the code will be run from and many organisations buy the office package that doesn't include access.

On workspaces, if it is not explicitly created, one will automatically be created.

For your ADO example it is better practice to close the connection as well as the recordset.

As an aside, at the risk of getting flamed it isn't strictly necessary to set objects to nothing any more, though it isn't doing any harm if you do.

Hope this helps,

Cheers

Kyle

 

[TomPC]

Kyle - what a perfectly crafted answer. I am very grateful indeed and have absolutely no follow-up questions for you because you made it all so clear.

Thanks,

Tom

 

[TomPC]

(And if there's any chance a clever chap like you could look at my other query - Help with a bit of SQL - I'd be really grateful!)