Print Page | Close Window

ExportSQL.zip

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Olislagers, Reinier
Forum Description: Reinier Olislagers's Library
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=453
Printed Date: 21 Oct 2017 at 7:01pm


Topic: ExportSQL.zip
Posted By: Reinier Olislagers
Subject: ExportSQL.zip
Date Posted: 09 May 2009 at 1:02pm

Access database export tool

(Latest update: 4.04: http://bitbucket.org/reiniero/impex/src - 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.

uploads/31/ExportSQL.zip -

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

Email: mailto:reinierolisl*gers@gmail.com - reinierolisl*gers@gmail.com



Replies:
Posted By: Reinier Olislagers
Date Posted: 25 Oct 2009 at 8:32am
October 31, 2009: updated version, ExportImportSQL.zip version 4.01
uploads/31/basExportImportSQL.zip -
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


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

Email: mailto:reinierolisl*gers@gmail.com - reinierolisl*gers@gmail.com


Posted By: Reinier Olislagers
Date 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



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

Email: mailto:reinierolisl*gers@gmail.com - reinierolisl*gers@gmail.com


Posted By: Reinier Olislagers
Date 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.


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

Email: mailto:reinierolisl*gers@gmail.com - reinierolisl*gers@gmail.com


Posted By: Reinier Olislagers
Date 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


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

Email: mailto:reinierolisl*gers@gmail.com - reinierolisl*gers@gmail.com



Print Page | Close Window