Results 1 to 13 of 13
  1. #1
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41

    Access 2016 and SQL Server 2014 with linked tables

    I'm trying to get a front end (access) and backend (SQL Server) working. The issue I'm having is that after using SQL Server 2017 Import and Export data tool and then going back and using Access import and link tool to link the tables, I keep getting Access error stating that the access database engine cannot find the input table or query 'tblname'



    I have a lot of code in the access front end that I need to continue to use.

    So, the question is what am I missing; or what do I need to do to get the front end working and back end communicating?

    thanks,
    Jeff

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should only need to link the SQL tables once. Then they stay linked.
    you dont use Import/Export data.
    In access, use External data, new data source, from DB, sql server, LINK TABLES.
    choose the tables.
    done.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Also make sure that when you linked the tables, they didn't end up with "dbo_" at the beginning of each table name. If so, that should be deleted. If there are a lot of tables, it's not hard to write a function that deletes that from all of them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Quote Originally Posted by ranman256 View Post
    you should only need to link the SQL tables once. Then they stay linked.
    you dont use Import/Export data.
    In access, use External data, new data source, from DB, sql server, LINK TABLES.
    choose the tables.
    done.
    So how do I get the access tables into SQL Server? Doesn't that need to be done before the linking? I tried the split database in Access but I stll ran into table names not matching.

    Are there any good sites to go from beginning to end with Access 2016 and SQL Server 2014?

  5. #5
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Quote Originally Posted by pbaldy View Post
    Also make sure that when you linked the tables, they didn't end up with "dbo_" at the beginning of each table name. If so, that should be deleted. If there are a lot of tables, it's not hard to write a function that deletes that from all of them.
    So, if the sql server tables (that were added using the ms server import/export data) do have a schema name you're saying to completely get rid of the schema or the front end will be confused?

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by jrdnoland View Post
    So, if the sql server tables (that were added using the ms server import/export data) do have a schema name you're saying to completely get rid of the schema or the front end will be confused?
    I'm saying that sometimes when you link to SQL Server tables, the original name of "tblname" will end up as a linked table named "dbo_tblname". Access will not recognize that since it's still looking for tblname. The schema is something SQL Server understands, not Access. It isn't even the correct syntax anyway (it would be dbo.tblname).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41

    sql server back end

    Sorry, no matter what I try I can't get this to work.

    Again, I have an access 2016 database with multiple tables, queries, forms code etc. And I want to add and link the tables to sql server 2014. I want to be able to use this database as I always have, but want the data table data stored in sql server 2014.

    None of the internet articles seem to explain using my database as the front end to the sql server back end and having everything function as it did before.

    I must be missing something, can someone more knowledgeable that me please give me a process with simple steps to accomplish this?

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Generally speaking, if you link to the SQL Server tables and the linked tables have the same names they had as Access tables, most things should work as before. There are typically tweaks required. If you're getting an error that Access can't find "tblname", does your linked table have that name? Not in SQL Server, but as it appears in Access. Can you post the code that fails, and/or a pic of the navigation pane showing the tables?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jrdnoland is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41

    table names

    Quote Originally Posted by pbaldy View Post
    Generally speaking, if you link to the SQL Server tables and the linked tables have the same names they had as Access tables, most things should work as before. There are typically tweaks required. If you're getting an error that Access can't find "tblname", does your linked table have that name? Not in SQL Server, but as it appears in Access. Can you post the code that fails, and/or a pic of the navigation pane showing the tables?


    I think the issue is that all my code needs to reference the table tblEmployee. The table in SQL Server is dbo_tblEmployee. I can't seem to remove the schema from sql server.

    I was under the impression that splitting access to sql server would let all of your existing code, forms, queries, models work as they did in just access.

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It doesn't really matter what the table's name is in SQL Server, just what the linked table's name is in Access. That's what an Access query is looking at. In Access, you can hit F2 on the linked table name and change it to "tblEmployee".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    jrdnoland is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Quote Originally Posted by pbaldy View Post
    It doesn't really matter what the table's name is in SQL Server, just what the linked table's name is in Access. That's what an Access query is looking at. In Access, you can hit F2 on the linked table name and change it to "tblEmployee".
    OK, I will do some more testing and see where I'm at.

    Appreciate your help!

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, post back if you're still stuck. Using Access as a front end to SQL Server is very common; most of my apps use SQL Server. Your statement "sql server would let all of your existing code, forms, queries, models work as they did in just access" is largely true.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a thought - have you set up an ODBC Data Source?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-14-2017, 09:17 PM
  2. Replies: 1
    Last Post: 06-25-2017, 01:37 AM
  3. Need help on Access Attachments Migration to SQL server 2014
    By sahadevreddy@gmail.com in forum Import/Export Data
    Replies: 2
    Last Post: 12-23-2016, 01:51 AM
  4. Replies: 1
    Last Post: 08-11-2016, 08:25 AM
  5. Replies: 4
    Last Post: 12-23-2015, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums