SQL Azure to MS Access – Using VBA to Dump Azure Tables into MS Access Tables

The first thing you need to get sorted when moving to SQL Azure is having the ability to get your information out and safe if needs be. When experimenting with MS Azure and for applications that don’t have sensitive information it is nice to have that information available in an easily accessible format. Here are a series of functions that will copy Azure Tables linked to database into local MS Access tables with the prefix ZCOPY.

The starting point in this should be an MS Access database that should be linked to your SQL Azure Database. Only those tables that are linked will be copied. Remember the 2GB limit on Access.

I think I have got all the functions here that are required to make it work and include the complete module at the bottom but first I will breakdown the modules and list describe what each of the functions do.

First create a table to store the list of tables in the Azure Database

Public Function CreateTableT0001AzureTablesGlobal()

Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "CREATE TABLE T0001AzureTablesGlobal " _
& "(PKID AUTOINCREMENT, " _
& "AzureTableName CHAR CONSTRAINT PKID " _
& "PRIMARY KEY);"

End Function

Now Create a Function that will hold the SQL that takes the tables and makes them locally.

Public Function CreateTableT0002SQL()

Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "CREATE TABLE T0002SQL " _
& "(PKID AUTOINCREMENT, " _
& "SQL MEMO CONSTRAINT PKID " _
& "PRIMARY KEY);"

End Function

A function that allows for stepping through the table
Public Function AddByteColumn(TblName As String, FieldName As String)
'Just use byte data type as only going to use this for a flag

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " BYTE;"

End Function

Step through the Linked Azure Tables and poupulate table T001 with their names

Public Function CreateandPopulateListofDBOTableNames()

'These will typically be the names of the SQL Server tables this should work both with SQL Server and SQL Azure

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rstList As DAO.Recordset

'Call CreateTableT0001AzureTablesGlobal

Set rstList = CurrentDb.OpenRecordset("T0001AzureTablesGlobal")
Set db = CurrentDb

For Each tdf In db.TableDefs
' ignore system and temporary tables and tables starting with T - personal choice option
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "T*") Then
With rstList
.AddNew
rstList!AzureTableName = tdf.Name
rstList.Update
End With
End If

Next

Set tdf = Nothing
Set db = Nothing

End Function

The next function is required to strip out additional spaces in names

Public Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

Dim strSQL As String

strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"

DoCmd.RunSQL strSQL

End Function

We can now write the VBA that will write the make table SQL that once run will put one make table query into the maketableSQL table for each Azure table.

Public Function CreateMakeTableSQL()

On Error GoTo Err_CreateMakeTableSQL
Dim rstSQL As DAO.Recordset
Dim rstSQLx As DAO.Recordset
Dim dbc As DAO.Database
Dim SQLStringAdd As String
Dim LCounter As Long

Set dbc = CurrentDb

LCounter = 1
While LCounter < 9000

LCounter = LCounter + 1
Set rstSQL = CurrentDb.OpenRecordset("SELECT T0001AzureTablesGlobal.PKID, T0001AzureTablesGlobal.AzureTableName, T0001AzureTablesGlobal.XFLag1 FROM T0001AzureTablesGlobal WHERE (((T0001AzureTablesGlobal.XFLag1) Is Null));")

SQLStringAdd = "SELECT * INTO COPY" & rstSQL!AzureTableName & " FROM " & rstSQL!AzureTableName & ";"

Set rstSQLx = CurrentDb.OpenRecordset("T0002SQL")
With rstSQLx
.AddNew
rstSQLx!SQL = SQLStringAdd
rstSQLx.Update
rstSQLx.Close
End With

With rstSQL
rstSQL.Edit
rstSQL!XFLag1 = 1
rstSQL.Update
rstSQL.MoveNext
rstSQL.Close
End With

Wend

Exit_CreateMakeTableSQL:
Exit Function

Err_CreateMakeTableSQL:
Select Case Err.Number
Case 3021
Resume Exit_CreateMakeTableSQL
Case Else
Resume Exit_CreateMakeTableSQL
End Select

End Function

And finally Run all the queries

