Reset primary key counter fields in Access DB 
Author Message
 Reset primary key counter fields in Access DB

Hi,

I have a few Access 2000 DB tables that include cunter fields set as primary
keys, so no duplicates allowed.
Is there a way to reset those fields from VB?
It seems that the only way to do that is to open Access delete the filed and
recreate it.

Thanks in advance.

Andy,



Sat, 02 Oct 2004 22:26:36 GMT  
 Reset primary key counter fields in Access DB
Here is a sample with DAO:

Dim db As Database

Set db = OpenDatabase("c:\db1.mdb")

'Delete the original index
db.TableDefs("table1").Indexes.Delete "PrimaryKey"

'Create a new Index
Dim NewIndex As DAO.Index

Set NewIndex = db.TableDefs("table6").CreateIndex("id2")

NewIndex.Name = "PrimaryKey"
NewIndex.Primary = True

Dim Indexfield As DAO.Field

Set Indexfield = NewIndex.CreateField("id2", dbInteger)

NewIndex.Fields.Append Indexfield

db.TableDefs("table1").Indexes.Append NewIndex

db.Close

Luke

(This posting is provided "AS IS", with no warranties, and confers no
rights.)



Mon, 04 Oct 2004 14:13:52 GMT  
 Reset primary key counter fields in Access DB
Thanks Luke,

I should have specified that I'm using ADO.
Any chance of  translating your post into ADO?

Thanks again.

Andy.



Quote:
> Here is a sample with DAO:

> Dim db As Database

> Set db = OpenDatabase("c:\db1.mdb")

> 'Delete the original index
> db.TableDefs("table1").Indexes.Delete "PrimaryKey"

> 'Create a new Index
> Dim NewIndex As DAO.Index

> Set NewIndex = db.TableDefs("table6").CreateIndex("id2")

> NewIndex.Name = "PrimaryKey"
> NewIndex.Primary = True

> Dim Indexfield As DAO.Field

> Set Indexfield = NewIndex.CreateField("id2", dbInteger)

> NewIndex.Fields.Append Indexfield

> db.TableDefs("table1").Indexes.Append NewIndex

> db.Close

> Luke

> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)



Mon, 04 Oct 2004 17:15:54 GMT  
 Reset primary key counter fields in Access DB
Hi Andy,

With ADO, you also need to use an object library "Microsoft ADO Ext. 2.x
for DLL and Security" (ADOX):

Dim cn As New ADODB.Connection

cn.Open "provider=microsoft.jet.oledb.4.0;data source=c:\db1.mdb"

Dim ca As New ADOX.Catalog
Set ca.ActiveConnection = cn

ca.Tables("table1").Indexes.Delete "PrimaryKey"

Dim cx As New ADOX.Index

cx.Name = "PrimaryKey"
cx.PrimaryKey = True
cx.Columns.Append "id1"

ca.Tables("table6").Indexes.Append cx

Luke

(This posting is provided "AS IS", with no warranties, and confers no
rights.)



Tue, 05 Oct 2004 13:54:30 GMT  
 Reset primary key counter fields in Access DB
Thanks Luke, I look into it.

Andy.



Quote:
> Hi Andy,

> With ADO, you also need to use an object library "Microsoft ADO Ext. 2.x
> for DLL and Security" (ADOX):

> Dim cn As New ADODB.Connection

> cn.Open "provider=microsoft.jet.oledb.4.0;data source=c:\db1.mdb"

> Dim ca As New ADOX.Catalog
> Set ca.ActiveConnection = cn

> ca.Tables("table1").Indexes.Delete "PrimaryKey"

> Dim cx As New ADOX.Index

> cx.Name = "PrimaryKey"
> cx.PrimaryKey = True
> cx.Columns.Append "id1"

> ca.Tables("table6").Indexes.Append cx

> Luke

> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)



Tue, 05 Oct 2004 16:55:40 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Joined Primary Key in Foxpro (Make 1 Primary Key from 2 fields)

2. ADO how to determine if a access database field is a primary key field

3. Data control, Primary Key, and counters

4. Use of Counters as Primary Keys

5. Problem with "counter" primary key

6. Data control, Primary Key, and counters

7. Checking For Counter Fields in Access 2.0 DB And VB4 16

8. Checking For Counter Fields in Access 2.0 DB And VB4 16

9. How do i reset a counter field?

10. Resetting counter fields

11. Help resetting counter field value?

12. Resetting Page Counter Field

 

 
Powered by phpBB® Forum Software