?
Solved

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

Posted on 2005-03-14
9
Medium Priority
?
322 Views
Last Modified: 2010-04-23
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
Comment
Question by:bemson
[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
  • 5
  • 4
9 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13541736
You can open an Access table in design mode and check all the data types available...
0
 

Author Comment

by:bemson
ID: 13551062
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
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13552066
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!

 

Author Comment

by:bemson
ID: 13578770
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
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13580861
>> 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
 

Author Comment

by:bemson
ID: 13587753
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
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13588778
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
 

Author Comment

by:bemson
ID: 13615595
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
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 500 total points
ID: 13618862
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

741 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