Reputation: 675
Access VBA SQL string too long
I have an Excel 2003 VBA program that creates an SQL string that way:
OrdresPlanif = ""
Do While Cells(i, 1).Value <> "CODES TEMPS"
noOrdre = Cells(i, 4).Value
If noOrdre <> "" Then
OrdresPlanif = OrdresPlanif + "(Ordre.Ordre) <> " & noOrdre & " And " ' String for the orders shecduled
End If
i = i + 1
Loop
OrdresPlanif = Left(OrdresPlanif, Len(OrdresPlanif) - 5) ' Remove last "and" from string OrdresNonPlanif
SQLnonPlanif = "SELECT Ordre.Ordre, Sum(Ordre.Duree) AS SommeDeDuree" _
& " FROM Ordre" _
& " WHERE (((Ordre.Date) >= #" & Date1 & "# And (Ordre.Date) <= #" & Date2 & "#) AND (" & MatOrString & ") And (" & OrdresPlanif & "))" _
& " GROUP BY Ordre.Ordre;"
Set rsNonPlanif = cn.Execute(SQLnonPlanif)
Problem is, there are about 100 ~ 150 noOrdre and ~15 MatOrString to add in the SQL statement. The error "SQL statement to complex" comes back from Access (2003). I have looked around for solutions but people are talking about concatenations alot but it doesn't seem to be a viable solution for me since I create my string from a loop.
Is there a way to fix this problem? Or is my strategy wrong, to start with?
[EDIT]
here i add a sample query generated from my code. When I try to paste it in access it gives the same error.
SELECT Ordre.Ordre, Sum(Ordre.Duree) AS SommeDeDuree FROM Ordre WHERE (((Ordre.Date) >= #07/11/2011# And (Ordre.Date) <= #14/11/2011#) AND (Ordre.Matricule=257 OR Ordre.Matricule=516 OR Ordre.Matricule=591 OR Ordre.Matricule=1797 OR Ordre.Matricule=1802 OR Ordre.Matricule=1828 OR Ordre.Matricule=2061 OR Ordre.Matricule=2070 OR Ordre.Matricule=2085 OR Ordre.Matricule=2103 OR Ordre.Matricule=2142 OR Ordre.Matricule=2211 OR Ordre.Matricule=2344 OR Ordre.Matricule=2436 OR Ordre.Matricule=2471 ) And ((Ordre.Ordre) <> 10000477 And (Ordre.Ordre) <> 11146074 And (Ordre.Ordre) <> 11146315 And (Ordre.Ordre) <> 11146662 And (Ordre.Ordre) <> 11146689 And (Ordre.Ordre) <> 11147233 And (Ordre.Ordre) <> 11147616 And (Ordre.Ordre) <> 11148129 And (Ordre.Ordre) <> 11147923 And (Ordre.Ordre) <> 11147924 And (Ordre.Ordre) <> 11147925 And (Ordre.Ordre) <> 11147192 And (Ordre.Ordre) <> 11147206 And (Ordre.Ordre) <> 11147217 And (Ordre.Ordre) <> 11147219 And (Ordre.Ordre) <> 11147220 And (Ordre.Ordre) <> 11147221 And (Ordre.Ordre) <> 11147255 And (Ordre.Ordre) <> 11147263 And (Ordre.Ordre) <> 11147265 And (Ordre.Ordre) <> 11147292 And (Ordre.Ordre) <> 11147295 And (Ordre.Ordre) <> 11147297 And (Ordre.Ordre) <> 11147298 And (Ordre.Ordre) <> 11147299 And (Ordre.Ordre) <> 11147300 And (Ordre.Ordre) <> 11146863 And (Ordre.Ordre) <> 11146864 And (Ordre.Ordre) <> 11147252 And (Ordre.Ordre) <> 11147547 And (Ordre.Ordre) <> 11147550 And (Ordre.Ordre) <> 11147551 And (Ordre.Ordre) <> 11147556 And (Ordre.Ordre) <> 11147588 And (Ordre.Ordre) <> 11147594 And (Ordre.Ordre) <> 11147595 And (Ordre.Ordre) <> 11147610 And (Ordre.Ordre) <> 11147684 And (Ordre.Ordre) <> 11147685 And (Ordre.Ordre) <> 11147696 And (Ordre.Ordre) <> 11147697 And (Ordre.Ordre) <> 11147698 And (Ordre.Ordre) <> 11147699 And (Ordre.Ordre) <> 11147700 And (Ordre.Ordre) <> 11147702 And (Ordre.Ordre) <> 11147720 And (Ordre.Ordre) <> 11147972 And (Ordre.Ordre) <> 11147974 And (Ordre.Ordre) <> 11147975 And (Ordre.Ordre) <> 11147976 And (Ordre.Ordre) <> 11147977 And (Ordre.Ordre) <> 11147978 And (Ordre.Ordre) <> 11147979 And (Ordre.Ordre) <> 11147980 And (Ordre.Ordre) <> 11147983 And (Ordre.Ordre) <> 11147985 And (Ordre.Ordre) <> 11147990 And (Ordre.Ordre) <> 11147991 And (Ordre.Ordre) <> 12156143 And (Ordre.Ordre) <> 12156147 And (Ordre.Ordre) <> 12156151 And (Ordre.Ordre) <> 12155605 And (Ordre.Ordre) <> 12155606 And (Ordre.Ordre) <> 12155607 And (Ordre.Ordre) <> 12155608 And (Ordre.Ordre) <> 12155609 And (Ordre.Ordre) <> 12155610 And (Ordre.Ordre) <> 12155611 And (Ordre.Ordre) <> 12155616 And (Ordre.Ordre) <> 12155436 And (Ordre.Ordre) <> 12155437 And (Ordre.Ordre) <> 12155438 And (Ordre.Ordre) <> 12155764 And (Ordre.Ordre) <> 12155765 And (Ordre.Ordre) <> 12155444 And (Ordre.Ordre) <> 12155429 And (Ordre.Ordre) <> 12155430 And (Ordre.Ordre) <> 12155431 And (Ordre.Ordre) <> 12155433 And (Ordre.Ordre) <> 12155434 And (Ordre.Ordre) <> 12155435 And (Ordre.Ordre) <> 12155426 And (Ordre.Ordre) <> 12155427 And (Ordre.Ordre) <> 12155604 And (Ordre.Ordre) <> 12155420 And (Ordre.Ordre) <> 12155419 And (Ordre.Ordre) <> 12155412 And (Ordre.Ordre) <> 12155538 And (Ordre.Ordre) <> 12155539 And (Ordre.Ordre) <> 12155540 And (Ordre.Ordre) <> 12155541 And (Ordre.Ordre) <> 12155542 And (Ordre.Ordre) <> 12155543 And (Ordre.Ordre) <> 12155544 And (Ordre.Ordre) <> 12155545 And (Ordre.Ordre) <> 12155546 And (Ordre.Ordre) <> 12155547 And (Ordre.Ordre) <> 12155442 And (Ordre.Ordre) <> 12155440 And (Ordre.Ordre) <> 12155441 And (Ordre.Ordre) <> 12155443 And (Ordre.Ordre) <> 12155446 And (Ordre.Ordre) <> 12155445 And (Ordre.Ordre) <> 12155448 And (Ordre.Ordre) <> 12154726 And (Ordre.Ordre) <> 12154727 And (Ordre.Ordre) <> 12154728 And (Ordre.Ordre) <> 12154729 And (Ordre.Ordre) <> 12154730 And (Ordre.Ordre) <> 12155570 And (Ordre.Ordre) <> 12155704 And (Ordre.Ordre) <> 12155935 And (Ordre.Ordre) <> 12155936 And (Ordre.Ordre) <> 12155937 And (Ordre.Ordre) <> 12155938 And (Ordre.Ordre) <> 12155939 And (Ordre.Ordre) <> 12155940 And (Ordre.Ordre) <> 12155941 And (Ordre.Ordre) <> 12153256 And (Ordre.Ordre) <> 13101358 And (Ordre.Ordre) <> 13101309 And (Ordre.Ordre) <> 13212576)) GROUP BY Ordre.Ordre;
Upvotes: 1
Views: 3906
Answers (4)
Reputation: 57093
Rather than using dynamic SQL (i.e. concatenating the values on the fly), consider creating a PROCEDURE with a large number of strongly-typed parameters that default to NULL. Here's a quick demo....
I've written the parameter names out in long hand but can of course be done in a loop. Just copy and paste into any VBA code module, no references required, (e.g. use Excel):
Sub AccessManyParams()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE Ordre (Ordre INTEGER NOT NULL UNIQUE);"
.Execute Sql
Sql = _
"INSERT INTO Ordre VALUES (55);"
.Execute Sql
Sql = _
"INSERT INTO Ordre VALUES (22);"
.Execute Sql
Sql = vbNullString
Sql = Sql & "CREATE PROCEDURE ChercherOrdres ( "
Sql = Sql & "ordre_001 INTEGER = NULL, "
Sql = Sql & "ordre_002 INTEGER = NULL, "
Sql = Sql & "ordre_003 INTEGER = NULL, "
Sql = Sql & "ordre_004 INTEGER = NULL, "
Sql = Sql & "ordre_005 INTEGER = NULL, "
Sql = Sql & "ordre_006 INTEGER = NULL, "
Sql = Sql & "ordre_007 INTEGER = NULL, "
Sql = Sql & "ordre_008 INTEGER = NULL, "
Sql = Sql & "ordre_009 INTEGER = NULL, "
Sql = Sql & "ordre_010 INTEGER = NULL, "
Sql = Sql & "ordre_011 INTEGER = NULL, "
Sql = Sql & "ordre_012 INTEGER = NULL, "
Sql = Sql & "ordre_013 INTEGER = NULL, "
Sql = Sql & "ordre_014 INTEGER = NULL, "
Sql = Sql & "ordre_015 INTEGER = NULL, "
Sql = Sql & "ordre_016 INTEGER = NULL, "
Sql = Sql & "ordre_017 INTEGER = NULL, "
Sql = Sql & "ordre_018 INTEGER = NULL, "
Sql = Sql & "ordre_019 INTEGER = NULL, "
Sql = Sql & "ordre_020 INTEGER = NULL, "
Sql = Sql & "ordre_021 INTEGER = NULL, "
Sql = Sql & "ordre_022 INTEGER = NULL, "
Sql = Sql & "ordre_023 INTEGER = NULL, "
Sql = Sql & "ordre_024 INTEGER = NULL, "
Sql = Sql & "ordre_025 INTEGER = NULL, "
Sql = Sql & "ordre_026 INTEGER = NULL, "
Sql = Sql & "ordre_027 INTEGER = NULL, "
Sql = Sql & "ordre_028 INTEGER = NULL, "
Sql = Sql & "ordre_029 INTEGER = NULL, "
Sql = Sql & "ordre_030 INTEGER = NULL, "
Sql = Sql & "ordre_031 INTEGER = NULL, "
Sql = Sql & "ordre_032 INTEGER = NULL, "
Sql = Sql & "ordre_033 INTEGER = NULL, "
Sql = Sql & "ordre_034 INTEGER = NULL, "
Sql = Sql & "ordre_035 INTEGER = NULL, "
Sql = Sql & "ordre_036 INTEGER = NULL, "
Sql = Sql & "ordre_037 INTEGER = NULL, "
Sql = Sql & "ordre_038 INTEGER = NULL, "
Sql = Sql & "ordre_039 INTEGER = NULL, "
Sql = Sql & "ordre_040 INTEGER = NULL, "
Sql = Sql & "ordre_041 INTEGER = NULL, "
Sql = Sql & "ordre_042 INTEGER = NULL, "
Sql = Sql & "ordre_043 INTEGER = NULL, "
Sql = Sql & "ordre_044 INTEGER = NULL, "
Sql = Sql & "ordre_045 INTEGER = NULL, "
Sql = Sql & "ordre_046 INTEGER = NULL, "
Sql = Sql & "ordre_047 INTEGER = NULL, "
Sql = Sql & "ordre_048 INTEGER = NULL, "
Sql = Sql & "ordre_049 INTEGER = NULL, "
Sql = Sql & "ordre_050 INTEGER = NULL, "
Sql = Sql & "ordre_051 INTEGER = NULL, "
Sql = Sql & "ordre_052 INTEGER = NULL, "
Sql = Sql & "ordre_053 INTEGER = NULL, "
Sql = Sql & "ordre_054 INTEGER = NULL, "
Sql = Sql & "ordre_055 INTEGER = NULL, "
Sql = Sql & "ordre_056 INTEGER = NULL, "
Sql = Sql & "ordre_057 INTEGER = NULL, "
Sql = Sql & "ordre_058 INTEGER = NULL, "
Sql = Sql & "ordre_059 INTEGER = NULL, "
Sql = Sql & "ordre_060 INTEGER = NULL, "
Sql = Sql & "ordre_061 INTEGER = NULL, "
Sql = Sql & "ordre_062 INTEGER = NULL, "
Sql = Sql & "ordre_063 INTEGER = NULL, "
Sql = Sql & "ordre_064 INTEGER = NULL, "
Sql = Sql & "ordre_065 INTEGER = NULL, "
Sql = Sql & "ordre_066 INTEGER = NULL, "
Sql = Sql & "ordre_067 INTEGER = NULL, "
Sql = Sql & "ordre_068 INTEGER = NULL, "
Sql = Sql & "ordre_069 INTEGER = NULL, "
Sql = Sql & "ordre_070 INTEGER = NULL, "
Sql = Sql & "ordre_071 INTEGER = NULL, "
Sql = Sql & "ordre_072 INTEGER = NULL, "
Sql = Sql & "ordre_073 INTEGER = NULL, "
Sql = Sql & "ordre_074 INTEGER = NULL, "
Sql = Sql & "ordre_075 INTEGER = NULL, "
Sql = Sql & "ordre_076 INTEGER = NULL, "
Sql = Sql & "ordre_077 INTEGER = NULL, "
Sql = Sql & "ordre_078 INTEGER = NULL, "
Sql = Sql & "ordre_079 INTEGER = NULL, "
Sql = Sql & "ordre_080 INTEGER = NULL, "
Sql = Sql & "ordre_081 INTEGER = NULL, "
Sql = Sql & "ordre_082 INTEGER = NULL, "
Sql = Sql & "ordre_083 INTEGER = NULL, "
Sql = Sql & "ordre_084 INTEGER = NULL, "
Sql = Sql & "ordre_085 INTEGER = NULL, "
Sql = Sql & "ordre_086 INTEGER = NULL, "
Sql = Sql & "ordre_087 INTEGER = NULL, "
Sql = Sql & "ordre_088 INTEGER = NULL, "
Sql = Sql & "ordre_089 INTEGER = NULL, "
Sql = Sql & "ordre_090 INTEGER = NULL, "
Sql = Sql & "ordre_091 INTEGER = NULL, "
Sql = Sql & "ordre_092 INTEGER = NULL, "
Sql = Sql & "ordre_093 INTEGER = NULL, "
Sql = Sql & "ordre_094 INTEGER = NULL, "
Sql = Sql & "ordre_095 INTEGER = NULL, "
Sql = Sql & "ordre_096 INTEGER = NULL, "
Sql = Sql & "ordre_097 INTEGER = NULL, "
Sql = Sql & "ordre_098 INTEGER = NULL, "
Sql = Sql & "ordre_099 INTEGER = NULL, "
Sql = Sql & "ordre_100 INTEGER = NULL, "
Sql = Sql & "ordre_101 INTEGER = NULL, "
Sql = Sql & "ordre_102 INTEGER = NULL, "
Sql = Sql & "ordre_103 INTEGER = NULL, "
Sql = Sql & "ordre_104 INTEGER = NULL, "
Sql = Sql & "ordre_105 INTEGER = NULL, "
Sql = Sql & "ordre_106 INTEGER = NULL, "
Sql = Sql & "ordre_107 INTEGER = NULL, "
Sql = Sql & "ordre_108 INTEGER = NULL, "
Sql = Sql & "ordre_109 INTEGER = NULL, "
Sql = Sql & "ordre_110 INTEGER = NULL, "
Sql = Sql & "ordre_111 INTEGER = NULL, "
Sql = Sql & "ordre_112 INTEGER = NULL, "
Sql = Sql & "ordre_113 INTEGER = NULL, "
Sql = Sql & "ordre_114 INTEGER = NULL, "
Sql = Sql & "ordre_115 INTEGER = NULL, "
Sql = Sql & "ordre_116 INTEGER = NULL, "
Sql = Sql & "ordre_117 INTEGER = NULL, "
Sql = Sql & "ordre_118 INTEGER = NULL, "
Sql = Sql & "ordre_119 INTEGER = NULL, "
Sql = Sql & "ordre_120 INTEGER = NULL, "
Sql = Sql & "ordre_121 INTEGER = NULL, "
Sql = Sql & "ordre_122 INTEGER = NULL, "
Sql = Sql & "ordre_123 INTEGER = NULL, "
Sql = Sql & "ordre_124 INTEGER = NULL, "
Sql = Sql & "ordre_125 INTEGER = NULL, "
Sql = Sql & "ordre_126 INTEGER = NULL, "
Sql = Sql & "ordre_127 INTEGER = NULL, "
Sql = Sql & "ordre_128 INTEGER = NULL, "
Sql = Sql & "ordre_129 INTEGER = NULL, "
Sql = Sql & "ordre_130 INTEGER = NULL, "
Sql = Sql & "ordre_131 INTEGER = NULL, "
Sql = Sql & "ordre_132 INTEGER = NULL, "
Sql = Sql & "ordre_133 INTEGER = NULL, "
Sql = Sql & "ordre_134 INTEGER = NULL, "
Sql = Sql & "ordre_135 INTEGER = NULL, "
Sql = Sql & "ordre_136 INTEGER = NULL, "
Sql = Sql & "ordre_137 INTEGER = NULL, "
Sql = Sql & "ordre_138 INTEGER = NULL, "
Sql = Sql & "ordre_139 INTEGER = NULL, "
Sql = Sql & "ordre_140 INTEGER = NULL, "
Sql = Sql & "ordre_141 INTEGER = NULL, "
Sql = Sql & "ordre_142 INTEGER = NULL, "
Sql = Sql & "ordre_143 INTEGER = NULL, "
Sql = Sql & "ordre_144 INTEGER = NULL, "
Sql = Sql & "ordre_145 INTEGER = NULL, "
Sql = Sql & "ordre_146 INTEGER = NULL, "
Sql = Sql & "ordre_147 INTEGER = NULL, "
Sql = Sql & "ordre_148 INTEGER = NULL, "
Sql = Sql & "ordre_149 INTEGER = NULL, "
Sql = Sql & "ordre_150 INTEGER = NULL) AS "
Sql = Sql & "SELECT * FROM Ordre WHERE Ordre NOT IN ("
Sql = Sql & "ordre_001, "
Sql = Sql & "ordre_002, "
Sql = Sql & "ordre_003, "
Sql = Sql & "ordre_004, "
Sql = Sql & "ordre_005, "
Sql = Sql & "ordre_006, "
Sql = Sql & "ordre_007, "
Sql = Sql & "ordre_008, "
Sql = Sql & "ordre_009, "
Sql = Sql & "ordre_010, "
Sql = Sql & "ordre_011, "
Sql = Sql & "ordre_012, "
Sql = Sql & "ordre_013, "
Sql = Sql & "ordre_014, "
Sql = Sql & "ordre_015, "
Sql = Sql & "ordre_016, "
Sql = Sql & "ordre_017, "
Sql = Sql & "ordre_018, "
Sql = Sql & "ordre_019, "
Sql = Sql & "ordre_020, "
Sql = Sql & "ordre_021, "
Sql = Sql & "ordre_022, "
Sql = Sql & "ordre_023, "
Sql = Sql & "ordre_024, "
Sql = Sql & "ordre_025, "
Sql = Sql & "ordre_026, "
Sql = Sql & "ordre_027, "
Sql = Sql & "ordre_028, "
Sql = Sql & "ordre_029, "
Sql = Sql & "ordre_030, "
Sql = Sql & "ordre_031, "
Sql = Sql & "ordre_032, "
Sql = Sql & "ordre_033, "
Sql = Sql & "ordre_034, "
Sql = Sql & "ordre_035, "
Sql = Sql & "ordre_036, "
Sql = Sql & "ordre_037, "
Sql = Sql & "ordre_038, "
Sql = Sql & "ordre_039, "
Sql = Sql & "ordre_040, "
Sql = Sql & "ordre_041, "
Sql = Sql & "ordre_042, "
Sql = Sql & "ordre_043, "
Sql = Sql & "ordre_044, "
Sql = Sql & "ordre_045, "
Sql = Sql & "ordre_046, "
Sql = Sql & "ordre_047, "
Sql = Sql & "ordre_048, "
Sql = Sql & "ordre_049, "
Sql = Sql & "ordre_050, "
Sql = Sql & "ordre_051, "
Sql = Sql & "ordre_052, "
Sql = Sql & "ordre_053, "
Sql = Sql & "ordre_054, "
Sql = Sql & "ordre_055, "
Sql = Sql & "ordre_056, "
Sql = Sql & "ordre_057, "
Sql = Sql & "ordre_058, "
Sql = Sql & "ordre_059, "
Sql = Sql & "ordre_060, "
Sql = Sql & "ordre_061, "
Sql = Sql & "ordre_062, "
Sql = Sql & "ordre_063, "
Sql = Sql & "ordre_064, "
Sql = Sql & "ordre_065, "
Sql = Sql & "ordre_066, "
Sql = Sql & "ordre_067, "
Sql = Sql & "ordre_068, "
Sql = Sql & "ordre_069, "
Sql = Sql & "ordre_070, "
Sql = Sql & "ordre_071, "
Sql = Sql & "ordre_072, "
Sql = Sql & "ordre_073, "
Sql = Sql & "ordre_074, "
Sql = Sql & "ordre_075, "
Sql = Sql & "ordre_076, "
Sql = Sql & "ordre_077, "
Sql = Sql & "ordre_078, "
Sql = Sql & "ordre_079, "
Sql = Sql & "ordre_080, "
Sql = Sql & "ordre_081, "
Sql = Sql & "ordre_082, "
Sql = Sql & "ordre_083, "
Sql = Sql & "ordre_084, "
Sql = Sql & "ordre_085, "
Sql = Sql & "ordre_086, "
Sql = Sql & "ordre_087, "
Sql = Sql & "ordre_088, "
Sql = Sql & "ordre_089, "
Sql = Sql & "ordre_090, "
Sql = Sql & "ordre_091, "
Sql = Sql & "ordre_092, "
Sql = Sql & "ordre_093, "
Sql = Sql & "ordre_094, "
Sql = Sql & "ordre_095, "
Sql = Sql & "ordre_096, "
Sql = Sql & "ordre_097, "
Sql = Sql & "ordre_098, "
Sql = Sql & "ordre_099, "
Sql = Sql & "ordre_100, "
Sql = Sql & "ordre_101, "
Sql = Sql & "ordre_102, "
Sql = Sql & "ordre_103, "
Sql = Sql & "ordre_104, "
Sql = Sql & "ordre_105, "
Sql = Sql & "ordre_106, "
Sql = Sql & "ordre_107, "
Sql = Sql & "ordre_108, "
Sql = Sql & "ordre_109, "
Sql = Sql & "ordre_110, "
Sql = Sql & "ordre_111, "
Sql = Sql & "ordre_112, "
Sql = Sql & "ordre_113, "
Sql = Sql & "ordre_114, "
Sql = Sql & "ordre_115, "
Sql = Sql & "ordre_116, "
Sql = Sql & "ordre_117, "
Sql = Sql & "ordre_118, "
Sql = Sql & "ordre_119, "
Sql = Sql & "ordre_120, "
Sql = Sql & "ordre_121, "
Sql = Sql & "ordre_122, "
Sql = Sql & "ordre_123, "
Sql = Sql & "ordre_124, "
Sql = Sql & "ordre_125, "
Sql = Sql & "ordre_126, "
Sql = Sql & "ordre_127, "
Sql = Sql & "ordre_128, "
Sql = Sql & "ordre_129, "
Sql = Sql & "ordre_130, "
Sql = Sql & "ordre_131, "
Sql = Sql & "ordre_132, "
Sql = Sql & "ordre_133, "
Sql = Sql & "ordre_134, "
Sql = Sql & "ordre_135, "
Sql = Sql & "ordre_136, "
Sql = Sql & "ordre_137, "
Sql = Sql & "ordre_138, "
Sql = Sql & "ordre_139, "
Sql = Sql & "ordre_140, "
Sql = Sql & "ordre_141, "
Sql = Sql & "ordre_142, "
Sql = Sql & "ordre_143, "
Sql = Sql & "ordre_144, "
Sql = Sql & "ordre_145, "
Sql = Sql & "ordre_146, "
Sql = Sql & "ordre_147, "
Sql = Sql & "ordre_148, "
Sql = Sql & "ordre_149, "
Sql = Sql & "ordre_150);"
.Execute Sql
End With
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = .ActiveConnection
With cmd
.CommandType = adCmdStoredProc
.CommandText = "ChercherOrdres"
.Parameters.Append .CreateParameter("ordre_091", adInteger, , , "22")
.Parameters.Append .CreateParameter("ordre_147", adInteger, , , "-99")
Dim rs
Set rs = .Execute
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub
Upvotes: 0
Reputation: 91376
It is by no means impossible to refer to both an Excel sheet and an Access database in the same SQL statement.
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim strExcel As String
Dim s As String
Dim i As Integer, j As Integer
strFile = "Z:\docs\LTD.mdb"
''Access and Excel 2007 / 2010
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strExcel = "[Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=Z:\Docs\Book1.xlsm]"
strSQL = "SELECT * " _
& "FROM New a " _
& "INNER JOIN " & strExcel & ".[Sheet1$] b " _
& "ON a.ID = b.ID"
rs.Open strSQL, cn, 3, 3
''Pick a suitable empty worksheet for the results
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Upvotes: 0
Reputation: 43076
Although mwolfe02's solution is correct, it does not scale well (like the original problematic SQL). The NOT IN expression simply allows you to fit more values into your SQL string before you hit the length limit.
It would be more scalable, though more cumbersome, to put the values into a temporary table. This would also allow you to use an outer join with a filter for null values, which would probably run more quickly because the NOT IN approach is inefficient. Example:
SELECT Ordre.Ordre, Sum(Ordre.Duree) AS SommeDeDuree
FROM Ordre LEFT JOIN TemporaryTableName
ON Ordre.Ordre = TemporaryTableName.Ordre
WHERE Ordre.Date >= #07/11/2011#
AND Ordre.Date <= #14/11/2011#
AND Ordre.Matricule In (257,516,591,...,2436,2471)
AND TemporaryTableName.Ordre IS NULL
GROUP BY Ordre.Ordre;
Upvotes: 2
Reputation: 24237
Instead of all of those ANDs and ORs you should use IN statements. For example,
SELECT Ordre.Ordre, Sum(Ordre.Duree) AS SommeDeDuree
FROM Ordre
WHERE Ordre.Date >= #07/11/2011#
AND Ordre.Date <= #14/11/2011#
AND Ordre.Matricule In (257,516,591,...,2436,2471)
AND Ordre.Ordre Not In (10000477, 11146074, ... ,13101309,13212576)
GROUP BY Ordre.Ordre;
Upvotes: 2
Related Questions
- Getting error for too few parameters in SQL string in VBA
- VBA Excel SQL Query limits fields to 255 characters
- SQL too long for String
- Access VBA SQL String Syntax eror
- Access VBA: Cannot combine variable and string in SQL query
- VBA SQL String Run-Time Error 3075
- Can't run query in Access via vba with a long definition
- VBA SQL query strings, how much is supported?
- Query is too long
- VBA string length problem