Solved

Create Table syntax from SQL2000 to dBase

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
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 …
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…

785 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