Go to Top

Tips for designing SQL Server tables for Microsoft Access

We LOVE using SQL Server with Access! I frequently mention that SQL Server Express was the best thing to happen for Access, the two combined have the best 1-2 punch in the industry.

I encourage you to explore using both technologies, or better yet, hire us to do the legwork. Here are some table design tips that will make it easy for you to integrate Access with SQL Server:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

  • When converting Yes/No fields to SQL, use the Bit data type and set the default value to zero, (0). This means the default value will be false unless you set it to true in Access. Keep that in mind as you use the fields in Access.
  • Create Indexes for foreign keys. Unlike Access, you must create your own indexes for primary keys coming from other tables.
  • Always add a TimeStamp field to your tables. Otherwise you’ll start running into issues with multiple users and large amount of records. The timestamp field is a special field in SQL Server and it’s used by Access to determine when the record was last updated. Don’t use the field for anything in your forms, reports, code or queries, it will not work.
  • Use Money field type when you need a field with decimal places and the precision needed is 4 or less decimal places.

I hope these tips can help you with your database, and if you ever need to host your database with us, please click here for more information: https://accessexperts.com/services/sql-server-hosting-access/

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

3 Responses to "Tips for designing SQL Server tables for Microsoft Access"

    • Juan Soto
      July 16, 2012 - 9:39 pm Reply

      The article on TechNet is not correct. Omitting the timestamp field will cause problems with editing data in Access.

    • Ben Clothier
      July 20, 2012 - 8:20 pm Reply

      Tom, in addition the author also get basic fact wrong – In data type he lists “timestamp” (I prefer to call them rowversion) as equivalent to Access’ Date/Time when in fact the rowversion/timestamp data type are binary data type that does not even record date/time at all.

      Refer to this documentation @ http://msdn.microsoft.com/en-us/library/bb188204(v=sql.90).aspx where it explains that if a rowversion/timestamp is included in the query, Access will use it to reselect the row after an update and this is efficient in avoiding Write Conflict errors and/or #Deleted scenarios that may happen when you have different results (e.g. floating precision number data types, trigger modifying columns) than what Access originally submitted.

      Hope that additional details help.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.