Solved

Import text file from MySQL server using MS Access pass-through query.

Posted on 2011-02-20
3
397 Views
Last Modified: 2012-05-11
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?

Thank you!
Kelly
0
Comment
Question by:kellysteevens
  • 2
3 Comments
 
LVL 9

Expert Comment

by:borki
ID: 34938931
I don't know the implementation details of the MySQL ODBC driver, but I suspect it was designed to retrieve data using normal SQL. LOAD DATA is a non standard SQL extension and is likely not supported by the ODBC driver. Someone else may have a better explanation or knowledge...

I suggest you try the approach you hinted: Export your Access data to a file, then FTP that to the server then import it on the server.

Another approach, which IMHO would be much better is to write a webservice that you can feed from within Access and update the website based database directly.

0
 

Accepted Solution

by:
kellysteevens earned 0 total points
ID: 34958011
I did the command line approach and the thread for that was located here:

http://www.experts-exchange.com/OS/Linux/Q_26840408.html#a34958007
0
 

Author Closing Comment

by:kellysteevens
ID: 34990902
Post other thread on workaround.  See referred link.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 43
Close Print Preview button not active 12 35
MS Access Tables Linking 6 37
PHP - AJAX and MySQL it works only if the value is a number 12 33
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now