Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.
Exporting from a linked table to MySQL

Exporting from a linked table to MySQL

Exporting data from a linked table (a table in a foreign database connected via an odbc-link to the current Access database, could be Centura, Oracle, ...) into MySQL. This works at least with Access 2000, I am not sure wether it also works with Access 97.

sql="SELECT * INTO [ODBC;DSN=mysql_pu;UID=wwwrun;PWD=xxxxxxxxx;].ACCOUNTS
FROM SYSADM_ACCOUNTS"
docmd.runsql sql

Because there are usually a bunch of tables to export I simply write a perl-script which creates me all lines needed and after that I copy the program into a subroutine in Access:

my ($odbc_dsn, $user, $pass) = ("mysql_pu","wwwrun","xxxxxxxxx");

while () {
        chomp;
        my $accessname=$_;
        my $mysqlname = $accessname;
        $mysqlname =~ s/^SYSADM_//;

        my $cmd = "SELECT * INTO [ODBC;DSN=$odbc_dsn;UID=$user;PWD=$pass;].$mysqlname FROM $accessname";
        print "sql=\"$cmd\"\n";
        print "docmd.runsql sql\n";
}

__DATA__
SYSADM_EXEMPLAR
SYSADM_ABOBESTAND
SYSADM_ABOEXEMPLARE
Impressum