Joshyy
asked on
copying an access table
i have a table called "Table1"
i want to make a copy of this table called "Table2"
then remove "Table1"
and rename "Table2" to "Table1"
i want to make a copy of this table called "Table2"
then remove "Table1"
and rename "Table2" to "Table1"
sorry, a function is missing:
Public Function GetTableDef(db As Database, tabelle As String) As TableDef
On Local Error GoTo Fehler
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
Dim TDef As TableDef
Set GetTableDef = Nothing
For Each TDef In db.TableDefs
If TDef.Name = tabelle Then
Set GetTableDef = TDef
Exit For
End If
Next
'------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
Exit Function
Fehler:
Select Case Err
Case Else
MsgBox Error
End Select
End Function
Public Function GetTableDef(db As Database, tabelle As String) As TableDef
On Local Error GoTo Fehler
'-------------------------
Dim TDef As TableDef
Set GetTableDef = Nothing
For Each TDef In db.TableDefs
If TDef.Name = tabelle Then
Set GetTableDef = TDef
Exit For
End If
Next
'-------------------------
Exit Function
Fehler:
Select Case Err
Case Else
MsgBox Error
End Select
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On Local Error GoTo Fehler
'_________________________
Dim NewTDef As TableDef
Dim RefField As Field
Dim NewField As Field
Dim RefInd As Index
Dim NewInd As Index
Dim RefProp As Property
'_________________________
Set NewTDef = New TableDef
'_________________________
For Each RefProp In RefTDef.Properties
NewTDef.Properties(RefProp
NewTDef.Properties(RefProp
DoEvents
Next
NewTDef.Properties.Refresh
'_________________________
For Each RefField In RefTDef.Fields
Set NewField = NewTDef.CreateField
For Each RefProp In RefField.Properties
NewField.Properties(RefPro
NewField.Properties(RefPro
DoEvents
Next
NewTDef.Fields.Append NewField
Next
NewTDef.Fields.Refresh
'_________________________
For Each RefInd In RefTDef.Indexes
Set NewInd = NewTDef.CreateIndex
For Each RefProp In RefInd.Properties
NewInd.Properties(RefProp.
NewInd.Properties(RefProp.
DoEvents
Next
For Each RefField In RefInd.Fields
Set NewField = NewInd.CreateField
For Each RefProp In RefField.Properties
NewField.Properties(RefPro
NewField.Properties(RefPro
DoEvents
Next
NewInd.Fields.Append NewField
Next
NewTDef.Indexes.Append NewInd
Next
NewTDef.Indexes.Refresh
'_________________________
Set CloneTDef = NewTDef
Exit Function
Fehler:
Select Case Err
Case 3001, 3219, 3251, 3267, 3270, 3420
Resume Next
Case Else
MsgBox Error
Exit Function
End Select
End Function
Sub ReplaceTable(NewTDef As TableDef, db As Database)
On Local Error GoTo Fehler
'_________________________
If Not GetTableDef(db, NewTDef.Name) Is Nothing Then
db.TableDefs.Delete (NewTDef.Name)
db.TableDefs.Refresh
End If
db.TableDefs.Append NewTDef
db.TableDefs.Refresh
'_________________________
Exit Sub
Fehler:
Select Case Err
Case Else
MsgBox Error
Exit Sub
End Select
End Sub
Call ReplaceTable(CloneTDef(TDe