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