Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    8

    Unanswered: Run sql from the command line in MS Access

    Hi,

    I wonder would anyone know if its possible to update MS Access from the command line, e.g I have a batch file in which i'd like to embed SQL,

    I have done similar in Unix scripts by calling sqlplus to an oracle dB but not sure if its possible in MS Access, I think it may be possible to output to a MySQL db then link the two but plan A would be better, any ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You know I don't know.

    You could certainly write a vbs script to do so and call that from your bat file....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2007
    Posts
    8

    Smile

    thanks, but due to the systems and queues, I need to trigger it from outside the dB, hence call sql - insert statement as part of the bat file if possible

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes you can

    if you consider calling an instance of MS Access appropriate from a command line.

    Im not sure how it would work say with a Win95 or earlier command line, but the current OS's all fake the command line interpreter. so the batch file should appropriately trigger a windows program. If its a regular process then you could queue the process as part of windows scheduler.. that does work

    you can set all manner of parameters by using what are command line switches. I think if you want to you can also set a macro or procedure to run on opening.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cmac
    thanks, but due to the systems and queues, I need to trigger it from outside the dB, hence call sql - insert statement as part of the bat file if possible
    Does my suggestion not fit with that?

    Quote Originally Posted by Healdem
    yes you can
    Mark - what is the DOS syntax for executing SQL against an Access\ JET database?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create a batch file
    if MSAccess is in your path type msaccess, failing that specify the full path to the app

    in my case its
    \progra~1\micros~3\office10\msaccess.exe
    or as windows explorer knows it
    \programs and files\microsoft office\office10\msaccess.exe

    add any command line switches as required

    Id agree that you cannot run raw sql straight into access.. you'd have to do some workaround it.. eg in a macro or VBProc read the file, process each line sequentially and issue appropriate SQL commands. its not pretty but Access can be made to run from a batch file.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yeah - I have no problem with running access from the command line. It is the SQL part that I don't like so much.

    vb script has data objects available to it specifically for this sort of thing - dos does not.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id agree on vbscript, mind you for sheer devilment you could also use PHP on the command line. The more I look at that language, the more it seems to be being pushed into new areas

    but some anti virus is very sniffy about vbscript
    the error handling is in my view harder, the error reporting is also harder in my view. some of that may be down to my familarity with the VBA model.

    its also neater in my view to keep everything Access realted within the access stable, so that the poor unfortunate coming after you to maintain the system can see everything in the Access files. if its scattered into disparate elements its easy to overlook certain key elements.

    The number of times I've landed on customer sites to find the system documentation is either missing presumed dead, inaccurate, or incomplete. Id far rather fire up the VBA editor and try to understand whats there.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I thought the choice (if there is one) is dos vs vbs not dos vs vbs vs vba.

    Difficult to know for sure without more specifics I guess - vba might be applicable but if you are thinking what I think you are thinking (a proc that accepts a string that it executes as SQL) then I don't really see that as all that much neater\ transparent.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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