BullZip Home

In trying to upload 3 tables from Access 2007 to a remote SQL host at x10, I connect ok, but if I leave all the default checks intact, I upload no tables and get a message "There can be only one auto column and it must be defined as a primary key. All three tables have only one field defined as auto number and it is the primary key.

Bullzip is supposed to put a command PRIMARY KEY (`keyfieldname`) just before ) ENGINE...... yet dump shows it's not there. The above error message is coming back from the MYSQL server as it tries to run the migration query shown in the dump and can't find the PRIMARY KEY it requires.

If I uncheck either the move indices OR auto number properties, the 3 files upload. However, upon opening Access 2007 and putting the mysql connection links into the 3 tables, I get open errors.

If I manually edit the dump and insert the command and then copy the dump and paste it into SQL while logged into the server control panel for the database, it runs the query and builds the table. So the question is, Why isn't Bullzip putting that command in?

Anyone with a similar problem?

Ron

Hi Ron,

My software is not perfect

If you contact me via the contact form on the site then we can arrange that you send me a small sample database to reproduce the problem. That will help me fix it.

Regards,
Jacob

Hi Jacob....I can't find a contact form. Clicking on JR has word contact, but it's not a link. I'm at cove3@aol.com

I can confirm the following from working with Bullzip off and on for a couple of days:
1. I set up a small Access 2007 test.mdb with a couple of columns and two records. Primary key on 1 field, index with no duplicates & required on another, and index with duplicate & required on the 3d. Dump with all boxes checked except 1st shows missing SQL commands: PRIMARY KEY, INDEX, AND UNIQUE. RUN gives Error: -2147467259 (80004005) [MySQL][ODBC 5.2(w) Driver][mysqld-5.1.68-cll]Incorrect table definition; there can be only one auto column and it must be defined as a key- 0 records moved. (This is an MYSQL response to the script missing the PRIMARY KEY command)

2. My small database with 3 tables and 800 or so records does okay with two of the three tables. The Names and Record tables generate PRIMARY KEY, INDEX AND UNIQUE commands correctly, but the Owned table does not generate any of these. If I cut and paste the DUMP for only the Names and Record tables and paste into an X10 SQL for the database, the query runs and uploads the tables correctly.

3. BZ seems semi consistent. A couple of times the test.mdb did generate the missing commands, but most of the time not. Same with my small lit.mdb. Several times, Names and Record did not generate the missing commands, but the last 3 or 4 times I ran it, it did. But Owned table never has generated the commands.

4. Note: I discovered my hosting site x10 has disabled the drop database command. If it's included, RUN gives me an Access denied with no explanation. I had to be in control panel trying a drop query before I discovered the message that it was disabled by x10, I suppose for security.

I have the two databases and the associated dumps which I can send if you give me an email. I really like the user interface. With the growing awareness of Access users that a remote server front end can be created, I expect demand to grow, so I hope we can figure this out. MS Access has a built in Remote Server upload function, which worked perfectly on my database uploading to a free MS SQL Server Express I installed on a spare laptop. The problem is most hosting sites only offer MYSQL (to get remote MYSQL connect you have to be a premium member, though, but X10 is only $4 a month with a 3 year contract)

Ron

Jacob, further testing with my tiny test_be.mdb database. Unchecking the create index box creates and index for the primary key field, but not the indices for the other two fields. It creates no PRIMARY KEY command and no UNIQUE commands, but it does run and upload.

I'm not sure why since MYSQL seems to require a PRIMARY KEY command if AUTO_INCREMENT is specified, but apparently because the auto-number field has an index created for it, that's good enough. If I uncheck AUTO_INCREMENT, it runs, but again no PRIMARY KEY, INDEX or UNIQUE commands are created

Ron

Jacob, I looked at the dump created by Oracles Workbench in migrating from MS SQL to MYSQL. As I mentioned, I migrated my Access tables to a MS SQL Express server on my notebook pc, so I had the tables migrated and wanted to see how Workbench would do the migrate. Workbench does not have a direct Access to MYSQL migration tool

Following are differences I noted and perhaps you can tell what they mean.

1. There's some schema commands at the top
DROP SCHEMA IF EXISTS `lit` ;
CREATE SCHEMA IF NOT EXISTS `lit` COLLATE utf8_general_ci

2. There's different command format for indices
UNIQUE INDEX `idname1` (`idname1` ASC) ,
UNIQUE INDEX `namelf` (`namelf` ASC) ,
INDEX `occup` (`occup` ASC) )
COLLATE = utf8_general_ci;

3. There's a timestamp command
`upsize_ts` TIMESTAMP(0) NULL ,

4. There's a collate command
COLLATE = utf8_general_ci;

5. Lastly, comments from the Access table design are included
`ppricef` INT NULL DEFAULT 0 COMMENT ` Price in foreign currency' ,

I'm hopeful you can figure out what changes can be made to Bullzip to make it consistent, complete and useable without having to edit Dumps, as I think you have a winner with the interface

Ron