1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| 1. Function Transposer(strSource As String, strTarget As String)
2.
3. Dim db As Database
4. Dim tdfNewDef As TableDef
5. Dim fldNewField As Field
6. Dim rstSource As Recordset, rstTarget As Recordset
7. Dim i As Integer, j As Integer
8.
9. On Error GoTo Transposer_Err
10.
11. Set db = CurrentDb()
12. Set rstSource = db.OpenRecordset(strSource)
13. rstSource.MoveLast
14.
15. ' Create a new table to hold the transposed data.
16. ' Create a field for each record in the original table.
17. Set tdfNewDef = db.CreateTableDef(strTarget)
18. For i = 0 To rstSource.RecordCount
19. Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
20. tdfNewDef.Fields.Append fldNewField
21. Next i
22. db.TableDefs.Append tdfNewDef
23.
24. ' Open the new table and fill the first field with
25. ' field names from the original table.
26. Set rstTarget = db.OpenRecordset(strTarget)
27. For i = 0 To rstSource.Fields.Count - 1
28. With rstTarget
29. .AddNew
30. .Fields(0) = rstSource.Fields(i).Name
31. .Update
32. End With
33. Next i
34.
35. rstSource.MoveFirst
36. rstTarget.MoveFirst
37. ' Fill each column of the new table
38. ' with a record from the original table.
39. For j = 0 To rstSource.Fields.Count - 1
40. ' Begin with the second field, because the first field
41. ' already contains the field names.
42. For i = 1 To rstTarget.Fields.Count - 1
43. With rstTarget
44. .Edit
45. .Fields(i) = rstSource.Fields(j)
46. rstSource.MoveNext
47. .Update
48. End With
49.
50. Next i
51. rstSource.MoveFirst
52. rstTarget.MoveNext
53. Next j
54.
55. db.Close
56.
57. Exit Function
58.
59. Transposer_Err:
60.
61. Select Case Err
62. Case 3010
63. MsgBox "The table " & strTarget & " already exists."
64. Case 3078
65. MsgBox "The table " & strSource & " doesn't exist."
66. Case Else
67. MsgBox CStr(Err) & " " & Err.Description
68. End Select
69.
70. Exit Function
71.
72. End Function |
Partager