1

I am not an expert in MS Access, I have been given a text files basically with over hundred thousand records in it and have been asked to load that text file into the MS access database. As I know MySQL, I uploaded that text file into the MySQL database first using normal Load text file command and it worked as expected. However, when in Access 2003, I go to file menu to Get external data and browse the text file, it just don't do anything after I select the file.

I have tried loading through SQL dump, I created a table, switched to the SQL query but I can not copy paste all of those insert commands in it, as a matter of fact it says value is too big to be entered and I can not paste them one by one as the record number is huge.

Any one help? I will be grateful.

Improve this question

3 Answers 3

Reset to default
2

It's not clear to me what would cause you to be unable to import the text file directly. You'll have to give more details on what you're attempting in Access and where it's going wrong.

But if you've got the data in MySQL already and want to import from there, create a DSN for your MySQL database, then use IMPORT from ODBC, point to the DSN, and you'll be able to import the table from MySQL into Access. It also works with multiple tables at a time.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I needed to do this today, and connecting Access directly to MySQL using the ODBC driver worked (and was a much better solution than the error-prone csv export-import procedure we used last time).
1

You probably won't be able to import a mySQL dump directly into Access due to syntax differences.

One way would be to use a graphical tool like HeidiSQL to export the mySQL data as CSV that should be easy to get into Access.

Another idea would be importing the dump into mySQL, and then accessing mySQL from Access using the ODBC Connector.

Improve this answer

Comments

1

Is this a text file, such as tab delimited, or comma delimited?

Or, is this a file of sql commands?

Access should have no trouble with the text exported files from mySql. However, access can't directly consume a text file of sql commands to insert data.

You can either get the files exported as tab delimited, or if possible, you can even link the access database directly to the mySql server, and use some append/make tables.

Another approach would be to install mySql on your local machine, have it read the sql scripts, and then either export as tab or comma text files, or simply link access directly to that local running instance of MySql and use append queries to pull that data.

However, if that sql dump has each sql command separated by a ; then a simple bit of VBA code could read those sql commands. Something like this could work:

  Sub SqlScripts()

     Dim vSql()     As String
     Dim vSqls      As Variant
     Dim strSql     As String
     Dim intF       As Integer

     intF = FreeFile()
     Open "c:\sql.txt" For Input As #intF
     strSql = Input(LOF(intF), #intF)
     Close intF
     vSql = split(strSql, ";")

     On Error Resume Next
     For Each vSqls In vSql
        CurrentDb.Execute vSqls
        Debug.Print "--->" & vSqls
     Next


  End Sub

I suggest the tab or comma delimited approach. When you do a file->get external data, you should see a wizard startup that allows import of a text (delimited) file.

Improve this answer

7 Comments

10 years after your response, I have the following dump (several tables, but shortened to one table). How can I get it to access: IF object_id(N'Tabelle1', 'U') IS NOT NULL DROP TABLE [Tabelle1] CREATE TABLE [Tabelle1] ( [ID] INT NOT NULL IDENTITY, [spalte1] NVARCHAR(255), PRIMARY KEY ([ID]) ) SET IDENTITY_INSERT [Tabelle1] ON GO -- -- Dumping data for table 'Tabelle1' -- INSERT INTO [Tabelle1] ([ID], [spalte1]) VALUES (1, N'test')
10 years!!! - wow!!! - Since that exported seems to have MySQL specific commands MIXED IN with the data? Install MySQL. Consume that file, get it into nice tables. At that point either export again as nice clean csv, or even better is just link Access to MySQL and then either keep the data in MySQL, or use ODBC to work with the data in MySQL, but Access as the front end, or simply append the data out of MySQL. You can see in the answer below in 2013 - a poster suggests and did exactly that (import the script+ data into mysql, and then pointed Access via ODBC to MySQL).
where is the suggestion from the poster in 2013, cannot find it. Thanks indeed.
I'll re-quote (from post below). :::: I needed to do this today, and connecting Access directly to MySQL using the ODBC driver worked (and was a much better solution than the error-prone csv export-import procedure we used last time). –
User is simply-- Import the data to MySQL, and then connect access to MySQL. Because that scripted database has SO MUCH specific MySQL junk mixed in the scripts, then trying to read/use/run and interpret the MySQL specific syntax in that script is not going to work by trying to run/use/execute that script in access. In fact I would say the same if you trying to use SQL server, Oracle or any other NON my-sql database. The script looks VERY messy, but worse has specific syntax to MySQL. So, trying to use SQL server, or Access or any other system to interpret MySQL scripts is a dead end approach.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.