Converting from Access to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting from Access to SQL

Author

Topic

Raptor
Starting Member

8 Posts

Posted - 2009-07-07 : 10:48:28

I am in the middle of moving everything into SQL instead of Access. I have an access database that links to the SQL server and does appends.
This is what my access query looks like. Can someone help me convert this to SQL. I am pulling my hair out.

INSERT INTO [dbo_Patient Information] ( ProviderAcctNumber, [Admit Date], [Guarantor Last Name], [Guarantor First Name], SocialSecurityNumber, FirstName, LastName, Birthdate, Address, City, State, ZipCode, HomePhone, Charges, [AR Bal], [Health Insurance], [Health Insurance2], Status, EID, ReferredBy, MiscRef, [Health Insurance3], [Health Insurance4] )
SELECT Right("0000" & [PATNO],12) AS Expr1, DateSerial(Left(Right([service-date],5),4),IIf(Len([service-date])<9,Left([service-date],1),Left([service-date],2)),Left(Right([service-date],7),2)) AS Expr2, StrConv(IIf([GUAR-LAST-NAME]=[pat-last-name] And [guar-first-name]=[pat-first-name],Null,Trim([guar-last-name])),3) AS Expr8, StrConv(IIf([GUAR-LAST-NAME]=[pat-last-name] And [guar-first-name]=[pat-first-name],Null,Trim([guar-first-name])),3) AS Expr9, newaccts.[PAT-SSNO], StrConv(Trim([PAT-FIRST-NAME]),3) AS Expr10, StrConv(Trim([PAT-LAST-NAME]),3) AS Expr11, DateSerial(Right([pat-birthdate],4),IIf(Len([pat-birthdate])<8,Left([pat-birthdate],1),Left([pat-birthdate],2)),Left(Right([pat-birthdate],6),2)) AS Expr3, StrConv(Trim([PAT-ADD1]) & IIf(Trim([pat-add2]) Is Null Or [pat-add2] Like " *","",", " & Trim([Pat-add2])),3) AS Expr4, StrConv(Trim([PAT-CITY]),3) AS Expr12, newaccts.[PAT-STATE] AS Expr13, newaccts.[PAT-ZIP], newaccts.[PAT-PHONE], newaccts.[TOTAL-CHARGES], newaccts.[CURRENT-BALANCE], newaccts.INS1, newaccts.INS2, "2 - Work" AS Expr5, 3 AS Expr6, 19 AS Expr7, IIf([patno] Like "00009*","A","6") AS MiscRef, newaccts.INS1, newaccts.INS2
FROM (newaccts LEFT JOIN [Insurance Plans] ON newaccts.INS1 = [Insurance Plans].[Ins Code]) LEFT JOIN [Insurance Plans] AS [Insurance Plans_1] ON newaccts.INS2 = [Insurance Plans_1].[Ins Code]
WHERE (((newaccts.Added) Is Null));

jfuex
Starting Member

29 Posts

Posted - 2009-07-07 : 11:56:08

You do have a mess on your hands there. Your main problem is likely all the embedded VBA code int he query, some of which is not supported or uses different keywords in tSQL. Here are a few tips to help you along.

1) Get rid of the StrConv function call wrappers, you won't need them after you implement my second tip.
2) Convert the IIF to Select Case statements (See this page for more details: http://www.devx.com/tips/Tip/29137)
3) Here is an article on creating a replacement function in tSQL for DateSerial (http://jerrytech.blogspot.com/2008/04/no-dateserial-in-sql-well-here-function.html).
4) Left and right should work similarly in TSQL.

ooo
O

ooo
O

cbalian
Starting Member

1 Post

Posted - 2009-07-07 : 15:35:21

I am also in the process of converting multiple Microsoft Access reports into SQL. I've done multiple internet searches and there are tips but isn't there a (conversion tool) or something like a translator that you can copy/paste Access SQL code into it and it translates it to T-SQL code?

I mean if we can have translators for english to french or japanese to german you'd think Microsoft Access SQL to T-SQL would be possible. Hoping it is out there and I just haven't discovered it?

Thanks,
Chad

Chad

Raptor
Starting Member

8 Posts

Posted - 2009-07-07 : 23:20:34

I agree Chad, I look at jfuex's suggestions... Talk about lost.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources