what are the various other programs using which i can connect to odbc database besides ms-access.

Question

what are the various other programs using which i can connect to odbc database besides ms-access. i'm talking about programs in which i can run sql queries. i dont want to use ms-access because the size of the access file has become 1.5 gb .

why my database is 1.5 gb ?

i am using access to connect to odbc - mysql database . which is quite huge. in my access database i have several queries that create tables in the access database using odbc - mysql database thats why the size is huge . but 1.5 gb is still a bit too much for me to understand cause the sql dump of the whole database should not be more than 200 mb. maybe acess files are large in size compared to other databases.

now i dont want to use access anymore . but i want to use the existing sql queries in the ms-access database to operate the odbc-mysql database. also i want the queries that create table be modified so that that they create the database in mysql itself and not in ( access ) . so i am looking for this alternate program.

Answer 1

Click

Start->Programs->Administrative Tools->Data Sources (ODBC)

Click Add.

The list shows which apps you can connect to using ODBC.

Answer 2

If you want to view a MySQL database, I suggest you connect MySQL's own protocol, rather than ODBC. I can suggest SQLyog, as a good, easy to use tool for viewing a MySQL database.

Answer 3

About the size of your Access frontend: If you haven't done so already, try compacting the Access database file. Maybe the huge size is just a "leftover" from the temporary tables that were created and removed from the DB.

About alternatives:

• You can keep using Access as the frontend and just create your tables in mysql instead (use so-called "pass-through queries" to enter SQL statements (CREATE TABLE, INSERT INTO, ...) that are executed directly on the remote ODBC data source.

• If you want other programs, the answer depends on whether you want to do "database administration" (manually enter SQL statements and move around data) or "software development". In the latter case, you can use almost any development platform available on Windows at the moment; it won't be easy finding one without ODBC client support. In the former case, you might want to ask on http://serverfault.com.

Answer 4

Along the lines of GUI tools you can use to write SQL queries on top of MySQL data try looking into either the MySQL Query Browser, which is part of the MySQL GUI Tools, or SQLyog from Webyog. Either of these tools will allow you to right SQL queries, and review the results in a data-grid.

You haven't mentioned what you are doing with all this data, so it's tough to decipher if you are using the access DB as only a front-end client to view the data from MySQL, or if you have some application logic built into Access. However in either case, you really want to look into using a tool that uses MySQL's C api directly, rather than ODBC. The C api, which both of the tools I mentioned above will improve the performance of the queries you're running over ODBC, while at the same time reduce network and server load by not requiring the server to go through ODBC translation routines.

Since this post was tagged as Java, you might want to look into rolling the queries you have saved into a Java application and using JDBC to connect to your MySQL data. JDBC may not use ODBC as it's connection protocol but it will let you get to the data you have saved in your database. Of course the downside to this approach is that you'll have to roll your own application to look at the data you already have in MySQL, but the trade-off is you'll have the most control over what you can do with that data programmatically.

Answer 5

SQL Server is one potential candidate. A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers e.g. use the OLE DB provider for ODBC.