Adding linked table from sql server to FE

[KitaYama]

I'm trying to create DSN_Less linked tables of all user tables in a specified database in sql server To an Access FE.
While the following code works and I have no problem with it, I thought before taking any stupid step and going further, I better ask for someone checking it. (specially connection strings)

Any correction (if necessary) is much appreciated.

Sub Add_DSNLessTables()

    Dim stConnect As String
    Dim sql As String
    Dim db As DAO.Database
    Dim tbl As String
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    stConnect = "DRIVER=ODBC Driver 18 for SQL Server;SERVER=Server;DATABASE=Designers;Trusted_Connection=Yes;Encrypt=no;"
    Set db = CurrentDb

    With con
        .ConnectionString = stConnect
        .ConnectionTimeout = 10
        .Open
    End With

    If con.State = 0 Then Exit Sub
    sql = "select * from sys.tables where type = 'U'"

    rs.CursorLocation = adUseClient
    rs.Open sql, con, adOpenStatic
    stConnect = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=Server;DATABASE=Designers;Trusted_Connection=Yes;Encrypt=no;"

    Do
        tbl = rs.Fields("name")
        If DCount("[Name]", "MSysObjects", "[Name] = '" & tbl & "'") = 1 Then
            ' table exists. Refresh the link
            db.TableDefs(tbl).Connect = stConnect
        Else
            ' table doesn't exits. Add it
            AddLinkedTable tbl, tbl, stConnect
        End If
        rs.MoveNext
    Loop Until rs.EOF
End Sub


Public Function AddLinkedTable(strNameInSQLServer As String, _
                               strNameInAccess, _
                               stConnect As String)
    DoCmd.TransferDatabase acLink, "ODBC Database", stConnect, acTable, strNameInSQLServer, strNameInAccess
End Function

 

[Josef P.]

If DCount("[Name]", "MSysObjects", "[Name] = '" & tbl & "'") = 1 Then
            ' table exists. Refresh the link
            db.TableDefs(tbl).Connect = stConnect
        Else

Will it link correctly without RefreshLink?

 

[KitaYama]

No. It doesn't.
Since the tables had linked previously, I didn't noticed that.
I added the following:
db.TableDefs(tbl).RefreshLink

I appreciate the correction.

 

[KitaYama]

Something that I still don't understand.

Why ODBC can not be used when I want to open an ADODB connection to server to read a list of tables from sys.tables?

If I use the following connection string, I receive an error and the connection fails.

stConnect = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=...........

If I use the following, the connection passes.

stConnect = "DRIVER=ODBC Driver 18 for SQL Server;SERVER=...........


Thanks.

 

[Josef P.]

ADODB uses OLEDB => it needs an OLEDB driver.

Many connection strings (ODBC and OLEDB) are available here: https://www.connectionstrings.com/

 

[isladogs]

In your AddLinkedTables function code, you aren't using the table name from SQL Server

 

[KitaYama]

In your AddLinkedTables function code, you aren't using the table name from SQL Server

If I cut the unnecessary parts then I have :

    sql = "select * from sys.tables where type = 'U'"
    ......
    tbl = rs.Fields("name")
    .......
    ' table doesn't exits. Add it
    AddLinkedTable tbl, tbl, stConnect
    .......
 
End Sub


Public Function AddLinkedTable(strNameInSQLServer As String, _
                               strNameInAccess, _
                               stConnect As String)
    DoCmd.TransferDatabase acLink, "ODBC Database", stConnect, acTable, strNameInAccess, strNameInAccess
End Function

tbl (string variable) is passed twice to the function. The first as sql server table name, the second as Access FE's table name.
(In some rare situations, the name of tables are different. Hence twice the name is passed).
Am I wrong?

Thanks

 

[isladogs]

As you already know, the syntax is:
TransferDatabase (TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)

DoCmd.TransferDatabase method (Access) | Microsoft Learn

You aren't referencing the Source table used in the function header
I think the code should be:

DoCmd.TransferDatabase acLink, "ODBC Database", stConnect, acTable, strNameInSQLServer, strNameInAccess

 

[KitaYama]

Sorry. You're correct.
The code in my database is correct.
It seems that I've messed up while copy/paste the code here.

I corrected my post above.

You have good eyes to be able to catch that.
Million thanks.

 

[KitaYama]

ADODB uses OLEDB => it needs an OLEDB driver.

Many connection strings (ODBC and OLEDB) are available here: https://www.connectionstrings.com/

Thanks for solving the puzzle. It teased me a lot.
Need some more reading.

 

[Pat Hartman]

I usually keep a table of tablenames in the FE and I use that table to control the DSNless connections. This is because I might not always want to link to all tables in any given database. Sometimes, if I'm "borrowing" tables from another app, I'll just link one or two tables from "their" db.

 

[KitaYama]

I usually keep a table of tablenames in the FE and I use that table to control the DSNless connections. This is because I might not always want to link to all tables in any given database. Sometimes, if I'm "borrowing" tables from another app, I'll just link one or two tables from "their" db.

Yes, I was doing the same. But when an update of the database needs a new table, I have to add the name of the table to the list.
The database started with 18 tables and it now contains 107 tables.

I was just looking for a way to bypass this manual method.
Now, I add the necessary table(s) to sql server and that's it. Executing the above code brings in all necessary linked tables. No manual or additional step is required.

In case of shared tables between apps, I have a naming method to take care of it. Tables used in manufacturing, finance, shared etc has specific prefixes that an IF clause takes care of if the table is necessary for this app or not.