Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Angry Unanswered: MS Access - Azure SQL ODBC Connection

    This is driving me NUTS.

    I have a front end MS Access database connected to SQL Azure backend. A SQL Server Login window comes up in order to make the connection, but the Login ID and Password do not contain the proper information.

    I am using a code in VBA in order to automatically fill in the Login ID and Password so the users will not need to do this. Because it is Azure, the Login is quite complex. Using the connection string info PROVIDED BY AZURE, I have created a code as follows:


    Dim dbs As DAO.Database
    Dim tdf As DAO.Database

    Set dbs = CurrentDb
    Set tdf = dbs.TablesDefs("mytablename")

    tdf.Connect = "ODBC;Driver={SQL Server Native Client 10.0};Server=incrediblylongname.database.windows.n et;Database=myAzuredatabasename;Uid=incrediblylong userid;Pwd=mypassword;Encrypt=yes;"

    tdf.RefreshLink


    ALWAYS, without fail, I get 3146 ODBC call failed. HOWEVER, when the window comes up from me double-clicking on the linked table, and I type in the credentials manually (in the exact same login prompt) it connects just fine.

    Why does it work manually, but not from my VBA?

    I have asked our technical consultant and even Azure and have not come up with an answer.

    Please help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not used to work with Azure, but as far as I know, the "Server=" part of the connection string should be: "Server=tcp:[serverName].database.windows.net;"

    Is the "incrediblylongname" in your connection string begins with "tcp:"?
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    Thanks for your reply!

    To answer your question, the "incrediblylongname" does not include "tcp:".

    "Tcp:"also does not show up in the SQL Login prompt that shows up when I double-click one of the linked tables.

    I have attached a screenshot of the SQL Login as it appears when I double-click the Azure linked tables. Click image for larger version. 

Name:	SQL Login.jpg 
Views:	3 
Size:	21.8 KB 
ID:	13057

    It simply shows the servername.database.windows.net, no tcp: in front. I go from there and type in the Login ID and Password (manually) and it connects just fine without requiring the term tcp: showing up in the Server name.

    In fact, if I DO use "tcp:" in front of the server name.database.windows.net, it does not connect. It simply displays the SQL Logon prompt again WITHOUT the tcp: in front of it in the Server.

    When I put the "tcp:" in my VBA code, it shows "tcp:" in the Server location on the Login prompt. Since I know this does not work manually, it makes no sense to use it programatically. Although I am grateful for your interest and reply.

    If you have any other suggestions, I would truly appreciate them. This is something that I need to fix!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by melinda_b View Post
    ...Since I know this does not work manually, it makes no sense to use it programatically.
    When I want to create an attached table linked to a table in a SQL Server database, I use a procedure like this:
    Code:
    Sub AttachTable()
    
        Const c_ConnectString As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales_3;Trusted_Connection=Yes;"
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef
        With tdf
            .Name = "NewAttachedTable"
            .SourceTableName = "CF_Data"
            .Connect = c_ConnectString
        End With
        dbs.TableDefs.Append tdf
        dbs.TableDefs.Refresh
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    After executing that procedure (which works fine and creates an attached table into the current database), if I open the immediate window and type this:
    Code:
    ? CurrentDb.TableDefs("NewAttachedTable").Connect
    The answer is:
    ODBC;DRIVER=SQL Server;SERVER=SOLIMAN;APP=Microsoft Office 2003;WSID=SOLIMAN;DATABASE=Sales_3;Trusted_Connect ion=Yes
    Which is not precisely identical to the connect string I supplied when creating the attached table ("NewAttachedTable").

    You cannot assert that, because you see or use a particular connection string interactively, the same connection string will work programatically all the same. If the ODBC dialog box pops up when you run the code that tries to open a connection with the server, it means that something is missing or incorrect in the connection string you use.

    The "tcp:" parameter was just a wild guess, although I know that it is present is the canonical form of a string connection for Azure. Unfortunately, it does not work or is not enough to solve your problem. You should try to find a site (or a book) that explains in details how to compose a connection string for that particular platform, or you can hope that someone, here or in another forum, having more experience with Azure than I have (that should not be hard: my own experience with it is close to nil ) will be able to help you.

    Sorry for not being able to provide more help.
    Have a nice day!

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    So, if I understand you correctly, you are saying if my connection string is correct, the SQL Logon should not popup at all, but the connection should simply be made? Therefore, tcp: may truly be necessary?

    (And you were a help. Thank you!)

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can find more in-depth information about connecting an Access database to Azure at: SQL Azure - How To - .NET - Develop
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •