Is Access 2003/2007 compatible with SQL Server 2008

[Ian Bateman]

A client of mine has been running his e-commerce web site using SQL Server
2005 with php and using Microsoft Access 2003 as a front-end to that
database. The provider has now upgraded to SQL Server 2008 and everything is
falling apart.

I use the latest version of Access (2007) and that will not let me edit
queries any longer so I have no chance of trying to sort it out.

Do I now have to delete all my queries from the Microsoft Access Project and
re-create them using SQL Server Management Studio 2008?

Help please!!
Ian

 

[Tom van Stiphout]

On Fri, 1 May 2009 05:47:01 -0700, Ian Bateman

An Access Project does not contain queries, rather it provides a
window in the Views and Stored Procedures that live on the SqlServer.
So there is no need to delete and re-create. Just fire up SSMS2008 and
you will see all your familiar objects in the database, and you can
edit them using this superior interface.

-Tom.
Microsoft Access MVP

 

[Ian Bateman]

Thanks Tom,

So the following function contructed by Access will no longer work. What is
wrong with it?

ALTER FUNCTION dbo.fun_OrderListIS
()
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.Orders.OrderID,
dbo.Orders.OrderSource AS Src, dbo.Customers.FileAs AS Customer,
dbo.Orders.OrderDate AS [Order Date],
dbo.OrderStatuses.ShortStatus AS Stat
FROM dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID =
dbo.Orders.CustomerID INNER JOIN
dbo.OrderStatuses ON dbo.Orders.OrderStatusID =
dbo.OrderStatuses.OrderStatusID
WHERE (dbo.Orders.OrderSource = N'IS')
ORDER BY dbo.Orders.OrderID DESC )


Many thanks
Ian

 

[Tom van Stiphout]

On Fri, 1 May 2009 09:29:01 -0700, Ian Bateman

Open that function in SSMS and check the syntax. "will no longer work"
doesn't give us a lot to go on. Can you be more specific?
Personally when I write any sqlserver object I also include code to
test it, e.g.:
ALTER FUNCTION dbo.fun_OrderListIS()
RETURNS TABLE
AS
/*
select * from dbo.fun_OrderListIS
*/
RETURN ( SELECT TOP 100 PERCENT dbo.Orders.OrderID,
[etc.]

-Tom.
Microsoft Access MVP

Thanks Tom,

So the following function contructed by Access will no longer work. What is
wrong with it?

ALTER FUNCTION dbo.fun_OrderListIS
()
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.Orders.OrderID,
dbo.Orders.OrderSource AS Src, dbo.Customers.FileAs AS Customer,
dbo.Orders.OrderDate AS [Order Date],
dbo.OrderStatuses.ShortStatus AS Stat
FROM dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID =
dbo.Orders.CustomerID INNER JOIN
dbo.OrderStatuses ON dbo.Orders.OrderStatusID =
dbo.OrderStatuses.OrderStatusID
WHERE (dbo.Orders.OrderSource = N'IS')
ORDER BY dbo.Orders.OrderID DESC )


Many thanks
Ian

On Fri, 1 May 2009 05:47:01 -0700, Ian Bateman

An Access Project does not contain queries, rather it provides a
window in the Views and Stored Procedures that live on the SqlServer.
So there is no need to delete and re-create. Just fire up SSMS2008 and
you will see all your familiar objects in the database, and you can
edit them using this superior interface.

-Tom.
Microsoft Access MVP

 

[Paul Shapiro]

SQL 2008 might have gotten fussier about including sorting in a view or
table-valued function. It's never officially been supported, even though the
workaround of using Select Top 100 Percent worked in earlier versions. I'm
still using SQL 2005, but if I remember correctly, the 2005 release notes
warned this was deprecated functionality that might not be accepted in the
next release.

Access 2007 will not be able to edit any objects in SQL 2008. Historically,
Access has only supported editing objects in SQL versions prior to the
Access version release. You could still run a DDL sql statement from Access
to effect object changes, but the designers won't work with SQL 2008. I
agree with Tom that SMSS provides a better interface anyway.

Thanks Tom,

So the following function contructed by Access will no longer work. What
is
wrong with it?

ALTER FUNCTION dbo.fun_OrderListIS
()
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.Orders.OrderID,
dbo.Orders.OrderSource AS Src, dbo.Customers.FileAs AS Customer,
dbo.Orders.OrderDate AS [Order Date],
dbo.OrderStatuses.ShortStatus AS Stat
FROM dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID =
dbo.Orders.CustomerID INNER JOIN
dbo.OrderStatuses ON dbo.Orders.OrderStatusID =
dbo.OrderStatuses.OrderStatusID
WHERE (dbo.Orders.OrderSource = N'IS')
ORDER BY dbo.Orders.OrderID DESC )


Many thanks
Ian

On Fri, 1 May 2009 05:47:01 -0700, Ian Bateman

An Access Project does not contain queries, rather it provides a
window in the Views and Stored Procedures that live on the SqlServer.
So there is no need to delete and re-create. Just fire up SSMS2008 and
you will see all your familiar objects in the database, and you can
edit them using this superior interface.

-Tom.
Microsoft Access MVP