none
ODBC Failing on Link Table but not Passthrough Query RRS feed

  • Question

  • Does anybody have any advice on this? 

    I have in Access 2013 about 4 Linked tables through an ODBC but it will not connect via a table. However, if i use the same ODBC in a Passthrough query it works fine. 

    Wednesday, August 12, 2015 5:59 PM

Answers

  • The Driver is: 06.01.7601 and the server is the same as it was. the connection works as well. 

    Yes, my computer has both Access 2003 and Access 2013 on it and when i run the Access 2003 it connects just fine but does not in Access 2013. Could you try explaining to me how i would create a link table from scratch? I do not how to i only know how to link it via the ribbon and clicking on the ODBC button in the import/link category. And i tried setting the table def to the query def and it will not allow me to change the table def. 

    First, about changing the .Connect property of the TableDef:  I think I left out an important step.  Try doing it like this:

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    Set tdf = db.TableDefs("dbo_CONTACT")
    tdf.Connect = db.QueryDefs("CONTACT").Connect
    tdf.RefreshLink
    

    Apparently it doesn't stick unless you call the .RefreshLink method on the TableDef after changing the .Connect method.  The above code works for me.

    When I spoke of creating the linked table "from scratch", I was talking about doing it via the ribbon.  Yes, one can build the whole tabledef in code, but I don't think you should need to go to such lengths.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by JimR2015 Thursday, August 13, 2015 3:34 PM
    Thursday, August 13, 2015 3:27 PM

All replies

  • Does anybody have any advice on this? 

    I have in Access 2013 about 4 Linked tables through an ODBC but it will not connect via a table. However, if i use the same ODBC in a Passthrough query it works fine. 

    Could you give a bit more information?  I take it you mean you can't open the linked tables directly as datasheets.  What error message/number do you get?

    What is the .Connect property of the TableDefs?  You can get it by entering the command below in the Immediate window:

        ?CurrentDb.TableDefs("YourTableNameInQuotes").Connect

    What is the .Connect property of the pass-through query?  Similarly, you can get it by entering the command below in the Immediate window:

        ?CurrentDb.QueryDefs("YourQueryNameInQuotes").Connect

    Note:  if the .Connect strings have sensitive information like passwords, you should edit those out -- but make sure they are the same between the tables and the query?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 12, 2015 7:26 PM
  • ?CurrentDb.TableDefs("dbo_CONTACT").Connect
    ODBC;DSN=Bold1;Description=Bold1;UID=**;PWD=**;APP=Microsoft Office 2013;DATABASE=MA**
    ?CurrentDb.QueryDefs("CONTACT").Connect
    ODBC;Driver={SQL Server};Server=10.XXX.XX.XXX;Database=Ma**;UID=**;Pwd=**;

    I am unable to upload the exact image but the text states: 

    ODBC--call failed.

    [Miscrosoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'CONTACT'. (#102)

    Wednesday, August 12, 2015 8:30 PM
  • Hi JimR2015,

    >>CurrentDb.TableDefs("dbo_CONTACT").Connect
    ODBC;DSN=Bold1;Description=Bold1;UID=**;PWD=**;APP=Microsoft Office 2013;DATABASE=MA**
    ?CurrentDb.QueryDefs("CONTACT").Connect
    ODBC;Driver={SQL Server};Server=10.XXX.XX.XXX;Database=Ma**;UID=**;Pwd=**;<<

    It seems that you the link table was added successfully. What did you mean ODBC --call failed?

    In addtion, we can query the link table like a local table. There is not nesseary to query it via ODBC from the orgnal data source.

    If I misunderstood, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 13, 2015 7:45 AM
    Moderator
  • The ODBC --call failed was the error message that i received from Microsoft after i tried opening the linked table in datasheet mode. Also the reason i am querying the ODBC table via a passthrough query was because then it would be identical to the linked table and would be pulling the correct data, also less steps then pulling data from a table that i can connect to via the query. This issue however is only occurring in Access 2013 the link table is connecting just fine via access 2003. 
    Thursday, August 13, 2015 12:39 PM
  • ?CurrentDb.TableDefs("dbo_CONTACT").Connect
    ODBC;DSN=Bold1;Description=Bold1;UID=**;PWD=**;APP=Microsoft Office 2013;DATABASE=MA**
    ?CurrentDb.QueryDefs("CONTACT").Connect
    ODBC;Driver={SQL Server};Server=10.XXX.XX.XXX;Database=Ma**;UID=**;Pwd=**;

    I am unable to upload the exact image but the text states: 

    ODBC--call failed.

    [Miscrosoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'CONTACT'. (#102)

    Those two connection strings are not the same. The first one, the one for the table, refers to a DSN, while the second doesn't, but rather specifies the server and driver directly.   Can you open the ODBC Administrator utility and test the DSN directly to see if it connects okay?  What server and driver are specified by the DSN?

    You mention in another message that the linked tables work properly in Access 2003.  Is that on the same computer?  Do you have both Access 2003 and Access 2013 installed on the same computer, or did you upgrade from A2003 to A2013, and are therefore saying that it *used to* work under A2003?

    In Access 2013, can you create a new linked table, starting from scratch and specifying the connection details all over again, and have it work correctly?

    Suppose you take one of the existing linked tables, and replaced its .Connect string with the one used by the query, using a statement like this:

        CurrentDb.TableDefs("dbo_CONTACT").Connect = CurrentDb.QueryDefs("CONTACT").Connect

    Does the linked table work properly after that?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 13, 2015 2:27 PM
  • The Driver is: 06.01.7601 and the server is the same as it was. the connection works as well. 

    Yes, my computer has both Access 2003 and Access 2013 on it and when i run the Access 2003 it connects just fine but does not in Access 2013. Could you try explaining to me how i would create a link table from scratch? I do not how to i only know how to link it via the ribbon and clicking on the ODBC button in the import/link category. And i tried setting the table def to the query def and it will not allow me to change the table def. 

    Thursday, August 13, 2015 2:59 PM
  • The Driver is: 06.01.7601 and the server is the same as it was. the connection works as well. 

    Yes, my computer has both Access 2003 and Access 2013 on it and when i run the Access 2003 it connects just fine but does not in Access 2013. Could you try explaining to me how i would create a link table from scratch? I do not how to i only know how to link it via the ribbon and clicking on the ODBC button in the import/link category. And i tried setting the table def to the query def and it will not allow me to change the table def. 

    First, about changing the .Connect property of the TableDef:  I think I left out an important step.  Try doing it like this:

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    Set tdf = db.TableDefs("dbo_CONTACT")
    tdf.Connect = db.QueryDefs("CONTACT").Connect
    tdf.RefreshLink
    

    Apparently it doesn't stick unless you call the .RefreshLink method on the TableDef after changing the .Connect method.  The above code works for me.

    When I spoke of creating the linked table "from scratch", I was talking about doing it via the ribbon.  Yes, one can build the whole tabledef in code, but I don't think you should need to go to such lengths.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by JimR2015 Thursday, August 13, 2015 3:34 PM
    Thursday, August 13, 2015 3:27 PM
  • That worked Perfectly. Thank You!
    Thursday, August 13, 2015 3:34 PM