Brug af vildcard i SQL statement mellem Excel og Access
Brug af vildcard i SQL statement mellem Excel og AccessJeg kan ikke få følgende statement til at fungere i Excel 2007:
SELECT P3.EQU_ID_OPT, P1.EQU_ID, P4.EQU_ID, P2.EQU_ID, P.EQU_ID, P6.EQU_ID, P7.EQU_ID, P5.EQU_ID, P8.EQU_ID, P10.EQU_ID, P11.EQU_ID, P9.EQU_ID, P13.EQU_ID, P14.EQU_ID, P12.EQU_ID
FROM `C:\Documents and Settings\zst\My Documents\BAM\Config_BAM`.`Hent BAM`
WHERE P3.EQU_ID_OPT Like 'XX.*'
hvorimod dette virker:
SELECT P3.EQU_ID_OPT, P1.EQU_ID, P4.EQU_ID, P2.EQU_ID, P.EQU_ID, P6.EQU_ID, P7.EQU_ID, P5.EQU_ID, P8.EQU_ID, P10.EQU_ID, P11.EQU_ID, P9.EQU_ID, P13.EQU_ID, P14.EQU_ID, P12.EQU_ID
FROM `C:\Documents and Settings\zst\My Documents\BAM\Config_BAM`.`Hent BAM`
WHERE P3.EQU_ID_OPT Like 'XX.Boligsikring.Prod'
Koden ser således ud:
With ThisWorkbook.Worksheets(arknavn).QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access-database;DBQ=C:\Documents and Settings\zst\My Documents\BAM\Config_BAM.mdb;Mode=Share Deny None;DefaultDir=C:\Documents and Settings\zst\My Documents\BAM\Config_BAM.mdb" _
), Array("op;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.CommandText = "SELECT P3.EQU_ID_OPT, P1.EQU_ID, P4.EQU_ID, P2.EQU_ID, " & _
"P.EQU_ID, P6.EQU_ID, P7.EQU_ID, P5.EQU_ID, P8.EQU_ID, P10.EQU_ID, " & _
"P11.EQU_ID, P9.EQU_ID, P13.EQU_ID, P14.EQU_ID, P12.EQU_ID" _
& Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\zst\My Documents\BAM\Config_BAM`.`Hent BAM`" _
& Chr(13) & "" & Chr(10) & "WHERE P3.EQU_ID_OPT Like '" & CI & "'"
.Name = "Forespørgsel fra MS Access-database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
Debug.Print .CommandText
.Refresh BackgroundQuery:=False
End With
Håber nogen har et godt input til hvor fejlen ligger.
