Create Blank Access DB question 1

[CL1975]

I hope someone could help me with this problem that I am having. I am trying to create blank Access database from the Visual Basic front end and one of the table in my database will contain an autonumber field but I dont know how to get this to work from my application.
Thanks in Advance
JT

 

[MichaelRed]

property autoincrement = true
(all other aspects being appropiate)

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CL1975]

Michael,
how do you set property autoincrement = true. I try but autoincrement is not a defined word. If you would show me how that would be great. Thanks for your help.

 

[CCLINT]

.Fields(0).Properties("AutoIncrement&quot = True [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[Foada]

This code should get you started

'Add a reference to ADO Ext 2.xx for DLL and Security
Private Sub cmdCreateDB_Click()
Dim cat As New ADOX.Catalog
Dim tbl As New Table
Dim sConnection As String

sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\newDBName.mdb"
cat.Create sConnection
cat.ActiveConnection = sConnection
With tbl
.Name = "newTableName"
Set .ParentCatalog = cat
.Columns.Append "NewAutoInc", adInteger
.Columns("NewAutoInc&quot.Properties("AutoIncrement&quot = True
.Columns.Append "newColumn1Name", adInteger
.Columns.Append "newColumn2Name", adVarWChar, 25
End With
cat.Tables.Append tbl


Set tbl = Nothing
Set cat = Nothing
End Sub

Let me know if it helps. If you choose to battle wits with the witless be prepared to lose.

 

[CCLINT]

Oh yes...

.Columns(0) under ADO and .Fields(0) under DAO.

The questioner didn't mention what was being used, and I still forget to default to ADO....hopefully it has nothing to do with my age..... [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CL1975]

I can't seem getting it to works. I got an run-time error (property not found)
here is part of my code when creating an autonumber for the field below. Thanks again for all your help.
Set Indx = .CreateIndex("ControlNumIndex&quot
Indx.Primary = True
Indx.Fields.Append Indx.CreateField("ControlNum", bLong)
.Fields.Append .CreateField("ControlNum", dbLong)
.Indexes.Append Indx
.Fields(0).Properties("Autoincrement&quot = True

 

[CCLINT]

It needs to be set as an attribute under DAO.

DAO

Sub DAOCreateAutoIncrColumn()

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb&quot

' Get the Contacts table
Set tbl = db.TableDefs("Contacts&quot

' Create the new auto increment column
Set fld = tbl.CreateField("ContactId", dbLong)
fld.Attributes = dbAutoIncrField

' Add the new table to the database.
tbl.Fields.Append fld

db.Close

End Sub

ADOX

Sub ADOCreateAutoIncrColumn()

Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column

' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"

' Create the new auto increment column
With col
.Name = "ContactId"
.Type = adInteger
Set .ParentCatalog = cat
.Properties("AutoIncrement&quot = True
End With

' Append the column to the table
cat.Tables("Contacts&quot.Columns.Append col

Set cat = Nothing

End Sub

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[33216CLC]

CCLINT,


What if I have a field in my database that I would like to change to autonumber (using dao code in Vb). It is already defined as Long Integer. Could I do this? or would I have to add another field to my table?

Thanks for any help.

 

[CCLINT]

Should be able to...The values already in the field must be unique though.
I have never tried it. At "worst" you would need to create the new field, transfer the value from the old field, and the delete the old field.

Only a very few lines of code...

 

[johnwm]

I believe you will have to add a new field - I don't think jet will ever allow conversion of anything to autonumber
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

Well, actually, in most cases, you can change the field type...if there is not any data in the table.
In some cases, depending on the old and new field types, you may have to additionally set an JET engine option under ADO to allow partial bulk updates.
And other cases - field and data types, it is only logical that it will not work at all (changing a text field to a numeric field when the data is not all numeric).

The easiest way would be to use JET DDL and it should work under DAO and ADO. (You'll need to test this yourself though).
First, a the table's structure needs to be copied to a temporary table, w/o any data.
Then, the field in question in the temporary table needs to be changed to an Auto Increment field.
Then the data from the original table gets transfered to the temporary table; The original table gets deleted; The temporary table gets copied with data back to the original table's name, then the temporary table gets deleted:

With conn
.Execute "SELECT * INTO OrigTable FROM TempTable WHERE TRUE=0"
.Execute "ALTER TABLE TempTable ALTER COLUMN ID AUTOINCREMENT"
.Execute "INSERT INTO TempTable SELECT * FROM OrigTable"
.Execute "DROP TABLE OrigTable "
.Execute "SELECT * INTO OrigTable FROM TempTable"
.Execute "DROP TABLE TempTable"
End With

 

[33216CLC]

CCLINT,

I tried several times to update the field attribute to dbAutoIncrField, but to know avail.

Here is what I did (it works):

1. Delete the field in question
2. Append the new field using the code:

Set fld = tbl.CreateField("dependent_id", dbLong)
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld
3. Assigned the new field the same NAME and OriginalPosition of the deleted field.


This works out good; however, I would like to be able to find out if the field is already AutoNumber before I delete it. I have recently found that earler copies of my database structure actually have this field as AutoNumber, but my most recent copy somehow has it as a Long Integer. I do not know how this happened, maybe one of my coworkers mistakenly changed it from within Access. I would however want to be sure that when my program runs the problem is corrected.

I tried the code below, but it is not absolute:

Set fld = .Fields("dependent_id&quot
If fld.Attributes <> dbAutoIncrField Then
<Code>
End if




Any thoughts?

 

[CCLINT]

First you write:
>I tried several times to update the field attribute to dbAutoIncrField, but to know avail.

Then you write:
>This works out good

So, is it working or not working?


As for identifying if the field is already an AutoIncrement field under DAO use:

If the field is updatable, (recordset type is dbOpenDynaset), then the attribute setting for an auto increment field will be:

?rsDAO.Fields(0).Attributes = dbUpdatableField or dbAutoIncrField or dbFixedField

If not (recordset type is dbOpenSnapshot or dbOpenForwardOnly), then:

?rsDAO.Fields(0).Attributes = dbAutoIncrField or dbFixedField

If you are using the TableDefs collection then:

Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Set tbl = db.TableDefs(&quot;TheTable&quot
Set fld = tbl.Fields(&quot;AutoNumber&quot

Then of course, the field will not be updatable so:

?fld.Attributes = dbAutoIncrField or dbFixedField

dbFixedField is a value identifying if the field has a fixed length, which is the case for number fields, so the auto number field will carry both attributes.


>but my most recent copy somehow has it as a Long Integer

Actually, I would leave it as such, until you investigate more. Your co-worker may have determined that the Auto number field was not serving the purpose needed.

 

[33216CLC]

I'm sorry if I was not clear with what I was said. In your first response to my thread you said that I should be able to change the field to AutoNumber if it had no values. Well the field had no values; so I tried to several times to change it to AutoNumber and it did not work. I then did what I was hoping I would not need to do: I deleted the field and appended a new one in its place with AutoNumber as the field attribute - this worked.

So, first step did not work; second step did.


Thanks for your help.

 

[CCLINT]

Sorry, I had a typo here:

With conn
.Execute &quot;SELECT * INTO TempTable FROM OrigTable WHERE TRUE=0&quot;
.Execute &quot;ALTER TABLE TempTable ALTER COLUMN ID AUTOINCREMENT&quot;
.Execute &quot;INSERT INTO TempTable SELECT * FROM OrigTable&quot;
.Execute &quot;DROP TABLE OrigTable &quot;
.Execute &quot;SELECT * INTO OrigTable FROM TempTable&quot;
.Execute &quot;DROP TABLE TempTable&quot;
End With

 

[CCLINT]

And, If there is no data, then:


conn.Execute &quot;ALTER TABLE OrigTable ALTER COLUMN ID AUTOINCREMENT&quot;