PC Review


Reply
 
 
Bill Linares
Guest
Posts: n/a
 
      6th Sep 2003
How do you define a field as primary key in VBA?
Is sounds trivial, but I have not been able to find answer in on-line help


 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      6th Sep 2003
Using DAO, create an Index, and set its Primary property to Yes:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("MyTable")

'Create a new index. The name us usual, but not crucial.
Set ind = tdf.CreateIndex("PrimaryKey")
'Specify the field(s) in the index
With ind
.Fields.Append .CreateField("MyField")
.Primary = True 'This makes it the primary key
End With
'Add this new index to the TableDef's Indexes collection.
tdf.Indexes.Append ind

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Bill Linares" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How do you define a field as primary key in VBA?
> Is sounds trivial, but I have not been able to find answer in on-line help
>
>



 
Reply With Quote
 
 
 
 
Bill Linares
Guest
Posts: n/a
 
      6th Sep 2003
Why is it that I cannot append the table to the data-base?: Following line

db.TableDefs.Append tdf

produces error 3264, no field defined

Thanks for your troubles.


 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      6th Sep 2003
First you CreateTableDef().
Then CreateField(), and append it to the TableDef.
Finally, Append the TableDef to the database's TableDefs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Bill Linares" <(E-Mail Removed)> wrote in message
news:3f59cab9$(E-Mail Removed)...
> Why is it that I cannot append the table to the data-base?: Following line
>
> db.TableDefs.Append tdf
>
> produces error 3264, no field defined
>
> Thanks for your troubles.



 
Reply With Quote
 
TC
Guest
Posts: n/a
 
      7th Sep 2003
You have to create the fields in the *table* before you add them to the
index.

TC


"Bill Linares" <(E-Mail Removed)> wrote in message
news:3f5af000$(E-Mail Removed)...
> Isn't that what I'm doing?:
>
> Public Sub Test_AutoKey()
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Dim ind As DAO.Index
>
> 'Initialize
> Set db = CurrentDb()
> Set tdf = db.CreateTableDef("MyTable")
>
> 'Create a new index. The name us usual, but not crucial.
> Set ind = tdf.CreateIndex("PrimaryKey")
> 'Specify the field(s) in the index
> With ind
> .Fields.Append .CreateField("MyField")
> .Primary = True 'This makes it the primary key
> End With
> 'Add this new index to the TableDef's Indexes collection.
> tdf.Indexes.Append ind
> db.TableDefs.Append tdf
>
> 'Clean up
> Set ind = Nothing
> Set tdf = Nothing
> Set db = Nothing
> End Sub
>
>



 
Reply With Quote
 
Bill Linares
Guest
Posts: n/a
 
      7th Sep 2003
Isn't that what I'm doing?:

Public Sub Test_AutoKey()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.CreateTableDef("MyTable")

'Create a new index. The name us usual, but not crucial.
Set ind = tdf.CreateIndex("PrimaryKey")
'Specify the field(s) in the index
With ind
.Fields.Append .CreateField("MyField")
.Primary = True 'This makes it the primary key
End With
'Add this new index to the TableDef's Indexes collection.
tdf.Indexes.Append ind
db.TableDefs.Append tdf

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub


 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      7th Sep 2003
You have created a table, but the table has no fields.
Try this sequence:
1. CreateTableDef
2. CreateField
3. Append field to TableDef
4. Repeat steps 2 and 3 for other fields.
5. Append TableDef to TableDefs.

6. Create the index
7. CreateField in index (doesn't create a field, just a reference to
it).
8. Append field to Index's Fields
9. Append Index to TableDef's Indexes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Bill Linares" <(E-Mail Removed)> wrote in message
news:3f5af000$(E-Mail Removed)...
> Isn't that what I'm doing?:
>
> Public Sub Test_AutoKey()
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Dim ind As DAO.Index
>
> 'Initialize
> Set db = CurrentDb()
> Set tdf = db.CreateTableDef("MyTable")
>
> 'Create a new index. The name us usual, but not crucial.
> Set ind = tdf.CreateIndex("PrimaryKey")
> 'Specify the field(s) in the index
> With ind
> .Fields.Append .CreateField("MyField")
> .Primary = True 'This makes it the primary key
> End With
> 'Add this new index to the TableDef's Indexes collection.
> tdf.Indexes.Append ind
> db.TableDefs.Append tdf
>
> 'Clean up
> Set ind = Nothing
> Set tdf = Nothing
> Set db = Nothing
> End Sub
>
>



 
Reply With Quote
 
Bill Linares
Guest
Posts: n/a
 
      7th Sep 2003
Thanks mate


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Do I have to set the primary key to include primary key fields? boooney Microsoft Access Database Table Design 3 15th Apr 2010 12:17 AM
Data Primary key vs. Artificial (Autonumber) primary key M. Microsoft Access Database Table Design 78 14th Dec 2007 10:10 AM
Re: How to build a funciton to return primary key,alternate key and foreign key fields Allen Browne Microsoft Access VBA Modules 0 18th Jul 2005 01:51 PM
How to build a funciton to return primary key,alternate key and foreign key fields tonyck Microsoft Access VBA Modules 0 18th Jul 2005 01:07 PM
Cannot add primary key constraint since primary key is already set for the table Microsoft VB .NET 1 3rd Dec 2003 02:14 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:54 PM.