Execute Stored Procdure in SQL Server from Access (1 Viewer)

crosmill

Registered User.

Local time

Today, 19:58

Joined

Sep 20, 2001

Messages

285

• Sep 5, 2002

• #1

I have an Access front end for my SQL Server db.

I need to execute a stored procedure in SQL Server from Access or exectute the SQL from access to the SQL Server.

Does anyone know how I'd go about this.

I sort of half know what I'm doing, I've done a fare bit of ASP and VBA, but I can't find any reference to connect to the SQL Server from Access using VBA in my books.

I think if anyone knows the SQL Server vesion of:

SET db = currentdb()

I'd able able to work the rest out.

Thanks

 

crosmill

Registered User.

Local time

Today, 19:58

Joined

Sep 20, 2001

Messages

285

• Sep 5, 2002

• #2

Sorted it, created a Pass Through Query and called that from the VBA.

 

J

josef fukano

Guest

• Aug 29, 2003

• #3

Crossmill,
Can i see a sample of your pass through query? I am trying to do the exact same thing. All I have to do is execute the Stored procedure using Access on a client PC.

Thanks,
Joe

 

crosmill

Registered User.

Local time

Today, 19:58

Joined

Sep 20, 2001

Messages

285

• Sep 1, 2003

• #4

err, it's been a while since I did that. I can't remeber which db it's in, but I'll try and remeber/work it out for you.

OK you write the sp in SQL Server and the create a query in Access. Don't add any tables to the query. Select the menu 'Query' then 'SQL Specific' the 'Pass Trough Query'.

Select the properties for the query and set up the odbc connection. Select 'Yes' for setting the password, then type your query something like 'EXEC usp_spName'


I think that might do it.


Post back if you get stuck

Chris

 

J

josef fukano

Guest

• Sep 4, 2003

• #5

Thank you sooo much, ive been searching everywhere for that tip all i needed to know was that 'pass-through' was a type of query in access. Thanks again,

Joe