Menu
I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code or query I work with those tables very simple, I access them via name, like [Customers].
Also I have a stored procedure in SQL Server called sp_CopyData which I need to call from my VBA code. How can I do that without creating new connection to SQL Server (I already have it somewhere!? because I have access to tables)?
Or it's impossible? Appreciate any help. Thanks!
863
On the create tab, click Query design in the other group. Click close in the Show Table dialog box without adding any tables or queries. On the Design tab, click Pass-Through in the Query Type workgroup.
Creates a stored procedure. The Microsoft Access database engine does not support the use of CREATE PROCEDURE, or any of the DDL statements, with non-Microsoft Jet database engine databases.
SQL is a computer language for working with sets of facts and the relationships between them. Relational database programs, such as Microsoft Office Access, use SQL to work with data. Unlike many computer languages, SQL is not difficult to read and understand, even for a novice.
One way is you make a stored procedure in database then call it in job using script below.. sql('datastore','exec Stored_Procedure_Name'); 2. Other way to achieve it – import your stored procedure in function..
The right answer found out, it should be like:
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connect
qdef.SQL = "EXEC sp_CopyData"
qdef.ReturnsRecords = False ''avoid 3065 error
qdef.Execute
Create a pass-though query, and you can then use this throught the WHOLE application anytime you need to execute some T-SQL.
The code this becomes:
With CurrentDb.QueryDefs("qPass")
.SQL = "exec sp_copydata"
.ReturnsRecords = False ''avoid 3065 error
.Execute
End With
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
