Using VBA to pull Data from an Access database into Excel

Dev Shed Forums Sponsor:

February 20th, 2013, 10:53 AM

Axweildr

'fie' on me, allege-dly

Join Date: Mar 2003

Location: in da kitchen ...

Posts: 12,874

Folding Points: 162285 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 4 Months 2 Weeks 1 Day 20 h 28 m 56 sec
Reputation Power: 6421

Send a message via Google Talk to Axweildr

Using VBA to pull Data from an Access database into Excel

I have a button in an excel spreadsheet, which should build an sql statement on the fly based on the contents of cell A1, and based on the SQL built return the ResultSet from the query

Code:

Watch :   : SelectStatement : "SELECT Customers.* FROM Customers WHERE Also_known_as LIKE 'Tak*'" : String : Sheet1.SearchAddress_Click

This is the statement it's building and when I enter the text below into the SQL query window, it returns the expected results, any ideas as to what I'm missing? Or what do I need to look for ...

Code:

SELECT Customers.* FROM Customers WHERE Also_known_as LIKE 'Tak*'

vba Code:

Original - vba Code

Private Sub SearchAddress_Click()

Dim Connection As New ADODB.Connection
Dim ResultSet As New ADODB.Recordset
Dim SelectStatement As String

' Drive code for Access
Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp.mdb"
' open database
Connection.Open

Set ResultSet.ActiveConnection = Connection

SelectStatement = "SELECT Customers.* " & _
     "FROM Customers " & _
     "WHERE Also_known_as LIKE '" & _
     ActiveSheet.Cells(1, 1).Value & _
     "*'"
MsgBox SelectStatement

ResultSet.Open SelectStatement
Do Until ResultSet.EOF
    MsgBox (ResultSet.Fields(0).Value & "," & ResultSet.Fields(1).Value & "," & _
        ResultSet.Fields(2).Value & "," & ResultSet.Fields(3).Value & "," & _
        ResultSet.Fields(4).Value & "," & ResultSet.Fields(5).Value & "," & _
        ResultSet.Fields(6).Value & "," & ResultSet.Fields(7).Value & "," & _
        ResultSet.Fields(8).Value & "," & ResultSet.Fields(9).Value & "," & _
        ResultSet.Fields(10).Value & "," & ResultSet.Fields(11).Value)
Loop

End Sub

__________________
--Ax
without exception, there is no rule ...
Handmade Irish Jewellery
Targeted Advertising Cookie Optout (TACO) extension for Firefox

The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones

09 F9 11 02
9D 74 E3 5B
D8 41 56 C5
63 56 88 C0
Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. -- Jamie Zawinski
Detavil - the devil is in the detail, allegedly, and I use the term advisedly, allegedly ... oh, no, wait I did ...
BIT COINS ANYONE

February 20th, 2013, 11:26 AM

medialint

Type Cast Exception

Dev Shed God 20th Plane (14500 - 14999 posts)

Join Date: Apr 2004

Location: OAKLAND CA | Adam's Point (Fairyland)

Posts: 14,938

Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 6 Months 2 Weeks 2 Days 26 m 49 sec
Reputation Power: 8490

DAO vs ADO I believe you might have better luck with the % wildcard than the * wildcard ... try it?

__________________
medialint.com

“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

February 20th, 2013, 11:30 AM