|
||||||
|
|
||||||
|
Addendum 1Lesson 14: DateTime, Money for Max CompatibilityIn this Addendum, we will update the recommended SQL Server data types for Access applications, changing DateTime2 to DateTime and decimal to money for broader compatibility with VBA, ODBC, and older Access databases. I will show you how to alter existing columns, relink tables in Access, and discuss precision, compatibility, backups, and when DateTime2 or decimal may still be appropriate. Navigation
KeywordsSQL Server for Access, SQL Server DateTime vs DateTime2, SQL Server money vs decimal, VBA DLookup type mismatch, Access SQL Server linked tables, ALTER TABLE ALTER COLUMN, SQL Server data type compatibility, Access Linked Table Manager, DateTime2 VBA comp
More InformationTranscriptHey folks, Richard here. Before we wrap up the SQL Server for Access Users Level 1, I want to give you a quick update on a couple of the data types that we've been using so far in this course. Now, if you've been following along, you know that earlier in this level, I recommended using DateTime2 with a precision of 3 for our date and time fields and decimal 19.4 for our currency values. Well, we're going to make a small change once again to those recommendations moving forward. We're going to use the classic DateTime data type for dates and times and money for currency. And I want to take a minute briefly to explain why. It's been about eight months since I released Level 1. And during that time, I've been working on the rest of this course, building more examples, testing different configurations, and working with these data types in my own applications. I've also received a ton of great feedback from you guys: comments on the videos, emails from students, and questions from people who are trying to connect SQL Server to databases they've already been using for years. And one of the things that has become very clear is that not everybody is working with the same setup. Some of you are using the latest versions of Access and SQL Server. That's great. Others are still using Access 2010 or even older databases that have been running perfectly well for years. And a lot of people have said they really have no plans to upgrade because what they've got now is bulletproof. Some of you have applications written in VBA, some are connecting through ODBC, and others, like me, are still using classic ASP for certain projects, like my website. And that's perfectly fine. One of the reasons we're learning SQL Server in the first place is so we can connect it to all these different applications. But what I've discovered from both your feedback and from my own testing is that some of these newer data types don't always behave the way you'd expect when you're working with older code or different connection methods. Here, let me show you just one example. Here's the database that we built in class. We've got our credit limit here as a decimal 19.4. And we've got our customer since as a DateTime2.3, which is what I recommended so far. Now, if I switch over to my Access database, I've got that table connected right here, CustomerT. No problem. It's got customer since, credit limit, everything's working fine. You can edit these values, no big deal. Now, for those of you who know VBA code, I'm going to come into here and do my Hello World button. And I'm going to Dim D As Date, and I'm going to set D equal to DLookup customer since from the CustomerT. Just pulling any customer since value from CustomerT, and then message box it, and look what happens. Ready? Type mismatch. Why is that? Well, in this particular setup, DateTime2 values come back in a form that VBA can't directly assign to a Date variable. And yes, I know there are ways we can work around this. We can look at how the value is being returned, convert it where appropriate, make changes to how we're handling these newer data types. There are all kinds of workarounds. But that's additional complexity. And for the kinds of everyday business databases that we're going to be building in this course, I don't think we need to introduce all that extra work unless there's a really good reason for it. So, when you use the traditional SQL Server DateTime field with the same code, it works just fine. Here, let me show you. I'm going to close this database. Now we're going to change him to a traditional DateTime value. If you try right-clicking and modifying it here and using the visual designer, SSMS might not let you do it. So, we're just going to do a simple query. And this will be ALTER, ALTER, not altered, ALTER TABLE CustomerT. And we're going to ALTER COLUMN customer since. We're going to set it to a DateTime and make sure you put NULL on the end so it allows NULL values. And then execute, and then it's completed successfully. If I come over here now and refresh, F5, you can see now it's a DateTime value instead of DateTime2. And you shouldn't lose any precision because we don't have any fractional stuff in there. But now let's go back to Access. Now we're going to need to drop this table over here and relink to it because it's got the old specification. Anytime you make a change to your table, you have to relink it. And yes, later on in the course, I'm going to show you how to do that with some VBA code. You can just click a button and it'll relink it for you. But I'm going to relink it real quick. All right, External Data, New Data Source, From Database, SQL Server, Link. There's my Kirk server. Hit OK, pick the CustomerT, hit OK. And I'm going to rename this real quick. Rename, where there you are. Let's rename it to CustomerT. And now, when I click the button, there's my date. See, now that same code works just fine. And that's what I think we're going to go for for the rest of this course. We're going to aim for compatibility with as many setups as you guys all have out there because I got lots of emails, and people say my code doesn't work. My DLookups don't work. My recordsets aren't working. And that's why. So, we're not going to use the modern recommendation. We're going to use the backward-compatible recommendation moving forward. Now, if you've already built the table in Level 1 so far, don't panic. You don't have to stop everything and rebuild your whole database. If what you've got is working, that's fine. I'm going to make the same change to the credit limit field. We're going to just switch it over to money. This is just as simple as taking what we got here, and we're going to change credit limit to money. Money. And that's it. That's all you got to do. And, of course, relink the table again, which I'll do off camera. But not only have a lot of you complained that your VBA code wasn't working, but I've run into some issues myself. My website still runs ASP, and the decimal type is perfectly valid, but I've found that money gives me more straightforward currency handling. Now, does this mean that DateTime2 and decimal are bad data types? Absolutely not. In fact, DateTime2 gives you some advantages, including a wider range of dates and greater precision. And decimal is excellent when you need specific numeric precision for calculations. There's nothing inherently wrong with either one. But here's the thing: just because something is newer or technically offers more features, that doesn't necessarily mean it's the best choice for every application. So, after working with these types for the past several months and getting all your feedback, I've decided that for the rest of this course, I'd rather teach you the options that give us a straightforward starting point across the widest range of applications that we're likely to encounter. I'm going to cover all of this in a lot more detail in Beginner Level 2. I'll show you the different data types side by side, demonstrate the compatibility issues that I've encountered, and I'll explain why you might still want to use DateTime2 or the decimal fields. And I also want to thank everybody who's taken the time to send me feedback, whether it's through email or comments or questions on the website. I've always said that I learn just as much from teaching these courses as I hope you learn from watching them. And when I discover a better approach, especially one that's going to make things easier for more of my students, I'm definitely going to share it with you. And that's how we make these courses better. So, just keep those two changes in mind as we move forward: DateTime and money. In fact, I got to change the cheat sheet now. Credit limit is going to be money, and this is just going to be DateTime, and get rid of the two-three. And again, I like money and DateTime here. And we're going to get rid of "avoid money" because we're going to use money now. And we're going to put this up here. There. Now the cheat sheet is updated. You still want to avoid using float for currency, and we talked about why earlier. And this should say "most compatible with most versions of Access," but I ain't got room for that there, so I'm putting that there. Now, before we wrap things up, I want to clarify a couple of things. First, when we changed DateTime2 to regular DateTime, we didn't notice any difference in our existing data because we're not using fractional seconds in those records. But DateTime does have less precision than DateTime2. So, if you got timestamps with fractional seconds, some rounding can occur. But again, in my experience, most people that are building regular business-type databases don't have to worry about fractional seconds. If you do, then that's on you. You got to figure out, we'll talk about that in a future class. If you track order dates and the times employees checked in, if you're worried about fractions of a second, I don't want to work for you. But that's just something to be aware of before changing an existing database. Always, always back up first before you run any commands that I tell you to run in class on an actual production database. Second, I was just thinking, you saw me delete and recreate that linked table in Access. You don't necessarily have to do that every time you change your SQL Server table. You can go into the Linked Table Manager to refresh the connection and update the table definition. I just recreate the link to keep things simple. And like I said, in my databases, I got a button that I click that just does that for me. So, it's easiest just to drop it and relink to it. It's just habit. And finally, I do want to emphasize that switching to DateTime and money isn't some magic fix for every compatibility problem. I have received emails from some students that do have trouble using DLookups and recordsets and other VBA operations. And while these newer data types can certainly contribute to some of those issues, they're not necessarily the cause of every problem. But the important thing is that we're choosing a practical starting point for the kinds of Access and SQL Server applications that we're going to be building in this course. And I do want to reiterate one more thing. If you've already got an application that's working perfectly well with DateTime2 and decimal, there's no reason to rush out and change everything. If you've been working with it for the past eight months since you watched the rest of Level 1 here, great, keep it. I'm just making this change to address issues that have come up for a lot of people that have contacted me. And as always, back up your data before making any changes and test your application thoroughly afterwards. All right, so there you go. There's your addendum. If you're curious about all the technical details, I'll see you in Level 2. And as always, live long and prosper, my friends. I'll see you next time. IntroIn this Addendum, we will update the recommended SQL Server data types for Access applications, changing DateTime2 to DateTime and decimal to money for broader compatibility with VBA, ODBC, and older Access databases. I will show you how to alter existing columns, relink tables in Access, and discuss precision, compatibility, backups, and when DateTime2 or decimal may still be appropriate. QuizQ1. What data types does the instructor recommend as the most compatible default choices for this course? A. DateTime and money B. DateTime2 and decimal C. varchar and float D. date and currency Q2. Why did the instructor change the recommendation from DateTime2 to DateTime? A. DateTime2 cannot store dates before the year 2000 B. DateTime is more compatible with a wider range of Access, VBA, ODBC, and older application setups C. DateTime2 cannot be used in SQL Server tables D. DateTime takes more storage space than DateTime2 Q3. What problem occurred when VBA tried to assign a DateTime2 value returned by DLookup to a Date variable? A. Overflow error B. Object required error C. Type mismatch error D. Duplicate key error Q4. Why might DateTime work better than DateTime2 in some older Access and VBA applications? A. DateTime values are more likely to be returned in a form VBA can directly assign to a Date variable B. DateTime automatically converts all dates to text C. DateTime prevents NULL values from being stored D. DateTime does not require an ODBC connection Q5. What SQL statement can be used to change an existing CustomerSince column to DateTime while allowing NULL values? A. UPDATE CustomerT SET CustomerSince = DateTime NULL B. ALTER TABLE CustomerT ALTER COLUMN CustomerSince DateTime NULL C. CHANGE TABLE CustomerT CustomerSince TO DateTime D. CONVERT CustomerT.CustomerSince INTO DateTime Q6. After changing a SQL Server column data type, what may need to be done in Access? A. Compact the SQL Server database B. Rebuild all Access forms and reports C. Refresh or recreate the linked table definition D. Delete all records from the linked table Q7. Which Access feature can refresh a linked table connection and update its table definition? A. Linked Table Manager B. Visual Basic Editor C. Relationship Window D. Query Builder Q8. What is an important concern when changing DateTime2 values to DateTime? A. DateTime cannot store any time values B. DateTime may round fractional seconds because it has less precision C. DateTime changes all dates into UTC automatically D. DateTime removes all NULL values from the column Q9. What is the instructor's recommendation for an application that is already working properly with DateTime2 and decimal? A. Immediately convert every table to DateTime and money B. Convert only the primary key fields C. Leave it alone unless there is a reason to change it D. Replace SQL Server with local Access tables Q10. Which data type should still generally be avoided for currency values? A. money B. decimal C. float D. integer Q11. Why does the instructor recommend money for currency values in this course? A. It provides a straightforward starting point with better compatibility for common Access and older application setups B. It can store unlimited decimal places C. It is the only numeric type supported by SQL Server D. It prevents users from entering negative values Q12. What should always be done before changing data types in a production database? A. Rename every table B. Back up the database and test the application afterward C. Delete all linked tables permanently D. Remove all NULL values from every field Answers: 1-A; 2-B; 3-C; 4-A; 5-B; 6-C; 7-A; 8-B; 9-C; 10-C; 11-A; 12-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from SQL Server for Access Users Learning Zone is an important addendum to Level 1. I am updating my recommendations for two SQL Server data types that we have been using throughout the course: date and time fields, and currency fields. Earlier in this course, I recommended using DateTime2 with a precision of 3 for date and time values, and decimal(19,4) for currency values. After several months of additional testing, building more course examples, working with these data types in my own applications, and receiving feedback from students, I am changing those recommendations. Moving forward, I will generally use the traditional SQL Server DateTime data type for dates and times, and the money data type for currency. This is not because DateTime2 and decimal are bad data types. They are not. Both have important benefits, and there are situations where they are the better choice. However, for the types of Access and SQL Server applications we are building in this course, DateTime and money tend to provide better compatibility with a wider variety of Access versions, VBA code, ODBC connections, legacy databases, and older applications. Not everyone is working with the same environment. Some students are using the newest versions of Access and SQL Server, while others are using Access 2010 or older versions that have been running reliably for years. Some people are using VBA in Access. Others are connecting through ODBC, classic ASP, or other technologies. One major reason to learn SQL Server is so that it can work with all of these different kinds of applications. During my testing, and based on student feedback, I found that newer SQL Server data types do not always behave as expected with older code or connection methods. For example, suppose an Access database contains a linked SQL Server table with a DateTime2 field. You may be able to open the linked table, view the records, and edit the values without any obvious problems. However, if VBA uses Dlookup to retrieve a DateTime2 value and assign it directly to a VBA Date variable, Access may return a type mismatch error. There are ways to work around that problem. You can examine the returned value, convert it when appropriate, or adjust the way your VBA code handles the data. However, all of that adds complexity. For the everyday business databases covered in this course, I do not think we need that extra complexity unless there is a specific reason to use the newer data type. When the same SQL Server field uses the classic DateTime type, the VBA Dlookup example works properly. The returned value can be assigned to a VBA Date variable without causing a type mismatch. If you need to change an existing SQL Server column from DateTime2 to DateTime, you can use an ALTER TABLE statement to alter the column definition. If the column currently allows Null values, make sure the revised definition also allows Null values. SQL Server Management Studio may not always let you make this particular change through the table designer, so using a query is often the simpler option. After modifying the SQL Server table structure, Access may need to refresh or recreate the linked table definition. One simple method is to delete the linked table in Access and then link it to the SQL Server table again. This ensures that Access recognizes the new field specification. You do not always have to delete and recreate the link. You can also use the Linked Table Manager in Access to refresh the link and update the table definition. I often recreate the link because it is simple and reliable, and in my own databases I have VBA procedures that can handle relinking automatically. I am making a similar change to the credit limit field. Instead of decimal(19,4), I will now generally use the money data type. Decimal is a perfectly valid and highly useful SQL Server type. It is especially valuable when you need precise numeric calculations with a specific number of decimal places. However, I have found that the money type provides more straightforward currency handling in many Access, VBA, and classic ASP situations. The money type is not appropriate for every possible financial or scientific application. If you are performing calculations that require highly controlled precision, specialized rounding rules, or more decimal places than money supports, decimal may still be the right choice. But for ordinary business applications involving prices, balances, credit limits, invoices, and similar values, money is often a practical and compatible starting point. You should still avoid using float for currency values. Float is an approximate numeric type, which can lead to unexpected rounding behavior. Currency values should generally be stored using a data type designed for exact fixed-point values, such as money or decimal. If you have already completed the earlier Level 1 lessons using DateTime2 and decimal, there is no need to panic or rebuild your entire database. If your existing application is working properly, you can leave it alone. These changes are recommendations for future lessons and new projects, especially for students who have been encountering compatibility problems with VBA, Dlookup, recordsets, or other Access operations. Changing from DateTime2 to DateTime can affect precision. DateTime2 supports a wider date range and greater fractional-second precision than DateTime. If your application stores timestamps with fractional seconds, converting to DateTime can result in rounding. In many ordinary business databases, fractional seconds are not important. For example, most applications that track order dates, appointments, customer records, employee check-ins, or invoices do not need to distinguish events occurring within fractions of a second. However, if fractional-second precision matters in your database, you should carefully evaluate whether DateTime is appropriate before making the change. As always, back up your database before changing a production table. Test the SQL Server changes, refresh any Access links as needed, and thoroughly test your forms, queries, VBA code, reports, and external applications afterward. Switching from DateTime2 and decimal to DateTime and money is not a magic solution for every possible Access and SQL Server compatibility issue. Some Dlookup, recordset, and VBA problems can have other causes. However, using the more traditional SQL Server types gives us a practical baseline that works well across the widest range of Access versions, connection methods, and older applications. In Level 2, I will cover SQL Server data types in more detail. I will compare DateTime and DateTime2 side by side, explain the compatibility considerations, and discuss when decimal is preferable to money. I will also explain the reasons you may still choose the newer data types for specific situations. For now, remember the updated recommendations for this course: use DateTime for ordinary date and time fields, use money for typical currency fields, and avoid float for currency. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListChoosing DateTime for Access compatibility Choosing money for Access currency fields DateTime2 type mismatch in VBA DLookup Altering SQL Server column data types Refreshing Access linked tables after schema changes DateTime precision and fractional-second rounding Backing up before changing SQL Server tables Primary TopicsSQL Server date/time types, SQL Server currency types, Access linked tables, VBA compatibility, backward compatibility, schema changes Secondary TopicsDLookup behavior, recordsets, ODBC connections, classic ASP, Linked Table Manager, data backups, fractional-second precision |
||||||||||||||||||||||
|
| |||
| Keywords: SQL Server for Access, SQL Server DateTime vs DateTime2, SQL Server money vs decimal, VBA DLookup type mismatch, Access SQL Server linked tables, ALTER TABLE ALTER COLUMN, SQL Server data type compatibility, Access Linked Table Manager, DateTime2 VBA comp PermaLink Changing SQL Server to DateTime & Money for Maximum Compatibility with Microsoft Access |
