Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > Access-L Developer's Libraries > Olislagers, Reinier
  New Posts New Posts RSS Feed - ExportSQL.zip
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

ExportSQL.zip

 Post Reply Post Reply
Author
Message
Reinier Olislagers View Drop Down
Contributor
Contributor


Joined: 30 Jun 2008
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Reinier Olislagers Quote  Post ReplyReply Direct Link To This Post Topic: ExportSQL.zip
    Posted: 09 May 2009 at 1:02pm

Access database export tool

(Latest update: 4.04: http://bitbucket.org/reiniero/impex/src)

Frustrated with some other Access to Firebird conversion tools (SQL2GDB for instance) that don't support e.g. multicolumn primary indices, I updated Pedro Freire's exportSQL code to include Firebird.

Also, I included the mySQL import functionality that Laurent Bossavit wrote.

This code allows you to export MS Access tables, indices, and relations to Firebird 2.x databases. It should also support export/import to/from mySQL but that hasn't been tested.

If indicated, the Firebird export code also creates an empty view or stored procedure for each query and adds the original SQL as a comment. This allows you to more easily convert Access queries.

The code creates a script that you can run on your Firebird server or embedded server using e.g. the graphical Flamerobin or character based isql tools.

Note: contrary to SQL2GDB, this tool doesn't support exporting BLOBs/binary objects, as Firebird currently doesn't support importing BLOBs using scripts.

How to use: see instructions in the module basExportImportSQL.bas

Have you made improvements? Please send them to me at reinierolisl*gers at gmail dot  com

This code has been created in Access XP, but will probably run in Access 97-2003 at least.

Access 95 requires some changes as not all data types are supported. Access 2007 has not been tested.

For descriptions of updates of my original version: please see below.

Back to Top
Sponsored Links


Back to Top
Reinier Olislagers View Drop Down
Contributor
Contributor


Joined: 30 Jun 2008
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Reinier Olislagers Quote  Post ReplyReply Direct Link To This Post Posted: 25 Oct 2009 at 8:32am
October 31, 2009: updated version, ExportImportSQL.zip version 4.01

This version builds on the previous version and attempts export to more database types.
Although this is a work in progress, I did want to share this with you as it probably works better than the previous versions.
Highlights:
** Export to:
  • Firebird 2 (tested)
  • Microsoft SQL Server 2005+
  • Access - we will need a read implementation, too
  • Comma-separated values
  • mSQL v1
  • mSQL v2
  • MySQL
  • Oracle
  • PostgreSQL
NB: export routines have been set up with some default values for various databases. If you have recommendations for specific field types, trigger definitions etc, please let me know.
Changes may have inadvertently broken mSQL/MySQL or other database export. Please test.
** Base64 encoding for blobs; hex literal use for Firebird; specific solutions for some other databases that support this (e.g. MySQL escaped strings)
** Code cleanup.
** Fix for CSV output and MySQL output in version 4.01 thanks to Richard Winter.
Plans for the future:
** verify and clean up code even more (e.g. translation table can be enlarged to include destination database field types and field lengths).
** Test databases; possibly support some more (Sybase, SQLite, DB2)
** Add simple script import function for Access objects.

I'd be very grateful for suggestions for improvements - and I am very grateful to Pedro Freire and the other developers whose code I have extended (see attribution in source code).

Regards,
Reinier
Back to Top
Reinier Olislagers View Drop Down
Contributor
Contributor


Joined: 30 Jun 2008
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Reinier Olislagers Quote  Post ReplyReply Direct Link To This Post Posted: 14 Nov 2009 at 1:25am
14 November 2009, Updated version; version 4.02
Thanks to Richard Winter for testing and bug reporting.
  • Added preliminary support for Apache Derby. Help regarding stored procedure and BLOBs are welcome.
  • Fixes for CSV export (proper use of delimiter, quote only text fields, 1 file per table
  • Fixes for MySQL export (memo output to longtext instead of longblob, standard use of innodb, foreign keys should now work)
Test database export was run on Firebird 2.1, MySQL 5.1 without problems. CSV export looks good. MS SQL 2008 export should still work. Other databases untested; bug reports welcome.

Reinier

Back to Top
Reinier Olislagers View Drop Down
Contributor
Contributor


Joined: 30 Jun 2008
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Reinier Olislagers Quote  Post ReplyReply Direct Link To This Post Posted: 21 Jan 2010 at 10:39pm
January 22, 2010, updated version: 4.03. (download link: see initial post)

Thanks again to Richard Winter for testing and bug reporting.

Changes:
Added initial DB2 support based on Derby functionality
START WITH supported for Derby primary keys thanks to Richard Winter
Dropped import script option as it would be much easier to write  in VB.Net using regular expressions etc.
Changed Derby autonumbers from GENERATED ALWAYS to GENERATED BY DEFAULT
Some code cleanup.

As always, please send feedback via email or via the ACCESS-L list.
Back to Top
Reinier Olislagers View Drop Down
Contributor
Contributor


Joined: 30 Jun 2008
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Reinier Olislagers Quote  Post ReplyReply Direct Link To This Post Posted: 08 Feb 2011 at 8:08am
Updated version 4.04 (see link first post). Moved source code to bitbucket for easier upload.
Fixes problem with Accesss tables with spaces
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down