Ms Access Not Importing

[maxhugen]

I created file DSNs to both an Access 97 and an Access 2003 database. In each of these databases, I went to Tools > Options >View, and enabled Show > System Objects.

Using SQLyog's Migration Toolkit, I connected to each in turn, selected one small table for import, and tried it. Each test failed. In both cases the log simply shows:

Job started at Thu Jun 07 13:31:29 2007

ERROR:

ERROR: Import aborted…

As a newbie looking to migrate an Access application to the web using PHP and MySQL, I was looking forward to using a tool that would do the Access data import (and save the import specs) … but that log file wasn't much help.

I've no idea where to start. Any suggestions would be appreciated.

[adarsh]

Hello,

Please tell the SQLyog version and also please try SQLyog6.02 RC, if you get same error please attach the sample access.mdb file here which is giving error.

download link:

http://www.webyog.com/en/downloads.php

Thanks

[maxhugen]

I bought the Enterprise version today, especially for the import and other extra features. It is version 6.01. How would I get the version you mention?

Oops, sorry, should have followed your link first. Have downloaded 6.02RC, will try.

Cheers, Max

[maxhugen]

The 6.02 RC version throws the same error.

In case it's of interest, the Access .ldb file is still 'in use' after the error. Have to close SQLyog to release the lock.

I've attached the Access db, it's a test with just one table in it.

[peterlaursen]

Is the database open in Access when you are trying to import?

It imports OK for us!

[maxhugen]

Tried changing the table type from InnoDb to MyIsam. Same failure.

Turned on the ODBC trace, and had a squizz at the log, but I don't know what to make of it. One thing that caught my eye though, was the string “select * from `Trades` where 1=0”. Doesn't look right to me…. I didn't add a WHERE to SQLyog, and “where 1=0” looks pretty strange.

Just in case the log file might help, I've attached it.

MTIA

[maxhugen]

No, they are always closed.

[peterlaursen]

I think that SJA cannot access the .mdb file and that is why SJA will have to abort the job.

An access database is a 'flat file database' – no 'server proces' need to be running to read the data.

However we can import too when the database is open is access, but that may depend on the security settings in access itself and what you are doing.

So please start ensuring that no other program accesses the database file while importing.  Also verify that there is no security setting in the database file/folder preventing  the actual user to access it (in case 'simple file sharing' is turned off).

[peterlaursen]

OK .. you replied while I was writing!

try copying the file to another location – your own 'documents' folder for instance if it is not allready.

Your Windws version?

[peterlaursen]

your data displayed in SQLyog after import.

[maxhugen]

I've tried moving the db to 'My Documents', changed the DSN, and tried again but still no joy.

Using W2000

[maxhugen]

Peter, your cute little graphic gives me hope. 🙂

I did try this in the opposite direction, exporting from Access 97 to MySQL (via an ODBC dsn). This worked OK.

Could there be a prob with the Microsoft Access driver I'm using? It's version 4.00.6200.00 dated 20 May 03.

Cheers, Max

[peterlaursen]

Actually I was thinking the same!  Possibly an issue with the ODBC driver.

Do you have another program of some kind that can use the ODBC driver? (OpenOffice can but it is a huge download!)

Maybe you can try to import the .mdb into Access itself using ODBC and the DSN?

By the way: when you open the SQLyog Migration Wizard, does it then show the single table in the database?

[maxhugen]

peterlaursen wrote on Jun 7 2007, 05:15 PM:

I think that SJA cannot access the .mdb file and that is why SJA will have to abort the job.

It seems that the SJA is accessing the .mdb OK, as it displays the available tables etc. So that rules out permission based problems (I think), and problems with access to the file (in use, etc).

The ODBC trace log shows that SQLyog is running, and that it looks like it is working (success messages). Not that I'd actually know though!

So *if* we assume that SQLyog got as far as running ODBC calls *OK*, what happens next that could fail?

[maxhugen]

Tried Access import from Access via ODBC…. Access won't allow import, linking etc to other Access sources via ODBC.

