Help Please........

[Guest]

hi my code is working fine only thing is i want the newly created Sub_Modules
to assign a primary key can any help me out here is my code

On Error GoTo addfielderror
If txtmodule.Text = "" Then
MsgBox "Please Insert Module Name", vbInformation, "Adding"
txtmodule.SetFocus
Exit Sub
Else
d = FLUpper(txtmodule.Text)
que = "Insert into Modules" & "(Modules)" & " Values (" & "'" & d &
"'" & ")"
cn.Execute que
MsgBox "Record " & d & " Added to Modules.", vbInformation, "Adding"
If vbOK = 1 Then
txtmodule.Text = ""
txtmodule.SetFocus
End If
'Exit Sub
End If

Set db = OpenDatabase(App.Path & "\DB\scmregister.mdb")
'D:\VSS_WKF\SCM-VB\check\DB\scmregister.mdb")
Set tdf = db.CreateTableDef(d)

With tdf
.Fields.Append .CreateField("Sub_Modules", dbText)
db.TableDefs.Append tdf
Exit Sub
End With

addfielderror:
If Err Then
MsgBox "Module " & FLUpper(d) & " Alrady Exists", vbInformation, "Adding"
If vbOK = 1 Then
txtmodule.Text = ""
txtmodule.SetFocus
Exit Sub
ElseIf Err.Number = 3010 Then
MsgBox "Table Already Exists", vbOKOnly, "Table"
Else
MsgBox "Table " & d & " created."
End If
End If

TIA

 

[Guest]

There are several ways to assign a key field:

1) (easiest)
Private Sub Command0_Click()
' from: http://msdn2.microsoft.com/en-us/library/Bb177893.aspx
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = CurrentDb 'OpenDatabase("Northwind.mdb")
' Create a table with three fields and a primary
' key.
dbs.Execute "CREATE TABLE NewTable " _
& "(FirstName CHAR, LastName CHAR, " _
& "SSN INTEGER CONSTRAINT MyFieldConstraint " _
& "PRIMARY KEY);"
dbs.Close
End Sub

2) (through ADOX)
URL: http://support.microsoft.com/kb/252908

The line that may interest you is-
"objTable.Keys.Append "PrimaryKey", adKeyPrimary, "PrimaryKey_Field" "

Anyhow, good luck!

Cheers,
Al