Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: Select Into Query

    I'm trying to ingest data from an access database into mysql using adodb connection

    The following errors saying that the table in which I'm trying to ingest the data, is undeclared. The table structure matches with what I'm trying to import. What am I missing here?

    strSql = "Select distinct empcode,s_datetime into accesscontrol from [;Database=\\Mnemosyne\Administration\Attendance logs\universal.mdb;PWD=jaba67].transactions As T where T.s_datetime>=#" & startDate & "# and T.s_datetime<=#" & DateAdd("d", 1, endDate) & "#"

    db.Execute strSql

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wussupbuddy
    What am I missing here?
    you are missing the fact that SELECT INTO is invalid in mysql

    use CREATE SELECT instead

    da manual is your friend



    p.s. and here's a tip: mysql does not use octothorpes to delimit its date strings, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    6
    Thanks. So I changed it to

    strSql = "create table accesscontrol as Select distinct empcode,s_datetime from [;Database=\\Mnemosyne\Administration\Attendance logs\universal.mdb;PWD=jaba67].transactions As T where T.s_datetime>=#" & startDate & "# and T.s_datetime<=#" & DateAdd("d", 1, endDate) & "#"
    db.Execute strSql

    I'm still getting an error - run time error -2147467259(80004005)

    Though I do have access on this DB.

  4. #4
    Join Date
    Apr 2009
    Posts
    6
    Quote Originally Posted by r937
    p.s. and here's a tip: mysql does not use octothorpes to delimit its date strings, either
    Since it's the access db where the subquery would be running, don't I need to do this?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wussupbuddy
    I'm still getting an error
    where did the word "AS" come from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wussupbuddy
    Since it's the access db where the subquery would be running, don't I need to do this?
    not sure

    you did not really outline all the details of this setup

    your first post actually sounds like you're running the CREATE in mysql

    if you're running it in Access, i'm not sure the CREATE SELECT approach will work at all

    try going to mysql and creating the table manually (with a CREATE statement that you code yourself), then do an INSERT SELECT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2009
    Posts
    6
    Sorry abt the half cooked details. I have an access database from which I need to import data on a monthly basis into MySQL. So thats what the qury is supposed to do. I was originally using access so the original query worked without any problems.

    I have removed the "as" text. I put it in there since that was the syntax given on one of the sites. Thanks for your help.

Posting Permissions

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