Results 1 to 4 of 4

Thread: SQL, VBscript, ASP and Access 2000

  1. #1

    Thread Starter
    Fanatic Member Matt_T_hat's Avatar
    Join Date
    Dec 2001
    Location
    '76 Male Body Evil-Errors: 666
    Posts
    774

    SQL, VBscript, ASP and Access 2000

    I am trying to write a script that will add tables to a database.

    It has to be done this way for a number of reasons.

    Regardless I have tested this SQL in Access 2K and will be running it for an Access 2K DB too. The SQL worked fine in access but produces

    Microsoft JET Database Engine error '80040e14'

    Syntax error in CONSTRAINT clause.

    for the first SQL statement.

    VB Code:
    1. ScriptSQL = "Create Table [MyHome](ID AUTOINCREMENT CONSTRAINT PK1 Primary Key, UserID Integer, ModuleID Integer CONSTRAINT FOREIGNKEY REFERENCES module (id), SortVal Integer, Pos Integer);"
    2. set rs = my_Conn.Execute(ScriptSQL)
    3.  
    4. ScriptSQL = "Create Table [Module](ID AUTOINCREMENT CONSTRAINT PKModule Primary Key, File text(60), Source text(50), Code Text(5), AdminHelp Text(50) );"
    5. set rs = my_Conn.Execute(ScriptSQL)

    As the SQL worked perfectly the first time in access itself I am at a loss as to why it errors.

    help!
    ?
    'What's this bit for anyway?
    For Jono

  2. #2
    Hyperactive Member kayos's Avatar
    Join Date
    Apr 2004
    Location
    Largo, Florida
    Posts
    306
    It could be because you are setting a recordset object equal to a query that returns no values. Instead of usng:

    set rs = my_Conn.Execute(ScriptSQL)

    try simply using

    my_Conn.Execute(ScriptSQL)

    You can execute SQL commands without the use of the recordset object.


    If this post helps, please RATE MY POST!

    Using Visual Studio 2005 SE

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532
    Ooooorrrr..... it could be that the first table contains an FKey reference to the second table, which DOESN'T exist yet. Try reversing the lines.

    TG
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Fanatic Member Matt_T_hat's Avatar
    Join Date
    Dec 2001
    Location
    '76 Male Body Evil-Errors: 666
    Posts
    774
    Originally posted by techgnome
    Ooooorrrr..... it could be that the first table contains an FKey reference to the second table, which DOESN'T exist yet. Try reversing the lines.

    TG


    oh-bloodyhell-how-did-I-miss-that!!!

    Of course! that was stupid-o-matic dang dang dang.

    Thankyou.
    ?
    'What's this bit for anyway?
    For Jono

Posting Permissions

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



Click Here to Expand Forum to Full Width