Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012

    Unanswered: Using raw SQL in Access

    MySQL was more of my real introduction to databases (aside from a stint on Access with no programming involved) and so I'm used to being able to create my tables and load up data with pure, regular old SQL. For example, I could run the following in MySQL's command-line interface and generate my tables (altho this was designed for Access, since Access apparently doesn't automatically name constraints and 2007 apparently doesn't allow ON DELETE CASCADE):

    CREATE TABLE programs (
    program_id INT NOT NULL PRIMARY KEY,
    program_co_name VARCHAR(255) NULL,
    program_div_name VARCHAR(255) NULL,
    program_div_id VARCHAR(255) NULL,
    program_desc TEXT NULL
    CREATE TABLE forms (
    form_name VARCHAR(255) NULL,
    form_desc TEXT NULL,
    form_lastupdated DATETIME,
    clause_id INT NULL,
    co_id INT NULL,
    program_id INT NULL,
    CONSTRAINT form1 FOREIGN KEY (clause_id) REFERENCES clauses (clause_id),
    CONSTRAINT form2 FOREIGN KEY (co_id) REFERENCES companies (co_id),
    CONSTRAINT form3 FOREIGN KEY (program_id) REFERENCES programs (program_id)
    CREATE TABLE forms_programs (
    form_program INT NOT NULL PRIMARY KEY,
    form_id INT NOT NULL,
    program_id INT NOT NULL,
    CONSTRAINT form_program1 FOREIGN KEY (form_id) REFERENCES forms (form_id),
    CONSTRAINT form_program2 FOREIGN KEY (program_id) REFERENCES programs (program_id)
    Now, I can run pure SQL if I go to the Create->Query Design and select SQL but it stops after creating one table. So I have to copy them in one by one. How can I get it to run them all at once?

    I'm reading Access™ 2007 Programming by Example with VBA, XML, and ASP and around page 250-300 it discusses creating tables and inserting etc with DAO and ADO, but this looks really way different from the SQL I'm used to. I'm used to being able to insert actual SQL into my code - is that available in Access? It just seems a lot easier. For example, the book shows this on page 250:
    fld.ValidationRule="Like 'A*'"
    fld.ValidationText="AgentIDmustbeginwiththe letter 'A' "&_
    'CreateCaptionpropertyandset itsvalue
    ' add ittothecollectionoffieldproperties
    MsgBoxErr.Number&": "&Err.Description
    I guess (using CreateDatabase and db.Execute) is more what I'm looking for, although I don't really want to to create a new database - I just want to populate the current database with tables. So how would I do that?

    UPDATE: Have to run, but it looks like db.execute_many is what I'm looking for. I'll have to test it tomorrow.
    Last edited by lazycritic; 08-15-12 at 21:29.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Actually, you can assemble the SQL commands then have them executed using VBA, or use DAO or ADODB objects to create/alter/drop tables, or both. Here's a rather complex example:
    Function CreateEventLog(Optional ByVal DatabaseName As String, _
                            Optional ByVal RecreateDatabase As Boolean, _
                            Optional ByVal DropExistingTable As Boolean) As Long
        Const c_SQLCreate As String = "CREATE TABLE Tbl_Events ( SysCounter COUNTER (0,1) CONSTRAINT PrimaryKey PRIMARY KEY );"
        Const c_SQLDrop As String = "DROP TABLE Tbl_Events;"
        Const c_SQLInsert As String = "INSERT INTO Tbl_Events ( Event_Type, Database_Name, Extra1, Extra2 ) VALUEs ( -1, '@D', 'New EventLog', '@L' );"
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
        Dim varAttribute As Variant
        Dim strBackupDb As String
        Dim i As Long
        If Len(DatabaseName) = 0 Then DatabaseName = Replace(CurrentDb.Name, ".mdb", ".EventLog.mdb")
        If Len(Dir(DatabaseName)) <> 0 Then
            If RecreateDatabase = True Then
                strBackupDb = Replace(DatabaseName, ".EventLog.mdb", ".EventLog.bak.mdb")
                If Len(Dir(strBackupDb)) > 0 Then Kill strBackupDb
                Name DatabaseName As strBackupDb
            ElseIf DropExistingTable = False Then
               CreateEventLog = 58  ' File already exists error.
               Exit Function
            End If
        End If
        If Len(Dir(DatabaseName)) = 0 Then
            Set dbs = DBEngine.CreateDatabase(DatabaseName, dbLangGeneral)
            Set dbs = DBEngine.OpenDatabase(DatabaseName, True)
        End If
        For Each tdf In dbs.TableDefs
            If tdf.Name = "Tbl_Events" Then Exit For
        Next tdf
        If Not tdf Is Nothing Then
            If DropExistingTable = True Then
                Set tdf = Nothing
                dbs.Execute c_SQLDrop, dbFailOnError
                CreateEventLog = 3010   ' Table already exists error.
                Exit Function
            End If
        End If
        dbs.Execute c_SQLCreate, dbFailOnError
        Set tdf = dbs.TableDefs("Tbl_Events")
        For i = 1 To 7
            Set fld = tdf.CreateField(Choose(i, "Computer_Name", "User_Name", "Database_Name", "Event_Time", "Event_Type", "Extra1", "Extra2"))
            fld.Type = Choose(i, dbText, dbText, dbText, dbDate, dbLong, dbText, dbText)
            varAttribute = Choose(i, 127, 127, 127, Null, Null, 255, 255)
            If Not IsNull(varAttribute) Then fld.Size = varAttribute
            fld.Required = Choose(i, True, True, True, True, True, False, False)
            varAttribute = Choose(i, False, False, False, Null, Null, True, True)
            If Not IsNull(varAttribute) Then fld.AllowZeroLength = varAttribute
            varAttribute = Choose(i, "=Environ('COMPUTERNAME')", "=Environ('USERNAME')", Null, "Now()", 0, Null, Null)
            If Not IsNull(varAttribute) Then fld.DefaultValue = varAttribute
            tdf.Fields.Append fld
        Next i
        Set tdf = Nothing
        dbs.Execute Replace(Replace(c_SQLInsert, "@D", CurrentDb.Name), "@L", DatabaseName), dbFailOnError
        Set dbs = Nothing
    End Function
    You can also store the SQL commands in Access queries then have them executed in a loop inside a VBA procedure:
    Sub BatchSQLExec()
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            If (qdf.Type And dbQMakeTable) <> 0 Then    ' Execute CREATE TABLE queries only.
                dbs.Execute qdf.Name, dbFailOnError
            End If
        Next qdf
        Set dbs = Nothing
    End Sub
    Last edited by Sinndho; 08-16-12 at 04:04.
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Why would I want to make things more complex? I'm hoping to use basic SQL - it's very easy to understand and read. The point is to reduce complexity.

    It's turning out that it looks like there is no way to do what I want to do? When I tried to use db.Execute(sql) on the plaintext SQL above, it said there was an error in my syntax.

    I just want to run some basic CREATE TABLE SQL rather than transform those CREATE TABLE statements into complex objects and use awkward syntax. Is there a way to do that?

    When I tried to do db.Execute(sqltext) (where the text file has the issues CREATE TABLE above) it just says I have a syntax error.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    The problem with the SQL interpeter of Access is that it recognizes only a subset of the DDL SQL commands. If you want to create, drop, alter tables, you won't have any problem for defining Tables, Indexes, Columns and their data types (at least those that Access can handle).

    However instructions for creating constrains, such as those specifying the default value of a column do not work, although they do not always raise an error. In such cases, you'll need to instanciate the table into a DAO Tabledef object, then use its properties and method to modify the table, thats what is done in the loop part (For i = 1 to 7...) in my example where some properties such as DefaultValue and AllowZeroLength (= allow Null) are added to the columns of the newly created table.

    Another solution consists in using the ADODB library which "recognizes" a larger subset of DDL SQL commands. In any cases, do not expect to find the whole set of SQL commands that are available in "true" database servers such as SQL Server, MySQL or Oracle.
    Have a nice day!

  5. #5
    Join Date
    Aug 2012
    Thank you for your detailed help. I'll just accept the fact that some things in Access are going to take a little more work.

    The other thing that bugs me about Access is that, last time I tried using it, I couldn't mash up and show as much data at a time as I wanted. I was limited to one main form and another subform... anyway, that's a discussion for another time.

  6. #6
    Join Date
    Mar 2009
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts