Get Access Data from Excel using VBA

[jgmeza]

Hi,
I have an Access Database table called Alumni.
I need to get this data from an Excel spreadsheet using VBA only. I have to write a procedure in Excel that extracts the data from Access and stores it in an Excel spreadsheet called Students.

thanks

 

[SkipVought]

Hi,

Here's two procedures that might help as examples. The first, stores the database path in a cell named ConnectString. Add a reference to ADO Library

Sub ConnectToDatabase()
    sDB = Application.GetOpenFilename("Access Databases (*.mdb), *.mdb")
    If sDB <> False Then [ConnectString] = sDB
End Sub
Sub GetJobParameters()
    Dim cnn1 As ADODB.Connection
    Dim rst As Recordset
    Dim strCnn As String
    Dim varDate As Variant, sDB, lRow As Long, iCol As Byte

    ' Open a connection using the Microsoft Jet provider.
    Set cnn1 = New ADODB.Connection
    With cnn1
        .ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0&quot;
        .Open [ConnectString]
    End With
    
    Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = cnn1
        .CursorType = adOpenStatic
        On Error Resume Next
        .Open &quot;select * from tblJobMaster where JobNbr=&quot; & [JobNbr]
        .MoveFirst
        If Err.Number = 0 Then
            [JobName] = .Fields(&quot;JobName&quot;).Value
            [ContractorName] = .Fields(&quot;Contractor&quot;).Value
            [ContractorAddress] = .Fields(&quot;Physical1&quot;).Value & &quot; &quot; & _
                .Fields(&quot;Physical2&quot;).Value
            [ContractorFaxPhone] = .Fields(&quot;ContrFax&quot;).Value & &quot; / &quot; & .Fields(&quot;ContrPhone&quot;).Value
        Else
            [JobName] = &quot;&quot;
            [ContractorName] = &quot;&quot;
            [ContractorAddress] = &quot;&quot;
            [ContractorFaxPhone] = &quot;&quot;
        End If
        
        rst.Close
        cnn1.Close
        Set rst = Nothing
        Set cnn1 = Nothing
    End With
End Sub

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com