Primary key in a different database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
How does one go about setting up a primary key for a table in a
separate db
Click to expand...


' 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
 
Back
Top