Solved

Create Table syntax from SQL2000 to dBase

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

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.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

739 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