• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

sql Info Required for field type examples for Create & Alter Table using vb code for Access db

I am looking for a reference to the range of field types available to either create or alter an Access data base

eg
Create table
--------------

field type  string(20)
               number (not null)
               decimal
               autonumber
               number (double)
etc

Alter Table
-------------

Alter Table customers Add ID Decimal

and any of the above
etc

What is the entire availability of types that can be coded in vb.net

also any reference to examples of same would be appreciated

0
bemson
Asked:
bemson
  • 5
  • 4
1 Solution
 
arif_eqbalCommented:
You can open an Access table in design mode and check all the data types available...
0
 
bemsonAuthor Commented:
I am already aware of those types, it is the vb syntax for each as they have unique coding for each type

I know how to put in a new text field & a number but the code fails when I try to put in decimal

That is the reason for the question so I have a library of code in vb for each type available
0
 
arif_eqbalCommented:
Hi bemson
I think you mean the mapping between the VB Types and the Access Types is it ??

I once wrote a program that created database on the fly and converted Oracle database to SQL Server and vice versa. I used the following function to map database and VB data types but it does not contain Access mapping. However this will give you a feel of it, like waht VB type should be what DataBase type and since you can get all Access types from Access you can map.

Hope this is what you want...




    Private Function GetMappedColumns(ByVal DT As String, ByVal TargetDataBase As String) As String

'Here DT is the VB Type, and return value is database type

        If TargetDataBase = "SQLSERVER" Then
            Select Case DT
                Case "System.Boolean"
                    Return "bit"
                Case "System.Byte"
                    Return "binary"
                Case "System.Char"
                    Return "char"
                Case "System.DateTime"
                    Return "DateTime"
                Case "System.Decimal"
                    Return "Numeric"
                Case "System.Double"
                    Return "float"
                Case "System.Int16"
                    Return "smallint"
                Case "System.Int32"
                    Return "int"
                Case "System.Int64"
                    Return "bigint"
                Case "System.Object"
                    Return "sql_variant"
                Case "System.String"
                    Return "nvarchar"
            End Select
        ElseIf TargetDataBase = "ORACLE" Then
            Select Case DT
                Case "System.Boolean"
                    Return "Char"
                Case "System.Byte"
                    Return "char"
                Case "System.Char"
                    Return "char"
                Case "System.DateTime"
                    Return "Date"
                Case "System.Decimal"
                    Return "Number"
                Case "System.Double"
                    Return "Number"
                Case "System.Int16"
                    Return "Number"
                Case "System.Int32"
                    Return "Number"
                Case "System.Int64"
                    Return "long"
                Case "System.Object"
                    Return "BLOB"
                Case "System.String"
                    Return "varchar2"
            End Select
        End If
    End Function
0
Technology Partners: 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!

 
bemsonAuthor Commented:
Thanks for taking an interest in my problem, however what I am looking for is this:

SQL="Alter Table Employee Add Column Salary Decimal"

this next sql works, but decimal does not

SQL="Alter Table Employee Add Column Phone Text(30)"

I am also after all the variations & sysntax for "Not Null" etc so when I need something, I can just look up the library of all the options.
0
 
arif_eqbalCommented:
>> SQL="Alter Table Employee Add Column Salary Decimal"

does not work, that's because you are working with Access and acces does not have a Decimal type you need to either use "Number" or "Currency"
As for all such variations, I wonder if anyone would have a ready made list, but as I said you can easily make one for you, look at Access dataTypes from the drop down while creating a table in Access and check out. And for the options like NOT NULL etc. look at the Tab strip (in design mode of Access for creating table)

0
 
bemsonAuthor Commented:
The problem is this, I have created an app & sold some copies.

It has a database which now needs to be updated by sending an email with new app to add a new field

On my own computer I have used access to create a Number field with 6 decimal places

I now need to create that field in the remote databases I have distributed

In future I may need to do this for any option that is available in Access

I have found the following on the web

