Post reply

Can i run a Access procedure/functions from the sql server.

  • April 7, 2005 at 7:08 am

    #66179

    LO,

    Can i run a Access procedure/functions from the sql server.

    Thanks.

     

  • April 8, 2005 at 3:15 am

    #551403

    The simple answer is no, but you can still run the Access subs/functions from your Form or Report, or better still rewrite them into a Stored Proc and get SQL to do the hard work.

    Remember not to treat the Server like a local database - hard drives are much quicker than networks!  Minimise the amount of data returned to Access, i.e. only the fields and rows that you need.

  • April 8, 2005 at 8:12 am

    #551522

    Be careful using access against SQL server:  If I remember correctly, even if you query a table in SQL server from access, in some cases access brings back all the rows from the table before running your query against it.




    Curtis Smith
    SQL Server DBA
    Well in worked in Theory ...

  • April 8, 2005 at 10:27 am

    #551609

    Actually, the answer is yes, sort of.  Access can be started with a command line switch to invoke a macro, you could code a set of macros to run the program/function to do whatever.

    You're not directly invoking the function from Access, you're having SQL start Access and tell it what to do.

    According to the Access help system:

    "

    /x macro Starts Access and runs the specified macro. Another way to run a macro when you open a database is to use an AutoExec macro.

    "

    It's not interactive, but since we don't know what you're trying to do with this, it might help.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • April 8, 2005 at 12:47 pm

    #551679

    I have done this in the past using a Pass-Through query out of Access. Remember to set up your ODBC connection string first.

    Regards,

    Ross

    Maricopa County DBA

  • April 10, 2005 at 1:09 am

    #551753

    Hi,

    You mean that i can exec macro(from access) from the command line in my server.

     

     

  • April 11, 2005 at 3:32 am

    #551789

    That is basically what Wayne West is saying, but you will need to be careful for two reasons.  Firstly, as Wayne states, it is not interactive and it will run in a blind process without a user interface. This means you will not be able to get any response from the user to the macro, e.g. deleting a record warning or clicking OK on a message/error box, and it will be difficult to control from a stored proc. 

    Secondly it will be slow and memory hungry. Each call will spawn a separate process, containing a new copy of the Access EXE.  If there is a problem in the macro the process may not be terminiated, and you will also have to quit Access in code.  If every user can call this macro then you could end up filling your virtual memory and killing your server.

    I would strongly advise you avoid this solution.  You may be working to a deadline here, and this may seem like an quick, easy solution, but it may cause you bigger headaches in the future.

    What does this macro do?  The people responding to this article may be able to suggest an alternative solution for you,  or even point you to a T-SQL equivalent routine...

  • April 11, 2005 at 4:42 am

    #551792

    the macro will exec internal function.

    How can i do that?

  • April 11, 2005 at 5:52 am

    #551801

    Which internal functions?  If you want help on this you'll need to be more informative.  I understand that you may not wish to publish your code, but a brief overview or at least your goals would give us an idea in which direction to point you, or a reason why this has to be done in Access!

  • April 11, 2005 at 8:53 am

    #551867

    Hi and Thanks

    i need to run access procedure ,after

    a running sql sricpt from the dts.

    regards.

  • April 11, 2005 at 9:10 am

    #551874

    DTS can run Visual Basic ActiveX Scripts.  Have you considered converting your Access macro to VB Script?

  • April 11, 2005 at 9:22 am

    #551878

    Im talking about creating macro in access.

    How can i convert/run it not using access?

     

  • April 12, 2005 at 2:29 am

    #552062

    Because Access's VBA communicates to Access's data using ADO.  You can use this from a VB Script too.  Or better still, you can use a DTS dataconnection and add a VB Script which will process on a row by row basis, which can be a very powerful tool.  See ActiveX Script Transformation Properties in Books on line for details.

  • April 12, 2005 at 3:49 am

    #552079

    in the access data base i have some local table And becuse of that i cannot convert it to vbscript?

    any other suggestion?

     

  • April 12, 2005 at 6:53 am

    #552116

    DTS the tables to sql server and do the work there. Looks like you'r trying to dig a big hole with a spoon instead of using a shovel... use the right tool for the right job.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply