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

Question

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

Lital

SSC Eights!

Points: 901
More actions
April 7, 2005 at 7:08 am

#66179

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

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

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

EdH334 SSCrazy Points: 2857 More actions · Answer 1

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.

Curtis Smith Hall of Fame Points: 3063 More actions · Answer 2

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 ...

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

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]

Ross Sines Ten Centuries Points: 1288 More actions · Answer 4

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

Lital SSC Eights! Points: 901 More actions · Answer 5

Hi,

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

EdH334 SSCrazy Points: 2857 More actions · Answer 6

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...

Lital SSC Eights! Points: 901 More actions · Answer 7

the macro will exec internal function.

How can i do that?

EdH334 SSCrazy Points: 2857 More actions · Answer 8

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!

Lital SSC Eights! Points: 901 More actions · Answer 9

Hi and Thanks

i need to run access procedure ,after

a running sql sricpt from the dts.

regards.

EdH334 SSCrazy Points: 2857 More actions · Answer 10

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

Lital SSC Eights! Points: 901 More actions · Answer 11

Im talking about creating macro in access.

How can i convert/run it not using access?

EdH334 SSCrazy Points: 2857 More actions · Answer 12

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.

Lital SSC Eights! Points: 901 More actions · Answer 13

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

any other suggestion?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

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.