Import text file from MySQL server using MS Access pass-through query.
Posted on 2011-02-20
I should start off saying I'm not the database admin. We house our MySQL database and web server remotely so sometimes when I'm trying to do things in MySQL and it requires permissions or understanding how the database was set up, I'm clueless.
I have a file sitting on the server that houses our MySQL database. If I go to PHPMyAdmin and type in this command, I can load the file into a MySQL table:
LOAD DATA LOCAL INFILE "/home/tmp/load_data.txt" INTO TABLE tempMyTable FIELDS TERMINATED BY "," ENCLOSED BY '"'
It took me awhile to figure out where to put the file in order to get this statement to work. Finally, I figured out to put it on the MySQL server via FTP and then run the statement.
So now, I'm trying run this statement as a pass-through query in Access, but I'm back to the same error that I was getting before FTP'ing the file:
Error 002: File Not Found
I run queries all the time through Access hitting the MySQL database. I also run lots of pass-through queries and everything works except for this one. I should add that I am connecting to the database through an ODBC MySQL 5.1 driver. That probably has something to do with it?
I need this to be a pass-through query because I have a bunch of other stuff in Access that needs to run too. I guess I could run the statement from command line but I don't know how to do that.
:o( This is driving me crazy. Anyone know what I'm doing wrong?