Primary key in a different database

[SHIPP]

How does one go about setting up a primary key for a table in a separate db
through Visual Basic where the table is not linked to the current db? Thanks
in advance for your help.

 

[SHIPP]

More information. This is the query I use to get the data into a blank
database. After it's there I want to set-up the primary key. I'm using Access
97.

strNewDatabase = rs("Lname") & "_" & rs("Fname") & "_" &
rs("EmployeeReportToID")
' Insert the employees for this Manager
strSQL = "SELECT DISTINCTROW tblEmployees.EmpID, tblEmployees.Fname, " _
& "tblEmployees.Lname, tblEmployees.BirthYear, tblEmployees.Gender, " _
& "tblEmployees.Minority, tblEmployees.OrigHireDate,
tblEmployees.Relocate, " _
& "tblEmployees.EmployeeReportToID, tblEmployees.ProjYrRetire, " _
& "tblEmployees.Status, tblEmployees.DegreeID,
tblEmployees.YearCompleted, " _
& "tblEmployees.MajorField, tblEmployees.College,
tblEmployees.OtherTraining INTO tblEmployees IN " _
& Chr(34) & strFull & Chr(34) & " FROM tblEmployees " _
& "WHERE (((tblEmployees.EmployeeReportToID)=" & GetManagerID() & "));"
db.Execute strSQL, dbFailOnError

 

[Bas Cost Budde]

in the following code, I have a recordset open (in variable rp) which
gives me the field names. The code is part of an automated setup
structure derived from the CASE project on my site, if you're interested
in more details.

set db=opendatabase(path)
set td=db.tabledefs(table)

Set ID = td.CreateIndex("PrimaryKey")
ID.Primary = True
ID.Required = True
ID.Unique = True
Do While Not rp.EOF
Set fd = ID.CreateField(rp(0))
ID.Fields.Append fd
ID.Fields.Refresh
rp.MoveNext
Loop
td.Indexes.Append ID
td.Indexes.Refresh

 

[Tim Ferguson]

How does one go about setting up a primary key for a table in a
separate db

' open the separate db -- this is DAO land. Similar for
' ADO but you need a connection object instead
Set db = DBEngine.OpenDatabase(etc, etc)

' make the command
strSQL = "ALTER TABLE MyTable " & _
"ADD CONSTRAINT pk PRIMARY KEY (MyPKColumn)"

' make it so
db.Execute strSQL, dbFailOnError



Hope that helps


Tim F