Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: Error In Insert Into Statement Access To SQL

    Hi all

    Using Access 2010 and SQL Server 2012

    I added a database through SSMS and added a table named tblEmployee (dbo.tblEmployee)

    The table has 3 fields
    LName (nvarchar(50), null)
    FName (nvarchar(50), null)
    Code (nvarchar(50), null)

    The access table has 3 fields
    FName, text
    LName, text
    Code, text

    I found a code snippet here to insert from the Access table to the SQL table
    UtterAccess Discussion Forums > Appending Access table to SQL Server table (and vice-versa) usin

    The code is generating this error
    Run-time error '3134':
    Syntax error in INSERT INTO statement
    In the connection string below, I'm not sure what the network portion is, I left as I found it from the tutorial.

    Any ideas?

    thx
    w

    Code:
    Option Compare Database
    Sub AppToSQL()
    
    Dim strODBCConnect As String
    Dim strSQL As String
    'Code from:
    'http://www.utteraccess.com/forum/lofiversion/index.php/t1639013.html
    
    'Connection string
    strODBCConnect = "[ODBC" & _
              ";DRIVER=SQL Server;SERVER=SIM-PC\NET01" & _
              ";DATABASE=tstDatabase;Network=DBMSSOCN" & _
              ";Trusted_Connection=yes;]"
    
    strSQL = "INSERT INTO " & strODBCConnect & ".dbo.tblEmployee" & _
              "SELECT * FROM tblEmpl"
    '
    CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
    
    
    
    End Sub

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    First of all, what SQL statement is being generated?

    Secondly, if you are using Access as a front end to SQL Server, I think that you need to use the CurrentProject object, rather than CurrentDatabase.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Weejas,

    The SQL statement is
    Code:
    strSQL = "INSERT INTO " & strODBCConnect & ".dbo.tblEmployee" & _
              "SELECT * FROM tblEmpl"
    I tried to change CurrentDb to CurrentProject, that generated this error
    Run-time error '438':
    Object doesnt support this property or method
    thx
    w

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    What you've given me is the line to create a SQL statement. I asked for the actual statement. You can get this by adding the following line immediately after the one you've quoted:
    Code:
    debug.Print strSQL
    This will output the contents of variable strSQL to the Immediate window for inspection.

    My other suggestion was a guess based on previous experience, although I'm having to juggle several versions of applications here at the moment - sorry about that.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Weejas,

    I ran the debug.print and saw that a space was missing after the name of the table
    I fixed that, ran the code, no errors

    Ran a query on the SQL Database. All records were written from the Access table to the SQL table.

    Awesome!
    Thanks for your help

    Final code below in case it helps anyone

    thx
    w
    Code:
    Option Compare Database
    Sub AppToSQL()
    
    Dim strODBCConnect As String
    Dim strSQL As String
    'Code from:
    'http://www.utteraccess.com/forum/lofiversion/index.php/t1639013.html
    
    'Connection string
    strODBCConnect = "[ODBC" & _
              ";DRIVER=SQL Server;SERVER=SIM-PC\NET01" & _
              ";DATABASE=tstDatabase;Network=DBMSSOCN" & _
              ";Trusted_Connection=yes;]"
    
    strSQL = "INSERT INTO " & strODBCConnect & ".dbo.tblEmployee " & _
              "SELECT * FROM tblEmpl"
    '
    CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
    Last edited by goss; 01-14-13 at 09:05. Reason: Typo

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •