[Solved] Base hangs when opening tables in a MySQL database

[notorious.dds]

Hi all,

I've got a MySQL database that I ported from MS Access using mdbtools and essentially this: http://www.codediesel.com/data/migratin ... -to-mysql/

I've successfully connected base to my new MySQL database. However, some of the tables have issues upon trying to open them.

• The database has 6 tables.
  4 of them open fine.
  1 of them gives an error of "The data could not be loaded. Zero date value prohibited."
  The 6th one does nothing (when trying via OO on Windows 10) , or opens a window with nothing in it that cannot be closed (when trying via LO on Linux Mint).

I assume the issue is likely associated with values left in the MySQL database (after transfer from Access) which OO Base doesn't like. However, that's really just a guess.

My question is: How do I go about troubleshooting the issues with the 2 tables that I cannot open?

[UnklDonald418]

Create a Base database select Connect to an existing database, from the list select Microsoft Access and on the the next page navigate to and select the appropriate .mdb file ( use Microsoft Access 2007 if it is an .accdb file). Using that connection look for your tables and see if the data there is what you expect.
If it looks proper, try using the clipboard to Copy and Paste, which should open the Copy Table dialog to transfer the data into your MySQL database.

[notorious.dds]

Great Idea!

I'll post back after attempting this.

Thanks!

[notorious.dds]

Okay, so I got it worked out. Linking to the original Access DB didn't actually let me copy the tables. However, what it did do is actually give me an error from which I could go hunting in MySQL to correct the issues (unlike one of the tables that would just hang).

Essentially, I had dates with a value of '0', null values where null wasn't accepted, and so on.

Once cleaned up, Base can open all of the tables just fine.

Yeah!!

[Villeroy]

Linking to the original Access DB didn't actually let me copy the tables.

Select the table icon, right-click>Copy
Right-click in the other document's table section > Paste

[notorious.dds]

Linking to the original Access DB didn't actually let me copy the tables.

Select the table icon, right-click>Copy
Right-click in the other document's table section > Paste

Yeah, that's exactly what I tried... but it didn't work. See below

As it turns out, the issue was there was invalid data in the tables (i.e. null values where the column required a value, etc.). Not only did this invalid data prevent me from opening these tables in OO Base while attached to a MySQL datbase, it also prevented me from copying these tables from an ODB file connected to an Access DB to and ODB file connected to a MySQL DB. When attempting to do this with the invalid data present in the table, it would generate errors and ultimately fail.

HOWEVER, it was those errors that allowed me to track down the invalid data, correct it and thus make it work. Unfortunately, these errors didn't trigger when simply trying to open the tables froman ODB file connected to an Access DB. The tables simply wouldn't open.

In other words, the suggestion to copy and paste didn't work as desired, but did ultimately allow me to get where I wanted to go.