Temat: Odpiwotowywanie danych
Wersja pod accessa :D.
A co:P
ACC_SPLIT_TABLE "tabela_źródlo", "tabela_wuynikowa", Array("pola_ktore_zostaja_w_kolumnach"), Array("pola_do_odpiwotowania"),
Jeżeli nie podamy Array("pola_do_odpiwotowania") to program weźmie wszystkie z wyjątkiem Array("pola_ktore_zostaja_w_kolumnach"),
Function ACC_SPLIT_TABLE(source As String, target As String, con, fields) As String
Dim db As DAO.Database
Dim rs As DAO.recordset
Dim tdfNew As DAO.TableDef
Dim rsf
Dim i As Integer
Dim size As Long
Dim field As String
Dim sel As Boolean
Dim sel_f As Boolean
Dim field_c As String
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Set db = CurrentDb
Set rs = db.OpenRecordset(source, , dbReadOnly)
On Error Resume Next
db.TableDefs.Delete target
On Error GoTo 0
Set tdfNew = db.CreateTableDef(target)
'FIELDS
For i = LBound(con) To UBound(con)
On Error GoTo Err_h
field = con(i)
size = rs.fields(field).size
On Error GoTo 0
field_c = field_c & "[" & field & "], "
With tdfNew
.fields.Append .CreateField(field, dbText, size)
.fields(.fields.Count - 1).AllowZeroLength = True
End With
Next i
'FIELD
With tdfNew
.fields.Append .CreateField("FIELD", dbText, 10)
.fields(.fields.Count - 1).AllowZeroLength = True
End With
'VALUE
With tdfNew
.fields.Append .CreateField("VALUE", dbText, 100)
.fields(.fields.Count - 1).AllowZeroLength = True
End With
db.TableDefs.Append tdfNew
For Each rsf In rs.fields
If rsf.name <> "ID" Then
sel = False
sel_f = False
For i = LBound(con) To UBound(con)
If rsf.name = con(i) Then
sel = True
Exit For
End If
Next i
If IsArray(fields) Then
For i = LBound(fields) To UBound(fields)
If rsf.name = fields(i) Then
sel_f = True
Exit For
End If
Next i
Else
sel_f = True
End If
If sel = False And sel_f = True Then
sql1 = "INSERT INTO " & target & "(" & field_c & "[FIELD], [VALUE]) "
sql2 = "SELECT " & field_c & """" & rsf.name & """ AS zzz, " & rsf.name
sql3 = "FROM " & source & ";"
sql = sql1 & Chr(13) & sql2 & Chr(13) & sql3 & Chr(13)
db.Execute sql
End If
End If
Next rsf
rs.Close
Exit Function
Err_h:
ACC_SPLIT_TABLE = fields(i) & " not found in " & source
End Function
Ten post został edytowany przez Autora dnia 08.05.14 o godzinie 18:21