![]() Converting Access Queries to SQL Server — Database. Journal. com. In last. I introduced a Microsoft Access Add- In I created some. I affectionately call the Data. Fast. Utility. It includes a number of analysis tools that assist with the. Access application to SQL Server. Once you've created a macro, you need a way to execute or call it. Otherwise, all of your work designing and coding the VBA application would've been in vain. Within. Technical articles, content and resources for IT Professionals working in Microsoft technologies. If all of your queries. However, odds are your queries are not that simple and they're. T- SQL syntax standards. This month's article will focus on a few of the more common conversion. · Now I am a new guy on the block, so bear with me. I have a form that users are entering client information into all day, and I have been asked to set the databa. Microsoft Access Performance Tips to Speed up Your Access Databases by Dan Haught, Executive Vice President, and Luke Chung, President of FMS. This paper is featured on. While this is going to look a lot like a T- SQL. VBA. environment of Access to make the transition to the world of SQL Server script. Here are the conversion topics we will address: 1. Replacements. VBA functions; Date() and Now() 2. T- SQL. In. Str() and Mid() 3. How. IIF() function to T- SQL 4. Merciless. T- SQL string concatenation explained 5. Stored. Procedure verses View dilemma 6. Suggestions. T- SQL Functions. One last point that isn't grand enough to be a topic, but deserves mention is. DISTINCTROW keyword. This is an Access- specific SQL keyword that doesn't. SQL Server. I've read the help topic and hashed out the details with. Access developer friends, but I still can't remember the difference between. DISTINCTROW and DISTINCT. Suffice it to say, that you will need to convert any. DISTINCTROW to DISTINCT if you want it to run in SQL Server. With. that out of the way, let's get on to the fun stuff. Replacements for VBA functions; Date() and Now()Date functions should be really easy, but I have to admit. T- SQL disappointment. Maybe it's because I come from the. VBA, with simple, logical date functions and intelli. Sense (thank you. Red- Gate for SQL Prompt and Microsoft for SQL Server 2. Truth is, working with dates in SQL. Server is challenging for Access developers. Below is a 'cheat sheet' for some. Date() and Now()Simply stated, you use the GETDATE() function in T- SQL to return what you get. Now() method in VBA. That includes the TIME portion of the date. To. get just the date portion (Date() in VBA is not so simple. You need to call GETDATE(). You can look this up the syntax in SQL Server Books Online, but basically, the. Help system sucks. After years of reading how to use the CONVERT() function, I. I had always thought that the CONVERT function (see image below) required a. Date. Time datatype in connection with the date style argument: CONVERT(Date. Time, GETDATE(), 1. The last argument, 1. See MSDN. for a full explanation of the CONVERT function.) However, this is entirely. In order for any of the Styles to be applied, you must convert the date. CONVERT(VARCHAR(1. GETDATE(), 1. 01)This format will give you. This is the T- SQL, poor- excuse- of- an- equivalent for. Format() function in VBA. Yes, VBA, the ugly step- child. Maybe SQL Server 2. Access developers have enjoyed for nearly two decades. Am I being too harsh? Hmmm. Notice the last results column, in the screen shot. The TIME portion returns 6: 5. AM. What if you wanted a space in front of. AM? Sure, it can be done in T- SQL, but not without some pretty verbose. VBA Rules! For a full list of T- SQL date and time functions, see this MSDN article: Date. Time Functions (Transact- SQL)T- SQL equivalents for In. Str() and Mid()I often use other VBA functions in my Access queries. Trim(), Left(), In. Str() and Mid(). The LEFT() function in T- SQL. VBA, and the Trim() function requires only a simple. T- SQL, not being as intelligent or flexible as VBA, requires that. There is no TRIM() function, but you can do a Left. Trim and Right Trim combination that approximates the Trim() of VBA: SELECT LTRIM(RTRIM(' Now is the time ')) AS Trim. Results. The T- SQL equivalent of In. Str() is the CHARINDEX() function. The syntax for it's arguments is flip- flopped from the VBA version. The first. argument should be the string you are looking for, while the second is the string. The last argument is the starting location. This takes some. getting used to, but it works pretty much the same way as its VBA counterpart: SELECT CHARINDEX('is the', ' Now is the time ', 0) AS In. Str. Results. The VBA function to extract characters out of the middle of. Mid() function, is accomplished in T- SQL with the SUBSTRING(). If you are familiar with the Mid() function, you won't find the. It works the same way, as you can see from this example. SELECT SUBSTRING(' Now is the time ', 6, 6) AS Mid. Results. Below are the results as shown when run from SQL Server. Management Studio. How to convert the IIF() function to T- SQLThe IIF() function in VBA is also called the Conditional If. It might. look like this .. IIF([ytd_sales] > 4. Great!", "Ok")The T- SQL replacement for this. CASE statement. The full description is available on MSDN in the. CASE. (Transact- SQL). The screen shot below shows how it might be applied to the. Pubs database. Once you get used to the syntax, you'll find. CASE statement of T- SQL to be more powerful and flexible than nested IIF(). Yes, that's right! I said something nice about T- SQL. Yes, it actually. VBA in this instance. Even a broken clock is right twice a day. Merciless T- SQL string concatenation explained. Ok, we're not going to harp about this one for a long time. T- SQL a. bit deeper. In Access VBA, you can concatenate all kind of things into a single. It wasn't unusual for me to do something like this in a query .. Result. Msg: "You have " & [Order Count] & " orders ready to ship on " & [Ship Date] & "."Yeah, forget about doing that in T- SQL. Just forget it! Don't. It doesn't work. To concatenate strings in T- SQL, every piece of it. Plus, you don't 'ampersand' them together .. PLUS operator, like this .. SELECT 'You have ' + CONVERT(VARCHAR(8), [Order Count]) +. CONVERT(VARCHAR(1. Ship Date], 1) + '.'. You have 2. 3 orders ready to ship on 1. You should also note the use of the single quote, instead of. You'll get used to this too. However, on another positive note. SELECT statement to the next line without a line continuation. T- SQL scores again! Stored Procedure verses View dilemma. This topic is simpler yet. If you are converting an Access. MDB to link to SQL Server tables, views and procs, you really only have a. Stored Procedures are not updatable in an MDB. If you have. an Access Data Project, you can set a stored proc as the updatable recordsource. So, you use stored procs for the rowsources of combo and list. If you need to update the data. To improve performance on the read- only datasets, you should investigate the. Pass- Through queries. See my article in the archives, How. Execute SQL Stored Procedures from Microsoft Access. Suggestions for advanced issues: T- SQL Functions. The last category has to do with custom functions you may. Access VBA. Perhaps you have a call that performs a calculation. These will have to be painstakingly. T- SQL functions. The good news is, once you migrate the logic. Access. For example, let's say we wanted to encapsulate the logic shown above. CREATE FUNCTION fnc_Get. Sales. Status (@Sales. Amount MONEY). RETURNS NVARCHAR(8). DECLARE @Result. Msg NVARCHAR(8). SELECT @Result. Msg =. CASE. WHEN @Sales. Amount 7lt; 1. 00. THEN 'poor'. WHEN @Sales. Amount BETWEEN 1. AND 4. 00. 0 THEN 'Ok'. WHEN @Sales. Amount > 4. THEN 'GREAT!'. ELSE 'unknown'. RETURN @Result. Msg. Once created, you simply call the function from your SELECT. SELECT title, dbo. Get. Sales. Status(ytd_sales) As Sales_Status FROM title. You must remember to prefix the function call with the. DBO if an owner is not specified when. This is, of course, just a single example, and an oversimplification of the. Migrating the logic of your functions to T- SQL is not trivial, but it. If you take the time to migrate your. It's nearly always. SQL Server to process the data, than to cobble it. Conclusion. Whew! That was a lot and honestly, there are still more. Access. queries to T- SQL. I hope that the examples above are sufficient to get you started. Writing code in SQL Server can be fun, once you get the hang of it. See All Articles by Columnist. Danny J. Lesandrini. Excel VBA | Microsoft Excel VBAThis archive is from the original message board at www. Mr. Excel. com. All contents ©1. Mr. Excel. com. Visit our online store to buy searchable CD's with thousands of VBA and Excel answers. Microsoft Excel is a registered trademark of the Microsoft Corporation. Mr. Excel is a registered trademark of Tickling Keys, Inc.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |