?
Solved

DAO and VB - autoincrement solution?

Posted on 2004-03-25
9
Medium Priority
?
961 Views
Last Modified: 2013-12-25
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
Comment
Question by:Hendrik2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 10684219
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
 
LVL 2

Expert Comment

by:dmenard
ID: 10686175
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
 

Author Comment

by:Hendrik2
ID: 10687610
@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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Hendrik2
ID: 10687615
@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
 

Author Comment

by:Hendrik2
ID: 10687619
@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
 

Author Comment

by:Hendrik2
ID: 10687622
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
 

Author Comment

by:Hendrik2
ID: 10687647
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
 
LVL 2

Accepted Solution

by:
dmenard earned 500 total points
ID: 10687802
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
 

Author Comment

by:Hendrik2
ID: 10688825
thanks a lot - it works :-)

Greets
Hendrik
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question