· 8 years ago · Apr 13, 2018, 03:38 AM
1Sub Discon()
2
3 Const USE_MDB As Long = 1
4 Const USE_ACCDB As Long = 2
5
6 Dim version As Long
7
8 ' Hard code which version of the
9 ' Access Database Engine to use
10
11 version = USE_MDB
12
13 Dim fullFileName As String
14 Dim conString As String
15
16 If version = USE_MDB Then
17
18 fullFileName = Environ$("temp") & "DropMe.mdb"
19 conString = _
20 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
21 "Data Source=" & fullFileName
22
23 Else
24
25 fullFileName = Environ$("temp") & "DropMe.accdb"
26 conString = _
27 "Provider=Microsoft.ACE.OLEDB.12.0;" & _
28 "Data Source=" & fullFileName
29
30 End If
31
32 On Error Resume Next
33 Kill fullFileName
34 On Error GoTo 0
35
36 Dim cat
37 Set cat = CreateObject("ADOX.Catalog")
38 With cat
39
40 ' Create a new database file in user's temp folder
41 .Create conString
42
43 With .ActiveConnection
44
45 Dim Sql As String
46
47 ' Create a new base base with data (required to
48 ' be able to later create a virtual table)
49
50 Sql = _
51 "CREATE TABLE Customers (" & _
52 "CustomerID CHAR(5) NOT NULL UNIQUE);"
53 .Execute Sql
54
55 Sql = _
56 "INSERT INTO Customers (CustomerID)" & _
57 " VALUES ('ANTON');"
58 .Execute Sql
59
60 ' Create a virtual (viewed) table
61 Sql = _
62 "CREATE VIEW TableA AS " & _
63 "SELECT DT1.ID, DT1.[Date], DT1.Supplier_ID " & _
64 "FROM ( " & _
65 " SELECT DISTINCT 1 AS ID, '2009-10-23 00:00:00' AS [Date], " & _
66 " 1 AS Supplier_ID FROM Customers " & _
67 " UNION ALL " & _
68 " SELECT DISTINCT 2, '2009-10-23 00:00:00', 1 FROM Customers" & _
69 " UNION ALL " & _
70 " SELECT DISTINCT 3, '2009-10-24 00:00:00', 2 FROM Customers " & _
71 " UNION ALL " & _
72 " SELECT DISTINCT 4, '2009-10-25 00:00:00', 2 FROM Customers " & _
73 " UNION ALL " & _
74 " SELECT DISTINCT 5, '2009-10-26 00:00:00', 1 FROM Customers " & _
75 " ) AS DT1;"
76 .Execute Sql
77
78 ' Create VIEWs based on the virtual table
79
80 Sql = _
81 "CREATE VIEW TableA_StartDates (Supplier_ID, start_date) " & _
82 "AS " & _
83 "SELECT T1.Supplier_ID, T1.[Date] " & _
84 " FROM TableA AS T1 " & _
85 " WHERE NOT EXISTS ( " & _
86 " SELECT * " & _
87 " FROM TableA AS T2 " & _
88 " WHERE T2.Supplier_ID = T1.Supplier_ID " & _
89 " AND DATEADD('D', -1, T1.[Date]) = T2.[Date] " & _
90 " );"
91 .Execute Sql
92
93 Sql = _
94 "CREATE VIEW TableA_EndDates (Supplier_ID, end_date) " & _
95 "AS " & _
96 "SELECT T3.Supplier_ID, T3.[Date] " & _
97 " FROM TableA AS T3 " & _
98 " WHERE NOT EXISTS ( " & _
99 " SELECT * " & _
100 " FROM TableA AS T4 " & _
101 " WHERE T4.Supplier_ID = T3.Supplier_ID " & _
102 " AND DATEADD('D', 1, T3.[Date]) = T4.[Date] " & _
103 " );"
104 .Execute Sql
105
106 Sql = _
107 "CREATE VIEW TableA_Periods (Supplier_ID, start_date, end_date) " & _
108 "AS " & _
109 "SELECT DISTINCT T5.Supplier_ID, " & _
110 " ( " & _
111 " SELECT MAX(S1.start_date) " & _
112 " FROM TableA_StartDates AS S1 " & _
113 " WHERE S1.Supplier_ID = T5.Supplier_ID " & _
114 " AND S1.start_date <= T5.[Date] " & _
115 " ), " & _
116 " ( " & _
117 " SELECT MIN(E1.end_date) " & _
118 " FROM TableA_EndDates AS E1 " & _
119 " WHERE E1.Supplier_ID = T5.Supplier_ID " & _
120 " AND T5.[Date] <= E1.end_date " & _
121 " ) " & _
122 " FROM TableA AS T5;"
123 .Execute Sql
124
125 ' Attempt to use the nested VIEWs in a query
126
127 Sql = _
128 "SELECT * FROM TableA_Periods AS P1;"
129
130 Dim rs
131
132 On Error Resume Next
133 Set rs = .Execute(Sql)
134
135 If Err.Number = 0 Then
136 MsgBox rs.GetString
137 Else
138 MsgBox _
139 Err.Number & ": " & _
140 Err.Description & _
141 " (" & Err.Source & ")"
142 End If
143
144 On Error GoTo 0
145
146 End With
147 Set .ActiveConnection = Nothing
148 End With
149End Sub
150
151--- temp query ---
152
153- Inputs to Query -
154- End inputs to Query -
155
15601) Insert into 'Customers'
157
158
159
160--- temp query ---
161
162- Inputs to Query -
163- End inputs to Query -
164
16501) Insert into 'Customers'
166
167
168
169--- temp query ---
170
171- Inputs to Query -
172Table 'Customers'
173Table 'Customers'
174Table 'Customers'
175Table 'Customers'
176Table 'Customers'
177- End inputs to Query -
178
179 store result in temporary table
180 store result in temporary table
18101) Union result of '00)' and result of '00)'
182 store result in temporary table
18302) Union result of '01)' and result of '01)'
184 store result in temporary table
18503) Union result of '02)' and result of '02)'
186 store result in temporary table
18704) Union result of '03)' and result of '03)'
188
189
190
191--- temp query ---
192
193- Inputs to Query -
194Table 'Customers'
195Table 'Customers'
196Table 'Customers'
197Table 'Customers'
198Table 'Customers'
199Table 'Customers'
200Table 'Customers'
201Table 'Customers'
202Table 'Customers'
203Table 'Customers'
204- End inputs to Query -
205
206 store result in temporary table
207 store result in temporary table
20801) Union result of '00)' and result of '00)'
209 store result in temporary table
21002) Union result of '01)' and result of '01)'
211 store result in temporary table
21203) Union result of '02)' and result of '02)'
213 store result in temporary table
21404) Union result of '03)' and result of '03)'
21505) Restrict rows of result of 04)
216 by scanning
217 testing expression "Not "
218
219
220
221--- temp query ---
222
223- Inputs to Query -
224Table 'Customers'
225Table 'Customers'
226Table 'Customers'
227Table 'Customers'
228Table 'Customers'
229Table 'Customers'
230Table 'Customers'
231Table 'Customers'
232Table 'Customers'
233Table 'Customers'
234- End inputs to Query -
235
236 store result in temporary table
237 store result in temporary table
23801) Union result of '00)' and result of '00)'
239 store result in temporary table
24002) Union result of '01)' and result of '01)'
241 store result in temporary table
24203) Union result of '02)' and result of '02)'
243 store result in temporary table
24404) Union result of '03)' and result of '03)'
24505) Restrict rows of result of 04)
246 by scanning
247 testing expression "Not "
248
249
250
251--- temp query ---
252
253- Inputs to Query -
254Table 'Customers'
255Table 'Customers'
256Table 'Customers'
257Table 'Customers'
258Table 'Customers'
259Table 'Customers'
260Table 'Customers'
261Table 'Customers'
262Table 'Customers'
263Table 'Customers'
264- End inputs to Query -
265
266 store result in temporary table
267 store result in temporary table
26801) Union result of '00)' and result of '00)'
269 store result in temporary table
27002) Union result of '01)' and result of '01)'
271 store result in temporary table
27203) Union result of '02)' and result of '02)'
273 store result in temporary table
27404) Union result of '03)' and result of '03)'
27505) Restrict rows of result of 04)
276 by scanning
277 testing expression "Not "
278
279
280
281--- temp query ---
282
283- Inputs to Query -
284Table 'Customers'
285Table 'Customers'
286Table 'Customers'
287Table 'Customers'
288Table 'Customers'
289Table 'Customers'
290Table 'Customers'
291Table 'Customers'
292Table 'Customers'
293Table 'Customers'
294- End inputs to Query -
295
296 store result in temporary table
297 store result in temporary table
29801) Union result of '00)' and result of '00)'
299 store result in temporary table
30002) Union result of '01)' and result of '01)'
301 store result in temporary table
30203) Union result of '02)' and result of '02)'
303 store result in temporary table
30404) Union result of '03)' and result of '03)'
30505) Restrict rows of result of 04)
306 by scanning
307 testing expression "Not "
308
309
310
311--- temp query ---
312
313- Inputs to Query -
314Table 'Customers'
315Table 'Customers'
316Table 'Customers'
317Table 'Customers'
318Table 'Customers'
319Table 'Customers'
320Table 'Customers'
321Table 'Customers'
322Table 'Customers'
323Table 'Customers'
324Table 'Customers'
325Table 'Customers'
326Table 'Customers'
327Table 'Customers'
328Table 'Customers'
329Table 'Customers'
330Table 'Customers'
331Table 'Customers'
332Table 'Customers'
333Table 'Customers'
334Table 'Customers'
335Table 'Customers'
336Table 'Customers'
337Table 'Customers'
338Table 'Customers'
339- End inputs to Query -
340
341 store result in temporary table
342 store result in temporary table
34301) Union result of '00)' and result of '00)'
344 store result in temporary table
34502) Union result of '01)' and result of '01)'
346 store result in temporary table
34703) Union result of '02)' and result of '02)'
348 store result in temporary table
34904) Union result of '03)' and result of '03)'
350 store result in temporary table
351
352
353
354--- temp query ---
355
356- Inputs to Query -
357Table 'Customers'
358Table 'Customers'
359Table 'Customers'
360Table 'Customers'
361Table 'Customers'
362Table 'Customers'
363Table 'Customers'
364Table 'Customers'
365Table 'Customers'
366Table 'Customers'
367Table 'Customers'
368Table 'Customers'
369Table 'Customers'
370Table 'Customers'
371Table 'Customers'
372Table 'Customers'
373Table 'Customers'
374Table 'Customers'
375Table 'Customers'
376Table 'Customers'
377Table 'Customers'
378Table 'Customers'
379Table 'Customers'
380Table 'Customers'
381Table 'Customers'
382- End inputs to Query -
383
384 store result in temporary table
385 store result in temporary table
38601) Union result of '00)' and result of '00)'
387 store result in temporary table
38802) Union result of '01)' and result of '01)'
389 store result in temporary table
39003) Union result of '02)' and result of '02)'
391 store result in temporary table
39204) Union result of '03)' and result of '03)'
393 store result in temporary table
394
395
396
397--- temp query ---
398
399- Inputs to Query -
400Table 'Customers'
401Table 'Customers'
402Table 'Customers'
403Table 'Customers'
404Table 'Customers'
405Table 'Customers'
406Table 'Customers'
407Table 'Customers'
408Table 'Customers'
409Table 'Customers'
410Table 'Customers'
411Table 'Customers'
412Table 'Customers'
413Table 'Customers'
414Table 'Customers'
415Table 'Customers'
416Table 'Customers'
417Table 'Customers'
418Table 'Customers'
419Table 'Customers'
420Table 'Customers'
421Table 'Customers'
422Table 'Customers'
423Table 'Customers'
424Table 'Customers'
425- End inputs to Query -
426
427 store result in temporary table
428 store result in temporary table
42901) Union result of '00)' and result of '00)'
430 store result in temporary table
43102) Union result of '01)' and result of '01)'
432 store result in temporary table
43303) Union result of '02)' and result of '02)'
434 store result in temporary table
43504) Union result of '03)' and result of '03)'
436 store result in temporary table
437
438
439
440--- temp query ---
441
442- Inputs to Query -
443Table 'Customers'
444Table 'Customers'
445Table 'Customers'
446Table 'Customers'
447Table 'Customers'
448Table 'Customers'
449Table 'Customers'
450Table 'Customers'
451Table 'Customers'
452Table 'Customers'
453Table 'Customers'
454Table 'Customers'
455Table 'Customers'
456Table 'Customers'
457Table 'Customers'
458Table 'Customers'
459Table 'Customers'
460Table 'Customers'
461Table 'Customers'
462Table 'Customers'
463Table 'Customers'
464Table 'Customers'
465Table 'Customers'
466Table 'Customers'
467Table 'Customers'
468- End inputs to Query -
469
470 store result in temporary table
471 store result in temporary table
47201) Union result of '00)' and result of '00)'
473 store result in temporary table
47402) Union result of '01)' and result of '01)'
475 store result in temporary table
47603) Union result of '02)' and result of '02)'
477 store result in temporary table
47804) Union result of '03)' and result of '03)'
479 store result in temporary table
480
481SELECT 1 AS ID, '2009-10-23 00:00:00' AS aDate, 1 AS Supplier_ID
482
483SELECT 1 AS ID, '2009-10-23 00:00:00' AS aDate, 1 AS Supplier_ID
484UNION SELECT 2 AS ID, '2009-10-23 00:00:00' AS aDate, 1 AS Supplier_ID;
485
486SELECT
487 DT1.ID
488 , DT1.aDate
489 , DT1.Supplier_ID
490FROM (
491 SELECT
492 1 AS ID
493 , '2009-10-23 00:00:00' AS aDate
494 , 1 AS Supplier_ID
495 FROM Customers
496 WHERE CustomerID='ANTON'
497 UNION ALL
498 SELECT
499 2 AS ID
500 , '2009-10-23 00:00:00' AS aDate
501 , 1 AS Supplier_ID
502 FROM Customers
503 WHERE CustomerID='ANTON'
504 UNION ALL
505 SELECT
506 3 AS ID
507 , '2009-10-24 00:00:00' AS aDate
508 , 2 AS Supplier_ID
509 FROM Customers
510 WHERE CustomerID='ANTON'
511 UNION ALL
512 SELECT
513 4 AS ID
514 , '2009-10-25 00:00:00' AS aDate
515 , 2 AS Supplier_ID
516 FROM Customers
517 WHERE CustomerID='ANTON'
518 UNION ALL
519 SELECT
520 5 AS ID
521 , '2009-10-26 00:00:00' AS aDate
522 , 1 AS Supplier_ID
523 FROM Customers
524 WHERE CustomerID='ANTON'
525) AS DT1;