Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

access query to excel



I'm trying to catch several rows from an access database in a recordset butt I'm stuck on the SQL part in the code.
SQL statement works fine in access, butt only returns the headers when executed in VBA for excel.
Does someone has any idee what's wrong with my code?

Private Sub CB_ZOEK_Click()

 'variabelen voor databaseconnectie
 Dim cn As ADODB.connection
 Dim rs As ADODB.Recordset
 Dim fld As ADODB.Field
 Dim MyConn
 Dim i As Long
 Dim Overzet As Worksheet
 Set Overzet = Sheets("test")

 Dim SQL As String
 Dim FullPath As String

 'andere variabelen
 Dim Zoek, Temp As String
 Dim Werfnr, Postcode, Naam, Alias, Adres, Tel, GSM, Mail, BTW, Werfleider, Tekenaar, Staaltekenaar, Vertegenwoordiger As String

 'Bepaal wat je wilt zoeken
 Zoek = UF_Zoeken.TB_ZoekTerm.Value

 'Waarden bepalen
 If OptionButton_KlantNaamZoeken = True Or OptionButton_gemeenteZoeken = True Or OptionButton_ProjectNr = True Then

 FullPath = Pad & BS

 Set cn = New ADODB.connection
 MyConn = FullPath

 With cn
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .Open MyConn
 End With

 Set rs = New ADODB.Recordset
 rs.CursorLocation = adUseServer

 SQL = "SELECT Nummer, Jaar, Naam, [Naam alias], Werfadres, Werfpostcode, Werfplaats, Land, Telefoon, GSM, [E-mail], [BTW nr], werfleider, Tekenaar, Staaltekenaar, Vertegenwoordiger FROM Klanten "
 If OptionButton_KlantNaamZoeken = True Then
 SQL = SQL & "WHERE Naam Like " & Zoek & ";"
 rs.Open SQL, cn, adOpenForwardOnly
 End If

 Range("A1").CurrentRegion.Offset(1, 0).Clear

 i = 0
 With Range("A1")
 For Each fld In rs.Fields
 .Offset(0, i).Value = fld.Name
 i = i + 1
 Next fld
 End With

 Range("A2").CopyFromRecordset rs

 Set rs = Nothing
 Set cn = Nothing
 End Sub      


Changed  Dim Zoek, Temp As String to  Dim Zoek As String, Temp As String
Removed Dim Werfnr, Postcode, Naam, Alias, Adres, Tel, GSM, Mail, BTW, Werfleider, Tekenaar, Staaltekenaar, Vertegenwoordiger As String cause I don't need it in this code.

This returns only the headers to my excel file.
When I remove SQL = SQL & "WHERE Naam Like '*" & Zoek & "*';" I get the hole access table to excel. So in my opinion there is something wrong in the SQL part of my code.
Twone Sep 5, '17 at 4:24 am
I did a little research and find that the wild card in ADO is % (not *). Could you try that out?
Variatus (rep: 4879) Sep 5, '17 at 5:55 am
Was the solution.
Twone Sep 5, '17 at 8:00 am
Add to Discussion



the wild card in ADO is % (not *)


Answer the Question

You must create an account to use the forum. Create an Account or Login