Early Binding

[azizrasul]

How do I change the following code, which works, so that it is early binding?

    Dim strConnect As String
    Dim strAccessFile As String
    Dim strSQL As String
    Dim rst As Object
    Dim strLandParcelID As String
    
    On Error GoTo ErrorHandler
    
    strAccessFile = "C:\Users\Aziz.Rasul\Scheduling Database - Back End.accdb"
    
    If Application.Version < 12 Then
        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessFile
    Else
        strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strAccessFile
    End If

    'Late binding, so no reference is needed.
    Set cnn = CreateObject("ADODB.Connection")

    cnn.Open strConnect
    
    strSQL = "SELECT tblTHAs.customer, tblTHAs.landparcelID, tblTHAs.surveyID, tblTHAs.surveydate, tblTHAs.surveyorID, tblTHAs.secondID, tblTHAsDetailed.hazards FROM tblTHAs INNER JOIN tblTHAsDetailed ON tblTHAs.surveyID = tblTHAsDetailed.surveyID ORDER BY tblTHAs.landparcelID, tblTHAs.surveydate;"

'    cnn.Execute strSQL 'Use this to run an action query.
    
    Set rst = cnn.Execute(strSQL)
    
    Do While Not rst.EOF
        strLandParcelID = rst!landparcelID
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing

ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & " - " & Err.Description
    End If

 

[wideboydixon]

Firstly, in the VBA Editor, you'll need to go to Tool>References and add a reference to Microsoft ActiveX Data Objects 6.1 Library

Once you've done that, change the code to this:

    Dim strConnect As String
    Dim strAccessFile As String
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Dim strLandParcelID As String
    Dim cnn As ADODB.Connection
    
    On Error GoTo ErrorHandler
    
    strAccessFile = "C:\Users\Aziz.Rasul\Scheduling Database - Back End.accdb"
    
    If Application.Version < 12 Then
        strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAccessFile
    Else
        strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strAccessFile
    End If

    'Late binding, so no reference is needed.
    Set cnn = New ADODB.Connection

    cnn.Open strConnect
    
    strSQL = "SELECT tblTHAs.customer, tblTHAs.landparcelID, tblTHAs.surveyID, tblTHAs.surveydate, tblTHAs.surveyorID, tblTHAs.secondID, tblTHAsDetailed.hazards FROM tblTHAs INNER JOIN tblTHAsDetailed ON tblTHAs.surveyID = tblTHAsDetailed.surveyID ORDER BY tblTHAs.landparcelID, tblTHAs.surveydate;"

'    cnn.Execute strSQL 'Use this to run an action query.
    
    Set rst = cnn.Execute(strSQL)
    
    Do While Not rst.EOF
        strLandParcelID = rst!landparcelID
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing

ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & " - " & Err.Description
    End If

WBD

 

[azizrasul]

Great, many thanks. Oh and good luck with the England match.