Probeer het anders hier mee. copy-paste naar een VBA-module en... een dump naar SQL.
Je moet 'm wel elf nog een beetje tweaken voor gebruik van blobs/memo velden, geloof ik.<blockquote><font size="1" face="verdana, arial, helvetica">code:</font><hr><font face="courier, fixedsys, lucida console"><nobr>Option Compare Database
Option Explicit
' exportSQL version 2.0
' www.cynergi.net/prod/exportsql/
'
' (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net
' (C) Pedro Freire - pedro.freire@cynergi.net (do not add to mailing lists without permission)
'
' This code is provided free for anyone's use and is therefore without guarantee or support.
' This does NOT mean CYNERGI delegates its copyright to anyone using it! You may change the
' code in any way, as long as this notice remains on the code and CYNERGI is notified (if you
' publish the changes: if your changes/corrections prove valuable and are added to the code,
' you will be listed in a credit list on this file).
'
' You may NOT sell this as part of a non-free package:
' IF YOU HAVE PAID FOR THIS CODE, YOU HAVE BEEN ROBBED! CONTACT admin@cynergi.net!
' MODULE
' "exportSQL"
'
' GOAL
' Export all tables in a MS-Access database file to 2 text files:
' one containing SQL instructions to delete the new tables to be created,
' and the other with SQL instructions to create and insert data into
' the new tables. The table structure and data will resemble as much as
' possible the current Access database.
'
' HOW TO USE
' Copy-and-paste this text file into an Access module and run the first
' (and only public) function. in more detail, you:
' * Open the Access .mdb file you wish to export
' * in the default database objects window, click on "Modules", and then on "New"
' * The code window that opens has some pre-written text (code). Delete it.
' * Copy-and-paste this entire file to the code module window
' * You may hit the compile button (looks like 3 sheets of paper with an arrow on
' top of them, pressing down on them), or select Debug, Compile Loaded Modules
' from the top menu, just to make sure there are no errors, and that this code
' works on your Access version (it works on Access'97 and should work on Access'95)
' * Close the code module window - windows will prompt you to save the code:
' answer "Yes", and when promped for a name for the module, type anything
' (say, "MexportSQL")
' The module is now part of your Access database. To run the export, you:
' * Re-open the code module (by double-clicking on it, or clicking "Design"
' with it selected). Move the cursor to where the first "Function" keyword appears.
' Press F5 or select Run, Go/Continue from the top menu.
' * Alternativelly, click on "Macros" on the database objects window,
' and then on "New". On the macro window, select "RunCode" as the macro action,
' and "exportSQL" as the function name, bellow. Save the macro similarly to the
' module, and this time double-clicking on it, or clicking "Run" will run the export.
'
' BEFORE RUNNING THE EXPORT
' Before running the export, be sure to check out the Export Options just bellow this
' text, and change any according to your wishes and specs.
'
' TECH DATA
' Public identifiers:
' * Only one: "exportSQL", a function taking and returning no arguments. It runs the export.
' Functionallity:
' * Can export to mSQL v1, mSQL v2 or MySQL-recognised SQL statements
' * Excellent respect for name conversion, namespace verification, type matching, etc.
' * Detects default values "=Now()", "=Date()" and "=Time()" to create types like "TIMESTAMP"
' * Fully configurable via private constants on top of code
' * Exports two files: one for erasures, another for creations (useful when updating dbs)
' * Generates compatibility warnings when necessary
' * Code and generated files are paragraphed and easy to read
' * Access text and memo fields can have any type of line termination: \n\r, \r\n, \n or \r
' * Properly escapes text and memo fields, besides all types of binary fields
' * Closes all open objects and files on error
' * Known bugs / incomplete constructs are signalled with comments starting with "!!!!"
' * Two alternatives on absent date/time type on mSQL: REAL or CHAR field
' Export Options - change at will
Private Const DB_ENGINE As String = "MY" ' USE ONLY "M1" (mSQL v1), "M2" (mSQL v2) or "MY" (MySQL)
Private Const DB_NAME As String = "" ' Use empty string for current. Else use filename or DSN name of database to export
Private Const DB_CONNECT As String = "" ' Used only if above string is not empty
Private Const MSQL_64kb_AVG As Long = 2048 ' ALWAYS < 65536 (to be consistent with MS Access). Set to max expected size of Access MEMO field (to preserve space in mSQL v1)
Private Const WS_REPLACEMENT As String = "_" ' Use "" to simply eat whitespaces in identifiers (table and field names)
Private Const IDENT_MAX_SIZE As Integer = 19 ' Suggest 64. Max size of identifiers (table and field names)
Private Const PREFIX_ON_KEYWORD As String = "_" ' Prefix to add to identifier, if it is a reserved word
Private Const SUFFIX_ON_KEYWORD As String = "" ' Suffix to add to identifier, if it is a reserved word
Private Const PREFIX_ON_INDEX As String = "ix" ' Prefix to add to index identifier, to make it unique (mSQL v2)
Private Const SUFFIX_ON_INDEX As String = "" ' Suffix to add to index identifier, to make it unique (mSQL v2)
Private Const ADD_SQL_FILE As String = "c:\temp\esql_add.txt" ' Use empty if open on #1. Will be overwritten if exists!
Private Const DEL_SQL_FILE As String = "c:\temp\esql_del.txt" ' Use empty if open on #2. Will be overwritten if exists!
Private Const LINE_BREAK As String = "\n" ' Try "<br>". String to replace line breaks in text fields
Private Const QUERY_SEPARATOR As String = "\g" ' Terminator/separator of SQL queries (to instruct some monitor program to execute them)
Private Const COMMENT_PREFIX As String = "#" ' Use empty string for no comments
Private Const DISPLAY_WARNINGS As Boolean = True ' False to output the warnings to the files, only
Private Const DATE_AS_STR As Boolean = True ' False to use real number data type for date, time and timestamp (in mSQL only)
Private Const PARA_INSERT_AFTER As Integer = 3 ' Field count after which print INSERTs different lines
Private Const INDENT_SIZE As Integer = 5 ' Number of spaces on indents
' Global var to store inter-funtion data
Private warnings As String ' Not an option: do not set in any way
' Primary Export Function
Sub exportSQL()
On Error GoTo exportSQL_error
Dim cdb As Database
Dim ctableix As Integer, ctablename As String
If DB_NAME = "" Then
Set cdb = CurrentDb()
Else
Set cdb = OpenDatabase(DB_NAME, False, True, DB_CONNECT) ' Shared, read-only
End If
If ADD_SQL_FILE <> "" Then Open ADD_SQL_FILE For Output As #1
If DEL_SQL_FILE <> "" Then Open DEL_SQL_FILE For Output As #2
DoCmd.Hourglass True
If COMMENT_PREFIX <> "" Then
Print #1, COMMENT_PREFIX & " Exported from MS Access to " & IIf(Left$(DB_ENGINE, 2) = "MY", "MySQL", "mSQL")
Print #1, COMMENT_PREFIX & " (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net"
Print #1,
Print #2, COMMENT_PREFIX & " Exported from MS Access to " & IIf(Left$(DB_ENGINE, 2) = "MY", "MySQL", "mSQL")
Print #2, COMMENT_PREFIX & " (C) 1997-98 CYNERGI - www.cynergi.net, info@cynergi.net"
Print #2,
End If
'Go through the table definitions
For ctableix = 0 To cdb.TableDefs.Count - 1
Dim cfieldix As Integer, cfieldname As String
Dim fieldlst As String, sqlcode As String
Dim primary_found As Boolean
Dim crs As Recordset
' Let's take only the visible tables
If (((cdb.TableDefs(ctableix).Attributes And DB_SYSTEMOBJECT) Or _
(cdb.TableDefs(ctableix).Attributes And DB_HIDDENOBJECT))) = 0 Then
ctablename = conv_name("" & cdb.TableDefs(ctableix).Name)
Print #2,
Print #2, "DROP TABLE " & ctablename & QUERY_SEPARATOR
' CREATE clause
Print #1,
Print #1, "CREATE TABLE " & ctablename
Print #1, Space$(INDENT_SIZE) & "("
warnings = ""
fieldlst = ""
primary_found = False
' loop thorugh each field in the table
For cfieldix = 0 To cdb.TableDefs(ctableix).Fields.Count - 1
Dim typestr As String, fieldsz As Integer, dvstr As String
Dim found_ix As Boolean, cindex As Index, cfield As Field
' if this is not the first iteration, add separators
If fieldlst <> "" Then
fieldlst = fieldlst & ", "
Print #1, ","
End If
' get field name
cfieldname = conv_name("" & cdb.TableDefs(ctableix).Fields(cfieldix).Name)
fieldlst = fieldlst & cfieldname
' translate types
If DB_ENGINE = "M1" Or DB_ENGINE = "M2" Then
Select Case cdb.TableDefs(ctableix).Fields(cfieldix).Type
Case dbChar
typestr = "CHAR(" & cdb.TableDefs(ctableix).Fields(cfieldix).Size & ")"
Case dbText
fieldsz = cdb.TableDefs(ctableix).Fields(cfieldix).Size
If fieldsz = 0 Then fieldsz = 255
typestr = "CHAR(" & fieldsz & ")"
Case dbBoolean, dbByte, dbInteger, dbLong
typestr = "INT"
Case dbDouble, dbFloat, dbSingle
typestr = "REAL"
Case dbCurrency, dbDecimal, dbNumeric
typestr = "REAL"
warn "In new field '" & cfieldname & "', currency/BCD will be converted to REAL - there may be precision loss!", False
Case dbDate
typestr = IIf(DATE_AS_STR, "CHAR(19)", "REAL") ' use Access internal format: IEEE 64-bit (8-byte) FP
warn "In new field '" & cfieldname & "', date/time/timestamp will be converted to " & typestr & ".", False
Case dbTime
typestr = IIf(DATE_AS_STR, "CHAR(8)", "REAL") ' use Access internal format: IEEE 64-bit (8-byte) FP
warn "In new field '" & cfieldname & "', date/time/timestamp will be converted to " & typestr & ".", False
Case dbTimeStamp
typestr = IIf(DATE_AS_STR, "CHAR(19)", "REAL") ' use Access internal format: IEEE 64-bit (8-byte) FP
warn "In new field '" & cfieldname & "', date/time/timestamp will be converted to " & typestr & "." & IIf(DB_ENGINE = "M2", " Consider using pseudo field '_timestamp'.", ""), False
Case dbMemo
If DB_ENGINE = "M2" Then
typestr = "TEXT(" & MSQL_64kb_AVG & ")"
Else
typestr = "CHAR(" & MSQL_64kb_AVG & ")"
warn "In new field '" & cfieldname & "', dbMemo is not supported by mSQL v1 - fields larger than MSQL_64kb_AVG (" & MSQL_64kb_AVG & ") will not be accepted!", False
End If
Case dbBinary, dbVarBinary
typestr = "CHAR(255)"
warn "In new field '" & cfieldname & "', dbBinary and dbVarBinary are not supported by mSQL! - will use a text (CHAR(255)) field.", True
Case dbLongBinary
typestr = "CHAR(" & MSQL_64kb_AVG & ")"
warn "In new field '" & cfieldname & "', dbLongBinary is not supported by mSQL! - will use a text (CHAR(" & MSQL_64kb_AVG & ")) field.", True
Case Else
warn "In new field '" & cfieldname & "', dbBigInt and dbGUID are not currently supported!", True
Error 5 ' invalid Procedure Call
End Select
Else
Select Case cdb.TableDefs(ctableix).Fields(cfieldix).Type
Case dbBinary
typestr = "TINYBLOB"
Case dbBoolean
typestr = "TINYINT"
Case dbByte
typestr = "TINYINT UNSIGNED"
Case dbChar
typestr = "CHAR(" & cdb.TableDefs(ctableix).Fields(cfieldix).Size & ")"
Case dbCurrency
typestr = "DECIMAL(20,4)"
Case dbDate
typestr = "DATETIME"
Case dbDecimal
typestr = "DECIMAL(20,4)"
Case dbDouble
typestr = "REAL"
Case dbFloat
typestr = "REAL"
Case dbInteger
typestr = "SMALLINT"
Case dbLong
typestr = "INT"
Case dbLongBinary
typestr = "LONGBLOB"
Case dbMemo
typestr = "LONGBLOB" ' !!!!! MySQL bug! Replace by LONGTEXT when corrected!
Case dbNumeric
typestr = "DECIMAL(20,4)"
Case dbSingle
typestr = "FLOAT"
Case dbText
fieldsz = cdb.TableDefs(ctableix).Fields(cfieldix).Size
If fieldsz = 0 Then fieldsz = 255
typestr = "CHAR(" & fieldsz & ")"
Case dbTime
typestr = "TIME"
Case dbTimeStamp
typestr = "TIMESTAMP"
Case dbVarBinary
typestr = "TINYBLOB"
Case dbBigInt, dbGUID
warn "In new field '" & cfieldname & "', dbBigInt and dbGUID are not currently supported!", True
Error 5 ' invalid Procedure Call
Case Else
typestr = "LONGBLOB"
End Select
End If
' check not null and auto-increment properties
If ((cdb.TableDefs(ctableix).Fields(cfieldix).Attributes And dbAutoIncrField) <> 0) Then
If Left$(DB_ENGINE, 2) = "MY" Then
typestr = typestr & " NOT NULL AUTO_INCREMENT"
Else
typestr = typestr & " NOT NULL"
warn "In new field '" & cfieldname & "', mSQL does not support auto-increment fields! - they will be pure INTs." & IIf(DB_ENGINE = "M2", " Consider using pseudo field '_rowid' or SEQUENCEs.", ""), False
End If
ElseIf cdb.TableDefs(ctableix).Fields(cfieldix).Required = True Then
typestr = typestr & " NOT NULL"
End If
&nb
The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it is an aesthetic experience much like composing poetry or music.
:strip_exif()/u/27738/yelloguy_468x60_15k.gif?f=community)
/u/661/crop5cca8f904dc18_cropped.png?f=community)