Results 1 to 4 of 4
  1. #1
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206

    Manage Access 2003 ULS thru SQL or Code

    Hi Everyone!


    Happy New Year!

    It's good to be back here.
    I was wondering why the Access 2003 help file contains a topic on SQL statements for managing security (such as GRANT, REVOKE, etc.).

    But when I tried using them in the Data Definition query window, I got the message "Invalid SQL Statement..."

    I wonder if there's a way for me to manage security without using the ULS account and permission windows.

    Thanks in advance!

  2. #2
    Sullitec is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    United Kingdom
    Posts
    8

    Manage Access 2003 ULS thru SQL or Code

    Hi Evander

    Just to shed some light on your query, in an Access 2003 database which uses the Jet/Jet SQL, the GRANT and REVOKE statements are not supported, you have to manage security either through ULS or DAO.

    There are some cases where Jet SQL works better than Jet Data Access Objects and some limitations.

    Even though Jet provides an extensive library of programmable objects for managing a database, as you probably know, SQL is the true programming language of databases. Almost everything you can with DAO code, you can do with SQL. For example

    Construct a Database
    Use CREATE TABLE, ALTER TABLE, and CREATE INDEX.

    Create a Relationship
    Use CONSTRAINT with CREATE TABLE or ALTER TABLE.

    Copy a Table
    Use SELECT ... INTO.

    Insert records
    Use INSERT ... VALUES or INSERT ... SELECT.

    Update Records
    Use UPDATE ... SET.

    Delete Records
    Use DELETE.

    Before you can do anything with Jet SQL, however, you'll need to have available a reference to a database object so that you can use the Execute method to run an SQL statement

    Limitations of Jet SQL

    You can't completely ignore DAO however. There are some things that aren't available with Jet SQL.

    Rules and Defaults
    While relationships can be established with SQL, you'll need to use DAO to assign some of the extended properties for tables and fields.

    Creating a Database
    There's no Jet equivalent to the SQL CREATE DATABASE statement. You'll need to use DAO to create a new database.

    Manage Security
    Jet SQL does not provide the GRANT and REVOKE statements. Use DAO to manage security.

    Non-Jet Databases
    Jet SQL can only create Jet objects. You need to use DAO to create objects in other databases.

    Creating Queries
    Jet SQL has no CREATE VIEW statement. Use the CreateQueryDef method of the database object and set the SQL property to a valid SQL statement.
    Other Access Objects

    SQL cannot create any of the other Access objects, such as Forms, Reports, Macros, Modules, etc. However, these can be created programmatically in Access using some of the extended methods available in Access, such as CreateForm, CreateReport, etc. Using some of the macro actions available in code, you can even create a module and write code for it programmatically.

    Ultimately the full SQL DDL mentioned in the help are best used when linking an Access front end to an SQL backend (MySQl/MSSQL) or a situation where you might need remotely update table structures using SQL from the front end.

    See this article for more info

    Hope its useful

    Sully

  3. #3
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    Hey Sully!

    I read your reply weeks ago but I forgot to thank you. So thank you! Yes, your answer helped me a lot. I'm automating ULS using DAO now.

  4. #4
    Sullitec is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Location
    United Kingdom
    Posts
    8
    Your welcome. I know your legend in access circles from your previous posts so honoured to help just once. Cheers :-)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA code in Access 2003
    By coolpal9 in forum Access
    Replies: 3
    Last Post: 01-03-2012, 11:30 PM
  2. code changes from access 2003 to access 2007
    By daversb in forum Programming
    Replies: 1
    Last Post: 11-30-2010, 05:32 PM
  3. Replies: 5
    Last Post: 06-29-2010, 10:13 AM
  4. Access 2003 code vs Access 2007 Code
    By ralphjramirez in forum Access
    Replies: 5
    Last Post: 11-23-2009, 12:33 PM
  5. Replies: 1
    Last Post: 09-06-2006, 11:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums