SQL scripts with an MDB

[Pat Garard]

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?

 

[Allen Browne]

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]

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.

 

[Pat Garard]

Thanks Allen!

That's a Real Help!!

 

[Pat Garard]

Thanks Allen!

That's a Real Help!!

 

[Jamie Collins]

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.

--

 

[Jamie Collins]

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.

--

 

['69 Camaro]

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.

 

['69 Camaro]

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.

 

['69 Camaro]

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.

 

['69 Camaro]

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.