Mailing List Archive
Back to the month index |
Back to the list index
|
|
Back to the month index |
Back to the list index
|
Brian Andrews (brian@informate.co.nz)
Mon, 27 Jan 1997 16:26:10 +1300
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
- Next message: Ryan Scott: "[mSQL] embedding \n into fields"
- Previous message: Ryan Scott: "[mSQL] not null problem"
- Next in thread: Marcos Manhaes Marins: "Re: [mSQL] MS-Access to mSQL?"
Message-Id: <199701270328.QAA22391@ihug.co.nz> From: "Brian Andrews" <brian@informate.co.nz> Subject: Re: [mSQL] MS-Access to mSQL? Date: Mon, 27 Jan 1997 16:26:10 +1300----------
> From: Joshua Kramer <josh@iwaynet.net>
> To: msql-list@bunyip.com
> Subject: [mSQL] MS-Access to mSQL?
> Date: Monday, 27 January 1997 08:47
>
> Hello,
>
> I'm new to the list, and I've got a question: I'm going to be taking
> over a web site which used MS-Access as a database backbone. I've got a
> 1.8 meg .mdb file which I suspect is the MS-Access database. How can I
> convert this file to a tab-delimited database so I can import it to
> mSQL?
>
> Thanks,
> Josh
> --
Different Approach:
I have an Access function below which will create mSql insert statements
(not tabbed delimited files) for all tables
in a db. It will escape single quotes and restrict the length of
table and field names. It will also remove spaces from table and field
names.
It is based on a function originally by someone else - sorry I can't
remember your name!
Please note: this works for Access 7
here it is:
----------------------------------------------------------------------------
Function export_mSQL()
' Exports the database contents into a file in mSQL format
' IS NOT SELECTIVE! (exports ALL tables)
Dim dbase As DATABASE, tdef As Recordset, i As Integer, fd As Integer
Set dbase = CurrentDb()
' Open the export file
Open "\temp\ipacweb.txt" For Output As #1
Print #1, "# Converted from MS Access to mSQL "
Print #1, "# by Brian Andrews, (c) InforMate Technologies, 1997"
Print #1, ""
' Go through the table definitions
For i = 0 To dbase.TableDefs.Count - 1
' Let's take only the visible tables
If ((dbase.TableDefs(i).Attributes And DB_SYSTEMOBJECT) Or
(dbase.TableDefs(i).Attributes And DB_HIDDENOBJECT)) Then
Else
' We DROP the table in case it already exists
' and then create it again
tname = "" & dbase.TableDefs(i).Name
'remove spaces from tablename
For j = 1 To Len(tname)
If j < Len(tname) Then
If Mid$(tname, j, 1) = " " Then
s = Left$(tname, j - 1)
's = s & "" & Right$(t.Fields(j), Len(t.Fields(j))
- i + 1)
s = s & "" & Right$(tname, Len(tname) - j)
j = j + 1
found = True
tname = s
'Exit For
End If
End If
Next j
'restrict tablename to 19 chars
tname = Left$(tname, 19)
Print #1, ""
Print #1, ""
Print #1, "DROP TABLE " & tname & " \p\g"
Print #1,
Print #1, "CREATE TABLE " & tname & "("
' Step through all the fields in the table
For fd = 0 To dbase.TableDefs(i).Fields.Count - 1
'All fields are char at the moment - can be changed
Dim tyyppi As String, pituus As Integer, comma As String
Select Case dbase.TableDefs(i).Fields(fd).Type
Case DB_BOOLEAN, DB_INTEGER, DB_LONG, DB_BYTE,
DB_LONGBINARY
tyyppi = "char (8)"
Case DB_DOUBLE, DB_SINGLE, DB_CURRENCY
tyyppi = "char (20)"
Case DB_TEXT, DB_DATE
pituus = dbase.TableDefs(i).Fields(fd).Size
tyyppi = "char (" & pituus & ")"
Case DB_MEMO, db_GUID
'Special case fields - specific to one of my projects
If dbase.TableDefs(i).Fields(fd).Name = "Fund
Summary" Then
tyyppi = "char (1500)"
ElseIf dbase.TableDefs(i).Fields(fd).Name = "Fund
Analysis" Then
tyyppi = "char (3000)"
Else
tyyppi = "char (255)"
End If
End Select
' Don't print the separating comma after the last field
If fd < dbase.TableDefs(i).Fields.Count - 1 Then
comma = ","
Else
comma = ""
End If
' Print the field definition
'remove spaces from fieldname
stuff = "" & dbase.TableDefs(i).Fields(fd).Name
For j = 1 To Len(stuff)
If j < Len(stuff) Then
If Mid$(stuff, j, 1) = " " Then
s = Left$(stuff, j - 1)
's = s & "" & Right$(t.Fields(j), Len(t.Fields(j))
- i + 1)
s = s & "" & Right$(stuff, Len(stuff) - j)
j = j + 1
found = True
stuff = s
'Exit For
End If
End If
Next j
stuff = Left$(stuff, 19)
'mSQL 1 primary key declaration - always on first field
If fd = 0 Then
Print #1, " " & stuff & " " & tyyppi & " primary key" &
comma
Else
Print #1, " " & stuff & " " & tyyppi & comma
End If
Next fd
Print #1, ")\p\g"
Print #1, ""
Dim recset As Recordset
Set recset = dbase.OpenRecordset(dbase.TableDefs(i).Name)
' Step through the rows in the table
recset.MoveFirst
Do Until recset.EOF
Dim row As String, it As String
row = "INSERT INTO " & tname & " VALUES ("
' Go through the fields in the row
For fd = 0 To recset.Fields.Count - 1
Dim is_string As String
is_string = ""
'Select Case recset.Fields(fd).Type
'Case DB_TEXT, DB_MEMO, db_GUID, DB_DATE
is_string = "'"
'End Select
'**** escape single quotes
stuff = "" & recset.Fields(fd).Value
For j = 1 To Len(stuff)
If Mid$(stuff, j, 1) = "'" Then
s = Left$(stuff, j - 1)
's = s & "\" & Right$(t.Fields(j), Len(t.Fields(j))
- i + 1)
s = s & "\" & Right$(stuff, Len(stuff) - j + 1)
j = j + 1
found = True
stuff = s
'Exit For
End If
Next j
row = row & is_string & stuff & is_string
If fd < recset.Fields.Count - 1 Then
row = row & ","
End If
Next fd
' Add trailers and print
row = row & ")\p\g"
Print #1, row
' Move to the next row
recset.MoveNext
Loop
recset.Close
Set recset = Nothing
End If
'En'd If
Next i
Close #1
dbase.Close
Set dbase = Nothing
End Function
-------------------------------------------
Cheers
brian
----------------------------------------------------------------------------
Brian Andrews
InforMate Technologies Limited
Technology Consultants for Information Automation
Auckland, New Zealand
Ph (+64) 9-489-1001
Fax (+64) 9-489-1002
Mob (+64) 21-OFFICE (633-423)
brian@informate.co.nz
http://www.informate.co.nz
----------------------------------------------------------------------------
--------------------------------------------------------------------------
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!
- Next message: Ryan Scott: "[mSQL] embedding \n into fields"
- Previous message: Ryan Scott: "[mSQL] not null problem"
- Next in thread: Marcos Manhaes Marins: "Re: [mSQL] MS-Access to mSQL?"