Sql ошибка синтаксиса create table

I received a MySQL data dump and am trying to insert the data into a set of temporary tables. The creation statement for the first table is shown below. When I run this I receive the error: «You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp_books'( 'ID'int( 11 ) NOT NULL AUTO_INCREMENT, 'start'varchar( 20 ) ' at line 1«. I’ve checked the documentation for MySQL syntax, and I don’t see that the problem is.

CREATE TABLE 'temp_books' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'start' varchar(20) NOT NULL,
  'customer_id' int(11) NOT NULL DEFAULT '0',
  'total_num' int(11) NOT NULL,
  'amount' double(5,2) NOT NULL DEFAULT '0.00',
  'changed' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ('ID'),
  UNIQUE KEY 'start' ('start')
) ENGINE=MyISAM AUTO_INCREMENT=4853 DEFAULT CHARSET=latin1;

asked Jan 18, 2009 at 2:37

CREATE TABLE temp_books (
  ID int(11) NOT NULL AUTO_INCREMENT,
  start varchar(20) NOT NULL,
  customer_id int(11) NOT NULL DEFAULT '0',
  total_num int(11) NOT NULL,
  amount double(5,2) NOT NULL DEFAULT '0.00',
  changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ID),
  UNIQUE KEY start (start)
) ENGINE=MyISAM AUTO_INCREMENT=4853 DEFAULT CHARSET=latin1;

2

I had to delete the quote marks, as well as the default for the changed field, as well as the default charset. Hopefully that won’t affect the data.

Your CREATE VIEW statement will work if executed using an OLEDB connection, e.g., from an external application or via the CurrentProject.Connection object in Access VBA. However, Access does not accept CREATE VIEW statements when entered into the SQL pane of the Query Designer within Access itself (which uses a DAO connection, not OLEDB). In that context it is expecting a CREATE TABLE statement (as indicated by the error message.)

For more details see a related question here:

Attempting CREATE VIEW in Access gives «Syntax error in CREATE TABLE statement»

Your CREATE VIEW statement will work if executed using an OLEDB connection, e.g., from an external application or via the CurrentProject.Connection object in Access VBA. However, Access does not accept CREATE VIEW statements when entered into the SQL pane of the Query Designer within Access itself (which uses a DAO connection, not OLEDB). In that context it is expecting a CREATE TABLE statement (as indicated by the error message.)

For more details see a related question here:

Attempting CREATE VIEW in Access gives «Syntax error in CREATE TABLE statement»

Answers

• • Edited by
    Van Dinh
    Monday, November 11, 2013 1:33 PM
    Typos
  • Marked as answer by
    Fei XueMicrosoft employee
    Sunday, November 17, 2013 1:31 PM

• Build + Setup a pass though query in the query builder.

  Then use this code:
      strSql = your sql create command as you have

     Set qdfPass = CurrentDb.QueryDefs(«MyPass»)
     qdfPass.SQL = strSql
     qdfPass.Execute

  So once you have a pass-through query built then it rather easy to send commands to the server as per above, and even better is the above is min amount of code.

  Albert D. Kallal (Access MVP)
  Edmonton, Alberta Canada 

  • Marked as answer by
    Fei XueMicrosoft employee
    Sunday, November 17, 2013 1:33 PM

• Rhett

  You’re mixing apples and oranges. Thois example uses either user/pwd or trusted connection.

      Dim cnn As ADODB.Connection
      Dim strSQL As String
      Dim cmd As ADODB.Command
      
      Set cnn = New ADODB.Connection
  '    'OLEDB:
  '    'Standard:
  '    strConnect = "Provider=sqloledb;Data Source=MySQLServer;Initial Catalog=MyDatabase;" _
  '        & "User Id=sa;Password=asdasd;"
      
      'Trusted:
      cnn.ConnectionString = "Provider=sqloledb;Data Source=MySQLServer;" _
              & "Initial Catalog=MyDatabase;Integrated Security=SSPI;"
      cnn.ConnectionTimeout = 30
      cnn.Open
      
      strSQL = "CREATE TABLE zzzzTest(ConID int IDENTITY(1,1) PRIMARY KEY, " _
          & "LastName nvarchar(50), FirstName nvarchar(50))"
      
      
      Set cmd = New ADODB.Command
      cmd.ActiveConnection = cnn
      cmd.CommandType = adCmdText
      cmd.CommandText = strSQL
      Debug.Print cmd.CommandText
      cmd.Execute
      cnn.Close
      Set cmd = Nothing
      Set cnn = Nothing
      

  ---

  Bill Mosca
  www.thatlldoit.com
  http://tech.groups.yahoo.com/group/MS_Access_Professionals

  • Marked as answer by
    Fei XueMicrosoft employee
    Sunday, November 17, 2013 1:33 PM