Page 1 of 1

Open Table in MySQL

PostPosted: Sun May 18, 2014 5:10 am
by datadon
I'm have a problem opening a table in a mysql connection. The connection test successful and the NB drop downs show the table. I select the table to open from the drop down but when I run it and try to open in rectangle it says it cannot open the table.

Code: Select all
dbpOpenDatabase "ProjDB" "Provider=MSDASQL.1;Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;User=xxx;Password=xxx;Option=3"


dbpOpenTable "ProjDB" "tasklistqr" ""



I can make a valid connection in access to mysql and it will show the table correctly. It does use an updated mysql driver. 5.2 ANSI

Any thoughts if you have had this problem before?

Re: Open Table in MySQL

PostPosted: Sun May 18, 2014 2:40 pm
by Neosoft Support
Can you open the table in the MySql command line utility?

http://dev.mysql.com/doc/refman/5.6/en/mysql.html

Re: Open Table in MySQL

PostPosted: Sun May 18, 2014 3:07 pm
by datadon
Well I think you lost me on that one. I downloaded and installed the mysql utilities but don't have a clue how to use them.

Just a bunch of black boxes flashing on and off, kind of like me after pulling 2 shifts and driving 200 miles.

how can I help you help me?

Re: Open Table in MySQL

PostPosted: Mon May 19, 2014 5:29 am
by datadon
I tried connecting to a different table in the same database and it displays fine. Seems there is something about the one table it does not like.

Any idea on how to find that? The table displays fine when linking to access 2010 with newer ODBC connection.

Re: Open Table in MySQL

PostPosted: Mon May 19, 2014 9:14 am
by Neosoft Support
Check the table's field names and make sure they contain only letters, numbers and "_" characters and no spaces.

Re: Open Table in MySQL

PostPosted: Fri May 23, 2014 11:30 am
by datadon
Time to update this post. Thanks to some offline help by Dave, I was able to narrow the problem down to one small item that stopped the database from being linked to the mysql.

It was a simple matter of changing the datetime and timestamp fields in the mysql table to a null default value. It I used any other default value it would not import. Had nothing to do with whatever was in the fields, just the default value.

Yeah, a little weird, but those are the hard cold facts.

While here, anybody know how to actually edit the linked table and get the revisions back into the mysql. It always seems to disconnect if I try to make any changes. Right now I just export/import it into a new mdb and then import back into mysql via phpmyadmin. It works but a little sloppy. This is not my realm as you can see so any help is appreciated.

Don

Re: Open Table in MySQL

PostPosted: Sat May 24, 2014 10:26 am
by Neosoft Support
While here, anybody know how to actually edit the linked table and get the revisions back into the mysql. It always seems to disconnect if I try to make any changes.


Does DBPro report any error messages?

Re: Open Table in MySQL

PostPosted: Sat May 24, 2014 1:01 pm
by datadon
All it says is something to the effect of "connection lost"

It seems that any action to modify the database shuts it down. I'll monitor it a bit more closely and watch for errors. I'm curious if others have this issue at all?

Re: Open Table in MySQL

PostPosted: Mon May 26, 2014 10:13 am
by Neosoft Support
Connecting to a remote Internet database from a desktop app can be very tricky. Most web servers have their security settings configured to prevent it by default.

If you're losing the connection it's probably something in the server's configuration.