locked
How to use sql insert and left function is ms Access VBA module RRS feed

  • Question

  • I have table called main table and there is 2 columns in it "Department name" and "DeptID" Dept name column has values such as

    DP302-Finance and DeptID column is empty. i want to write sql insert statement that will just select first 5 characters from Department name and insert into DeptID. for e.g DP302-Finance in Deptarment name should be copied into DeptID columns as DP302. I am using following sql in access module but i am getting synatx error in insert into statement.

    Sub che()
    Dim objDB As DAO.Database
    Dim strsql As String
    strsql = "insert into main .(DeptID) (select left [Department name, 5]as DeptId from main)"
    Set objDB = CurrentDb()
      objDB.Execute strsql, dbFailOnError
      Set objDB = Nothing
    End Sub

    Friday, November 12, 2010 10:01 PM

Answers

  • Hi zaveri cc

    strsql = "insert into main .(DeptID) (select left [Department name, 5]as DeptId from main)"

    So the query you execute reads

    insert into main .(DeptID)
    (select left [Department name, 5]as DeptId from main)

    There are various syntactical errors in this query, but there is also
    a logical error. You don't want to insert new rows, you want to update
    values in existing rows. For that, you would need an UPDATE statement. Like this:

    UPDATE main
    SET   DeptID = LEFT([Department name], 5);

    However - if DeptID is always equal to the first five characters of
    department name, it's better to create this column as a computed
    column. Since the column already exists, you'll have to drop and then recreate it. You can do this as follows:

    ALTER TABLE main
      DROP COLUMN DeptID;
    ALTER TABLE main
      ADD DeptID AS LEFT([Department name], 5);

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by WeiLin Qiao Monday, November 15, 2010 11:13 AM
    • Marked as answer by Tom Li - MSFT Wednesday, November 24, 2010 2:41 AM
    Saturday, November 13, 2010 4:12 PM

All replies

  • I'm not answering your question because I haven't worked with VBA in quite awhile, but I wanted to point out that you should consider creating a default value of LEFT([Department Name]) for the column. You can do that in SQL Server Management Studio, using the table design feature. It's easy, simple, reliable, and you don't pass redundant information accross the network - slowing everything down (slightly). That's the SQL Server way to approach that business problem.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Friday, November 12, 2010 11:06 PM
    Answerer
  • Hello,

    as Rick wrote, the field name in bracket

    Try it with this:
    - No dot before the field list at insert
    - use LEFT([Field], 5)
    - No braces around the SELECT statement

    strsql = "insert into main (DeptID) select left([Department name], 5]) from main"


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Saturday, November 13, 2010 7:13 AM
  • Hi zaveri cc

    strsql = "insert into main .(DeptID) (select left [Department name, 5]as DeptId from main)"

    So the query you execute reads

    insert into main .(DeptID)
    (select left [Department name, 5]as DeptId from main)

    There are various syntactical errors in this query, but there is also
    a logical error. You don't want to insert new rows, you want to update
    values in existing rows. For that, you would need an UPDATE statement. Like this:

    UPDATE main
    SET   DeptID = LEFT([Department name], 5);

    However - if DeptID is always equal to the first five characters of
    department name, it's better to create this column as a computed
    column. Since the column already exists, you'll have to drop and then recreate it. You can do this as follows:

    ALTER TABLE main
      DROP COLUMN DeptID;
    ALTER TABLE main
      ADD DeptID AS LEFT([Department name], 5);

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by WeiLin Qiao Monday, November 15, 2010 11:13 AM
    • Marked as answer by Tom Li - MSFT Wednesday, November 24, 2010 2:41 AM
    Saturday, November 13, 2010 4:12 PM