If you’re working with Oracle databases and need to recreate tables in Microsoft Access for proof of concept or other tasks, Oracles ALL_TAB_COLUMNS view is an invaluable resource. This table provides detailed metadata about all tables, fields, and data types in a given Oracle instance. Here’s how I used it to streamline my process:
Step 1: Export Oracles ALL_TAB_COLUMNS to CSV
To begin, I exported the ALL_TAB_COLUMNS data to a CSV file. This file serves as the foundation for recreating Oracle tables in Access.
Step 2: Import into Access
I imported the CSV file into Access to a table called tblCreateTables. This table served as the blueprint for generating the tables defined in Oracle.
Step 3: Enhance the Table Structure
To ensure smooth operations and tracking, I added:
- A Unique ID field containing my AppName.
- A Processed field to mark completed records. This will show any records which have changed
Step 4: Populate Data from Oracle
Using a query tool, I populated tblCreateTables with data from Oracle. This allowed users to review and make modifications as needed directly in Access.
Step 5: Generate the SQL Script
After users completed their changes, the system generated an SQL script. This script was ready for execution by database administrators once the necessary approvals were documented in JIRA. For details see this post.
Example Function to Create Tables
The function I used to create tables from tblCreateTables is shown below.
This approach not only simplifies the recreation of Oracle tables in Access but also enables collaborative workflows by allowing users to review and refine the data before final execution. Whether you’re prototyping or managing database transitions, leveraging ALL_TAB_COLUMNS with Access can save significant time and effort.
Function to make Oracles All_tab_Columns Tables
Function MakeTables(tmpTable As String)
On Error GoTo ErrorHandler
Dim rst As Recordset
Dim Table As DAO.TableDef
Dim rstField As Recordset
Dim Index As DAO.Index
Dim Field As DAO.Field
Dim tmpWorkingonTable As String
If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tmpTable & "'")) Then
DoCmd.SetWarnings False
DoCmd.Close acTable, tmpTable, acSaveYes
DoCmd.DeleteObject acTable = acDefault, tmpTable
MakeTables = "Table " & tmpTable & "deleted..."
DoCmd.SetWarnings True
End If
Set rst = CurrentDb.OpenRecordset("Select * from tblCreateTable where TABLE_NAME='" & tmpTable & "' order by cdbl(COLUMN_ID) asc")
If rst.RecordCount = 0 Then
MakeTables = "Cant find the table " & tmpTable
Set rst = Nothing
Else
rst.MoveFirst
Set Table = CurrentDb.CreateTableDef(StrConv(rst!Table_Name, vbUpperCase))
End If
tmpWorkingonTable = rst!Table_Name
'add a unique id to the table
Set Field = Table.CreateField("zz_YourApp_ID", dbLong)
Field.Attributes = dbAutoIncrField
Table.Fields.Append Field
Set Field = Table.CreateField("zz_YourApp_ID_Processed", dbLong)
Field.DefaultValue = 0
Table.Fields.Append Field
Do While Not rst.EOF
Select Case rst!Data_Type
Case "CHAR", "VARCHAR2", "CHAR"
If Nz(rst!Data_length, 0) > 255 Then ' field longer than 255 must be long text or memo on access
Set Field = Table.CreateField(rst!Column_name, dbMemo, IIf(Nz(rst!Column_name, 0) = 0, 50, rst!Data_length))
Else
Set Field = Table.CreateField(rst!Column_name, dbText, IIf(Nz(rst!Column_name, 0) = 0, 50, rst!Data_length))
End If
If Nz(rst!DEFAULT_ON_NULL, "N") = "Y" Then
'set a default value if allowed when null
If Nz(rst!Data_Default, "") <> "" Then
Field.DefaultValue = rst!Data_Default
End If
End If
If Nz(rst!Nullable, "N") = "N" Then
'required field
Field.Required = True
Field.AllowZeroLength = False
Else
Field.Required = False
Field.AllowZeroLength = True
End If
If Nz(rst!DEFAULT_ON_NULL, "N") = "Y" Then
'set a default value if allowed when null
If Nz(rst!Data_Default, "") <> "" Then
Field.DefaultValue = rst!Data_Default
End If
End If
Table.Fields.Append Field
Case "DATE", "TIMESTAMP(6)"
'-------------------
'adding dates as text - issue with loading the sql date to the tables
'--------------------
Set Field = Table.CreateField(rst!Column_name, dbText)
If Nz(rst!DEFAULT_ON_NULL, "N") = "Y" Then
'set a default value if allowed when null
If Nz(rst!Data_Default, "") <> "" Then
Field.DefaultValue = rst!Data_Default
End If
End If
If Nz(rst!Nullable, "N") = "N" Then
'required field
Field.Required = True
Field.AllowZeroLength = False
Else
Field.Required = False
Field.AllowZeroLength = True
End If
Table.Fields.Append Field
Case "NUMBER", "LONG"
If Nz(rst!Data_precision, 0) = 0 Then
'no decimals
Set Field = Table.CreateField(rst!Column_name, dbLong)
Else
'decimals
Set Field = Table.CreateField(rst!Column_name, dbDouble)
End If
If Nz(rst!Nullable, "N") = "N" Then
'required field
Field.Required = True
'Field.AllowZeroLength = False
Else
Field.Required = False
'Field.AllowZeroLength = True
End If
If Nz(rst!DEFAULT_ON_NULL, "N") = "Y" Then
'set a default value if allowed when null
If Nz(rst!Data_Default, "") <> "" Then
Field.DefaultValue = rst!Data_Default
End If
End If
Table.Fields.Append Field
Case "BLOB", "CLOB"
Set Field = Table.CreateField(rst!Column_name, dbMemo, IIf(Nz(rst!Column_name, 0) = 0, 50, rst!Data_length))
If Nz(rst!Nullable, "N") = "N" Then
'required field
Field.Required = True
'Field.AllowZeroLength = False
Else
Field.Required = False
'Field.AllowZeroLength = True
End If
If Nz(rst!DEFAULT_ON_NULL, "N") = "Y" Then
'set a default value if allowed when null
If Nz(rst!Data_Default, "") <> "" Then
Field.DefaultValue = rst!Data_Default
End If
End If
Table.Fields.Append Field
End Select
' If rst!Primary Then
' Set Index = Table.CreateIndex("PrimaryKey")
' Set Field = Index.CreateField(rst!Column_name)
' Index.Fields.Append Field
' Index.Primary = True
' Table.Indexes.Append Index
' End If
rst.MoveNext
Loop
CurrentDb.TableDefs.Append Table
Debug.Print "Table " & tmpTable & "created..."
MakeTables = tmpWorkingonTable & " - Table Created"
Set rst = Nothing
Exit Function
ErrorHandler:
MakeTables = "Error when making table " & tmpWorkingonTable & " Err No:" & Err.Number & " - " & Err.Description
Set rst = Nothing
Exit Function
End Function
Oracle page relating to all_tab_columns