Alex & Access

February 26, 2014

Office 2013 Service Pack 1

Filed under: Access 2013 — access @ 10:01 am

Microsoft has announced availability of Service Pack 1 (SP1) for the Office 2013 and SharePoint 2013. According to Issues Fixed by Service Pack 1 several Access crashes were fixed, the only new feature introduced with SP1 is "Access app developers can now upgrade the apps they have made available in the Store or a corporate catalog".

February 18, 2014

Passing recordset or table to store procedure

Filed under: Access, SQL Server — access @ 2:09 pm

These days you can pass a table variable to SQL Server stored procedure, it can be easy done in .NET, but not sure you can do this in Access with VBA. But we can do the same using XML. Imagine you need to pass Order details of several rows. Create a string variable with following XML:
<Table><Row ProductID="1" Qty ="2" Price="3"></Row><Row ProductID ="3" Qty ="4" Price="5"></Row></Table>

Make a store procedure like:

CREATE PROCEDURE [dbo].[spListOrder]

@OrderID int,

@XML as varchar(max)

AS

begin

declare @docHandle int

–load DOM

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML Select * from (Select ProductID, Qty, Price FROM OPENXML(@docHandle, N’/Table/Row’) WITH (ProductID int, Qty float, Price money)

END

And run it as:

ExecureSQL "spListOrder " & lngOrder & "," & strXML

 

That is! Note,  you you run once sp_xml_preparedocument – and you can use (Select ProductID, Qty, Price FROM OPENXML(@docHandle, N’/Table/Row’) WITH (ProductID int, Qty float, Price money) as any other select statement, in joins, updates, inserts, etc.

Blog at WordPress.com.