I've been working with SSMA to migrate my (fairly large) A2007 app from ACCDE/ACCDB (Front End/Back End) to ACCDE/SS2k5. I have to say, the most recent version is a joy....

I've been in and out of IT for over 30 years. I have a degree in Computer Science and an MBA in Finance. I was a master mainframe COBOL programmer...

I tested Lowell's contribution, thank you, Lowell and Ninja. I altered it slightly, here's the final query:

SELECT *

FROM

(

SELECT

ROW_NUMBER() OVER (PARTITION BY W.WorkerID ORDER BY W.ID DESC...

Only one table in use:

USE [CPASD]

GO

/****** Object: Table [dbo].[tblStaffAugTrans] Script Date: 08/09/2011 18:08:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblStaffAugTrans](

[ID] [int] IDENTITY(1,1) NOT NULL,

[VendorID] [int] NOT...

OK, that answered my question. I'll try it.

Interesting... "change the definition..." Do you suggest I do that in the view definition on SS2k5, or the connection string in Access (not really practical, since every user has his...

I'm probably not being very clear.... Lemme try.

Construction Project Accounting System (CPAS...)

Front end...A2007, no tables, just forms, reports, queries. All table references linked.

Back end...Split

A2007 -- all...

The front end is in Access. Access opens up a DAO recordset on the SQL Server data. If rows are deleted, it sees they were deleted and notifies the user...

I am mid-conversion. I have over a hundred forms to test before I can convert. There is no halfway conversion. Users continue to use the Access BE until I can...

Well, I've fixed the problem. I set up the form to run the (Access) query, creating a "temp" table (residing on SQL Server), then, I wrote a brief SP to...

SSMA is a very good tool provided free from MS that will convert an Access (Oracle, MySQL, etc) database to SQL Server.

For Access, it converts data types, creates triggers,...

Sql Server Migration Assistant

With a little thought, I was able to use the IsNumeric function:

ProjNbr: IIf(IsNumeric(Right([ProjectNbr],Len([Projectnbr])-1)),CInt(Right([ProjectNbr],Len([Projectnbr])-1)),0)

Which SSMA will translate nicely into a case statement.

I'll read up on IsAllDigits....

You misunderstand. I've not yet converted the query to SQL Server. You've shamed me into getting to it, but its final implementation will have to wait...

On another note, see the "Val" function? My project numbers are of the form "C99999", though a few are totally non-numeric. Val() works nicely by returning a zero for mal-formed...