August 2, 2012 at 3:27 pm
I have a client with well established technology. They are using a front end MS Access and a back end of SQL Server. I'm adding a little extra functionality. They want to be able to filter on a grid and pass that information to a SQL Server stored procedure as a parameter, which will be converted to a T-SQL compatible filter. I'm sure there's something already built somewhere, but my meager searching skills can't find anything. Does anyone have anything prebuilt that does this?
An example would be:
Access query:
(field1 in ("abc", "def") and field2 in (123, 456) and ((((field3="ghi"))) and ((field4 Like "2*")))) and (((((field5>2000))) and ((field6 Between #1/1/2012# And #1/1/2013#))) and ((Not field7="jkl")))
Converted to SQL:
(field1 in ('abc', 'def') and field2 in (123, 456) and ((((field3='ghi'))) and ((field4 Like '2%')))) and (((((field5>2000))) and ((field6 Between '1/1/2012' And '1/1/2013'))) and ((Not field7='jkl')))
Of course, I'd need to handle mids, instrings, lefts, rights, trims, and other common functions that experienced Access users might use.
I can build one, and will probably get started on it in the morning, but I'd prefer to not rebuild the wheel. And, if, you know, those excessive and ugly parentheses disappeared... well....
August 2, 2012 at 4:05 pm
I've personally never ran into anything that can convert Access DAO.SQL to T-SQL effectively. There's just enough differences and assumptions in the two that they don't play nice directly except for simplistic select views.
They always end up as more work instead of less, in the few times I've used them. However, I should caveat that the last time I tried was 7+ years ago. Between mis-translations of logic, function differences, and everything else I was always better off directly writing the code from the original than allowing an interpreter to do it for me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 3, 2012 at 10:03 am
Well, that's a shame. I'm pretty much restricted to operating this way for this need - but it matches up with the fact that I could find no prebuilt procedures to do the conversion.
Guess I'll build my own then...
Thanks!
August 3, 2012 at 12:09 pm
james.massey (8/3/2012)
Well, that's a shame. I'm pretty much restricted to operating this way for this need - but it matches up with the fact that I could find no prebuilt procedures to do the conversion.Guess I'll build my own then...
Thanks!
I can't help you much except to offer my sympathy. I've done my share of Access to SQL query migrations and it can be a drag. There are tools out there that get some praise but I personally have not had any success with them and had to do it manually instead.
For me it was not that bad (e.g. TSQL ' {single-quote} = Access " {double-quote}, TSQL Char = Access CHR, TSQL % = Access *, TSQL LOWER = Access LCASE, TSQL UPPER=UCASE, etc)
Dates and time is, by far, the toughest part.
This cheat sheet was the most helpful thing I found for this task:
http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx
Shoot me a message if you get stuck.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply