Creating PK via code

[John]

Hi

Is there a way to create PK called ID via code, given the table name as
string?

Many Thanks

Regards

 

[John]

Hi

Sorry, forgot to add, the table is a linked table coming from a backend
access db.

Thanks

Regards

 

[Douglas J. Steele]

To add a field to the table, you'd have to do it in the backend database.
You cannot add a field only for the front-end. Is that what you're looking
for?

 

[John]

Hi

I have a routine running in front end and as part of processing I need to
add a PK to any table that does not have it before processing the table.
Problem is all tables are linked and coming from a backend.

However if it is not possible then I'll go with doing it at backend. I need
to know if it is possible to add a PK via code?

Thanks

Regards

 

[Peter Hibbs]

The Back End Update utility has code to do that at :-
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=410

Peter Hibbs.

 

[Tony Toews [MVP]]

Is there a way to create PK called ID via code, given the table name as
string?

See the 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

But what are you going to put in the PK? An autonumber field? What
if the table already has a autonumber PK with a different name?

Tony

 

[John]

Hi Tony

Many thanks. There isn't another autonumber PK in the table. I am checking
that already.

Thanks again

Regards

 

[Douglas J. Steele]

Yes, it's possible. Presumably you mean that you want ID to be an Autonumber
field as well as a PK (you can't add a field and make it a PK unless you can
assure that it has a unique value for each field)

Dim dbBackend As DAO.Database
Dim tdfTable As DAO.TableDef
Dim fldID As DAO.Field
Dim idxPrimary As DAO.Index

Set dbBackend = OpenDatabase("E:\Folder\File.mdb")
Set tdfTable = dbBackend.TableDefs("SomeTableName")
' Create a Long Integer field named ID to add to the table
Set fldID = tdfTable.CreateField("ID", dbLong)
' Make ID an AutoNumber
fldID.Attributes = fldID.Attributes + dbAutoIncrField
' Append ID to the table.
tdfTable.Fields.Append fldID
' Create an index named PrimaryKey
Set idxPrimary = tdfTable.CreateIndex("PrimaryKey")
' Create a field named ID to add to the index
Set fldID = idxPrimary.CreateField("ID", dbLong)
' Append ID to the index
idxPrimary.Append fldID
' Make the index the Primary Key
idxPrimary.Primary = True
' Append the index to the table
tdfTable.Indexes.Append idxPrimary
dbBackend.TableDefs.Refesh

 

[John]

Thanks. Gives me error 'Method or data member not found." on Append in
'idxPrimary.Append fldID' and on Refresh in 'dbBackend.TableDefs.Refesh'.

Regards

 

[Tony Toews [MVP]]

Thanks. Gives me error 'Method or data member not found." on Append in
'idxPrimary.Append fldID' and on Refresh in 'dbBackend.TableDefs.Refesh'.

Post your code.

Tony

 

[John]

Dim dbBackend As DAO.Database
Dim tdfTable As DAO.TableDef
Dim fldID As DAO.Field
Dim idxPrimary As DAO.Index

Set dbBackend = OpenDatabase("E:\Folder\File.mdb")
Set tdfTable = dbBackend.TableDefs("SomeTableName")
' Create a Long Integer field named ID to add to the table
Set fldID = tdfTable.CreateField("ID", dbLong)
' Make ID an AutoNumber
fldID.Attributes = fldID.Attributes + dbAutoIncrField
' Append ID to the table.
tdfTable.Fields.Append fldID
' Create an index named PrimaryKey
Set idxPrimary = tdfTable.CreateIndex("PrimaryKey")
' Create a field named ID to add to the index
Set fldID = idxPrimary.CreateField("ID", dbLong)
' Append ID to the index
idxPrimary.Append fldID
' Make the index the Primary Key
idxPrimary.Primary = True
' Append the index to the table
tdfTable.Indexes.Append idxPrimary
dbBackend.TableDefs.Refesh

 

[Tony Toews [MVP]]

Dunno where you got your code from but ...

idxPrimary.Append fldID

should be

idxPrimary.Fields.Append fldID

dbBackend.TableDefs.Refesh

Not sure what the above should be but likely not required. I don't
have any such in my code I execute against another MDB. You might
need it if it was in the MDB from which you were executing the code.
Basically I'd comment it out and if you get wierdnesses happening
later then post back.

Tony

 

[Brendan Reynolds]

Not sure what the above should be but likely not required. I don't
have any such in my code I execute against another MDB. You might
need it if it was in the MDB from which you were executing the code.
Basically I'd comment it out and if you get wierdnesses happening
later then post back.

It's a typo, Tony, the second "r" is missing from the word "Refresh".

 

[Tony Toews [MVP]]

It's a typo, Tony, the second "r" is missing from the word "Refresh".

Yup, you're right. Thanks for noticing.

Tony