Mailing List Archive


Back to the month index Back to the list index
Marko A. Grönroos (magi@utu.fi)
Mon, 2 Sep 1996 22:39:28 +0300 (EET DST) 

Date: 	Mon, 2 Sep 1996 22:39:28 +0300 (EET DST)
From: Marko A. Grönroos <magi@utu.fi>
Subject: Re: [mSQL] mSQL and MS Access
Message-Id: <Pine.GSO.3.93.960902222220.13344A-100000@sol.utu.fi>

I'm not currently receiving msql-list, so please write to me directly (and
perhaps cc to msql-list.

On Tue, 27 Aug 1996, Brian Goldfarb wrote:
> I have a Microsoft Access database and this script to export to mSQL.. Now
> I have no clue what to do with it.

This little script wasn't really intended to be
"published", so it may not be suited for everyone as it is. Adapting it to
your particular need DOES require some skills in the Access scripting
language...
  To use it:
 1) paste the text into an Access Module.
 2) compile the module.
 3) open Debug Window in the module editor
 4) type "export_msql<ENTER>"
This will export ALL of your tables into file \temp\dbase-export.sql.
To export just a part of the files or to save the output to another file,
you need to edit the source. I can't use Access on my machine for a while
so I can't modify the script. You might want to change it to:

    public sub export_mSQL(tablename as string, filename as string)

I'll post a newer version when I can access Access again (I'm working in
Linux for a few weeks now and booting to Windows loses all my beautiful
Xwindows).

I think the following modifications should do it (sorry if this gets
messy):

> >>------------------------------------- clipclipclipclip
> >-----------------------------------------
> >>
> >>Public Sub export_mSQL()

Should be: public sub export_mSQL(tablename as string, filename as string)

> >> ' Exports the database contents into a file in mSQL format
> >> ' IS NOT SELECTIVE! (exports ALL tables)
> >>
> >> Dim dbase As Database, tdef As TableDef, i As Integer, Field As Integer
> >> Dim Fields As Collection, fld As Field
> >> Set dbase = CurrentDb
> >>
> >> ' Open the export file
> >> Open "\temp\dbase-export.sql" For Output As #1

Should be: Open filename For Output As #1

> >>
> >> Print #1, "# Converted from MS Access to mSQL by export_mSQL"
> >> Print #1, "# export_mSQL by Marko Grönroos (magi@utu.fi), 1996"
> >> Print #1, ""
> >>
> >> ' Go through the table definitions
> >>
> >> For Each tdef In dbase.TableDefs
> >>
> >> ' Let's take only the visible tables
> >> ' Yes I know this If-Else looks weird, but Access's Not worked
> incorrectly....
> >> If ((tdef.Attributes And dbSystemObject) Or (tdef.Attributes And
> >dbHiddenObject)) Then
> >> Else

Should be:
        if tdef.name=tablename then
(forget the Else-line too)

> >>
> >> ' We DROP the table in case it alread exists
> >> ' and then create it again
> >>
> >> Print #1, ""
> >> Print #1, ""
> >> Print #1, "DROP TABLE " & tdef.Name & " \g"
> >> Print #1, "CREATE TABLE " & tdef.Name & " ("
> >>
> >> Debug.Print "Exporting " & tdef.Name
> >>
> >> ' Step through all the fields in the table
> >>
> >> For Field = 0 To tdef.Fields.Count - 1
> >>
> >> Dim tyyppi As String, pituus As Integer, comma As String
> >> Select Case tdef.Fields(Field).Type
> >> Case dbBoolean, dbInteger, dbLong, dbByte, dbLongBinary
> >> tyyppi = "int"
> >> Case dbDouble, dbSingle, dbCurrency, dbDate
> >> tyyppi = "real"
> >> Case dbText
> >> pituus = tdef.Fields(Field).Size
> >> tyyppi = "char (" & pituus & ")"
> >> Case dbMemo, dbGUID
> >> tyyppi = "char (50)"
> >> End Select
> >>
> >> ' Don't print the separating comma after the last field
> >>
> >> If Field < tdef.Fields.Count - 1 Then
> >> comma = ","
> >> Else
> >> comma = ""
> >> End If
> >>
> >> ' Print the field definition
> >>
> >> Print #1, " " & tdef.Fields(Field).Name & " " & tyyppi
> >& comma
> >>
> >> Next Field
> >>
> >> Print #1, ")\g"
> >> Print #1, ""
> >>
> >> ' Ok, then we print the actual contents of the table
> >> ' This should be done after all the tabledefs are printed, but
> >I'm lazy
> >>
> >> Dim recset As Recordset
> >> Set recset = dbase.OpenRecordset(tdef.Name)
> >>
> >> ' Step through the rows in the table
> >>
> >> recset.MoveFirst
> >> Do Until recset.EOF
> >> Dim row As String
> >> row = "INSERT INTO " & tdef.Name & " VALUES ("
> >>
> >> ' Go through the fields in the row
> >>
> >> For Field = 0 To recset.Fields.Count - 1
> >> Dim is_string As String
> >>
> >> is_string = ""
> >> Select Case recset.Fields(Field).Type
> >> Case dbText, dbMemo, dbGUID
> >> is_string = "'"
> >> End Select
> >>
> >> row = row & is_string & recset.Fields(Field).Value &
> >is_string
> >> If Field < recset.Fields.Count - 1 Then
> >> row = row & ","
> >> End If
> >> Next Field
> >>
> >> ' Add trailers and print
> >>
> >> row = row & ")\g"
> >> Print #1, row
> >>
> >> ' Move to the next row
> >>
> >> recset.MoveNext
> >> Loop
> >>
> >> recset.Close
> >> Set recset = Nothing
> >>
> >> End If
> >>
> >> Next tdef
> >>
> >> Close #1
> >>
> >> dbase.Close
> >> Set dbase = Nothing
> >>End Sub
> >>
> >>-------------------------------------- clipclipclip
> >-----------------------------------------
> >>
> >>--
> >>----
> >>-- Marko Grönroos, magi@utu.fi (http://www.utu.fi/~magi/), magi@nic.funet.fi
> >>-- YO-kylä 27 A 9, 20540 Turku, Finland, Tel#+358-21-373337
> >>----
> >>--------------------------------------------------------------------------
> >>To remove yourself from the Mini SQL mailing list send a message containing
> >>"unsubscribe" to > >>"unsubscribe" to msql-list-request@bunyip.com. Send a message containing
> >>"info msql-list" to majordomo@bunyip.com for info on monthly archives of
> >>the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!
> >>
> >>
> >-Kerry
> >-----------------------------------------------------------------
> >Kerry Garrison garrison@delta.net
> >Delta Design Services http://www.delta-design.com
> > Web Site Design, On-line Business Planning, and Training
> > (714) 778-0370 (714) 778-1064 FAX
> > http://www.deltanet.com/users/garrison/
> >-----------------------------------------------------------------
> >
> >
> >
> -----------------------------------------------------------------
> Brian Goldfarb briang@delta.net
> Delta Design Services http://www.delta-design.com
> Web Site Design, On-line Business Planning, and Training
> (714) 490-2000 Ext: 3030 (714) 778-1064 FAX
> -----------------------------------------------------------------
>

--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com. Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!