Set primary key on a temp database table

[JimS]

Following code cobbled together creates a table in a temp db. Works fine. How
do I make the "RowNbr" column into a primary key (and thus create an index on
it...)?

Thanks.

 

[JimS]

Oops, shoulda included the code...


' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) & "
temp.mdb"

' Make sure there isn't already a file with the name of
' the new database.

If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)

strTableName = "tblTempMatreq"

'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists
If TableExists(strTableName) Then
CurrentDb.TableDefs.delete strTableName
End If

' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
.Fields.Append .CreateField("RowNbr", dbDouble)
rstCellMap.MoveFirst
While Not rstCellMap.EOF
If rstCellMap!Action = "Keep" Then
.Fields.Append .CreateField(rstCellMap!colname, dbText, 250)
End If
rstCellMap.MoveNext
Wend
dbsTemp.TableDefs.Append tdfNew
End With
dbsTemp.TableDefs.Refresh

 

[Tony Toews [MVP]]

Oops, shoulda included the code...

There should be sample code at TempTables.MDB page at my website which
illustrates how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tony Toews [MVP]]

.Fields.Append .CreateField("RowNbr", dbDouble)

A primary key being a single or double field might not work that well
given the imprecise nature of floating point numbers in computers.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/