Solved

Create Table syntax from SQL2000 to dBase

Posted on 2004-10-06
3
336 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
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…
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

943 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now