| View previous topic :: View next topic |
| Author |
Message |
lithium3141
Tux's lil' helper
Joined: 22 Dec 2004
Posts: 100
|
 Posted: Tue Jun 06, 2006 1:12 am Post subject: [SOLVED] Mysql/MS access compatibility? |
 |
|
I have a database that's been steadily growing in size for a couple weeks now, and I need to package up all that data and send it to a friend. The only problem is that he uses MS Access and I've been using MySQL 4.1.19. Is there a way (using mysqldump or another freely available Linux-based program) to either export the MySQL db in such a way that it's Access-readable or to write an Access database directly? I've googled around for various methods of doing so, but all the converters I've seen are crippleware until I pay a fee. Any thoughts?
Last edited by lithium3141 on Thu Jun 08, 2006 6:24 pm; edited 1 time in total |
|
| Back to top |
|
 |
savage
Apprentice
Joined: 01 Jan 2003
Posts: 161
|
| Posted: Tue Jun 06, 2006 3:20 am Post subject: |
|
|
| I guess I would look into writing a CSV file and then import that into access.... Unless you can access your (mysql) database from windows - in which case you can use an ODBC connection to an access file and read from your mysql server and write to the access database.... |
|
| Back to top |
|
|
coutts99
Apprentice
Joined: 19 Sep 2002
Posts: 171
Location: Sunderland, UK
|
| Posted: Tue Jun 06, 2006 7:43 am Post subject: |
|
|
You could try connecting to your database in Access via an ODBC connection, then exporting the data from Access itself?
Dunno been a while since I played with Access and mySQL. |
|
| Back to top |
|
|
lithium3141
Tux's lil' helper
Joined: 22 Dec 2004
Posts: 100
|
| Posted: Tue Jun 06, 2006 3:33 pm Post subject: |
|
|
I guess that's always an option, but I was hoping I could find something that doesn't require a direct link between his computer and mine. The MySQL database is in such a place that getting a MyODBC connection would be more hassle than it's worth.
I've recently discovered the magic of SELECT ... INTO OUTFILE, which generates a comma-separated values file. Can Access work with those in a reasonable fashion? What would be the best way to export 80 tables, each with 700+ records, into CSV file(s)? |
|
| Back to top |
|
|
JeliJami
Veteran
Joined: 17 Jan 2006
Posts: 1086
Location: Belgium
|
| Posted: Tue Jun 06, 2006 3:58 pm Post subject: |
|
|
| lithium3141 wrote: | I guess that's always an option, but I was hoping I could find something that doesn't require a direct link between his computer and mine. The MySQL database is in such a place that getting a MyODBC connection would be more hassle than it's worth.
|
MySQL is available for MS Windows.
_________________
Unanswered Post Initiative | Search | FAQ
Former username: davjel |
|
| Back to top |
|
|
lithium3141
Tux's lil' helper
Joined: 22 Dec 2004
Posts: 100
|
| Posted: Tue Jun 06, 2006 6:42 pm Post subject: |
|
|
| I was kinda hoping I wouldn't have to go there. The end goal is to get the database converted and readable on Access on Windows without installing any additional software on the guy's computer, including Windows ports of MySQL, MyODBC, or anything else. Good idea though, thanks. |
|
| Back to top |
|
|
lithium3141
Tux's lil' helper
Joined: 22 Dec 2004
Posts: 100
|
| Posted: Thu Jun 08, 2006 6:24 pm Post subject: |
|
|
Solved the problem. I used the following PHP script to export the contents of each table into a text file:
| Code: | <?php
$conn = mysql_connect("localhost","user","pass");
mysql_select_db("db",$conn);
$tables = mysql_query("SHOW TABLES;",$conn);
while($row = mysql_fetch_assoc($tables)) {
$tablename = $row['Tables_in_db'];
mysql_query("SELECT '$tablename',field1,field2 INTO OUTFILE /tmp/$tablename.txt FROM $tablename;",$conn);
}
mysql_close($conn);
?> |
Then just | Code: | | cat /tmp/*.txt >> /root/alldata.txt |
to have one large file with all the data. The '$tablename' in the PHP script adds the name of the table to the beginning of each line in each text file, so when they're all concatenated together, we can still figure out which piece of data came from which table. Emailed it off and it worked like a charm  Thanks for everyone's suggestions! |
|
| Back to top |
|
|
|
Other Things Gentoo
