Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Unanswered: Executing SQL statements in Access

    Hi to all,

    Can MS Access 2003 or later execute SQL scripts/statements? Statements like the create/insert actions?

    I am talking about not only executing one statement, but a bunch/group of action statements. More like a dump of a table, a backup SQL file of a database.

    thanks and god bless
    Programming is fun!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Unless I'm missing something here, the answer is a simple Yes. Access has it's own SQL version (JET) but it is quite capable.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    What you mean maybe is the execution of SQL statements in the query window? Yes, it is possible, but I am referring to
    the importing of a SQL script/file and then executing it as a "whole" in access? Is that possible?

    I can't seem to find that feature in the access 2003 application itself. Where exactly is this feature located?

    Thanks
    Programming is fun!

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Where are you importing / wanting to import the SQL statements from? Text files? SQL server?

    And in what version are those SQL statements? SQL Server? JET? MySQL?

    Either way you are most likely going to need to write some VBA to do it, but I'm sure it can be done.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2007
    Posts
    77
    I am asking if Access can be able to read a simple SQL script file, which contains CREATE TABLE, INSERT or any action statements. I mean, this will came from an external source, which is a script file, not that you will manuall type in the query window of Access itself.

    You will be reading it from a file and then executing it in Access? If I am not mistaken, Access hasn't this feature right?

    I am sorry if I got you confuse in the first place.

    Thanks
    Programming is fun!

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sokay, and no, there is no native command that can just pick up a [text] file and process it as an SQL statement.

    You still haven't said where this script actually comes from. I ask because it is most likely not an Access SQL statement and so even if it could read the [text] file then it wouldn't understand it.

    You'd have to design your own command to do such a thing.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Mar 2007
    Posts
    77
    I see now. Thanks

    About the script file, it will come from MySQL. A dump file, backup of some sort and then re-execute it in access, thereby assuming that access will execute it on its own. HEehehehhehe :d

    thanks
    Programming is fun!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Depends on the SQL statement itself then. If the MySQL string is simple, it just might be easy to import it and execute it.

    Can you post a sample?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is no native access function that will import say a MySQL script (*.sql) and run that script.

    however MySQL script files are ascii text, so you could very easily write your own function to rerad the script file and execute the appropriate SQL statements. personally I wouldn't attempt to to exectute a series of SQL commnads in one execute statement. in part thats because there is a finite limit to the size of any variable in Access, but you loose controil of where the problem lies (if you issue a series of execute statements you [should] know where the process failed).

    However I suspect the main thing that will trip you up is the differences in syntax between MySQL and JET, although they are both SQL tools, they are not 100% compatible to either the standard or each other. to do this I think you will be better off either usign one of the propriatory tools such as dbtools.

    If 'all' you want to do is to truck data around.. then persoanlly I'd 'just' export the data form MySQL into a file.. using wahtever tool you can use (mySQL Query Browser, PHPMyAdmin etc) and then import that data into Access.. you can automate this by using a macro or calling a fnction using the windows scheduler.

    I think others may be able to give you a better answer if you elaborate on what your requirement is.. what are you actually trying to do, and where is it falling apart right now.

    HTH

  10. #10
    Join Date
    Mar 2007
    Posts
    77
    I am not really doing something out of my question. All I wanted was to ask if it was possible to execute SQL scripts in Access.

    I just want to passed data around in different RDMS, that's all.

    Thanks and god bless
    Programming is fun!

Posting Permissions

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