VBA met SQL string en REPLACE

[jjr333]

I can't get the 3rd sql query to work in Access, but in Excel I get an SQL-syntaxis error (1004). Any suggestions?

this works in Access:

SELECT tActiviteiten.IDAct, Len([tActiviteiten]![Ma])-Len(Replace([tActiviteiten]![Ma],"a","")) AS MaA
FROM tBewoners INNER JOIN tActiviteiten ON tBewoners.IDBew = tActiviteiten.IDBew
WHERE (((tActiviteiten.IDAct)="A01"));

this works in Excel:

sSQL = "SELECT tActiviteiten.IDAct, Len([tActiviteiten]![Ma]) AS MaA "
sSQL = sSQL & "FROM tBewoners INNER JOIN tActiviteiten ON tBewoners.IDBew = tActiviteiten.IDBew "
sSQL = sSQL & "WHERE tActiviteiten.IDAct=" & "'A01'" & ""

this doesn't work in Excel (SQL syntaxis error, 1004):

sSQL = "SELECT tActiviteiten.IDAct, Len([tActiviteiten]![Ma])-Len(Replace([tActiviteiten]![Ma],'a','')) AS MaA "
sSQL = sSQL & "FROM tBewoners INNER JOIN tActiviteiten ON tBewoners.IDBew = tActiviteiten.IDBew "
sSQL = sSQL & "WHERE tActiviteiten.IDAct=" & "'A01'" & ""

 

[snb]

sSQL = replace("SELECT tActiviteiten.IDAct, Len([tActiviteiten]![Ma])-Len(Replace([tActiviteiten]![Ma],#a#,##)) AS MaA","#",chr(34))

 

[jjr333]

Bedankt voor de snelle reactie! Ik kom er op terug. J.

 

[jjr333]

Snb, krijg het niet voor elkaar, Excel geeft nog steeds de syntax error 1004...

Sub GetABCBewonerDag() ''NB: RUN ONLY ONCE !
Dim sSQL As String
Dim myPad As String
Dim myDatabase As String
Dim myBew As String
Dim myTab As String

myPad = ThisWorkbook.Worksheets("Parameters").Range("cPadDatabase")
myDatabase = "TestSQL.mdb"
myBew = ThisWorkbook.Worksheets("Parameters").Range("cIDNr")
myTab = "qABCBewoner"

With ThisWorkbook.Worksheets(myTab).QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=" & myPad & "\" & myDatabase & ";DefaultDir=" & myPad & ";Driver={Driver do Microsoft Access (*.mdb)"), Array( _
"};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")), Destination:=Worksheets(myTab).Range("A1"))

sSQL = Replace("SELECT Tabel1.IDBew, Tabel1.IDAct, Len(Tabel1!Ma)-Len(Replace(Tabel1!Ma,#a#,##)) AS MaA ", "#", Chr(34))
sSQL = sSQL & "FROM Tabel1"



.CommandText = sSQL
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[snb]

Dit lijkt mij voldoende:

c0 = ThisWorkbook.Sheets("Parameters").Range("cPadDatabase") & "\TestSQL.mdb"

With ThisWorkbook.Sheets("qABCBewoner").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=" & c0 & ";", Range("A1"))
  .CommandText = Replace("SELECT Tabel1.IDBew, Tabel1.IDAct, Len(Tabel1!Ma)-Len(Replace(Tabel1!Ma,#a#,##)) AS MaA FROM Tabel1", "#", Chr(34))
  .refresh False
End With

Omdat ik niet kan beoordelen wat zich in
ThisWorkbook.Worksheets("Parameters").Range("cPadDatabase")
bevindt kan ik niet beoordelen of c0 een geldige bestandsnaam is.

 

[jjr333]

Snb, spijt me, maar ik krijg nog steeds dezelfde syntax error 1004. Op een of andere manier wordt de sql string niet geaccepteerd....

De setup hieronder wordt meerdere keren in verschillende variaties gebruikt, zonder problemen, alleen nu ik "Replace" gebruik gaat het mis.

Sub GetABCBewonerDag() ''NB: RUN ONLY ONCE !
Dim sSQL As String
Dim myPad As String
Dim myDatabase As String
Dim myTab As String

myPad = ThisWorkbook.Worksheets("Parameters").Range("cPadDatabase")
myDatabase = "TestSQL.mdb"
myTab = "qABCBewoner"

With ThisWorkbook.Worksheets(myTab).QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=" & myPad & "\" & myDatabase & ";DefaultDir=" & myPad & ";Driver={Driver do Microsoft Access (*.mdb)"), Array( _
"};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")), Destination:=Worksheets(myTab).Range("A1"))

sSQL = Replace("SELECT Tabel1.IDBew, Tabel1.IDAct, Len(Tabel1!Ma)-Len(Replace(Tabel1!Ma,#a#,##)) AS MaA ", "#", Chr(34))
sSQL = sSQL & "FROM Tabel1"

.CommandText = sSQL
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[snb]

Als je mijn code draait:

- welke waarde heeft c0 ?

- in welke regel krijg je de foutmelding ?

 

[jjr333]

Snb,

Dit werkt:

Sub GetABCBewonerDag()
Dim c0 As String
c0 = "K:\TestSQL.mdb"
With ThisWorkbook.Sheets("qABCBewoner").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=" & c0 & ";", Range("A1"))
.CommandText = Replace("SELECT Tabel1.IDBew, Tabel1.IDAct, Len(Tabel1!Ma) AS MaA FROM Tabel1", "#", Chr(34))
.Refresh False
End With
End Sub

Dit werkt niet:

Sub GetABCBewonerDag()
Dim c0 As String
c0 = "K:\TestSQL.mdb"
With ThisWorkbook.Sheets("qABCBewoner").QueryTables.Add("ODBC;DSN=MS Access-database;DBQ=" & c0 & ";", Range("A1"))
.CommandText = Replace("SELECT Tabel1.IDBew, Tabel1.IDAct, Len(Tabel1!Ma)-Len(Replace(Tabel1!Ma,#a#,##)) AS MaA FROM Tabel1", "#", Chr(34))
.Refresh False
End With
End Sub

Error 1004 Error SQL-Syntaxis, op regel .Refresh False
Of Excel kan niet overweg met "Replace" of de opmaak van de string klopt niet.... Ik kom er niet uit.

 

[snb]

Kijk eens in Access.
Maak daar eens een Query met een berekend veld. Je zult zien dat bij de mogelijkheden replace ontbreekt en als je hem gebruiikt je een foutmelding krijgt: ergo, verzin een list.
Suggestie: maak bij voorkeur een Query in Access. Ook die kun je vanuit Excel als databasequery importeren.

 

[jjr333]

De query werkt in Access.
Zie bijlage.
Bedankt voor de moeite.
J.

 

[jjr333]

"Replace" staat er tussen!
Groet,
J.