0

We're using the Microsoft Jet OLEDB provider to insert data into a legacy system.

This system requires that we insert data by creating a DBF file, which has the format of:

employee Numeric (10,0),
jobcode Numeric (10,0),
date date

So, we are doing the following:

string strConnDbase = @"Provider = Microsoft.Jet.OLEDB.4.0" +
    ";Data Source = " + ruta +
    ";Extended Properties = dBASE IV" +
    ";User ID=Admin;Password=;";

Then, we run a command like:

string sql = "CREATE TABLE 20110112 ( EMPLOYEE Numeric(10,0), JOBCODE Numeric(10,0), DATE Date)";

Unfortunately, this "sql" statement is not running. IE, the column named "date" is a keyword, so we cannot create the table.

We've tried escaping (single and double quotes) the column name, but that doesn't work either.

How can we build a table with the column being named "date"?

Thanks!

-- Anthony

Anthony
  • 167
  • 2
  • 16

2 Answers2

3

Try square brackets:

CREATE TABLE 20110112 ( EMPLOYEE Numeric(10,0), JOBCODE Numeric(10,0), DATE [Date])

I don't have an OLEDB target handy, so this is just a guess, FWIW.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
Chris B. Behrens
  • 6,255
  • 8
  • 45
  • 71
0

You can't create a table starting with a number... and it's really bad practice to create column names based on reserved words (date).

Just prefix with SOMEthing... even if its just "T" for "Table".

create table T20110112

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • 1
    Jet/ACE allows you to create a table whose name starts with a digit, or is entirely numbers. I just ran `CREATE TABLE 211123` successfully in the Access QBE, and it created the table without any problems. Whether or not it's good practice is a different question -- but it is certainly possible. – David-W-Fenton Feb 08 '11 at 05:14