access query to excel

0

Hi,

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

 Overzet.Activate
 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

 rs.Close
 cn.Close
 Set rs = Nothing
 Set cn = Nothing
 End Sub      
Answer
Discuss

Discussion

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: 1378) Sep 5, '17 at 5:55 am
Was the solution.
Twone Sep 5, '17 at 8:00 am
Add to Discussion

Answers

0

the wild card in ADO is % (not *)

Discuss

Answer the Question

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