OLE DB, ADO, ODBC

Object Linked and Embedding, Database (OLE DB)

It is an API for access various datasource in a uniform manner.

ole db

ActiveX Data Object (ADO)

It is a middleware layer for accessing and manipulate data through OLE DB Provider.

ADO

Reference:

https://msdn.microsoft.com/en-us/library/ms675532(v=vs.85).aspx

https://en.wikipedia.org/wiki/ActiveX_Data_Objects

Open Database Connectivity (ODBC)

It is a api for accessing relational database management system (RDBMS).

The task is accomplished by ODBC Driver.

odbc

 

 

 

VBA Example – Create a directory sheet

DownloadcreateDirecotySheet

Function: Create a directory sheet whose cells is a hyperlink to the sheet

Image 007Image 008

 

Image 006

Source Code

Sub generateDirectorySheet()
directorySheetName = “Directory"

‘ No. of Worksheets
WS_Count = ActiveWorkbook.Worksheets.Count

Dim directorySheet As Worksheet
requiredToCreateDirectorySheet = True

Set sheetNames = CreateObject(“System.Collections.ArrayList")

For i = 1 To WS_Count
If Worksheets(i).Name = directorySheetName Then
Set directorySheet = Worksheets(i)
requiredToCreateDirectorySheet = False
Else
sheetNames.Add (Worksheets(i).Name)
End If
Next i

If requiredToCreateDirectorySheet = True Then
Set directorySheet = Worksheets.Add(Before:=Worksheets(1))
directorySheet.Name = directorySheetName
End If

For i = 0 To sheetNames.Count() – 1
directorySheet.Hyperlinks.Add anchor:=directorySheet.Cells(i + 1, 1), Address:="", SubAddress:=sheetNames(i) + “!A1″, TextToDisplay:=sheetNames(i)
Next i

End Sub

 

 

 

Excel VBA Example – Convert Create Table SQL into Excel Table

Download: CreateTableSQL2Excel

(Please rename createtablesql2excel.xlsx to createtablesql2excel.xlsm)

Function: Convert Create Table SQL into Excel\

Limitation: The SQL file can only contain create table statement for one table. No more table would be supported

Image 004

Image 005Image 006Image 007

 

 

Create Table SQL

Create Table TABLE_NAME (
 COLUMN_NAME1 VARCHAR2(5 BYTE) NOT NULL,
 COLUMN_NAME2 VARCHAR2(300),
 COLUMN_NAME3 VARCHAR2
);

Source Code

Sub CreateTableSQL2Excel()
 
 ' Read text file into varibale inputText
 filePath = ActiveWorkbook.Path + "\" + Cells(1, 2)
 Open filePath For Input As #1
 inputText = ""
 Do Until EOF(1)
 Line Input #1, textline
 inputText = inputText & textline
 Loop
 Close #1
 
 ' convert into upper case
 inputText = UCase(inputText)
 
 ' replace " " by " "
 inputText = Replace(inputText, " ", " ")
 
 ' Get table name and the column information
 inputText = Replace(inputText, "CREATE TABLE ", "")
 tableName = Left(inputText, InStr(1, inputText, "(") - 1)
 inputText = Right(inputText, Len(inputText) - InStr(1, inputText, "(") - 1)
 inputText = Left(inputText, (InStrRev(inputText, ");") - 1))
 inputText = Trim(inputText)
 columnInfo = Split(inputText, ",")
 
 ' Distribute column data to the tabe
 For i = LBound(columnInfo) To UBound(columnInfo)
 columnInfo(i) = Trim(columnInfo(i))
 
 ' Process the Data type attribute first
 subColumnInfoAttrs = Split(columnInfo(i), "(")
 If UBound(subColumnInfoAttrs) > 0 Then
 columnInfo(i) = subColumnInfoAttrs(0)
 For j = LBound(subColumnInfoAttrs) + 1 To UBound(subColumnInfoAttrs)
 columnInfo(i) = columnInfo(i) + Split(subColumnInfoAttrs(j), ")")(1)
 subColumnInfoAttrs(j) = Split(subColumnInfoAttrs(j), ")")(0)
 dataTypeAttrs = Split(subColumnInfoAttrs(j), " ")
 For k = LBound(dataTypeAttrs) To UBound(dataTypeAttrs)
 Cells(i + 3, k + 3).Value = dataTypeAttrs(k)
 Next k
 Next j
 End If
 
 
 columnAttrs = Split(columnInfo(i), " ")
 For j = LBound(columnAttrs) To UBound(columnAttrs)
 If j = 1 Then
 Cells(i + 3, 1).Value = columnAttrs(0)
 End If
 If j = 2 Then
 Cells(i + 3, 2).Value = columnAttrs(1)
 End If
 If j = 3 Then
 If columnAttrs(2) & " " & columnAttrs(3) = "NOT NULL" Then
 Cells(i + 3, 5).Value = "Y"
 End If
 End If
 Next j
 Next i

End Sub

Reference: Create Table SQL Statement Information

 

 

 

 

 

 

VBA Example – DML Generator

Download: dmlgenerator

Function: Generate Insert Data and Delete Data SQL file.

Note: For the Cell whose type is String, the value would be surrounded by ‘ ‘. If the Value is surrounded by " “, " " would be removed and not ‘ ‘ would added. The delete statement is determined by (PK). Without (PK), delete statement could not know which record to be deleted.

Image 001

Image 001

It would create two file.

Image 005Image 006

Source Code

Sub genSql()

 tableName = Cells(1, 2)

 Set pkAL = CreateObject("System.Collections.ArrayList")
 
 noOfColumn = 1
 Do While Cells(2, noOfColumn) <> ""
 ' Determine which column is primary key then put it into an array list
 If InStr(Cells(2, noOfColumn), "(PK)") > 0 Then
 pkColumn = Replace(Cells(2, noOfColumn), "(PK)", "")
 pk = Array(noOfColumn, pkColumn)
 pkAL.Add (pk)
 End If
 noOfColumn = noOfColumn + 1
 Loop
 
 ' insert sql file path
 insertSqlFilePath = ActiveWorkbook.Path + "\insert_" + tableName + ".sql"
 Open insertSqlFilePath For Output As #1
 
 ' delete sql file path
 deleteSqlFilePath = ActiveWorkbook.Path + "\delete_" + tableName + ".sql"
 Open deleteSqlFilePath For Output As #2
 
 '
 insertPrefix = "Insert into " + tableName + " values ("
 insertSuffix = ");"
 deletePrefix = "delete " + tableName + " where "
 deleteSuffix = ";"
 
 ' Loop until not ""
 i = 3
 Do While Cells(i, 1).Value <> ""
 
 insertValueArrStr = Cells(i, 1).Text
 For j = 2 To noOfColumn - 1
 If Cells(i, j).Value Like """*""" Then
 rCellValue = Right(Cells(i, j).Text, Len(Cells(i, j).Text) - 1)
 cellValue = Left(rCellValue, Len(rCellValue) - 1)
 insertValueArrStr = insertValueArrStr + ", " + cellValue
 ElseIf TypeName(Cells(i, j).Value) = "String" Then
 insertValueArrStr = insertValueArrStr + ", '" + Cells(i, j).Text + "'"
 Else
 insertValueArrStr = insertValueArrStr + ", " + Cells(i, j).Text
 End If
 Next j
 
 
 If pkAL.Count() > 0 Then
 deleteConArrStr = pkAL(0)(1) + "=" + Cells(i, pkAL(0)(0)).Text
 For j = 1 To pkAL.Count - 1
 If TypeName(Cells(i, pkAL(j)(0)).Value) = "String" Then
 deleteConArrStr = deleteConArrStr + " and " + pkAL(j)(1) + "= '" + Cells(i, pkAL(j)(0)).Text + "'"
 End If
 Next j
 End If
 
 Print #1, insertPrefix + insertValueArrStr + insertSuffix + ""
 Print #2, deletePrefix + deleteConArrStr + deleteSuffix + ""
 
 i = i + 1
 Loop
 
 Close #1
 Close #2
 
End Sub



Reference: Create Table SQL Statement Information

 

 

 

 

Excel VBA Example – DDL Generator

DownloadDDLGenerator

(Rename ddlgenerator.xls to ddlgenerator.xlsm)

please rename to file from ddlgenerator.xlsx to ddlgenerator.xlsm

 

Function: It would generate Oracle SQL files for specified table manipulation.

 

Image 011

Image 002

Four files would be generated

Image 001Image 014

Image 003

In the working path of the excel, you could find two sql file. One is the add column ddl file while another is drop column ddl file. The file name is DDL_AddColumn_<table name>.sql,  DDL_DropColumn_<table name>.sql, DDL_CreateTable_<table name>.sql and DDL_DropTable_<table name>.sql

Source Code

 

Sub genDDL()

 For i = 1 To Worksheets.Count
 If Worksheets.Item(i).Name = "TableAddColumn" Then
 Dim currentWorksheet As Worksheet
 Set currentWorksheet = Worksheets.Item(i)
 
 ' Table Name
 tableName = currentWorksheet.Cells(1, 2)
 
 ' #1 as Add Column DDL file
 addColumnDDLFile = ActiveWorkbook.Path + "\DDL_AddColumn_" + tableName + ".sql"
 Open addColumnDDLFile For Output As #1
 
 ' #2 as Drop Column DDL file
 dropColumnDDLFile = ActiveWorkbook.Path + "\DDL_DropColumn_" + tableName + ".sql"
 Open dropColumnDDLFile For Output As #2
 
 ' #3 as Create Table DDL file
 createTableDDLFile = ActiveWorkbook.Path + "\DDL_CreateTable_" + tableName + ".sql"
 Open createTableDDLFile For Output As #3
 
 ' #4 as drop table DDL file
 dropTableDDLfile = ActiveWorkbook.Path + "\DDL_DropTable_" + tableName + ".sql"
 Open dropTableDDLfile For Output As #4
 
 Print #1, "ALTER TABLE " + tableName + " ADD ("
 Print #2, "ALTER TABLE " + tableName + " DROP ("
 Print #3, "Create Table " + tableName + " ("
 Print #4, "Drop Table " + tableName; ";"
 
 ' Reading columnValue until not empty
 i = 3 ' row number
 Do While currentWorksheet.Cells(i, 1) <> ""
 
 ' For add column statement
 preparedPartialAddColumnSQL = currentWorksheet.Cells(i, 1) + " " _
 + currentWorksheet.Cells(i, 2) + "(" _
 + currentWorksheet.Cells(i, 3).Text
 
 ' If it specify the unit, add unit other do not add it
 If (currentWorksheet.Cells(i, 4) <> "") Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + " " + currentWorksheet.Cells(i, 4)
 End If
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + ")"
 
 ' If it marked "Y" for not null then add not null to the prepared partial sql statement
 If (currentWorksheet.Cells(i, 5) = "Y") Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + " NOT NULL"
 End If
 
 ' If text line have text, add "," at the end of line
 If currentWorksheet.Cells(i + 1, 1) <> "" Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + ","
 End If
 
 ' For drop column statement
 preparedPartialDropColumnSQL = currentWorksheet.Cells(i, 1)
 If currentWorksheet.Cells(i + 1, 1) <> "" Then
 preparedPartialDropColumnSQL = preparedPartialDropColumnSQL + ","
 End If
 
 Print #1, " " + preparedPartialAddColumnSQL
 Print #2, " " + preparedPartialDropColumnSQL
 Print #3, " " + preparedPartialAddColumnSQL
 i = i + 1
 columnValue = currentWorksheet.Cells(3, 1)
 Loop
 
 Print #1, ");"
 Print #2, ");"
 Print #3, ");"
 
 ' Close output file
 Close #1
 Close #2
 Close #3
 Close #4
 
 End If
 Next i

 
End Sub


Feasible Aspects

  • Index
  • Tablespace
  • DB Connection
  • Constraints
  • Primary Key
  • Foreign Key

 

Reference: Create Table SQL Statement Information

Image 001
Proposed New Excel Template (For provide more function)

 

Excel VBA Example-

Print Cells Value to Text

Pseudocode

  • Loop Row of Sheet1 Until Value <> “"
  • Print Cell Value
  • End Loop

Code

Sub PrintCellsValue2Text()

' #1 as output file
outputfile = ActiveWorkbook.Path + "\output.txt"
Open outputfile For Output As #1

' Loop the Rows Until Cell(i,1) is not ""
i = 1
Do Until Trim(Cells(i, 1)) = ""

' Print the Cell Value to #1
Print #1, ""
Print #1, Cells(i, 1).Value
Print #1, ""

i = i + 1
Loop

' Close #1
Close #1

' Alert Message For Complete
MsgBox "Completed"

End Sub