Jonesmann
06-27-2008, 10:46 AM
Hi,
i have a SQL Query that is used multible times by a for loop, like this:
For Each StartYear In YearAr
sqlstring = "SELECT `" & CStr(StartYear ) & "`.Jan, `" & CStr(StartYear ) & "`.Feb FROM `C:\Users\sanne\Desktop\AM\as_ss_08_V2`.`" & CStr(StartYear ) & "` `" & CStr(StartYear ) & "` WHERE (`" & CStr(StartJahr) & "`.ID=" & CStr(FirmID) & ")"
conntring = "ODBC;DSN=Microsoft Access-Datenbank;DBQ=C:\Users\sanne\Desktop\AM\as_ss_08_V2.mdb;DefaultDir=C:\Users \sanne\Desktop\AM;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
With ActiveSheet.QueryTables.Add(Connection:=conntring, Destination:=Range("I16"), Sql:=sqlstring)
.Name = "Query from Microsoft Access-Datenbank"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False
End With
Next
I get the following Error:
Runtimer Error:1004 - General ODBC Error
At the line: .Refresh BackgroundQuery = False
Whats the Problem with the QUery???
i have a SQL Query that is used multible times by a for loop, like this:
For Each StartYear In YearAr
sqlstring = "SELECT `" & CStr(StartYear ) & "`.Jan, `" & CStr(StartYear ) & "`.Feb FROM `C:\Users\sanne\Desktop\AM\as_ss_08_V2`.`" & CStr(StartYear ) & "` `" & CStr(StartYear ) & "` WHERE (`" & CStr(StartJahr) & "`.ID=" & CStr(FirmID) & ")"
conntring = "ODBC;DSN=Microsoft Access-Datenbank;DBQ=C:\Users\sanne\Desktop\AM\as_ss_08_V2.mdb;DefaultDir=C:\Users \sanne\Desktop\AM;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
With ActiveSheet.QueryTables.Add(Connection:=conntring, Destination:=Range("I16"), Sql:=sqlstring)
.Name = "Query from Microsoft Access-Datenbank"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False
End With
Next
I get the following Error:
Runtimer Error:1004 - General ODBC Error
At the line: .Refresh BackgroundQuery = False
Whats the Problem with the QUery???