Haven't anything outside of MS progs that I could use to try try the Access ODBC driver. Still, it 'looks' like that part is working…

[peterlaursen]

hmmm ..

let us try something different.  Does the MySQL user that you use for the job have all required privileges, such as 'CREATE' ?

[maxhugen]

Sorry, didn't see your reply…

I'm using the root user with all privileges for testing, which was created when I installed MySQL. Checked it, looks OK.

[maxhugen]

Just had a thought… would you be able to turn on ODBC Trace, do that import to MySQL again, and attach the log for me? Maybe I can spot a difference between my log and yours?

[adarsh]

Hello,

Here i am attaching the ODBC trace log file.

Thanks

[maxhugen]

Thanks Adarsh

Whilst I'm not really sure of what i'm looking for, I noticed your log had a different SELECT in it:

“select `TradeID`,`Trade` FROM `Trades` “

Mine has:

“select * from `Trades` where 1=0”

I'm struggling to figure out the seeming differences between the ODBC outputs. Somehow I feel its got to be the Access driver I'm using. Sigh…

[adarsh]

Hello,

Please tell your driver details.

we are using this driver.

Driver name Version

---

---

Microsoft Access Driver [*.mdb] 4.00.6304.00

Thanks

[maxhugen]

Hmmm… mine is version 4.00.6200.00

Yours looks quite a bit later, mine is dated 2003.

[maxhugen]

Just for a laugh, do you think I could try your copy of the Access ODBC driver? It's the file ODBCJT32.DLL.

I don't know if some dll registration issue would stop me from backing up my copy, and trying yours. But hey, it would be worth a try…

[peterlaursen]

i can add that the version on my Vista is 6.xxxxxxxxxxxxxx dated november 2006.

[maxhugen]

I've tried searching Microsoft's downloads and MSDN areas, can't find anywhere to download a later version of the Access ODBC driver. How frustrating!

[maxhugen]

Well, made some progress… sort of.

I've been using a file DSN for both MySQL, and the Access db I was trying to import. Found I couldn't link MySQL tables to Access using a file DSN, it seems to connect AOK, but the list of tables in Access's Link dialog was empty. I then tried a User DSN, and this worked.

So I tried this to import the Access tables. Created a User DSN for the Access db, and retried the import into MySQL. After selecting “Import tables…”, SQLyog threw an error on every table:

[Microsoft][ODBC Microsoft Access Driver] Query support unavailable.

After the errors had finished, the list of tables was displayed and I continued, but I found that it imported only the table definitions, without any data.

Can anyone suggest the cause of this problem?

MTIA

[peterlaursen]

I have no answer to this, but I never used anything but a 'user DSN' or 'system DSN' for Access.

[Graycode]

The problem is probably in the driver and ODBC versions.

Try using the BullZip package for Access to MySQL loading. It doesn't rely on ODBC or the underlying driver versions.

http://www.bullzip.com/products/a2m/info.php

[adarsh]

Hello,

Thank you for reporting.

'file-DSN' has broken in SQLyog 6.0, issue is fixed in our development tree and we will release SQLyog 6.03 Beta1 today.

Thanks

[peterlaursen]

We believe we fixed that now. We will release 6.03 beta 1 shortly with the fix!

[maxhugen]

Back again, I'm sorry, with the same sort of problem. Since my previous posts, I've bought a new workstation, software details are:

Windows: XP SP2

SQLyog: 6.16

Access ODBC driver: 4.00.6304.00

MDB version: Access 2000

ODBC DSN type: User

Symptoms:

Using the Migration Toolkit, after selecting 'Copy table(s) from data source', I first get a msg 'The underlying database seems to be Microsoft Access. To correctly import…'

I click to continue.

Next, another msg pops up '[Microsoft][ODBC Microsoft Access Driver] Query support unavailable.' Click . The same msg appears for second time, click again.

All table schemas are successfully imported, but no data.

This is driving me nuts, can anyone suggest what I might try?

[Mahesh]

maxhugen wrote on Mar 24 2008, 09:30 AM:

