Converting Access database to SQL Server w/ flat files

I recently received a large Access database that I needed to do a lot
of analysis on. Since I prefer working with SQL Server (to put things
mildly) and I dislike writing VB/VBA code, I thought I'd share my
solution. I know someone will find this useful when searching the
archives.
There are probably much more efficient ways of doing this, but this
solution works well enough for me. There are three functions here that
simplify converting an Access schema to SQL Server and loading the data
into that schema. The first function generates all of the CREATE TABLE
statements for SQL Server. There may be some data types off the list,
since I only used the ones I needed for this conversion. Also, there
are no data contraints or indexes defined. The second dumps all the
data in all the tables to pipe-delimitted flat files and the third
generates all the BULK INSERT statements to load the data into SQL
Server. While I could've left the table names as they were and used
brackets or quotes, I hate writing them in my SQL, so I decided to
replace bad characters with underscores. I hope I've saved someone some
time here.
-Alan
'By Alan Samet 3/8/2006
'Use this code at your own risk. I claim no liability. If it works for
you and you share it
'please give me credit. If it messes things up, take the credit
yourself.
'
'There are three functions you need to use:
' GenerateSqlServerSchema -- creates the schema for SQL Server
' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
' GenerateBulkInsertStatements -- generates the BULK INSERT
statements
'
'All paths need to be fully-qualified. Where a directory is an
argument,
'don't append the ending backslash (\)
Public Function GetSqlServerDataType(accessDataType As Integer, size As
Integer) As String
Select Case accessDataType
Case dbInteger: '3
GetSqlServerDataType = "INT"
Case dbLong: '4
GetSqlServerDataType = "BIGINT"
Case dbSingle, dbDouble: '6, 7
GetSqlServerDataType = "FLOAT"
Case dbCurrency: '5
GetSqlServerDataType = "MONEY"
Case dbDate: '8
GetSqlServerDataType = "DATETIME"
Case dbText: '10
GetSqlServerDataType = "VARCHAR(" & size & ")"
Case Else
Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
End Select
End Function
Function CleanName(name As String)
'Dim c As New Collection
'c.Add "_", "-"
'c.Add "_", " "
'c.Add "", "(R)"
'c.Add "_", "/"
'Dim s As Variant
CleanName = name
'For Each s In c
' CleanName = Replace(CleanName, s, c(s))
'Next
CleanName = Replace(CleanName, "-", "_")
CleanName = Replace(CleanName, " ", "_")
CleanName = Replace(CleanName, "(R)", "")
CleanName = Replace(CleanName, "/", "_")
CleanName = Replace(CleanName, "_&", "_And")
CleanName = Replace(CleanName, "1st", "First")
CleanName = Replace(CleanName, "2nd", "Second")
CleanName = Replace(CleanName, "3rd", "Third")
CleanName = Replace(CleanName, "4th", "Fourth")
End Function
Public Function GenerateSqlServerSchema(databasePath As String) As
String
Dim db As Database
Set db = OpenDatabase(databasePath)
'Dim types(50) As Boolean
Dim t As TableDef
Dim c As Field
Dim createTableSql As String
Dim fullScript As String
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
'Debug.Print t.Name
createTableSql = "CREATE TABLE " & CleanName(t.name) &
vbCrLf & "(" & vbCrLf
Dim bIsFirst As Boolean
bIsFirst = True
For Each c In t.Fields
If Not bIsFirst Then createTableSql = createTableSql &
","
bIsFirst = False
createTableSql = createTableSql & vbTab &
CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
vbCrLf
'types(c.Type) = True
Next
createTableSql = createTableSql & ")"
fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
& vbCrLf & vbCrLf & "GO"
End If
Next
GenerateSqlServerSchema = fullScript
' Dim i As Integer
' For i = 0 To UBound(types)
' If types(i) Then Debug.Print i
' Next
End Function
Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
String)
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
Dim i As Integer
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
Output As #1
Dim values() As String
ReDim values(t.Fields.Count - 1) As String
With t.OpenRecordset(dbOpenForwardOnly)
While Not .EOF
For i = 0 To .Fields.Count - 1
values(i) = Nz(.Fields(i).Value, "")
Next
Print #1, Join(values, "|")
.MoveNext
Wend
End With
Close #1
End If
Next
db.Close
End Sub
Public Function GenerateBulkInsertStatements(databasePat
h As String,
rootExportPath As String) As String
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
GenerateBulkInsertStatements = GenerateBulkInsertStatements
& _
"RAISERROR('Loading " & CleanName(t.name) & "', 10,
10)" & vbCrLf & _
"BULK INSERT " & CleanName(t.name) & " FROM '" & _
rootExportPath & "\" & CleanName(t.name) & _
".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
& vbCrLf
End If
Next
db.Close
End FunctionIs there a reason you didn't just use Data Transformation Services?
You could just create the package and if necessary even run
the package from your vbscript.
Why go through the trouble of doing all this manually?
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141871152.130312.288310@.i39g2000cwa.googlegroups.com...
>I recently received a large Access database that I needed to do a lot
> of analysis on. Since I prefer working with SQL Server (to put things
> mildly) and I dislike writing VB/VBA code, I thought I'd share my
> solution. I know someone will find this useful when searching the
> archives.
> There are probably much more efficient ways of doing this, but this
> solution works well enough for me. There are three functions here that
> simplify converting an Access schema to SQL Server and loading the data
> into that schema. The first function generates all of the CREATE TABLE
> statements for SQL Server. There may be some data types off the list,
> since I only used the ones I needed for this conversion. Also, there
> are no data contraints or indexes defined. The second dumps all the
> data in all the tables to pipe-delimitted flat files and the third
> generates all the BULK INSERT statements to load the data into SQL
> Server. While I could've left the table names as they were and used
> brackets or quotes, I hate writing them in my SQL, so I decided to
> replace bad characters with underscores. I hope I've saved someone some
> time here.
> -Alan
> 'By Alan Samet 3/8/2006
> 'Use this code at your own risk. I claim no liability. If it works for
> you and you share it
> 'please give me credit. If it messes things up, take the credit
> yourself.
> '
> 'There are three functions you need to use:
> ' GenerateSqlServerSchema -- creates the schema for SQL Server
> ' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
> ' GenerateBulkInsertStatements -- generates the BULK INSERT
> statements
> '
> 'All paths need to be fully-qualified. Where a directory is an
> argument,
> 'don't append the ending backslash (\)
> Public Function GetSqlServerDataType(accessDataType As Integer, size As
> Integer) As String
> Select Case accessDataType
> Case dbInteger: '3
> GetSqlServerDataType = "INT"
> Case dbLong: '4
> GetSqlServerDataType = "BIGINT"
> Case dbSingle, dbDouble: '6, 7
> GetSqlServerDataType = "FLOAT"
> Case dbCurrency: '5
> GetSqlServerDataType = "MONEY"
> Case dbDate: '8
> GetSqlServerDataType = "DATETIME"
> Case dbText: '10
> GetSqlServerDataType = "VARCHAR(" & size & ")"
> Case Else
> Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
> End Select
> End Function
> Function CleanName(name As String)
> 'Dim c As New Collection
> 'c.Add "_", "-"
> 'c.Add "_", " "
> 'c.Add "", "(R)"
> 'c.Add "_", "/"
> 'Dim s As Variant
> CleanName = name
> 'For Each s In c
> ' CleanName = Replace(CleanName, s, c(s))
> 'Next
> CleanName = Replace(CleanName, "-", "_")
> CleanName = Replace(CleanName, " ", "_")
> CleanName = Replace(CleanName, "(R)", "")
> CleanName = Replace(CleanName, "/", "_")
> CleanName = Replace(CleanName, "_&", "_And")
> CleanName = Replace(CleanName, "1st", "First")
> CleanName = Replace(CleanName, "2nd", "Second")
> CleanName = Replace(CleanName, "3rd", "Third")
> CleanName = Replace(CleanName, "4th", "Fourth")
> End Function
> Public Function GenerateSqlServerSchema(databasePath As String) As
> String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> 'Dim types(50) As Boolean
> Dim t As TableDef
> Dim c As Field
> Dim createTableSql As String
> Dim fullScript As String
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> 'Debug.Print t.Name
> createTableSql = "CREATE TABLE " & CleanName(t.name) &
> vbCrLf & "(" & vbCrLf
> Dim bIsFirst As Boolean
> bIsFirst = True
> For Each c In t.Fields
> If Not bIsFirst Then createTableSql = createTableSql &
> ","
> bIsFirst = False
> createTableSql = createTableSql & vbTab &
> CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
> vbCrLf
> 'types(c.Type) = True
> Next
> createTableSql = createTableSql & ")"
> fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
> & vbCrLf & vbCrLf & "GO"
> End If
> Next
> GenerateSqlServerSchema = fullScript
> ' Dim i As Integer
> ' For i = 0 To UBound(types)
> ' If types(i) Then Debug.Print i
> ' Next
> End Function
> Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
> String)
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> Dim i As Integer
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
> Output As #1
> Dim values() As String
> ReDim values(t.Fields.Count - 1) As String
> With t.OpenRecordset(dbOpenForwardOnly)
> While Not .EOF
> For i = 0 To .Fields.Count - 1
> values(i) = Nz(.Fields(i).Value, "")
> Next
> Print #1, Join(values, "|")
> .MoveNext
> Wend
> End With
> Close #1
> End If
> Next
> db.Close
> End Sub
> Public Function GenerateBulkInsertStatements(databasePat
h As String,
> rootExportPath As String) As String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> GenerateBulkInsertStatements = GenerateBulkInsertStatements
> & _
> "RAISERROR('Loading " & CleanName(t.name) & "', 10,
> 10)" & vbCrLf & _
> "BULK INSERT " & CleanName(t.name) & " FROM '" & _
> rootExportPath & "\" & CleanName(t.name) & _
> ".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
> & vbCrLf
> End If
> Next
> db.Close
> End Function
>|||Well, for one, I've never had any use for DTS. From my understanding,
you have to use that cumbersome GUI to make DTS packages. With the
exception of well-done tools like Interface Builder on OSX, I don't
like using a GUI to develop my applications -- especially when I can't
work with code. I've been able to do anything that DTS can do using SQL
Server Agent and script (preferably Python). The code that I just
posted only took a couple hours to write, so it really wasn't that much
work, aside from the unpleasantness of working with Access and VBA.
Also, in this situation, I don't want my SQL Server touching this
Access database. Since there are a lot of objects that I haven't looked
at in this database, I was running it on a virtual machine, pulling the
data out and then loading the data onto my development machine. This
method totally isolates the two.
-Alan|||No offense, but I have to agree with Robbe - DTS may have been a better
choice. The GUI tools reduce the time it takes to build stuff and
encapsulates all sorts of error checking etc. While it took a couple
of hours for you to write this manually, someone familiar with DTS
would probably accomplish the same result as the code posted in about 5
to 10 minutes. (And with fewer debugging steps - unless like most of
us you write perfect code all the time. LOL!)|||The Access Upsizing Wizard is also a real easy to use tool. The last
time I used it was probably 6 or 8 yrs ago... surely it is even better
now though. Some documentation here:
http://support.microsoft.com/defaul...B;EN-US;Q325017|||I do write perfect code all the time. It's why I dislike being forced
to use Microsoft's development tools so much.
-Alan