MyODBC Question

[arekdal]

I have managed to - after many hrs. - to get access 2000 to connect to Mysql data source and am moving tables to/from Mysql. when I do so I lose the primary key attribute as well as the Auto_increment property during both exporting and importing operations. Is it possible to retain these table properties/attributes? Access 2000 will not allow addition of auto_increment after data is stored in table. Can someone help this newbie, please........

Thanks,

Andrew

[KodeKrash]

I have never gotten MyOBDC to work completely cleanly... I would be interested to know if anyone has some RTFM on this too. I can't seem to find anything useful on the net.

[Mike733]

I have never done what you have done, but I have had ODBC problems with oracle, so we had to do some funny stuff. MySQL ODBC driver has a bunch of checkboxes to make the ODBC connection act differently for some known differences some with Access, but I did not see 2000.

This is from one of the MySQL ODBC readmes. (Sorry, I change the names after I read them, so I don't know the original name, but I think there were only two.) With windows, you don't need the codes; just match the names to those in the ODBC Edit and click the box:


--------------------------------------------------
IMPORTANT NOTES:

MyODBC 2.50.18 has a new nice interface to the different MyODBC options.
(On Unix the option is still a number).

The Option argument is used to tell MyODBC that the client isn't 100% ODBC
compliant. The following options are listed in the same order as they
appear in the MyODBC connect screen:

1 The client can't handle that MyODBC returns the real width of a column.
2 The client can't handle that MySQL returns the true value of
affected rows. If this flag is set then MySQL returns 'found rows'
instead. One must have MySQL 3.21.14 or newer to get this to work.
4 Make a debug log in c:\myodbc.log. This is the same as putting
MYSQL_DEBUG=d:t:O,c::\myodbc.log in AUTOEXEC.BAT
8 Don't set any packet limit for results and parameters.
16 Don't prompt for questions even if driver would like to prompt
32 Simulate a ODBC 1.0 driver in some context.
64 Ignore use of database name in 'database.table.column'. (MySQL 3.22)
128 Force use of ODBC manager cursors (experimental)
256 Remove use of extended fetch (experimental)
512 Pad CHAR fields to full column length.
1024 SQLDescribeCol() will return fully qualifed column names
2048 Use compressed protocol (if server supports it)
4096 Tell server to ignore space after function name and before '('
(needed by PowerBuilder). This will make all function names keywords!
8192 Connect with named pipes to a mysqld server running on NT.
16384 Change LONGLONG columns to INT columns, as some applications can't
handle LONGLONG.
32768 Return 'user' as Table_qualifier and Table_owner from SQLTables
(test)
65536 Read my.cnf
131072 Add some extra safety checks (should not bee needed but...)
262144 Disable the new transaction support (To avoid newly introduced bugs)

On Unix; If you want to have many options, you should add the above flags!
For example Option 12 (4+8) gives you debugging without package limits!

Some clients that must set some MySQL options to work properly.
On Win32 one have to check the specific option in the ODBC manager
for the database connection.

BDE applications: 1 or 3; 'Don't optimize column widths' and
'Return matching rows'
Visual Objects: 1 'Don't optimize column widths'
Vision 2 'Return matching rows'
Active server pages 2 'Return matching rows'
Access 7.0: 2 'Return matching rows'
Access 2.0: 2+32 'Return matching rows' and 'Simulate ODBC 1.0'
ODBCETE32 + quiktest 512+1 'Don't optimize column widths' + 'Pad CHAR'
Visual Basic / ADO 2+16384 ''Return matching rows / BIGINT -> INT'

--------------------------------------------------

[arekdal]

Thanks for your input Mike. I hav'nt yet been successful in geting the importing to work yet. So I have resorted to mysqlimport and LOAD DATA INFILE/OUTFILE methods. It's a bit more work than I had hoped for but will work ok for now until I find better solution. Maybe I could build an HTML/PHP interface that would automate most of the tasks......


Thanks,

Andrew