Searching an Access Database

[AndrewKent]

Hi there,

I'm having a little bit of a problem trying to write VBA coding that will return data from an Access database into Excel. I have the basics up and running but for some reason I think there is something wrong as the coding I have ONLY pulls back the field names...

Sub FindRecord()

    Dim DBName, DBLocation, FilePath As String
    Dim DBConnection As ADODB.Connection
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set DBConnection = New ADODB.Connection
    DBName = "Relational Database.mdb"
    DBLocation = "\\fs10edx\grpareas\fpd\Development\Database Prototype\"
    FilePath = DBLocation & DBName

    With DBConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open FilePath
    End With
    
    Call FindCustomerData(DBConnection)

    DBConnection.Close
    Set DBConnection = Nothing
    
    Worksheets("Input").Activate
    Range("A1").Select

End Sub

Sub FindCustomerData(DBConnection As ADODB.Connection)

    Dim DBRecordSet As ADODB.Recordset
    Dim Field As ADODB.Field
    Dim Query As String
    Dim Start As Long
    
    Query = "SELECT * FROM tblCustomerData WHERE Surname = '" & Worksheets("Input").Range("C24").Value & "'"

    Set DBRecordSet = New ADODB.Recordset
    DBRecordSet.CursorLocation = adUseServer
    DBRecordSet.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
    
    Start = 0
    With Worksheets("Input").Range("E24")
        For Each Field In DBRecordSet.Fields
            .Offset(0, Start).Value = Field.Name
            Start = Start + 1
        Next Field
    End With

    DBRecordSet.Close
    Set DBRecordSet = Nothing

End Sub

...the table contains only three test records, two of which have the surname Smith. if I put Smith into C24, it only brings back the field names and no records. I can see the records in my database (the code connects to it without a problem and there are no error messages) however it doesn't pull anything back, when I should expect it to bring back two records.

Can anyone help?

Andy

 

[AndrewKent]

Nevermind, I forgot to add a line of code...

Worksheets("Input").Range("E25").CopyFromRecordset DBRecordSet

...I'm a complete chump!