Back again, I'm sorry, with the same sort of problem. Since my previous posts, I've bought a new workstation, software details are:

Windows: XP SP2

SQLyog: 6.16

Access ODBC driver: 4.00.6304.00

MDB version: Access 2000

ODBC DSN type: User

Symptoms:

Using the Migration Toolkit, after selecting 'Copy table(s) from data source', I first get a msg 'The underlying database seems to be Microsoft Access. To correctly import…'

I click to continue.

Next, another msg pops up '[Microsoft][ODBC Microsoft Access Driver] Query support unavailable.' Click . The same msg appears for second time, click again.

All table schemas are successfully imported, but no data.

This is driving me nuts, can anyone suggest what I might try?

HI,

This message pops each time you select a query( message/query selected to migrate)

can you please tell,

Are you able to migrate properly if tables only used ?

Please check the permissions for tables and queries ?

see this link,

http://www.webyog.com/whitepaper/Preparing…__Migration.pdf

[maxhugen]

Hi Mahesh,

I am selecting 'Copy table(s) from data source' – not using queries. As per the link you provided, I have given the default Admin user and the Admins group full administration permissions to the 5 system tables, but it hasn't made any difference.

During the migration, the status window shows each table schema being successfully imported, but at the end the msg '[Microsoft][ODBC Microsoft Access Driver] Query support unavailable.' is repeated.

[maxhugen]

I've given up trying the migration with SQLyog Enterprise. I used Graycode's suggestion of the Bullzip 'Access To MySQL' product at http://www.bullzip.com/products/a2m/info.php. It worked AOK.

[peterlaursen]

maxhugen wrote on Mar 24 2008, 11:12 PM:

During the migration, the status window shows each table schema being successfully imported, but at the end the msg '[Microsoft][ODBC Microsoft Access Driver] Query support unavailable.' is repeated.

The error is mentioned here but with not much additional info:

http://support.microsoft.com/kb/172570

(more here

http://search.microsoft.com/results.aspx?q…&OtherSite=

.. but almost the same!)

I wonder if you are trying to import some 'saved queries' (not tables)? If so try to omit. Also you can try to 'narrow' it to a specific table.

Can you share a .mdb file where this is reproducable? I still think it might be related to some settings with the file itself.

If you can share such file please keep as small as possible and please zip and zip with the jobfile! Create a ticket if you want privacy.

I think we should check some of this:

Is it a common problem or a problem with a single file?

Did you check security settings/properties for the file?

What Access version was used for creating this file/the files?

On what Windows version are you doing this? Driver version?

Can you view data in Access itself?

Do you have an option to copy the file to another computer?

Was the database created as the same user and on the same as you are when trying to import.

(my apology if you replied some of it already).

etc. etc. …

If you can do it is fine, but you can also let us.

Finally try if you can import into another program with ODBC (another Access database for instance)? If yes will this new one import?

[peterlaursen]

Looks like this one EXPORTs to MySQL with the MyODBC driver (not imports with the access driver).

[maxhugen]

Hi Peter,

Well, this has got me stumped! I tried the Migration with some other mdbs to test if it was a common problem – and now it works!

I tried an A2003 mdb first, no prob.

Then an A97 mdb, again OK.

Then I went back to the A2000 mdb I've been having issues with, and tried it again – it worked!!!

I haven't done anything to the A2000 mdb, nor the ODBC DSN, so I'm quite at a loss to explain the previous problem behaviour.

Cheers, Max

[peterlaursen]

that is weird indeed.

… but I think that the error “query support not avail” means that it was not possible to retrieve data for a “stored query” (or a VIEW if you like). VIEWs fromcan be imported to a table on . But if the driver fails to retrieve the data I think this error occurs. I have done that with Access2000 without problems however, but there might be problems with some specific “stored queries”. Or maybe JET version on that system was not able to handle that file?

This is my best guess.

[maxhugen]

As per my previous posts, I wasn't trying to import any queries, only the tables themselves.

When I was having issues, I tried rebooting, everything I could think of. But now I can't recreate the problem, no matter what I try!

[Author]

Posts