Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2006
    Posts
    34

    Unanswered: Insert into external table using vba

    Can anyone tell me why this does not work ?

    Thanks in advanced

    Dim SQL As String

    SQL = "INSERT INTO tblcategory ( cat_name )IN 'C:\cmr\office.mdb'"
    SQL = SQL & "SELECT tblcategory.cat_name"
    SQL = SQL & "FROM tblcategory;"

    CurrentDb.Execute SQL

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SQL needs spaces.
    although i don't guarantee it will work, this will have a much better chance:

    SQL = "INSERT INTO tblcategory ( cat_name ) IN 'C:\cmr\office.mdb'"
    SQL = SQL & " SELECT tblcategory.cat_name"
    SQL = SQL & " FROM tblcategory;"

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2006
    Posts
    34
    Thanks but still no results ?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry!
    not my thing - replying just to bump your question up to the top.
    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Posts
    90
    It is to do with the spaces. Your code needs to be:

    Code:
    Dim SQL As String
    
    SQL = "INSERT INTO tblcategory ( cat_name )" & " " & "IN 'C:\cmr\office.mdb' "
    SQL = SQL & "SELECT tblcategory.cat_name "
    SQL = SQL & " FROM tblcategory;"
    
    CurrentDb.Execute SQL

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try:

    SQL = "INSERT INTO tblcategory ( cat_name ) IN 'C:\cmr\office.mdb'"
    SQL = SQL & " (SELECT tblcategory.cat_name"
    SQL = SQL & " FROM tblcategory);"
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Posts
    90
    Have you tesed that code?

    It doesn't work.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Goldy
    Have you tesed that code?

    It doesn't work.
    Well it looks like we're all wrong ... Yours neither.

    What is the intent here? Are we trying to push the data in the table to another database or are we pulling from the other database????


    What's the answer James????
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2004
    Posts
    90
    Mine works...I'm pretty sure.

    Sorry for being so curt. I didn't mean it, it's just the way I come accross sometimes (I'm pretty sure I have aspergers, but I've never been diagnosed)

    Anyway..the only way I can read it from his code is that he's trying to push the data into a table in another database.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Goldy
    Mine works...I'm pretty sure.

    Sorry for being so curt. I didn't mean it, it's just the way I come accross sometimes (I'm pretty sure I have aspergers, but I've never been diagnosed)

    Anyway..the only way I can read it from his code is that he's trying to push the data into a table in another database.
    Sorry to say but, outside of some cosmetics, your improvement is identical to izy's ... I'll have to set up a test suite ...


    I was wrong ... It does work. Did a test in QBE with no probs ... Ex:

    INSERT INTO Category ( CatName ) IN 'C:\Mike Misc\SendTo.mdb'
    SELECT Category.CatName
    FROM Category;

    So ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Posts
    90
    Quote Originally Posted by M Owen
    Sorry to say but, outside of some cosmetics, your improvement is identical to izy's ..
    Don't be sorry. I agreed that Izy had the correct reason as to why the code wasn't working...the lack of spaces for the SQL code.

    He gave a solution, but it didn't go far enough with the spaces.

    I gave one that did.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...so despite the discussion (and an interesting view on how to add a space to a literal string), jamesf still forgot a space.


    jamesf: do a
    debug.print SQL
    after you build the SQL, just to (Ctrl-G) see how it looks: you are dropping a space! copy/paste the debug output here if the issue is still not yet resolved

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry Goldy, i dont understand the idea that
    "blah " <> "blah" & " "

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Sep 2006
    Posts
    34
    Hi all

    Sorry havent replied sooner been out of the office. What I am trying to do is :
    I have a front end that links to different backends. these backends can be created at runtime and some information like categories can be copied accross from a previoulsy created one to the new backend.

    Ideally the table data to be copied would be set by a variable eg VarPath(C:\cmr\office.mdb).

    So in short the answer is I want to push that data into another database.

    I dont seem to be able to see any information from debug.print sql

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Post

    I dont seem to be able to see any information from debug.print sql

    Dim SQL As String
    SQL = "INSERT INTO blah blah
    SQL = SQL & "SELECT blah blah
    SQL = SQL & "FROM blah blah
    debug.print SQL '<<<<<<<<<<< DELETE ME LATER
    beep '<<<<<<<<<<< DELETE ME LATER
    stop '<<<<<<<<<<< DELETE ME LATER

    CurrentDb.blah blah

    Ctrl-G when it beeps and post what you see

    izy
    currently using SS 2008R2

Posting Permissions

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