setting primary index for copied table

[waldemar]

I am copying tables with this function; however there is major problem with it:

Access does not copy the primary index (where that key icon appears) which seems neccessary for many query options (later in the application). That means in the new table there is NO primary index..

Does anybody know how to set the primary index for the new table???


Function copytable(dbpath As String, tablekind As String, tablename As String, totablename As String)
Dim db As Database, rs As Recordset
If dbpath = "" Then
' ----- wenn dbpath leer, dann prüfen wir die internen managertabellen -----
Set db = CurrentDb
Else
' ----- wenn wir einen dbpath haben, prüfen wir die externe tabelle -----
Set db = DBEngine.OpenDatabase(dbpath)
End If

DoCmd.SetWarnings False

DoCmd.RunSQL ("SELECT [" & tablename & "].* INTO [default" & tablekind & "] FROM [" & tablename & "];&quot
DoCmd.TransferDatabase acExport, "Microsoft Access", dbpath, acTable, "default" & tablekind, totablename

DoCmd.SetWarnings True

Set db = Nothing
End Function


Thanks a lot
waldemar

 

[930driver]

Here is a function that I use to create indexes. The only tricky part might be in the array parameter for the fields. Put all the fields that you want included in the index into the string array. Set fUnique and fPrimary to True for a primary key.

Watch out for line wraps when you read this.

Function CreateIndex(db As Database, strTable As String, strIndexName As String, strFields() As String, _
fUnique As Boolean, fPrimary As Boolean) As Boolean
On Error GoTo Err_CreateIndex

Dim tbldef As TableDef
Dim indx As Index
Dim indxfld As Field
Dim i As Integer

Set tbldef = db.TableDefs(strTable)

On Error Resume Next 'first get rid of index, if it exists
tbldef.Indexes.Delete strIndexName
On Error GoTo Err_CreateIndex

Set indx = tbldef.CreateIndex(strIndexName)
With indx
.Unique = fUnique
.Primary = fPrimary
For i = 0 To UBound(strFields) - 1
.Fields.Append .CreateField(strFields(i))
Next i
End With
tbldef.Indexes.Append indx
tbldef.Indexes.Refresh

CreateIndex = True 'return true on success
Print #intUdLogFile, "Index " & strIndexName & " created in table " & strTable & "."

Exit_CreateIndex:
On Error Resume Next
Set indx = Nothing
Set tbldef = Nothing

Exit Function

Err_CreateIndex:
Select Case Err
Case 0 'insert Errors you wish to ignore here
Resume Next
Case Else 'All other errors will trap
Beep
MsgBox Err.Description, , "Error in function Backend Upgrades.CreateIndex"
Resume Exit_CreateIndex
End Select
Resume 0 'FOR TROUBLESHOOTING
End Function
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"