How to Add new column of Long Integer in MS-Access Database through Code

[Jagjit]

Hi

How to Add new column of Long Integer in MS-Access Database through Code with 2 decimal places.

Thanks

[Arnoutdv]

A long integer with 2 decimal places?
The definition of an integer is that it has no fraction.

[dilettante]

Maybe the question is about "fixed point" values?

Well you could convert to/from Single, Double, Currency, etc. in your SQL:

    Set Connection = New ADODB.Connection
    Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='demo.mdb'"
    Set cmndDumpTable = New ADODB.Command
    With cmndDumpTable
        .CommandType = adCmdText
        .CommandText = "SELECT [ID],[Product]," _
                     & "Format$(CCur([Price])/100,'$#,##0.00') AS Price," _
                     & "[Price] AS [Price Actual] FROM [Products]"
        .Prepared = True
        .Name = "DumpTable"
        .ActiveConnection = Connection
    End With
    Set cmndInsertNew = New ADODB.Command
    With cmndInsertNew
        .CommandType = adCmdText
        .CommandText = "INSERT INTO [Products]([Product],[Price])" _
                     & "VALUES(?,Clng(Int(? * 100)))"
        .Prepared = True
        .Name = "InsertNew"
        .ActiveConnection = Connection
    End With

[Jagjit]

Hi

I have created text field like this . Just guide how long integer field with 2 decimal places.

Dim rs0 As ADODB.Recordset
Set rs0 = New ADODB.Recordset
If rs0.State = 1 Then rs0.Close
rs0.Open "Select * from trans", cnn1, adOpenDynamic, adLockOptimistic, adCmdText
ctr = 0
For Each f In rs0.Fields
If f.Name = "fname" Then
ctr = 1
End If
Next
If ctr = 0 Then
sql = "ALTER TABLE test ADD COLUMN fname Text(20)"
cnn2.Execute sql
End If

Thanks

[dilettante]

Integers, long or otherwise, do not have "decimal places." That's why they are called integers.

I showed you one way to treat an integer as a fixed-point value, but it sounds like you want something else. Maybe a float or currency field?

Jet has no fixed-point data type. Maybe see: Data Type Support (OLE DB)

[Jagjit]

Hi

If i have to take float or currency field with 2 decimals then what should be changed in the below line
sql = "ALTER TABLE test ADD COLUMN fname Text(20)"

Thanks

[Arnoutdv]

Have you checked the link provided by dilettante? The answer is in that table.

[ChrisE]

Hi


like this..

Dim strsqlB As String
strsqlB = "Alter Table Test Add Column fname CURRENCY;"
adoConnection.Execute strsqlB
adoConnection.Close

regards
Chris

[Jagjit]

Hi Chrise

How to set default value 0

Thanks

[Arnoutdv]

By adding "DEFAULT 0"

Have you ever used a search engine??

[ChrisE]

By adding "DEFAULT 0"

Have you ever used a search engine??

I(he) think(s) we are the search engine

I'll remember the name for the next time

regards
Chris

[Jagjit]

Hi ChrisE

Thanks for your comments & reply.

Thanks

[Jagjit]

Hi

When i write the below line to set Default value then it gives Syntax error
sql = "ALTER TABLE transact ADD COLUMN discountamt Long Integer 'Default 0' "

Below code works fine . It creates a field.
ctr = 0
For Each f In rs.Fields
If f.Name = "discountamt" Then
ctr = 1
End If
Next
If ctr = 0 Then
sql = "ALTER TABLE transact ADD COLUMN discountamt Long Integer"
cnn1.Execute sql
End If

Thanks

[gibra]

You can't set both Long and Integer field type.
You have to use Long OR integer

Replace your code with:

ALTER TABLE transact ADD discountamt LONG DEFAULT 0

or

ALTER TABLE transact ADD discountamt INTEGER DEFAULT 0

[dilettante]

Both INTEGER and LONG (as well as INT, INTEGER4) are synonyms in Jet SQL. They are all 32-bit signed integers.

SMALLINT, SHORT, INTEGER2 are the 16-bit signed integer type synonyms.

[Jagjit]

Hi

I have written below code , not working

sql = "ALTER TABLE temp ADD COLUMN discount INTEGER DEFAULT 0"
cnn1.Execute sql

Thanks

[ChrisE]

hi,

the 2 code samples from gibra works just fine, ist your connection valid

regards
Chris

[Jagjit]

Hi

This code works

sql = "ALTER TABLE transact ADD COLUMN discountamt Long Integer"
cnn1.Execute sql

Thanks

[gibra]

This code works

Sure ???