Sql Server SP to Access

Question

Sql Server SP to Access

JM-92548

Ten Centuries

Points: 1108
More actions
July 11, 2005 at 9:22 am

#66678

I have a stored procedure I need to recreate in Access 2003.
Does anyone have any pointers.
It looks like there is a create procedure in access but I cont get it to work.

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

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

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

Procedures in access mdb files?? That's new (to me anyways). Are you sure it's not and access data project that is connected on the server?

JM-92548 Ten Centuries Points: 1108 More actions · Answer 2

I really am not sure. Access is new to me. I have not worked with Access much.

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

What is the filename of the db?

if it's .mdb then it's probabely an access db, else (adp), it's an access data project that is connected to an sql server.

To be sure, hit files/connexion/ his test connexion. If you don't get an error from this it's because you are connected to a server.

JM-92548 Ten Centuries Points: 1108 More actions · Answer 4

The file is access because its an MDB...

OK how about this. Is it possible to create a temporary table in access like in Sql.

In Sql we use the ##tbl_name. In access is there something similar?

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

I think you have to create a perm table and drop it manually. The syntaxe seems to be the same between sql server and access. Also have you considered making a perm table that would hold the temp data? This would lessen the overhead on the db.

Also the real question is why do you need a temp table?

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

Forgot to post this link for an all-access forum :

http://www.AccessMonster.com

noeld SSC Guru Points: 96590 More actions · Answer 7

I may not be an Access Monster but i can tell you that you can call sp in Sql Server through a MS Access (mdb) Pass-through query and it works very well

Cheers

* Noel

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

He didn't specify he was using sql server for this project .

JM-92548 Ten Centuries Points: 1108 More actions · Answer 9

I need to use access and recreate the SP that I have in SqlServer.

noeld SSC Guru Points: 96590 More actions · Answer 10

To re-create a stored procedure you will be better off using QA. To call it use the MS Access Passthrough query type!

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 11

I assumed he meant that the SP was in SQL Server .... and I was right

* Noel

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

Still that guessing game... do you think he understood what you meant or does he still need help?

noeld SSC Guru Points: 96590 More actions · Answer 13

I hope that he knows that QA = Query analyzer

and

I hope that he can write : exec spname in the Passthrough query

and

I hope that he creates an ODBC DSN for th passthough to use

and... well after writing this much I am not sure if he knows

* Noel

James Rimell Right there with Babe Points: 791 More actions · Answer 14

I do not think it has yet been established if the original poster wants to recreate a SP type data manipulation in a standalone access db (if not why are they using an mdb file and not an adp). About the only way in Access to do this standalone is by a combination of queries and VB, since the SQL impentation in Access leaves a lot to be desired.