Help getting new field in DAO to become a primary key and indexed.

[Mightyginger]

Hi guys, this is going to be something trivial I've not done but I've looked over it several times and have no idea why it doesn't work. I use this sub CreateTable to create a new Table called StrTableName in database strDbName. It creates the field Date and the table fine but Date isn't a primary field for some reason and isn't indexed even though I thought I'd specified it. What am I doing wrong please?

Thanks,

Neil.


Sub CreateTable(strDbName As String, strTableName As String)

Dim db As Database
Dim tbl As TableDef
Dim Fld As DAO.Field
Dim FldIndex As DAO.Field
Dim Idx As Index
Dim strDb As String
Dim strDbTableQuery As String
Dim i As Integer

Set db = OpenDatabase(strDbName)
'Create a new table definition for a table
Set tbl = db.CreateTableDef(strTableName)
'Create a new field in table "strDbName"
Set Fld = tbl.CreateField("Date", dbDate)
'Append field to table
tbl.Fields.Append Fld
'Create primary key index.
Set Idx = tbl.CreateIndex("PrimaryKey")
Idx.Primary = True
Idx.Required = True
Idx.Unique = True
Set FldIndex = Idx.CreateField("Date")
Idx.Fields.Append FldIndex
db.TableDefs.Append tbl

'Close the database
db.Close

End Sub

 

[PHV]

...
Idx.Fields.Append FldIndex
[!]tbl.Indexes.Append Idx[/!]
db.TableDefs.Append tbl
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mightyginger]

Thats great. I have another question though if I can? I'm using this database to save daily data and so I use this sub to store the data. The first thing it does is to check that the date it's storing for doesn't exist as a value in the Date field. To use this I'm using the Seek method. There is a problem though. It says that the field Date is not an Index in the table. I've opened it and it is Indexed (No Duplicates) and it is Primary. To test something I sent one of the other fields which isn't Primary to be Indexed and then changed the field name below equal to that and it works fine. Are you just not allowed to do this if the field is a Primary field?

Thanks,


Neil.


Sub SaveData(varInputWorksheet As Variant, strDbSource As String, Inputi As Integer, Inputj As Integer, strInputTable As String, dtInputDate As Date)
Dim db As Database, rs As Recordset
Dim tbl As TableDef
Set db = OpenDatabase(strDbSource)
Set tbl = db.TableDefs(strInputTable)
Set rs = db.OpenRecordset(strInputTable, dbOpenTable)
Dim i As Integer
Dim j As Integer

i = Inputi
j = Inputj

'check to see if there is an entry for todays date
rs.Index = "Date"
rs.Seek "=", dtInputDate
If rs.NoMatch = True Then 'no entry for that date
'check if field exists and if not create it
rs.AddNew
rs.Fields("Date") = dtInputDate
Do Until varInputWorksheet.Cells(i, j) = "TYPE"
rs.Fields(varInputWorksheet.Cells(i, j + 1)) = varInputWorksheet.Cells(i, j + 4)
i = i + 1
Loop
End If
db.Close
Set db = Nothing

End Sub

 

[Mightyginger]

Actually, I think I've fixed it. If it's a Primary key then you don't put the name of the field you just write
rs.Index = "PrimaryKey"

Sorry about that.

 

[PHV]

you don't put the name of the field
Correct, you have to put the name of the index.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886