AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, February 26, 2014

Office 2013 Service Pack 1

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".

Labels:

Thursday, February 20, 2014

AllAPI.net new address

Just to keep it here: as AllAPI.net is no longer exists, there is a "saved copy" of it at allapi.mentalis.org

Labels:

Tuesday, February 18, 2014

Passing recordset or table to store procedure

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.

Labels: , ,