Searching an Access Database

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
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...

Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Nevermind, I forgot to add a line of code...

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

...I'm a complete chump!
 
Upvote 0

Forum statistics

Threads
1,191,719
Messages
5,988,296
Members
440,148
Latest member
sandy123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top