[AccessD] Code to auto-load an ORACLE schema

Hi Jim, I only have an excel spreadsheet to go off - the oracle schema had to get cleaned up and I came up with those columns.

I have this code but it is breaking when trying to put general date format.
Sub CreateTableAndFields()
Dim XL As Excel.Application
Dim R As Excel.Range
Dim WS As Excel.Worksheet
Dim WB As Excel.Workbook
Dim rBegin As Range
Dim rEnd As Range
Dim Cell As Excel.Range
Dim D As DAO.Database
Dim T As DAO.TableDef
Dim F As DAO.Field
Dim Ar()
Dim P As DAO.Property
Dim sLastTable As String
Dim ub As Long
Set XL = MyXL
Set D = CurrentDb
Set WB = XL.ActiveWorkbook
Set WS = WB.ActiveSheet
Set R = WB.Names("MyFields").RefersToRange
Set rBegin = R.Cells(1, 1).Offset(1, 0) 'Has the table name
Set rEnd = WS.Cells(WS.Rows.Count, 1).End(xlUp)
If rEnd.Row > R.Cells(1, 1).Row Then
    'There is data
    For Each Cell In XL.Range(rBegin, rEnd).Cells
        If sLastTable <> XL.Trim(Cell) Then 'originally this is empty string therefore will deal with 1st occurrence
            '1 =Table Name (don't need this but may as well)
            '2 = Field Name
            '3 = Type
            '4 = Size
            '5 = Decimals
            '6 = ValidationRule
            '7 = Required
            ReDim Ar(1 To 7, 1 To 1)
            On Error Resume Next
            DoCmd.DeleteObject acTable, XL.Trim(Cell) 'Get rid of old table, if any
            On Error GoTo 0
            Set T = D.CreateTableDef(Name:=XL.Trim(Cell))
            ub = 1
        Else
            ub = UBound(Ar, 2) + 1
            ReDim Preserve Ar(1 To 7, 1 To ub)
        End If
        Ar(1, ub) = XL.Trim(Cell) 'Table Name
        Ar(2, ub) = XL.Trim(Cell.Offset(0, 1)) 'Field Name
        Ar(3, ub) = CLng(Cell.Offset(0, 2)) ' Type
        Ar(4, ub) = CLng(Cell.Offset(0, 3)) ' Size
        If Len(XL.Trim(Cell.Offset(0, 4))) > 0 Then
            Ar(5, ub) = CLng(Cell.Offset(0, 4)) 'Decimals
        End If
        If Len(XL.Trim(Cell.Offset(0, 5))) > 0 Then
            Ar(6, ub) = XL.Trim(Cell.Offset(0, 5)) 'ValidationRule
        End If
        If XL.Trim(Cell.Offset(0, 6)) = "NOT NULL" Then
            Ar(7, ub) = True 'Required
        Else
            Ar(7, ub) = False
        End If
        Set F = T.CreateField(Ar(2, ub), Ar(3, ub), Ar(4, ub))
        F.Required = Ar(7, ub)
        If CStr(Ar(6, ub)) <> "" Then
            F.ValidationRule = CStr(Ar(6, ub))
        End If
        If CStr(Ar(5, ub)) <> "" Then
            Set P = F.CreateProperty(Name:="DecimalPlaces", Type:=2)
            P.Value = Ar(5, ub)
        End If
        If CLng(Ar(3, ub)) = 8 And CLng(Ar(4, ub)) = 8 Then
            Set P = F.CreateProperty(Name:="Format", Type:=10)      ''''''BREAKING HERE
            P.Value = "General Date"
        End If
        T.Fields.Append F
        If ub = 1 Then
            D.TableDefs.Append T
'            D.TableDefs.Refresh
        End If
        
        sLastTable = Cell
    Next
End If

End Sub


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Friday, May 18, 2012 6:18 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Code to auto-load an ORACLE schema

Have you looked at Toad? Many moons ago, I used Toad to export out of Oracle into DBase format and then imported it into Access. It was A2K as I remember.

I also believe that there is a option in MySQL that will allow you to export out of Oracle and I believe you can import from MySQL into Access but have not checked it out. The new Oracle has some interesting feature as well.

There are some good third-party software products that can perform exports and imports... The name escapes me but the price was somewhere around $100 at module.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Benson, William (GE Global Research, consultant)
Sent: Friday, May 18, 2012 10:13 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Code to auto-load an ORACLE schema

I have some Oracle table information that looks like below. I would like to have some tables in my Access database get auto-created based on this kind of information.

Does someone have a routine to do this kind of thing? 

My bigger issues are the data sizes, and the special NUMBER (p,s) - which I don't think Access will handle except perhaps through validation rules, which I don't have the skill at this time to write.

I know this is just a sample of the data types I might need to be concerned with.


Table Name	Field Name		Type & Size		My Comments
CUSTOMER	ADDRESS_LINE1        	VARCHAR2(240) 	is the default
Subtype CHAR or BYTE
CUSTOMER	CREATED_DT           	DATE          	
EMPLOYEE	LAST_LOGIN_DT      	TIMESTAMP(6)   	
EQUIPMENT	CAPACITY_FACTOR         NUMBER(38)             	
EQUIPMENT	COMMENTS                       VARCHAR2(4000)
would have to treat as memo
EQUIPMENT	UNIT_NUMBER                VARCHAR2(30 CHAR)      is the
default CHAR or BYTE
EQUIPMENT	INTRV_BIS_C                    	NUMBER(5,2)            	How
to handle this - validation rule?

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com