Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL scripts with an MDB

Discussion in 'Information Technology' started by Pat Garard, Jul 28, 2006.

Tags:
  1. Pat Garard

    Pat Garard
    Expand Collapse
    Guest

    G'Day All,

    I have some Oracle .SQL files from a small project. They are created at
    home, and remotely executed after uploading to a corporate Oracle DB.

    I would like to use to them to create, and populate, Tables in a New
    MDB file at home.

    How may this be done?
    --
    Regards,
    Pat Garard
    Melbourne, Australia
    _______________________
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005
    Where are the 1984 manuscripts? Hard Talk Jun 6, 2015
    Heritage Rare Guru Granth Sahib Manuscripts need Preservation, says Dr. Mohinder Singh History of Sikhism Sep 10, 2013
    Events Manuscripts on display at National Museum of Pakistan. History of Sikhism Mar 3, 2013
    Nature Elephant in Orang National Park Scripts History Breaking News Dec 17, 2010

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Hi Pat

    This will depend what is in the SQL. Databases tend to use
    variations/extensions on the pure SQL language, as well as execution
    differences, so these scripts may not work directly in Access without
    modification.

    You can create a table in Access by executing a DDL SQL statement. The
    example shows most of the field types, and most of the options. Some of
    these options only work in Access 2000 and later, and only if executed under
    ADO code (so not through the query interface, which uses DAO.) For more info
    on the field types compared to what they are called in the Access interface
    and other contexts, see:
    Field type names (JET, DDL, DAO and ADOX)
    at:
    http://allenbrowne.com/ser-49.html

    There are major limitations in creating tables this way in Access. For
    example, I would never consider building a data structure without setting
    the AllowZeroLength property of Text and Memo fields to No, and you can
    cannot do this just with DDL.

    Sub CreateTableDDL()
    Dim cmd As New ADODB.Command
    Dim strSql As String

    cmd.ActiveConnection = CurrentProject.Connection

    'Create the Contractor table.
    strSql = "CREATE TABLE tblDdlContractor " & _
    "(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
    "Surname TEXT(30) WITH COMP NOT NULL, " & _
    "FirstName TEXT(20) WITH COMP, " & _
    "Inactive YESNO, " & _
    "HourlyFee CURRENCY DEFAULT 0, " & _
    "PenaltyRate DOUBLE, " & _
    "BirthDate DATE, " & _
    "Notes MEMO, " & _
    "CONSTRAINT FullName UNIQUE (Surname, FirstName));"
    cmd.CommandText = strSql
    cmd.Execute
    End Sub

    You say you also want to populate the tables. To do this you would need to
    be able to connect to the Oracle data, and execute an Append query statement
    to populate your tables. Your network admin people will be able to tell you
    how practical that is. In general, they are not keen on people accessing
    corporate data from home.

    Of couse, if you could just link the tables, you could then use a MakeTable
    query to create local Access copies of the data. There would be no need to
    create the tables with DDL.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Pat Garard bigpond:net:au>" <apgarard-> wrote in message
    news:%23iWdcSjfGHA.324@TK2MSFTNGP02.phx.gbl...
    > G'Day All,
    >
    > I have some Oracle .SQL files from a small project. They are created at
    > home, and remotely executed after uploading to a corporate Oracle DB.
    >
    > I would like to use to them to create, and populate, Tables in a New
    > MDB file at home.
    >
    > How may this be done?
    > --
    > Regards,
    > Pat Garard
    > Melbourne, Australia
    > _______________________
     
  4. Pat Garard

    Pat Garard
    Expand Collapse
    Guest

    Thanks Allen!

    That's a Real Help!!
    --
    Regards,
    Pat Garard
    Melbourne, Australia
    _______________________

    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:eiXscqjfGHA.5096@TK2MSFTNGP02.phx.gbl...
    > Hi Pat
    >
    > This will depend what is in the SQL. Databases tend to use
    > variations/extensions on the pure SQL language, as well as execution
    > differences, so these scripts may not work directly in Access without
    > modification.
    >
    > You can create a table in Access by executing a DDL SQL statement. The example
    > shows most of the field types, and most of the options. Some of these options
    > only work in Access 2000 and later, and only if executed under ADO code (so
    > not through the query interface, which uses DAO.) For more info on the field
    > types compared to what they are called in the Access interface and other
    > contexts, see:
    > Field type names (JET, DDL, DAO and ADOX)
    > at:
    > http://allenbrowne.com/ser-49.html
    >
    > There are major limitations in creating tables this way in Access. For
    > example, I would never consider building a data structure without setting the
    > AllowZeroLength property of Text and Memo fields to No, and you can cannot do
    > this just with DDL.
    >
    > Sub CreateTableDDL()
    > Dim cmd As New ADODB.Command
    > Dim strSql As String
    >
    > cmd.ActiveConnection = CurrentProject.Connection
    >
    > 'Create the Contractor table.
    > strSql = "CREATE TABLE tblDdlContractor " & _
    > "(ContractorID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, " & _
    > "Surname TEXT(30) WITH COMP NOT NULL, " & _
    > "FirstName TEXT(20) WITH COMP, " & _
    > "Inactive YESNO, " & _
    > "HourlyFee CURRENCY DEFAULT 0, " & _
    > "PenaltyRate DOUBLE, " & _
    > "BirthDate DATE, " & _
    > "Notes MEMO, " & _
    > "CONSTRAINT FullName UNIQUE (Surname, FirstName));"
    > cmd.CommandText = strSql
    > cmd.Execute
    > End Sub
    >
    > You say you also want to populate the tables. To do this you would need to be
    > able to connect to the Oracle data, and execute an Append query statement to
    > populate your tables. Your network admin people will be able to tell you how
    > practical that is. In general, they are not keen on people accessing corporate
    > data from home.
    >
    > Of couse, if you could just link the tables, you could then use a MakeTable
    > query to create local Access copies of the data. There would be no need to
    > create the tables with DDL.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Pat Garard bigpond:net:au>" <apgarard-> wrote in message
    > news:%23iWdcSjfGHA.324@TK2MSFTNGP02.phx.gbl...
    >> G'Day All,
    >>
    >> I have some Oracle .SQL files from a small project. They are created at
    >> home, and remotely executed after uploading to a corporate Oracle DB.
    >>
    >> I would like to use to them to create, and populate, Tables in a New
    >> MDB file at home.
    >>
    >> How may this be done?
    >> --
    >> Regards,
    >> Pat Garard
    >> Melbourne, Australia
    >> _______________________

    >
    >
     
  5. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Allen Browne wrote:
    > This will depend what is in the SQL. Databases tend to use
    > variations/extensions on the pure SQL language, as well as execution
    > differences, so these scripts may not work directly in Access without
    > modification.
    >
    > You can create a table in Access by executing a DDL SQL statement. The
    > example shows most of the field types, and most of the options.


    Allen, For the OP to stand a better chance of mapping from Oracle (or
    any other SQL implementation) to Jet, you could've picked some Jet DDL
    types which are closer to Standard SQL <g>:

    TEXT(30) would be NVARCHAR(30) or VARCHAR(30)

    CURRENCY would be NUMERIC(19, 5)

    DOUBLE would be FLOAT

    YESNO would be... well, SQL-92 doesn't have a 'boolean' data type
    (three value logic and all that) so the best for portability would be

    col_name INTEGER DEFAULT 0 NOT NULL, CHECK (col_name IN (0, 1))

    For more details see:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

    Jamie.

    --
     
  6. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Pat.

    > I would like to use to them to create, and populate, Tables in a New
    > MDB file at home.


    Oracle SQL scripts are generally incompatible with Jet SQL. Jet doesn't
    understand Oracle schemas, nor multiple SQL statements within the script.
    (Jet can only execute one SQL statement (query) per SQL script.) Jet also
    doesn't understand the syntax used for some of the basic Oracle data types,
    let alone any of the complex data types commonly used in PL/SQL.

    > How may this be done?


    You'll need to translate Oracle SQL into Jet SQL to create the tables and
    then append the data. You'll also have to break it down into multiple
    queries, one SQL statement for each operation. For example, write one query
    for creating a table, then another one for appending the data to that table.
    The Oracle scripts already give you the syntax for creating each table,
    complete with the table constraints and defaults. You just need to
    translate the data types so that Jet can use them, but you'll also have to
    change the database option to use SQL Server compatible syntax (ANSI 92) if
    you're using the SQL View pane to execute the queries. Otherwise, you'll
    have to use the Immediate Window or write VBA procedures using the ADODB
    library for these query executions.

    For an explanation of Oracle data types when creating the tables, if you're
    using Oracle 10g, then please see the following Web page:

    http://www.psoug.org/reference/datatypes.html

    If you're using Oracle 9i, then please see the following Web page:

    http://www.ucs.louisiana.edu/~oracle9i/server.901/a90125/sql_elements2.htm

    For the syntax for populating these new tables, try the following:

    Multiple-record append query:

    INSERT INTO targetTableName [(field1[, field2[, ...]])] [IN
    externaldatabase]
    SELECT [sourceTableName.]field1[, field2[, ...]
    FROM sourceTableName

    Single-record append query:

    INSERT INTO targetTableName [(field1[, field2[, ...]])]
    VALUES (value1[, value2[, ...])

    Oracle puts out a huge amount of documentation. The hard part is finding
    the correct manual for looking up information. Fortunately, Oracle has
    created a cross-referenced index that can search through the documentation
    library for Oracle 9i and 10g. Please see the following Web page for Oracle
    documentation searches:

    http://tahiti.oracle.com

    If you're using an older version of Oracle, then please see the "Previously
    Released Oracle Documentation" section on the following Web page:

    http://www.oracle.com/technology/documentation/index.html

    Follow the links until you get to "View Library," where you'll be able to
    search the library of documentation for that library just as if it were on
    the Tahiti search page.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Pat Garard bigpond:net:au>" <apgarard-> wrote in message
    news:%23iWdcSjfGHA.324@TK2MSFTNGP02.phx.gbl...
    > G'Day All,
    >
    > I have some Oracle .SQL files from a small project. They are created at
    > home, and remotely executed after uploading to a corporate Oracle DB.
    >
    > I would like to use to them to create, and populate, Tables in a New
    > MDB file at home.
    >
    > How may this be done?
    > --
    > Regards,
    > Pat Garard
    > Melbourne, Australia
    > _______________________
    >
    >
     
  7. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Jamie.

    > Allen, For the OP to stand a better chance of mapping from Oracle (or
    > any other SQL implementation) to Jet, you could've picked some Jet DDL
    > types which are closer to Standard SQL <g>:


    Jamie, please don't offer advice to others about Oracle SQL without first
    checking Oracle documentation.

    > TEXT(30) would be NVARCHAR(30) or VARCHAR(30)


    Trained Oracle professionals haven't used NVARCHAR or VARCHAR in more years
    than I can remember. These data types have been deprecated in every major
    version of Oracle since either the ANSI SQL-89 or ANSI SQL-92 standards were
    published. (I don't know which of these ANSI standards defined them, but
    perhaps someone else has this documentation.)

    The reason for this deprecation is that these two data types were defined by
    ANSI standards after many database engines had been on the market for years,
    including Oracle's. Oracle's pre-ANSI definitions were different, so Oracle
    had to redefine its versions of these data types to match the ANSI
    standards. Oracle's NVARCHAR2 and VARCHAR2 data types are equivalent to the
    ANSI NVARCHAR and VARCHAR data types, respectively. Oracle still has the
    old NVARCHAR and VARCHAR data types for backwards compatibility, but since
    the definitions are different, don't expect Oracle to always handle them the
    same as SQL Server would.

    > CURRENCY would be NUMERIC(19, 5)


    That's an ANSI SQL data type and an Oracle 10g NUMBER subtype, but many
    Oracle installations are still using earlier versions of Oracle, so the
    NUMERIC subtype is not available to them. For Oracle 10g, a trained Oracle
    professional uses NUMBER(p,s) for accuracy, but uses DECIMAL(p,s) for speed.
    (Where p is the number of significant digits required, and s is the scale
    for decimal numbers for the currency -- generally 2 for U.S. dollars, but
    occasionally 3, such as when tenths of a cent for gasoline prices are being
    stored.) For earlier versions of Oracle, trained Oracle professionals
    merely use the NUMBER(p,s) syntax. Please see the following Web page for a
    reference to Oracle 10g data types:

    http://www.psoug.org/reference/datatypes.html

    Please see the following Web page for a reference to Oracle 9i data types:

    http://www.ucs.louisiana.edu/~oracle9i/server.901/a90125/sql_elements2.htm

    I'm sure you use SQL Server more often than you use Oracle, so it's easy to
    confuse SQL Server data types with Oracle data types. Please refer to the
    "Mapping Data Types from SQL Server to Oracle" table of the following Web
    page for the equivalent data types:

    http://msdn2.microsoft.com/en-US/library/ms151738.aspx

    > YESNO would be... well, SQL-92 doesn't have a 'boolean' data type
    > (three value logic and all that)


    Even if ANSI SQL doesn't offer the boolean data type, Oracle PL/SQL does, so
    it's possible (although very unlikely) that Pat's Oracle scripts use
    booleans.

    > so the best for portability would be
    > col_name INTEGER DEFAULT 0 NOT NULL, CHECK (col_name IN (0, 1))


    First, one should _never_ create a table constraint without naming it,
    because the database engine will assign a name to it, a name that may be
    difficult to determine programmatically. (Perhaps you can offer the syntax
    to those Access developers who made the mistake of following your advice, in
    order to determine this database-generated constraint name?) The
    recommended syntax for a check constraint on such a table as you suggested
    is:

    CREATE TABLE tblMyTable
    (col_name INTEGER DEFAULT 0 NOT NULL,
    CONSTRAINT Boolean_CHK CHECK (col_name IN (0, 1)));

    .. . . where Boolean_CHK is the name of the check constraint. By creating
    the constraint with a name, referencing the data dictionary (if available)
    or documentation will allow the database developer to first drop the
    constraint if the table needs to be dropped or altered in any way that
    affects this constraint. The syntax for dropping this constraint would be:

    ALTER TABLE tblMyTable
    DROP CONSTRAINT Boolean_CHK;

    Also, your syntax doesn't port to Access very easily. It has to be
    translated into a VBA procedure that requires the ADODB library, or the
    entire Access database must be converted to use SQL Server compatible syntax
    (ANSI 92) before using your query's syntax in the SQL View pane. Most
    Access developers will just use a Validation Rule and Validation Text, not a
    check constraint, to do the job, because it's less work and far more user
    friendly.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1148373717.301388.237250@j55g2000cwa.googlegroups.com...
    >
    > Allen Browne wrote:
    >> This will depend what is in the SQL. Databases tend to use
    >> variations/extensions on the pure SQL language, as well as execution
    >> differences, so these scripts may not work directly in Access without
    >> modification.
    >>
    >> You can create a table in Access by executing a DDL SQL statement. The
    >> example shows most of the field types, and most of the options.

    >
    > Allen, For the OP to stand a better chance of mapping from Oracle (or
    > any other SQL implementation) to Jet, you could've picked some Jet DDL
    > types which are closer to Standard SQL <g>:
    >
    > TEXT(30) would be NVARCHAR(30) or VARCHAR(30)
    >
    > CURRENCY would be NUMERIC(19, 5)
    >
    > DOUBLE would be FLOAT
    >
    > YESNO would be... well, SQL-92 doesn't have a 'boolean' data type
    > (three value logic and all that) so the best for portability would be
    >
    > col_name INTEGER DEFAULT 0 NOT NULL, CHECK (col_name IN (0, 1))
    >
    > For more details see:
    >
    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
    >
    > Jamie.
    >
    > --
    >
     

Share This Page