none
Linking Access Front End to SQL Express backend RRS feed

  • Question

  • This is new technology to me and I have not been able to find guidance specific to my issue

    I want to move back end Access 2016 tables to SQL Express

    I have installed SQL Express 2017 and managed to import Access tables using the Import and Export Data Wizard but I can't update the front end databases to link to the SQL tables

    The back end databases I imported from now contain links to the SQL tables but I do not see a way to link to the SQL tables from the front end

    Friday, October 4, 2019 12:04 AM

Answers

  • Ok, let’s take this step by step.

    Yes, the “trick” suggestion was just that – a trick suggest to get you up and running. In fact, now that you have the tables linked, then you can use the built in access linked table manager (say to point to a different database).

    >it does not show any Access databases

    Ok, but when you launch the SSMS (sql manager studio - can you see the database? -- do not skip answering this question!!!!)

    It sounds like you skipped (did not read) the options you get. During the link process, you ARE given a chance to choose WHICH database you wish to connect to. (You likely skipped that step – the default database is “master” which of course will not show your tables in the “given” database.

    Remember, on sql server, you can have “many” databases. I sure you fire up Sql manger studio and you can see/view the databases and thus drill down to the tables. Keep in mind that just like when working with a access back end, your table design changes etc. now occur in the back end – but your back end is now managed with SSMS.

    So, the link process is quite straight forward, but it is “very” easy to skip or miss some of the options (especially the one that lets you select the database you want to link to).

    Might as well post some screen shots here – this should help you, and others in this fine community that want a step by step.

    A few things:

    Let access launch the ODBC panel. If you use the windows control panel, but I HIGH recommend you don’t (at least until you gain experience).

    The reason for letting access launch the ODBC panel is 9 out of 10 times you want to launch + use the x32 bit ODBC manger. By letting Access launch this config panel, you always can be sure you get and are using the correct config panel. If you use the windows control panel, then by DEFAULT you get the x64 ODBC manager, which is VERY likely NOT the one you need or want to use. By letting Access launch the ODBC panel, it will automatic ALWAYS choose the correct bit size (x32 or x64) automatic for you.

    As noted, do keep in mind that during this link process, the manager will append a dbo_ in front of your table names. (Which will break forms and code until such time you re-name the linked tables to what their name was before).

    Now if the data or tables you need/want from SQL server were never migrated from Access, then leaving the “dbo_” name as part of the table name is not a big deal. But if you add one new table, then when you link, the dbo_ will be part of the name, and you can in the access nav pane simply re-name the linked table.

    Ok, let’s just link in (again) one of your tables using the ODBC manger and access.

    My bets are you simply missed the part/option where you can select the database you want to use.

    Ok, from Access, we start here:

    and then:

    then:

    then:

    Now above is asking how to connect to SQL server. For now, because this is YOUR computer, then you can use "windows" auth. However, if you going to share the database on your network - just keep in mind that for the future, you WILL have to create a sql logon. But for now, lets walk before we can run and use the above.

    Now in the above, I have attempted to highlight the step you likely missed. So in above you can (will/must) select the database you want to connect to. You do NOT want to skip or miss the above step, else you will connect to master, and not your desired database.

    Ok, in above we are ALL THE WAY back to the first panel, but the name we typed in (a FILE dsn) is now ready for use. The name of this DSN should be already filled out, and thus we can just hit ok for above.

    As above shows, you should now see the list of your tables. Now, if you are using SQL logons, you must double, triple, qudipile (and MUCH MORE) check the box to save your password. We are NOT using passwords right now, but in the future, you will spend days, years, months of fustration if you skip that save password box. Write a note about this for the future. Make a mental note, make a cheet sheet, you MUST NOT forget about that save password box, or you spend a afternoon wasting your time.

    At this point simply click on the tables you want to link, and hit ok. You are done. The whole process takes less then one minute, and these "one time" table linking events as noted are quite simular to linking to a back end. To add a new table (that you just created in that sql database, you start the above, but now can select the DSN "file name" that you created above, and you not have to enter and wade through all of those panels.

    Good luck!

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by BVitter Saturday, October 5, 2019 11:34 PM
    Saturday, October 5, 2019 9:21 PM

All replies

  • First, go to Windows Control panel and click on Administrative Tools>Data Sources. Build an ODBC connection.

    On the Access Ribbon with no objects open, you should see a tab for External Data. Click the ODBC icon. Choose the Link selection and follow the wizard using your newly built ODBC connection.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Friday, October 4, 2019 9:28 PM
  • If you open the back end, and if the table links work, then you can open the front end (keep the back end open as a separate copy of access running).

    Now, delete  your table links in the front end, and then simply drag + drop the linked table  from the back end into the nav pane on the front end.

    Of course the above is a bit of a "trick". The normal approach is often to simply click on the odbc option in the ribbon (External data tab--> Import and link). From that ODBC in the import, you can then setup a linked table to the SQL server. However this approach will result in all the databases having dbo_TableName, and you have to re-name them to remove the dbo_ prefix if you want code/forms and the rest of the application to work as before. So the drag + drop of the table links from the back end will result in the table names not having the dbo_ prefix (which you don't want in your Access front end).

    The whole process of setting up linked tables is VERY similar to that of using the linked table manager to link to a back end, but now you are linking to SQL server.

    So the drag + drop trick might be worth a try, since you thus get to keep the correct table names on the client side of things. 

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Friday, October 4, 2019 10:08 PM
  • Dale Fye has made an interesting addin that should take care of this...check his Article at EE :https://www.experts-exchange.com/articles/32503/A-more-efficient-Linked-Table-Manager.html
    Saturday, October 5, 2019 8:11 AM
  • I am clearly missing something in this process

    I used the SQLExpress 2017 Import and Export Wizard to import Access tables into SQL

    The original Access database now contains a SQL table and a link but when I open an Access Front End to attempt to link to the SQL table (using the ODBC or From SQL Server) it does not show any Access databases, just the SQLMigration tables created during the Import process. I can't find any SQL tables to link to.

    Copying the link from the Access database I imported from works but there must be a better way to do this
    Saturday, October 5, 2019 5:13 PM
  • Ok, let’s take this step by step.

    Yes, the “trick” suggestion was just that – a trick suggest to get you up and running. In fact, now that you have the tables linked, then you can use the built in access linked table manager (say to point to a different database).

    >it does not show any Access databases

    Ok, but when you launch the SSMS (sql manager studio - can you see the database? -- do not skip answering this question!!!!)

    It sounds like you skipped (did not read) the options you get. During the link process, you ARE given a chance to choose WHICH database you wish to connect to. (You likely skipped that step – the default database is “master” which of course will not show your tables in the “given” database.

    Remember, on sql server, you can have “many” databases. I sure you fire up Sql manger studio and you can see/view the databases and thus drill down to the tables. Keep in mind that just like when working with a access back end, your table design changes etc. now occur in the back end – but your back end is now managed with SSMS.

    So, the link process is quite straight forward, but it is “very” easy to skip or miss some of the options (especially the one that lets you select the database you want to link to).

    Might as well post some screen shots here – this should help you, and others in this fine community that want a step by step.

    A few things:

    Let access launch the ODBC panel. If you use the windows control panel, but I HIGH recommend you don’t (at least until you gain experience).

    The reason for letting access launch the ODBC panel is 9 out of 10 times you want to launch + use the x32 bit ODBC manger. By letting Access launch this config panel, you always can be sure you get and are using the correct config panel. If you use the windows control panel, then by DEFAULT you get the x64 ODBC manager, which is VERY likely NOT the one you need or want to use. By letting Access launch the ODBC panel, it will automatic ALWAYS choose the correct bit size (x32 or x64) automatic for you.

    As noted, do keep in mind that during this link process, the manager will append a dbo_ in front of your table names. (Which will break forms and code until such time you re-name the linked tables to what their name was before).

    Now if the data or tables you need/want from SQL server were never migrated from Access, then leaving the “dbo_” name as part of the table name is not a big deal. But if you add one new table, then when you link, the dbo_ will be part of the name, and you can in the access nav pane simply re-name the linked table.

    Ok, let’s just link in (again) one of your tables using the ODBC manger and access.

    My bets are you simply missed the part/option where you can select the database you want to use.

    Ok, from Access, we start here:

    and then:

    then:

    then:

    Now above is asking how to connect to SQL server. For now, because this is YOUR computer, then you can use "windows" auth. However, if you going to share the database on your network - just keep in mind that for the future, you WILL have to create a sql logon. But for now, lets walk before we can run and use the above.

    Now in the above, I have attempted to highlight the step you likely missed. So in above you can (will/must) select the database you want to connect to. You do NOT want to skip or miss the above step, else you will connect to master, and not your desired database.

    Ok, in above we are ALL THE WAY back to the first panel, but the name we typed in (a FILE dsn) is now ready for use. The name of this DSN should be already filled out, and thus we can just hit ok for above.

    As above shows, you should now see the list of your tables. Now, if you are using SQL logons, you must double, triple, qudipile (and MUCH MORE) check the box to save your password. We are NOT using passwords right now, but in the future, you will spend days, years, months of fustration if you skip that save password box. Write a note about this for the future. Make a mental note, make a cheet sheet, you MUST NOT forget about that save password box, or you spend a afternoon wasting your time.

    At this point simply click on the tables you want to link, and hit ok. You are done. The whole process takes less then one minute, and these "one time" table linking events as noted are quite simular to linking to a back end. To add a new table (that you just created in that sql database, you start the above, but now can select the DSN "file name" that you created above, and you not have to enter and wade through all of those panels.

    Good luck!

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by BVitter Saturday, October 5, 2019 11:34 PM
    Saturday, October 5, 2019 9:21 PM
  • OK, I can import Access tables into a SQL database on the server

    But when I try to find the SQL database on a local PC I am not able to see the server based SQL server

    In SSMS I can only see a local SQL server, the network server tab does not show any network servers

    WHat do I have to do to use the network SQL server?

    Tuesday, October 29, 2019 11:56 PM
  • Well given the current info i would advise against any kind of migration.

    Its clear that you lack certain knowledge to tackle this initial task so it would be much better to just forget about it just for now.

    Google for tutorials "Access to MSSQL migration" ...read them...understand them and then you should be able to do the migration...but unless you are already qualified in MSSQL you won't get very far and the performance of your application will be terrible.

    If you just want to test how to migrate the tables from Access to MSSQL the easiest way should :Access to MSSQL

    Wednesday, October 30, 2019 6:59 AM
  • Well, when you say you can’t see sql server?

    Do you mean the instance you have running on the SAME pc? Or some instance running on another PC on the same network?

    In most cases, if SQL server been installed + setup on a server, then you will as a general rule see that server if you try to setup a connection from Access, or say launch SSMS on your local computer (it should allow you to connect to server based editions of SQL server). So the  drop down box should show all the SQL servers that are available (or at least ones that are broadcasting this fact).

    And I don’t recall a “local” vs “network” option in SSMS.

    That drop down box of servers is this one in SSMS:

    In above, I don’t see some local vs network option here?

    Certainly some connection dialog s for import or some wizards you launch from SSMS will often allow you to select local vs network, but as a general rule the basic connection dialog in SSMS looks as a above, and no real distinction is made between your local edition of SQL server (if you have) vs. that of the available servers running on your network.

    The instances of SQL server running on your server SHOULD show. If they don’t, then likely the install and setup was not done correctly, or perhaps the “server browser” option is not setup nor running on that server. (that browser service is what makes the sql instance discover able).

    As a general rule, the server browser has to be running. In fact with the “latest” edition of SSMS, I found that it does not even find local editions of SQL server express unless I also start + run what is called the “server” browser service. That is this one:

    In above, you as a general rule need BOTH of the above running. That is the sql server, and the note the second option “sql server browser”. That second service needs to be running for SSMS (and other programs) to “discover” the instance of SQL server.

    For some time, with a local edition of SQL server, you did NOT need to run the server browser service as I have above (you only required the first sql server/service to be running).

    If the browser service is not running, you can still often connect, but you not get the “convenience” of the server name popping up in the list of available SQL servers if you don’t’ run the sql browser service in addition to the SQL server service running as I have in the above list. (so you have to tpe in the name – and give the IP address a try in place of the server name – that often also works).

    So, not clear which/what/where/when network tab you are talking about, but the above first screen shot is where you should see your “list” of SQL servers that are discover able on your network.

    And that “list” should also include any local edition of SQL server you have running. I should VERY much note that before SSMS v.18, then you usually did not need nor require to run the sql browser service as I have in the 2<sup>nd</sup> screen shot.

    In most cases you could and would see your instances of SQL server (local) even without the browser service. However, SSMS v.18 does now seem to require the sql browser service to be running before SSMS can and will find the sql instance running on that particular computer box (be it local, or some server box).

    So, you not always see the instances of SQL server on your network, but you can still often connect to such servers, but often the IP name is required in place of the SERVER name and instance of SQL running. Keep in mind that if you use the IP address, you STILL MUST include the sql “instance”

    So, you might have

    SQLXYZ\SQLEXPRESS

    And if you use IP address, then you need:

    192.168.0.10\SQLEXPRESS

    To connect.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Thursday, October 31, 2019 4:30 AM
  • SQL Server and SQL Browser are running on the server (ECHO_Server, Windows 2012 R2)

    But I can't connect from a networked PC (Windows 10 1903, 64bit) when I use the specific server name or browse for network servers


    Thursday, October 31, 2019 3:18 PM
  • Hum, ok. Can anything else connect to these SQL servers? (or the server box on the network?).

    By default, express editions of SQL server don't allow remote connections. And by default, they don't allow TC/IP connections either. So, quite sure that TC/IP connections need to be enabled. And if those are new servers, then the firewall setting may well have to be changed. 

    So you have to enable remote access to sql server. 

    turn on tc/ip

    Browser service running.

    I "seem" to remember that during the express install it would offer to configure the firewall. But I would check if TC/IP is enabled, and the allow remote connections is enabled. I suppose if you are on a domain, then you might check if you can with standard windows file explore see and reach the server that way. (before you even try connecting). Can you ping the server?

    I certainly had to setup sql server a lot of times, but we really trending into network and support issues - not really Access ones. I would would suggest you poke around in some of the SQL groups, and see what suggestions they cook up.  Such groups are going to have far more knowledge then what you can expect in this group. 

    It I suppose could also be any type of virus or network protection system you run on the client computer. So ports (tc/ip) 1433, and UDP 1434 is a possible looking at on the server side (and possible network protection software on the client side).

    If I can't ping the server, or say even browse to files, or remote into the server? And I also ensure that remote use of SQL is turned on, TC/IP is turned on, sql browser service is running? Well, beyond this point, then its likely time to talk to the network and support people who maintain that network.  I know my way around networks quite well, and after going though the above "basics", then we moving out of my knowledge base rather quick. If the above "basics" don't work, then it time to talk to the server and network  people. 

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Friday, November 1, 2019 3:56 AM
  • Have you enabled TCP/IP & Named Pipes protocols...try both the name of the server and the IP

    And just for testing establish a telnet connection to see if its listening

    https://www.mssqltips.com/sqlservertip/4828/verify-connectivity-to-sql-server/

    Friday, November 1, 2019 9:30 AM
  • Thank you for all the help

    Unfortunately as a volunteer at a small non-profit, I am the network support and well as Access developer

    I did enable TCP/IP and Named Pipes and disabled the client firewall but I still can't ping the SQL server

    Saturday, November 2, 2019 1:47 PM
  • After you enable TCP/IP  for sql server, you do need  to restart sql server and very likly the browser service. As noted, from any computer, you need to be able to see and use folders, or at least ping the server (don't worry about SQL server just yet).

    So, the first goal/thing is to ensue you can see and use or at least ping the server.

    I would for testing of course disable the firewalls on both ends - just to remove this possible issue. But, regardless, don't  forget to re-start sql server service, and the browser anytime you re-configure SQL server for things like TC/IP - the setting don't take effect until you re-start sql server (you don't have t re-boot the server, but you do from the above sql config screen where you turned on network access do the restart of both sql server and the browser service). 

    If at this point? Well then try some of the sql groups for some suggestions - you not get much viewership in a access group. 

    R

    Albert

    Saturday, November 2, 2019 7:00 PM
  • Success!

    I had previously installed SQL Server Express on the client I was using to try to link to the server

    After I uninstalled the local version, I was able to find the network service

    Thank you again for all the work you put into helping with this issue

    Sunday, November 3, 2019 9:32 PM