Public Function RunQueriesFromTable2(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL2 As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL2 = rstZ!SQL
DoCmd.RunSQL strSQL2
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

And a script to pull all of this together

Public Function GetAzureScript()

DoCmd.SetWarnings False
Call CreateTableT0001AzureTablesGlobal
Call CreateandPopulateListofDBOTableNames
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", " ", "")
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", Chr(10), "")
Call AddByteColumn("T0001AzureTablesGlobal", "XFLag1")
Call CreateTableT0002SQL
Call CreateMakeTableSQL
Call FindXReplaceY("T0002SQL", "SQL", " ", "")
Call FindXReplaceY("T0002SQL", "SQL", Chr(10), "")
Call FindXReplaceY("T0002SQL", "SQL", "SELECT*INTOCOPY", "SELECT * INTO ZCOPY")
Call FindXReplaceY("T0002SQL", "SQL", "FROM", " FROM ")
Call RunQueriesFromTable("T0002SQL")
DoCmd.SetWarnings True

End Function

The complete module

Option Compare Database
Option Explicit

Public Function GetAzureScript()

DoCmd.SetWarnings False
Call CreateTableT0001AzureTablesGlobal
Call CreateandPopulateListofDBOTableNames
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", " ", "")
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", Chr(10), "")
Call AddByteColumn("T0001AzureTablesGlobal", "XFLag1")
Call CreateTableT0002SQL
Call CreateMakeTableSQL
Call FindXReplaceY("T0002SQL", "SQL", " ", "")
Call FindXReplaceY("T0002SQL", "SQL", Chr(10), "")
Call FindXReplaceY("T0002SQL", "SQL", "SELECT*INTOCOPY", "SELECT * INTO ZCOPY")
Call FindXReplaceY("T0002SQL", "SQL", "FROM", " FROM ")
Call RunQueriesFromTable("T0002SQL")
DoCmd.SetWarnings True

End Function

Public Function CreateandPopulateListofDBOTableNames()

'These will typically be the names of the SQL Server tables this should work both with SQL Server and SQL Azure

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rstList As DAO.Recordset

'Call CreateTableT0001AzureTablesGlobal

Set rstList = CurrentDb.OpenRecordset("T0001AzureTablesGlobal")
Set db = CurrentDb

For Each tdf In db.TableDefs
' ignore system and temporary tables and tables starting with T - personal choice option
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "T*") Then
With rstList
.AddNew
rstList!AzureTableName = tdf.Name
rstList.Update
End With
End If

Next

Set tdf = Nothing
Set db = Nothing

End Function

Public Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

Dim strSQL As String

strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"

DoCmd.RunSQL strSQL

End Function

Public Function CreateTableT0001AzureTablesGlobal()

Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "CREATE TABLE T0001AzureTablesGlobal " _
& "(PKID AUTOINCREMENT, " _
& "AzureTableName CHAR CONSTRAINT PKID " _
& "PRIMARY KEY);"

End Function

Public Function CreateTableT0002SQL()

Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "CREATE TABLE T0002SQL " _
& "(PKID AUTOINCREMENT, " _
& "SQL MEMO CONSTRAINT PKID " _
& "PRIMARY KEY);"

End Function

Public Function AddByteColumn(TblName As String, FieldName As String)
'Just use byte data type as only going to use this for a flag

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " BYTE;"

End Function

Public Function CreateMakeTableSQL()

On Error GoTo Err_CreateMakeTableSQL
Dim rstSQL As DAO.Recordset
Dim rstSQLx As DAO.Recordset
Dim dbc As DAO.Database
Dim SQLStringAdd As String
Dim LCounter As Long

Set dbc = CurrentDb

LCounter = 1
While LCounter < 9000
LCounter = LCounter + 1
Set rstSQL = CurrentDb.OpenRecordset("SELECT T0001AzureTablesGlobal.PKID, T0001AzureTablesGlobal.AzureTableName, T0001AzureTablesGlobal.XFLag1 FROM T0001AzureTablesGlobal WHERE (((T0001AzureTablesGlobal.XFLag1) Is Null));")

SQLStringAdd = "SELECT * INTO COPY" & rstSQL!AzureTableName & " FROM " & rstSQL!AzureTableName & ";"

Set rstSQLx = CurrentDb.OpenRecordset("T0002SQL")
With rstSQLx
.AddNew
rstSQLx!SQL = SQLStringAdd
rstSQLx.Update
rstSQLx.Close
End With

With rstSQL
rstSQL.Edit
rstSQL!XFLag1 = 1
rstSQL.Update
rstSQL.MoveNext
rstSQL.Close
End With

Wend

Exit_CreateMakeTableSQL:
Exit Function

Err_CreateMakeTableSQL:
Select Case Err.Number
Case 3021
Resume Exit_CreateMakeTableSQL
Case Else
Resume Exit_CreateMakeTableSQL
End Select

End Function

Public Function RunQueriesFromTable2(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL2 As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL2 = rstZ!SQL
DoCmd.RunSQL strSQL2
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, MS Access, Productivity, Programming, SQL Azure, SQL MS Access, SQL Server, VBA Code MS Access. Bookmark the permalink.