ODBC connection to SQL Server 2008 R2

On ODBC  using SQL Native Client.
Many possible problems. But consider trying with Server Firewall off. If this is successful then consider the following changes on firewall which have worked for me.

Firewall settings on server are:


  • On the Start menu, click Run, type firewall.cpl and then click OK.
  • In the Windows Firewall dialog box, click the Exceptions tab, and then click Add Port.
  • In the Add a Port dialog box, in the Name text box, type SQL Server <instance name>.
  • In the Port number text box, type the port number of the instance of the Database Engine, such as 1433 for the default instance.
  • Verify that TCP is selected, and then click OK.
  • To open the port to expose the SQL Server Browser service, click Add Port, type SQL Server Browser in the Name text box, type 1434 in the Port Number text box, select UDP, and then click OK.
    To allow named pipes access through the firewall, you must also enable File and Printer Sharing through the firewall.
  • —————

    Also Firewall on server must add program SQLServer.exe which is at <program files><Microsoft SQL Server><MSSQL10_50.Instance Name\MSSQL\Binn\sqlservr.exe


    Prefer not using older ODBC SQL server, but if do then on client configuration dynamically chosen ports is relevant

    Email tips and tricks



    Delayed send possible. Create message then go to options. Send only after a date time.

    Recall email is possible.

    Outlook Winword using resource and not closing in task manager

    Outlook > Tools > Options > Mail Format > ensure that "Use Microsoft Word 2003 to edit e-mail messages" both boxes are unticked

    00/01/1900 in MS Excel

    00/01/1900 is zero i.e. 0 formatted as a date ‘dd/mm/yyyy’
    Custom Formats in MS Excel accept multiple arguments, so if you want a 0 but formatted as a blank then use a custom format
    that’s 2 x ;; on the end, indicating put nothing if you get a zero

    MS Access Fomatting when linked to SQL Server 2008 Dates

    If linking to the new SQL Server date types, which are better in SQL Server and for the future, then MS Access 2003 treats them like strings.
    To resolve:
    • Create a view in SQL Server 2008 and use CONVERT(datetime, MyDate) with optional ,105 on end.
    • Link to the view in MSAccess. Possibly renaming the view as the original linked table name, so that it will still work throughout existing Access design
    • You may need to set unique keys during linking process. This helps MS Access to know what are unique keys so that it will be able to update records if required.
    • Voila. Fixed


    MS Access 2007 Create MDE for use with MS Access 2003 or earlier

    If starting with MS Access 2007 then making an MDE it is not possible to open this MDE from earlier versions.
    You may think that saving the database in MS Access 2002-2003, then making the MDE would work. However it will not.

    A solution:

    • Save as MS Access 2002-2003. Perhaps with a different name say MyApp2002.mdb
    • Open the new 2002-2003 database in MS Access 2002 or 2003 on another machine.
    • Make the MDE file from this machine as MyApp.mde
    • Voila !! Should now work

    Another more complicated solution:

    • Distribute the application from MS Access 2007
    • Use MS Access 2007 run-time

    Steve 13-Aug-2009

    You may receive write conflict errors when you update records of a linked SQL Server table in Access 2000

    Add a timestamp. Even if a view include this column.

    Office save/backup my settings

    Save My Settings does not exist in Office 2007.
    However see below copied from link:
    1. The Quick Access Toolbars for Excel, Word, and PowerPoint are saved in
    separate ".QAT" files (stands for Quick Access Toolbar), in the following
    directory in Windows XP:

    C:\Documents and Settings\(User Name)\Local Settings\Application

    In that folder look for:    Excel.qat, PowerPoint.qat, Word.qat, etc.
    So for example, if you want to transfer your Excel toolbar settings to
    another computer, or after you reinstall Office 2007, just copy your custom
    Excel.qat to that same folder, replacing the original.

    2. All other settings (options menu) are stored in the registry here:


    You can just export the whole "12.0" registry tree to a .REG file, and store
    it somewhere. When you need to use it (after reinstall or on a different
    computer), just double click on that REG file to update the registry.  Before
    you do that, I suggest that you backup your registry just to be safe, if
    something goes south.

    Good luck