Alex & Access

November 12, 2015

ADP+ for Access 2013 with SQL Server

Filed under: Access, SQL Server — access @ 12:13 pm

Another way to connect Access and SQL server – ADP+ library from Joakim Dalby, Denmark. Looks like it is build on ADO, and should work the same way as Access ADP. Would be good to know if anybody tried it?

I personally use  linked tables and DAO/pass-through tables, tried to use ADP several times, but every time switched back to mdb.

September 8, 2015

SQL Server version comparison and OS compatibility

Filed under: SQL Server — access @ 12:43 pm

Good to check when you are planning to upgrade Windows or SQL Server:

Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads

June 3, 2015

ADP+ framework for Access

Filed under: Access, SQL Server — access @ 8:53 am

Missed ADP projects? ADP+ can help you. this is a Data Access Layer for updatable form and subform and for report and subreport to have a client-server rapid ap­pli­ca­tion deve­lop­ment framework in Ac­cess 2013 connected to a SQL Server da­ta­ba­se 7.0, 2000, 2005, 2008, 2012, 2014 and the forthcoming SQL Server 2016.

February 6, 2015

Few FMS papers for SQL Server and upsizing

Filed under: SQL Server — access @ 10:12 am

Worth reading:

Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads

For the first time, all the different versions of SQL Server Express from 2005 to 2014 are shown, compared, and referenced with download links. I can’t even begin to share how time consuming it was to put this together and go through all the details of which O/S each version supports. Just because Microsoft web pages list the versions they support don’t necessarily mean it works when you actually try to install it. I’m sure there are still some minor inaccuracies among the SP releases. Let me know if you’ve experienced something different from what we’ve listed.

Automating the Backup of Your Microsoft SQL Server Express Databases

If you’re using SQL Server Express, you still need to create backups of the database. Here’s how to automate it which wasn’t easy to discover.

When and How to Upsize Microsoft Access Databases to SQL Server

The original version of this was written when Microsoft selected us to do a joint Access to SQL Server Upsizing campaign a decade ago. I’ve updated it to better explain why and why not people should upsize their Access databases and an overview of what the options are and what to do.

Microsoft Access to SQL Server Upsizing Resource Center

Our new Upsizing site with links to our whitepapers and Microsoft resources that help with the whole upsizing process and use of SQL Server Express.

November 17, 2014

Security Update MS14-066 causes major performance problems in Microsoft Access / SQL Server applications

Filed under: Access, SQL Server — access @ 8:51 am
Security Update MS14-066 causes major performance problems in Microsoft Access / SQL Server applications

 

Update: Switching to "SQL Server Native Client 10.0" ODBC driver helps to avoid performance issues, looks like update has effect on default "SQL Server" driver only

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.

January 2, 2014

"with recompile" SP option

Filed under: SQL Server — access @ 9:42 am

In addition to my recent post Running SP with Access pass-through query. set nocount on did the trick, but only for one day. My next try was to add with recompile option to store procedure declaration, and after 2 weeks I must admit this was a solution!

CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)
with recompile
as

December 17, 2013

Running SP with Access pass-through query

Filed under: Access, SQL Server — access @ 8:36 am

Got very strange behavior running SQL Server stored procedure with Access pass-through query. I have a SP with several parameters, temporary table and select statement, like:

CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)

    as

CREATE TABLE #Orders ( ID INT)

exec (‘Insert into #Orders Select ID  from tblOrders Where ID ‘ + @FilterExpr)

SELECT ID from #Orders

drop table #Orders

 

And I run it in Access using MySP ‘= 1’

This query/SP worked several years. During these years database have grown, of course, don’t know if that was a reason, but once users start to get a timeout running that query more that 180 sec. Same time running MySP ‘= 1’ in SQL Server management console worked just fine. Furthermore, there is a copy of database on same SQL server, and this SP works fine there. After some time I found how to fix it, it was set nocount on, and now my proc works fine, as below:

CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)

    as

BEGIN

set nocount on

CREATE TABLE #Orders (ID INT)

exec  (‘Insert into #Orders Select ID  from Orders Where ID ‘ + @FilterExpr)

SELECT ID from #Orders

drop table #Orders

END

I don’t know why setting nocount on help here, if anybody have explanation – please let me know. Anyway – keep this trick in mind when you writing stored procedures to be used in Access.

August 19, 2013

Running Excel under SQL Agent task

Filed under: SQL Server — access @ 6:43 am

I have a long time running small VB6 application as SQL Agent task, which creates an Excel spreadsheet, saves it on server and sends to mail recipient. Recently my customer has upgraded both Windows Server and SQL Server to 2008 R2, and application stopped working. I found that WorkSheet.SaveAs method keep failing and producing Error 1004 (Application-defined or object-defined error), which looks like Excel has no permission to access file system. I checked user account, used to run this step, he has enough rights to destination folder, and when I run this app under that user – it works ok. Only under SQL Agent it fails! It took me 3-4 hours to google this problem and found this one – The SSIS and Excel Story Continues! I run "MMC comexp.msc", go to the properties of Microsoft Excel Application, under Identity, changed it to The Interactive User from The Launching User (which is set by default). After making this change and restart SQL Agent my application starts working. But only for a while…

I found that sometimes it works, sometimes produces same error, and sometimes can’t even launch Excel. Fortunately, in the same blog I found another post on this issue Error ‘Microsoft Office Excel cannot access the file’ while accessing Microsoft Office 11.0 Object Library from SSIS, which helped me to fix this issue. You only have to create one folder:

For Windows 2008 Server x64: C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows 2008 Server x86: C:\Windows\System32\config\systemprofile\Desktop

So, looks like a configuration problem of Server 2008 R2 and Office 11, people talking about it since last year, Microsoft really could fix it for that time…

December 20, 2012

sp_BLITZ – SQL Server Takeover Script

Filed under: SQL Server — access @ 11:17 am

sp_BLITZ – Stored procedure to do a fast SQL Server health check, it helps you rapidly assess configuration, security, health, and performance issues. Just copy script into SSMS query windows, run it to create SP and then run SP.

Older Posts »

Blog at WordPress.com.