none
DSN-less connection to SQL Server for linked tables in Access 2016/2019 using Microsoft OLE DB Driver 18.3.0 for SQL Server RRS feed

  • Question

  • I can't figure out how to implement the Microsoft OLE DB Driver 18.3.0 for SQL Server when linking tables to Access database using VBA.

    The existing link procedure uses the following connection string:

    strConnect = "ODBC; DRIVER=SQL Server;SERVER=Myserver;DATABASE=MyDB;Integrated Security=true;" 

    ........

    Set td = db.CreateTableDef(strTableName, dbAttachSavePWD, strTableNameShort, strConnect)
    db.TableDefs.Append td

    But can't figure out how to build the connection string for linking tables using the Microsoft OLE DB Driver 18.3.0 for SQL Server driver...


    WebColNo

    Tuesday, November 19, 2019 6:31 PM

All replies

  • I would probably create a new DSN, make it a File DSN, select that driver and use it to link a few tables. Then inspect the Connect property of those tables. In the Immediate window (Ctrl+G):

    ?currentdb.tabledefs("YourTableName").Connect


    -Tom. Microsoft Access MVP

    Tuesday, November 19, 2019 7:26 PM
  • Hi

    Thanks for your answer :-)

    I have installed the driver, but not able to find it when creating New Data Source ....


    WebColNo

    Tuesday, November 19, 2019 8:26 PM
  • Yeah, it's too bad Bill Gates doesn't call me before they make a decision :-)

    There are TWO odbc data source apps, one for each bitness: 32 and 64. They have the same file names but obviously in a different folder. The 64-bit one is in the system32 folder - go figure. The 32-bit one is in the syswow64 folder.

    To stay on the safe side, you would want to use the ODBC option from within Access. It should launch the one with the correct bitness.


    -Tom. Microsoft Access MVP

    Tuesday, November 19, 2019 10:08 PM
  • Sorry, but I did not understand your feedback...

    The question is:

    Is there a way to use/force access to use the "Microsoft OLE DB Driver for SQL Server" when linking

    tables and views from SQL server i Access 2016/2019

    Currently I'm using this connection string:

    ODBC;DRIVER=SQL Server;SERVER=Myserver;DATABASE=MyDB;Integrated Security=true;

    But can't figure out how to implement the updated OLE DB Driver...


    WebColNo

    Wednesday, November 20, 2019 9:38 AM
  • See www.connectionstrings.com

    With User ID /Password

      Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

    Trusted Conntction

      Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

    or

      Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Trusted_Connection=Yes;

    Matthias Kläy, Kläy Computing AG 

    Wednesday, November 20, 2019 12:15 PM
  • If I try your suggestion for connection string in the parameters for the ".CreateTableDef" I get the following error:

    3170 : Could not find installable ISAM.

    PS: This problem is only relevant for connection string for linking tables from SQL 2017 in Access 2016/2019


    WebColNo

    Wednesday, November 20, 2019 2:24 PM
  • Hm, I think the only way you can *link* tables in Access to SQL Server is with ODBC. You can use the OLEDB connection only in code  with an ADODB connection.

    See https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/tabledef-connect-property-dao

    Matthias Kläy, Kläy Computing AG 

    Wednesday, November 20, 2019 2:52 PM
  • Hm, Hm....

    That's a bit strange, ..

    How is it then possible to implement the OLEDB driver for opening views and tables from SQL servers

    Ref:

    https://support.office.com/en-us/article/connect-access-to-sql-server-050d88f3-b2d6-4e76-b6f9-f3c556f139ea

    (Avoid mixing connection string and database access technologies)



    WebColNo

    Wednesday, November 20, 2019 4:56 PM
  • It is unfortunate, but the article fails to distinguish in  CLEAR way,  that the context and discussion about using oleDB vs ODBC is in the context of using code, and NOT linked tables.

    So, if you writing code  in VBA to fill + load up a recordset, or writing VBA to execute some sql upddate (or delete) command, then once again you can use  oleDB, or you can use ODBC. Your choice.

    HOWEVER:

    If you are talking about linked tables in Access? They are 100% limited to  using ODBC connections - oleDB is NOT a choice. And to be REALLY clear?

    Well, up to Access 2010, oleDB was a possible choice in a round about way. If you created what is called a ADP project (Access Data Project), then all of your linked tables were in fact oleDB to SQL server. However, in those ADP projects, you are also allowed to introduce ODBC linked datasources too!

    However, for all intensive purposes? Right now, and the traditional long standing in Access is to use ODBC for linked tables. 

    so, how to use a view?

    Simply use the linked table manager to create a link. This process is identical to creting a linked table to sql server, and you can choose to link to a table, or a view. Once done, then reports, or code or forms or whatever can be based on this linked table. This means you don't need to code connection strings in your VBA, and in fact you can build a report or a form with ZERO lines of code to achieve this goal.

    Calling or using a store procedure.

    For sending a raw SQL command, say to call a store procedure , or even just general T-SQL commands to sql server?

    Create a pass-though query. 

    Thus, to call call a store proc, you could go:

    Currentdb.QuerfyDefs("NameOfPTQuery").Execute

    The above would of course just run the pass-though query.

    If the store proce in question returns records, then:

    dim rst      as DAO.RecordSet

    set rst = currentdb.queryDefs("NameOfPTquery").OpenRecordSet()

    So, note how it is only two lines of code, and we still not having to mess with connection strings in code.

    If you need to pass a parameter to the store proc? Then this is fine:

    With currentDb.QueryDefs("MyPTQuery")

       .SQL = "EXEC GetInvoices " & txtDate

    end with

    ' now launch report or form based on above query. So, the store proc will now feed the report.

    Or, shove results into a recordset

    rst = currentDB.QueryDefs("MyPTQuery").OpenReocrdSet

    So, all in all? Using ODBC as your primary connection technology is the way to go for linked tables (it is your ONLY choice).

    And  as above shows, even for 99%  of your code, your find DAO is the least amount of hassle, and the least amount of code also.

    For calling and using store procs, update commands etc.? You could introduce oleDB into your project, but then you have to deal with connection strings in code. This requires you set a reference to the ADO library. (with DAO, you don't need a reference - it built into access). 

    One last tip:

    If you link Access tables using a FILE dsn, then Access by default and automatic for you creates DSN-less connections. I recommend doing this, since after linking your front end, then you can deploy the application to each workstation, and no DSN or connection strings or anything needs to be setup on each workstation. 

    So, Access will without extra effort on your part will by default create DSN-less connections for you, but you have to use a FILE DSN. (if you use a "user" or "system" DSN, then the result is NOT dsn-less connections). 

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Thursday, November 21, 2019 4:30 AM