[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

DAO and VB - autoincrement solution?

Hi there

I want to create a database with this layout:

ID -- Feld 2
1      one      
2      two      

The ID must autoincemented like MySQL autoincement function. How can i do this?

This is the Code i use for creating my database..

[code]
      ' Name der Datenbank '
        DBName = App.Path & ".\" & DBName
     
        ' leere Datenbank anlegen '
        Set db = CreateDatabase(DBName, dbLangGeneral & Passwort, dbEncrypt)
       
        ' Tabellendef 1 anlegen '
        Set tbl = db.CreateTableDef("Tabelle1")
       
        ' 1tes Feld in Tabelle 1 anlegen und anhängen '
        Set fld = tbl.CreateField("ID", dbText, 50): tbl.Fields.Append fld
       
        ' 2tes Feld in 1. Tabelle anlegen und anhängen '
        Set fld = tbl.CreateField("Feld2", dbText, 50):  tbl.Fields.Append fld
       
        ' Tabelle in Datenbank anlegen '
        db.TableDefs.Append tbl
   
        ' Primary-Key in Tabelle1 auf Feld1 setzen '
        Set ind = tbl.CreateIndex("PrimaryKey")
        ind.Fields = "Feld1"
        ind.Primary = True
        ind.Unique = True
[/code]

This is code i found in the net, how can i autoincrement the ID?
I never used a database engine from Visual Basic.

thanks 4 help and sorry for my bad english
greets from Germany
Hendrik
0
Hendrik2
Asked:
Hendrik2
  • 6
  • 2
1 Solution
 
DabasCommented:
Hallo Hendrik2,
Is this a MySQL database. In this case I recommend not to use DAO
If it is an MSAccess .mdb file, then you have the autonumber field

Dabas
0
 
dmenardCommented:
Set the ID field attribute to dbAutoIncrField :

Set fld = tbl.CreateField("ID", dbText, 50)
fld.Attributes = fld.Attributes Or dbAutoIncrField
tbl.Fields.Append fld
0
 
Hendrik2Author Commented:
@Dabas
Its a .mdb file that the script makes (DAO 3.5). How can i autonumber the field ?

@dmenard
if i change the code to yours i get the error "invalid argument - Ungültiges Argument. (Fehler 3001)" at these Line:

   db.TableDefs.Append tbl

I also checked this with a big integer

   Set fld = tbl.CreateField("ID", dbBigint, 16):  ' bigint is better for incrementing :-)

but i get the same error.

Here is the code again:


        '  Felder in Tabelle 1 anlegen und anhängen '
        Set fld = tbl.CreateField("ID",dbBigInt, 16):
        fld.Attributes = fld.Attributes Or dbAutoIncrField:
        tbl.Fields.Append fld
   
        Set fld = tbl.CreateField("Titel", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Beschreibung", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Kategorie", dbText, 50): tbl.Fields.Append fld
       
        ' Tabelle in Datenbank anlegen '
        db.TableDefs.Append tbl


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Hendrik2Author Commented:
@Dabas
Its a .mdb file that the script makes (DAO 3.5). How can i autonumber the field ?

@dmenard
if i change the code to yours i get the error "invalid argument - Ungültiges Argument. (Fehler 3001)" at these Line:

   db.TableDefs.Append tbl

I also checked this with a big integer

   Set fld = tbl.CreateField("ID", dbBigint, 16):  ' bigint is better for incrementing :-)

but i get the same error.

Here is the code again:


        '  Felder in Tabelle 1 anlegen und anhängen '
        Set fld = tbl.CreateField("ID",dbBigInt, 16):
        fld.Attributes = fld.Attributes Or dbAutoIncrField:
        tbl.Fields.Append fld
   
        Set fld = tbl.CreateField("Titel", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Beschreibung", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Kategorie", dbText, 50): tbl.Fields.Append fld
       
        ' Tabelle in Datenbank anlegen '
        db.TableDefs.Append tbl


0
 
Hendrik2Author Commented:
@Dabas
Its a .mdb file that the script makes (DAO 3.5). How can i autonumber the field ?

@dmenard
if i change the code to yours i get the error "invalid argument - Ungültiges Argument. (Fehler 3001)" at these Line:

   db.TableDefs.Append tbl

I also checked this with a big integer

   Set fld = tbl.CreateField("ID", dbBigint, 16):  ' bigint is better for incrementing :-)

but i get the same error.

Here is the code again:


        '  Felder in Tabelle 1 anlegen und anhängen '
        Set fld = tbl.CreateField("ID",dbBigInt, 16):
        fld.Attributes = fld.Attributes Or dbAutoIncrField:
        tbl.Fields.Append fld
   
        Set fld = tbl.CreateField("Titel", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Beschreibung", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Kategorie", dbText, 50): tbl.Fields.Append fld
       
        ' Tabelle in Datenbank anlegen '
        db.TableDefs.Append tbl


0
 
Hendrik2Author Commented:
Dabas
-------
Its a .mdb file that the script makes (DAO 3.5). How can i autonumber the field ?

dmenard
----------
if i change the code to yours i get the error "invalid argument - Ungültiges Argument. (Fehler 3001)" at these Line:

   db.TableDefs.Append tbl

I also checked this with a big integer

   Set fld = tbl.CreateField("ID", dbBigint, 16):  ' bigint is better for incrementing :-)

but i get the same error.

Here is the code again:


        '  Felder in Tabelle 1 anlegen und anhängen '
        Set fld = tbl.CreateField("ID",dbBigInt, 16):
        fld.Attributes = fld.Attributes Or dbAutoIncrField:
        tbl.Fields.Append fld
   
        Set fld = tbl.CreateField("Titel", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Beschreibung", dbText, 50): tbl.Fields.Append fld
        Set fld = tbl.CreateField("Kategorie", dbText, 50): tbl.Fields.Append fld
       
        ' Tabelle in Datenbank anlegen '
        db.TableDefs.Append tbl


0
 
Hendrik2Author Commented:
sorry for this mass posting i got always a tomcat post error after submitting???

@admin
please delete the laste 3 answers from me
thx

Hendrik
0
 
dmenardCommented:
Sorry, hendryk2 !

You're right, dbText is not very good for incrementing :-)

Well I think that you must use dbLong for an autoincrement field, not bigint. And we must not specify the size in this case, if my momory is good...

So try that :
       Set fld = tbl.CreateField("ID",dbLong):
       fld.Attributes = fld.Attributes Or dbAutoIncrField:
       tbl.Fields.Append fld
 
Does-it work ?

(sorry, I can test it right now)
0
 
Hendrik2Author Commented:
thanks a lot - it works :-)

Greets
Hendrik
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now