Solved

Create Table syntax from SQL2000 to dBase

Posted on 2004-10-06
3
338 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
  • 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 500 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

830 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