?
Solved

Create Table syntax from SQL2000 to dBase

Posted on 2004-10-06
3
Medium Priority
?
344 Views
Last Modified: 2012-06-21
I am using VB6 with ADODB.Recordset to create a dBase .dbf file.  This is the table structure in SQL2000.
      CREATE TABLE Test
      (
            LastName varchar(50),
            FirstName varchar(50),
            DOB datetime,
            Age int,
            Balance money,
            Hours numeric(18,2),
            Comment varchar(2000),
            Note text
      )

I can create the .dbf using:
      CREATE TABLE Test
      (
            LastName char(50),
            FirstName char(50)
      )


What is the syntax for the rest of the fields/types?  What is the limitations of the dBase fields.  Is char() limited to 254 characters?

Any info is appreciated.  Thanks.

0
Comment
Question by:sndip
[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
  • 2
3 Comments
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12247433
CREATE TABLE tblCustomers (
...CustomerID INTEGER NOT NULL,
...[Last Name] TEXT(5) NOT NULL,
...[First Name] TEXT(50) NOT NULL,
...Phone TEXT(10),
...Email TEXT(50))

Here are some of the jet data types
TEXT TEXT, TEXT(n), CHAR, CHAR(n), ALPHANUMERIC, ALPHANUMERIC(n), STRING, STRING(n), VARCHAR, VARCHAR(n), NTEXT(n), NCHAR, NCHAR(n), CHAR VARYING, CHAR VARYING(n), CHARACTER VARYING, CHARACTER VARYING(n), NATIONAL CHAR, NATIONAL CHAR(n), NATIONAL CHARACTER, NATIONAL CHARACTER(n), NATIONAL CHAR VARYING, NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING, NATIONAL CHARACTER VARYING(n) Up to 255 characters, 2 bytes per character unless compressed
MEMO LONGTEXT, LONGCHAR, NOTE, NTEXT 65,535 characters; 2.14 GB if not binary data

I hope this helps you
Al
0
 

Author Comment

by:sndip
ID: 12260986
Can you give me the jet sql equivalent to:
 CREATE TABLE Test
     (
          LastName varchar(50),
          FirstName varchar(50),
          DOB datetime,
          Age int,
          Balance money,
          Hours numeric(18,2),
          Comment varchar(2000),
          Note text
     )
0
 
LVL 4

Accepted Solution

by:
Excalibur_Software earned 2000 total points
ID: 12273020
>>Can you give me the jet sql equivalent to:

SQL                                                                          Access
>> CREATE TABLE Test                                  CREATE TABLE Test
>>     (                                                                (
>>          LastName varchar(50),                              LastName TEXT(50),
>>          FirstName varchar(50),                             FirstName TEXT(50),
>>          DOB datetime,                                          DOB DATETIME,
>>          Age int,                                                   Age INTEGER,
>>          Balance money,                                        Balance CURRENCY,
>>          Hours numeric(18,2),                                Hours INTEGER, I am not sure what you want here
>>          Comment varchar(2000),                          Comment LONGTEXT,
>>          Note text                                                 myNote LONGTEXT Here I changed your name Note is a resurved word
>>     )                                                                 )

This is a copy of the code I wrote to test out your query in Access 2003, All you need to do is copy it into the qury manager and run it. I created a table with this so I know it works how ever I created 2 memo fields because varchar can only have a max of 255 characters. below are the jet data types

Jet Data Type       Synonyms Storage                                                                                                   Size
TEXT                  TEXT, TEXT(n), CHAR, CHAR(n), ALPHANUMERIC, ALPHANUMERIC(n),                          Up to 255
                          STRING, STRING(n), VARCHAR, VARCHAR(n), NTEXT(n), NCHAR, NCHAR                      characters, 2 bytes
                          (n), CHAR VARYING, CHAR VARYING(n), CHARACTER VARYING,                                    per character unless
                          CHARACTER VARYING(n), NATIONAL CHAR, NATIONAL CHAR(n),                                  compressed
                          NATIONAL CHARACTER, NATIONAL CHARACTER(n), NATIONAL CHAR
                          VARYING, NATIONAL CHAR VARYING(n), NATIONAL CHARACTER
                          VARYING, NATIONAL CHARACTER VARYING(n)
MEMO                 LONGTEXT, LONGCHAR, NOTE, NTEXT                                                                        65,535 characters;
                                                                                                                                                         2.14 GB if not binary data
                                                                                                                                                           
NUMERIC            TINYINT INTEGER1, BYTE                                                                                            1 byte
                         SMALLINT SHORT, INTEGER2                                                                                       2 bytes
                         INTEGER LONG, INT, INTEGER4                                                                                    4 bytes
                         REAL SINGLE, FLOAT4, IEEESINGLE                                                                              4 bytes
                         FLOAT DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER                                                            8 bytes
                        DECIMAL NUMERIC, DEC                                                                                               17 bytes

I hope this helps you

Thank you for your time
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Progress
Suggested Courses

800 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