create table employers (
    id BIGINT not null,
    name VARCHAR(255),
    primary key (id)
)

create table employment_periods (
    id BIGINT not null,
    hourly_rate NUMERIC(12, 2),
    currency VARCHAR(12),
    employee_id BIGINT not null,
    employer_id BIGINT not null,
    end_date TIMESTAMP,
    start_date TIMESTAMP,
    primary key (id)
)

create table employees (
    id BIGINT not null,
    firstName VARCHAR(255),
    initial CHAR(1),
    lastName VARCHAR(255),
    taxfileNumber VARCHAR(255),
    primary key (id)
)

alter table employment_periods
    add constraint employment_periodsFK0 foreign key (employer_id) references employers
alter table employment_periods
    add constraint employment_periodsFK1 foreign key (employee_id) references employees
create sequence employee_id_seq
create sequence employment_id_seq
create sequence employer_id_seq
 

however I do not know what 12,2 is for the numeric & I suspect this is not for Access but something else, but it is along similar to what I am looking for, including whether to allow zero values etc.

As you suggest all the options are availble for peruse in Access, but the question still remains

"What is the syntax for doing this?"

you could spend hours trying out different syntax to see if it works.

Somebody somewhere must have a table of syntax variations, maybe I need to put this question in the database area.

thanks again for your suggestions
0
 
arif_eqbalCommented:
Hi bemson

So this is what you want, but unfortunately your choice of database is not very versatile. I am afraid Access does not give you as much flexibility.

create table employment_periods (
    id BIGINT not null,
    hourly_rate NUMERIC(12, 2),
    currency VARCHAR(12),
    employee_id BIGINT not null,
    employer_id BIGINT not null,
    end_date TIMESTAMP,
    start_date TIMESTAMP,
    primary key (id)
)

This is for SQL Server, and the Numeric (12,2) tells the database to use 12 digits and store upto 2 decimal places. In case you want the same for Access, you need to open Access and modify the Column in Design Mode. You can not get as much flexibility in Access but you can somewhat match it.

Access provides only limited options when using Create table statement.
Here's an eg. for Access that might interest you

create table TABLE1 (
     Field1 Double,
     Field2 Integer,
     Field3 Single,
     Field4 Currency,
     Field5 Long
)

This statement creates a table with Five fields
1. Field1 -> it will store upto 15 digits including Decimal values.
2. Filed2 -> It will store upto 15 digits including Decimal values.
3. Field3 -> It will store on 7 digits no Decimal
4. Field4 -> It will store 2 Decimal places
5. Field5 -> It is Long Integer it will also not store any decimal values

Now the first two i.e. Double & Single are of some importance here. Double stores a total of 15 digits including decimal values.
So if there are 5 digits to the Left of Decimal there can be 10 to the Right.
Or if there are 7 to the Left of decimal point there can be 8 to the Right.
Now you can control how many values should be allowed to the right of decimal (something like Field1 Numeric(15,4) in SQL Server) i.e. total 15 and out of that 4 should be the decimal point. But unfortunately you can not do it through a Create (or Alter ) statement, for it you need to open the table in Access Design mode and set its property.
Howevere if you use Create Statement you need to let Access decide how many digits after decimal to store...

The same applies for Single dataType only thing is it takes only 7 digits.

So I think you'll need to deal with these options only.
In case you do not want any decimal places go for Integer or Long
In case you need a decimal points go for Double or Single
And in case there should be only 2 values after decimal go for currency




0
 
bemsonAuthor Commented:
This seems to be the best info I will get, I am surprised that DotNet being a Microsoft Product can't link with access to provide better control

However, you get the Points next time I visit

I suspect you made a slight error in stating that Field2 would hold decimal as it is Integer!

you might want to comment on that.

Thanks again for your interest
0
 
arif_eqbalCommented:
I am sorry
I just copy pasted the Line for Field one and forgot to modify it
Well Integer you can as well understand will hold only Integer values
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now