472,371 Members | 1,512 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,371 software developers and data experts.

equivalent of varchar2?

Hi all,

What is the equivalent of VARCHAR2 in access?

thanks
hilz
Nov 13 '05 #1
14 10653
VARCHAR-2 ????

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"hilz" <no**@y.com> wrote in message
news:0L********************@comcast.com...
Hi all,

What is the equivalent of VARCHAR2 in access?

thanks
hilz

Nov 13 '05 #2
> VARCHAR-2 ????

Is there such a thing as VARCHAR-2 ????
Incase my question was not clear, here are some more details:

In Oracle, there is a datatype called VARCHAR2. It can hold more than the
length of the field.
for example ;
create table test (name VARCHAR(10));
Now if you insert into this table a row with name='a string that is longer
than 10 characters'
it will accept it and store it. I beleive the length (10) is just for
optimizing, and should be close to what you expect most of the values in
that field to be.

And if you insert into the table a row with name='abc' it will not pad the
string with spaces, and thus saving storage space.

Now in Access, declaring a filed as CHAR will have a fixed length that
cannot be exceeded, and if the value was shorter than the length, it is
padded with spaces to fill the whole length of the field, and that is not
really very desirable.

If i create my access table using an SQL statement, rather than the table
designer, i can set the field as VARCHAR, but not VARCHAR2.
and VARCHAR in this case does not pad the short string with spaces, which is
good, but it does not allow a string that is longer than the column width.
so my question is: is there such a datatype in Access that is similar to
VARCHAR2?

I remember there was a memo filed, but I also remember that it is not
recommended to use that for just any string field.

thanks
hilz
Nov 13 '05 #3

"hilz" <no**@y.com> wrote in message
news:0L********************@comcast.com...
Hi all,

What is the equivalent of VARCHAR2 in access?

thanks
hilz


Text.
Nov 13 '05 #4
Such a beast doesn't exist in Access.

"hilz" <no**@y.com> wrote in message
news:K9********************@comcast.com...
VARCHAR-2 ????


Is there such a thing as VARCHAR-2 ????
Incase my question was not clear, here are some more details:

In Oracle, there is a datatype called VARCHAR2. It can hold more than the
length of the field.
for example ;
create table test (name VARCHAR(10));
Now if you insert into this table a row with name='a string that is longer
than 10 characters'
it will accept it and store it. I beleive the length (10) is just for
optimizing, and should be close to what you expect most of the values in
that field to be.

And if you insert into the table a row with name='abc' it will not pad the
string with spaces, and thus saving storage space.

Now in Access, declaring a filed as CHAR will have a fixed length that
cannot be exceeded, and if the value was shorter than the length, it is
padded with spaces to fill the whole length of the field, and that is not
really very desirable.

If i create my access table using an SQL statement, rather than the table
designer, i can set the field as VARCHAR, but not VARCHAR2.
and VARCHAR in this case does not pad the short string with spaces, which
is
good, but it does not allow a string that is longer than the column width.
so my question is: is there such a datatype in Access that is similar to
VARCHAR2?

I remember there was a memo filed, but I also remember that it is not
recommended to use that for just any string field.

thanks
hilz

Nov 13 '05 #5
On Tue, 18 Jan 2005 20:29:44 -0500, "hilz" <no**@y.com> wrote:
Hi all,

What is the equivalent of VARCHAR2 in access?


I don't think there is one. You'll have to roll your own.

mike
Nov 13 '05 #6
"hilz" <no**@y.com> wrote
Now in Access, declaring a filed as
CHAR will have a fixed length that
cannot be exceeded, and if the value
was shorter than the length, it is
padded with spaces to fill the whole
length of the field, and that is not
really very desirable.


If you are talking about a Text field in a Jet database, this is absolutely
incorrect. Text and Memo fields are all variable-length, requiring no more
storage than the contents plus necessary controlling information. If you
specify a length on a Text field, that will be used as a maximum length, but
the field will NOT be padded with blanks or null characters to that length.

If you are defining fields other than by using the Table design UI, then you
are (1) wasting effort and (2) perhaps limiting yourself by using things
that work better in some other database than in Access.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #7
"hilz" <no**@y.com> wrote in message
news:K9********************@comcast.com...
VARCHAR-2 ????
Is there such a thing as VARCHAR-2 ????
Incase my question was not clear, here are some more details:

In Oracle, there is a datatype called VARCHAR2. It can hold more than the
length of the field.
for example ;
create table test (name VARCHAR(10));
Now if you insert into this table a row with name='a string that is longer
than 10 characters'
it will accept it and store it. I beleive the length (10) is just for
optimizing, and should be close to what you expect most of the values in
that field to be.

And if you insert into the table a row with name='abc' it will not pad the
string with spaces, and thus saving storage space.

Now in Access, declaring a filed as CHAR will have a fixed length that
cannot be exceeded, and if the value was shorter than the length, it is
padded with spaces to fill the whole length of the field, and that is not
really very desirable.

If i create my access table using an SQL statement, rather than the table
designer, i can set the field as VARCHAR, but not VARCHAR2.
and VARCHAR in this case does not pad the short string with spaces, which

is good, but it does not allow a string that is longer than the column width.
so my question is: is there such a datatype in Access that is similar to
VARCHAR2?

I remember there was a memo filed, but I also remember that it is not
recommended to use that for just any string field.

thanks
hilz


hilz,

I think you are a bit confused. Both about how VARCHAR2 behaves in Oracle
and how Text behaves in Jet. Neither permits insertion of data greater in
length than the field width, nor does either pad fields as you have
described. Jet does not have a data type equivalent to CHAR.

Regards
Nov 13 '05 #8

"Larry Linson" <bo*****@localhost.not> wrote in message
news:ZHkHd.6235$ef6.1630@trnddc07...
"hilz" <no**@y.com> wrote
> Now in Access, declaring a filed as
> CHAR will have a fixed length that
> cannot be exceeded, and if the value
> was shorter than the length, it is
> padded with spaces to fill the whole
> length of the field, and that is not
> really very desirable.
If you are talking about a Text field in a Jet database, this is

absolutely incorrect. Text and Memo fields are all variable-length, requiring no more
storage than the contents plus necessary controlling information. If you
specify a length on a Text field, that will be used as a maximum length, but the field will NOT be padded with blanks or null characters to that length.
If you are defining fields other than by using the Table design UI, then you are (1) wasting effort and (2) perhaps limiting yourself by using things
that work better in some other database than in Access.

Larry Linson
Microsoft Access MVP


You were right. I was confused, and now i have a little better understanding
about this matter, but what is wrong with creating the schema in Access
using an SQL script that is executed throught an ado connection?

thanks.

Nov 13 '05 #9
hilz wrote:
Hi all,

What is the equivalent of VARCHAR2 in access?


Text. Unfortunately, it has a character limit of 255 versus 2000 for
Oracle's varchar2.

However, for fields greater than 255 characters you can use a memo data
type. Unsure of max characters, but it's effectively unlimited for my
usages, at least. The nice things about memo type fields as compared to
the equivalentlong data type in Oracle are:

1) You can enter where criteria

2) It can be sorted

3) You can have MORE THAN ONE per table!

8)

If you get around to using Access as a FE to an oracle DB, results
displayed from Oracle long data types cn be searched and filtered in a
data sheet/form.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #10
>
1) You can enter where criteria

2) It can be sorted

You can't sort or index on a memo field.

Neil

3) You can have MORE THAN ONE per table!

8)

If you get around to using Access as a FE to an oracle DB, results
displayed from Oracle long data types cn be searched and filtered in a
data sheet/form.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nov 13 '05 #11
Memo = up to 64,000 characters

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cs**********@coranto.ucs.mun.ca...
hilz wrote:
Hi all,

What is the equivalent of VARCHAR2 in access?


Text. Unfortunately, it has a character limit of 255 versus 2000 for
Oracle's varchar2.

However, for fields greater than 255 characters you can use a memo data
type. Unsure of max characters, but it's effectively unlimited for my
usages, at least. The nice things about memo type fields as compared to
the equivalentlong data type in Oracle are:

1) You can enter where criteria

2) It can be sorted

3) You can have MORE THAN ONE per table!

8)

If you get around to using Access as a FE to an oracle DB, results
displayed from Oracle long data types cn be searched and filtered in a
data sheet/form.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nov 13 '05 #12
Neil Ginsberg wrote:
You can't sort or index on a memo field.


Quite right, I was thinking of standard menu filters. It's still miles
better than the Oracle Long data type, though. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #13

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cs**********@coranto.ucs.mun.ca...
hilz wrote:
Hi all,

What is the equivalent of VARCHAR2 in access?


Text. Unfortunately, it has a character limit of 255 versus 2000 for
Oracle's varchar2.


VARCHAR2 can be up to 4000 characters.
Nov 13 '05 #14
PC Datasheet wrote:
Memo = up to 64,000 characters


1,535 short :-)

--
This sig left intentionally blank
Nov 13 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Tillu | last post by:
One of the column in a new table can be ename - varchar2(20) or hase_code_ename - number(11) Hash Code generated by JAVA. We are going to create non-unique index on this column as one frequent...
1
by: Mat Hess | last post by:
We are currently developing a new application. In this application, we have a table which will hold a large number of rows, where many text fields (one text field per row) will be stored. The users...
0
by: Sven Mayer | last post by:
Assume a database (e.g. Oracle) field is declared as a) NUMBER (length=10) b) NUMBER (length=26) c) VARCHAR2 (length=1) d) VARCHAR2 (length=50) e) DATE How do I retrieve the contents from...
5
by: DTB | last post by:
I am trying to convert a complex function from Oracle to SQL Server and have come across Oracle's Instr() function. I see SQL Server has CHARINDEX() which is similar, however it does not provide...
20
by: Praveen | last post by:
Hi All, I have an application for that we used oracle 8i as the db and we have written procedures using VARRAYs. Now, the requirement came to migrate it on DB2 UDB. Can anyone pls tell me is...
5
by: Mitchell Thomas | last post by:
I am using Oracle as a backend and have a field set as Varchar2(2000). The problem i have is access interprets this as a memo field and allows the user to type more than 2000 characters but does...
1
by: Mullin Yu | last post by:
hi, i'm using System.Data.OracleClient; but it seems not having Varchar2 and i use Varchar instead. Then when i call a Oracle Procedure update a table with varchar2, i got the following error: ...
5
by: moorthyvisu | last post by:
Hi, I have a below scenario: Table name : ClientCode. Fields are : startcode varchar2(20) & endcode varchar2(20); The values in these two fields should not overlap. For Ex:
2
by: ghe | last post by:
Good day to all, Does anyone here know how to convert the specified Oracle code below into PHP? Oracle Code: TYPE myTable IS TABLE OF varchar2(1) INDEX BY BINARY_INTEGER; If the Oracle code i...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.