locked
SSMA for Access RRS feed

  • Question

  • Does 'SSMA for Access'  work with SQL Server Express 2017? I am unable to connect to my SQL Server in SSMA. Perhaps I am missing something.

    Ron

    Wednesday, August 8, 2018 3:01 PM

Answers

  • You could try + ensure that the SQL Server Browser service is running (this service is useally required to display and broadcast that SQL server is available).

    You can/should be able to launch the SQL server configuration manager (tap the windows key, and search for Sql server configuration).

    Sometimes, the server "service" that displays the instances of SQL server is not running.

    If you have a "default" instance of SQL server, then before you try above, simply type in a period and \SQLEXPRESS

    So try typing above into the server name/box - see if that it works:

    eg:

    .\SQLEXPRESS

    (I assume you using windows authentication for the logon.

    Try the above - if not then try launching the sql config manager as per above. And start the "browser" service. If that don't work, then in the sql config manger check out (click on) the Protocols for SQLEXPRESS and ensure that TCP/IP and shared memory is enabled.

    Regards

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by AmateurRon Friday, August 10, 2018 2:57 PM
    Thursday, August 9, 2018 5:23 PM

All replies

  • Hello Ron, What's the issue you got? I could easily to connect to my SQL Server Express 2017 database. Did you get any error message? Best Regards, Terry

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 9, 2018 6:03 AM
  • Thanks for the reply.

    The issue: I have my Access 2003 database in SSMA for Access Metadata Explorer. When I try to connect to my SQL Server Express, no server name shows up in the dropdown. This is true whether my server is running or not.


    Ron

    Thursday, August 9, 2018 4:30 PM
  • You could try + ensure that the SQL Server Browser service is running (this service is useally required to display and broadcast that SQL server is available).

    You can/should be able to launch the SQL server configuration manager (tap the windows key, and search for Sql server configuration).

    Sometimes, the server "service" that displays the instances of SQL server is not running.

    If you have a "default" instance of SQL server, then before you try above, simply type in a period and \SQLEXPRESS

    So try typing above into the server name/box - see if that it works:

    eg:

    .\SQLEXPRESS

    (I assume you using windows authentication for the logon.

    Try the above - if not then try launching the sql config manager as per above. And start the "browser" service. If that don't work, then in the sql config manger check out (click on) the Protocols for SQLEXPRESS and ensure that TCP/IP and shared memory is enabled.

    Regards

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by AmateurRon Friday, August 10, 2018 2:57 PM
    Thursday, August 9, 2018 5:23 PM
  • SQL Server Express is running

    SQL Server Browser service is running

    Protocols TCP/IP and shared memory are running.

    I still don't get any SQL Server in the dropdown when trying to connect in SSMA for Access.

    No error message - just no Server available.


    Ron

    Thursday, August 9, 2018 6:52 PM
  • As noted, you might not get the dropdown - so just type  in the server name. (as noted, you can use "." (dot) for your computer name (which in this case is the server name), you then add the "instance" of the sql server (useally SQLEXPRESS).

    I would fire up  SSMS (sql management  studio, and see if that can connect also).

    So don't worry about the dropdown - just force the issue and type in the name.

    If SSMS can't connect, then I doubt SSMA going to connect. As noted, you  can also check the protocols that are turned on.

    So type in your computer name (or a "dot"), and then the instance of the sql server you created - see if that works. So for now, don't worry if the drop down don't work - I always found you can type in the server name. I would ALSO VERY much suggest you use SSMS to try and connect first.

    Regards,
    Albert D. Kallal (Access MVP, 2003-2017)
    Edmonton, Alberta Canada

    Thursday, August 9, 2018 7:13 PM
  • SSMS connects OK.

    The documentation for Migration Assistant says to connect to SQL Express with pipe such as \\.\pipe\sql\query. That does not make sense to me.     Ron


    Ron

    Thursday, August 9, 2018 8:42 PM
  • The same you used for SSMS should work for SSMA.

    I assume you downloaded SSMA 7.8 for Access? Given that SSMS connects, I can't see why SSMA would not connect. Something is "strange" here, and we (and you) are clearly missing some detail that don't make sense here.

    I assume that you for testing launch SSMA, and cancel the wizard, and then simply choose "new project" - just hit ok for the default project name. At that point, then just click on connect to SQL server - you get very much the same dialog you get with SSMS.  My only "wild" guess left is that your opening some existing project, and it has some "messy" connection stuff in it. Try a blank new project (not use wizard) and simply hit connection to sql server.

    I suppose you could right click on a shortcut and run as "administer", but really, we all missing something simple here - I sure we all be "kicking" ourselves when we find the answer. I am at a loss here for additional suggestions. About the only suggest I can give is to ensure that you use the x32 bit version of SSMA, not the x64 bit version.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Thursday, August 9, 2018 9:57 PM
  • I am at a loss for a solution. I uninstalled/reinstalled SSMA for Access. I tried to connect with a new project without databases. I right clicked and ran as administrator. The results are consistent. The "Connect to SQL Server" dialog in SSMA does not allow a connection.

    Ron


    Ron

    Thursday, August 9, 2018 10:48 PM
  • Hello AmateurRon,

    >>The "Connect to SQL Server" dialog in SSMA does not allow a connection.

    What do you mean does not allow a connection? 

    If you mean that the drop down list does not show your database server name, just as Alert said, enter the serve name manually.

    Here is the simple demonstration.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 10, 2018 1:32 AM
  • This is embarrassing. I was neglecting to also input the destination database in the server connect dialog! Once I entered both the server name and the destination database, everything worked just fine. I was able to migrate the Access database to SQL Server Express. There is a reason I am called AmateurRon.

    Thanks for the much needed help.

    I marked Albert's first reply as the answer, since that was the actual solution.

    Ron


    Ron

    Friday, August 10, 2018 3:06 PM