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

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

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.

Since it's the access db where the subquery would be running, don't I need to do this?

where did the word "AS" come from?

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

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.