Access 2007 and pass-through query to SQL Server

[LEXCERM]

Hi there,

I've, today, created my first pass-through query (wahooooo!) which links my Access 2007 db to a query in SQL Server.

Is there a way that I can refresh the SQL Server query from Access?

For example, when I "refreshall" from Access, the pass-through always remains at the same number of records. Because I have no visible access to SQL Server, I was advised that the query in SQL Server needs to be refreshed each time and that I can do this via Access, but not sure how to.

My apologies if I am using incorrect terminology here, but I hope you get a gist of what I am asking.

Many thanks.

 

[LEXCERM]

Okay, I have a bit more info.

Apparently, I need to execute an SQL stored procedure via Access, but no values are returned. Apparently, this SP updates a table in SQL.

I've been told that I can use a pass-through query or ado methods. Now, my two questions are:-

a) which method is best practice
b) can someone please provide a link on how I can write these strings/code.

I have the ODBC and SP connection details etc and tried using some ADO code, but it just fails miserably.

I'm novice at this btw!

Many thanks in advance.

 

[LEXCERM]

Got a bit further with this and have this error on the following code: "the execute permission was denied on the object CreateTable":

On Error GoTo myError
Dim dataConn As ADODB.Connection
Dim dataRS As ADODB.Recordset
        
Set dataConn = New ADODB.Connection
dataConn.Open "DRIVER=SQL Server;SERVER=SERVER1;UID=portA;PWD=itport;"
Set dataRS = dataConn.Execute("[dbo].[CreateTable]")

EndRoutine:
Set dataConn = Nothing
Set dataRS = Nothing
Exit Sub

myError:
MsgBox Err.Number & vbNewLine & Err.Description
Resume EndRoutine

Thank you.