Linking tables

[KitaYama]

I've written a function that syncs a database to a sql server.

1. It connects to a sql server and opens a recordset of all tables and views in the sql server.
   If it finds a linked table/view in CurrentDB, re-links and refreshes the connection string.
   if it doesn't find a linked table/view, adds a new linked table/view.
2. And then deletes all other tables/views in CurrentDB.

stConnect = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=Server;DATABASE=MyDatabase;Trusted_Connection=Yes;Encrypt=no;"
Do
    tbl = rs.Fields("name")
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tbl & "'") = 1 Then
        db.TableDefs(tbl).Connect = stConnect
        db.TableDefs(tbl).RefreshLink
    Else
        DoCmd.TransferDatabase acLink, "ODBC Database", stConnect, acTable, tbl, tbl
    End If
    DoEvents
    rs.MoveNext
Loop Until rs.EOF

My problem is: (it's not actually a problem. I simply need to know why)
Even though re-linking and adding linked tables/views are done with the same connection string, After running the function, Linked Table Manager shows two data source.
One contains the re-linked objects, the other contains added objects.

And if I check their connection string they are different.

Relinked object show:

Added objects show:

Both are working without problem.
Does anyone know why the connection strings are different?

Thanks.

 

[arnelgp]

i think was thinking that if you find the table in MsysObjects, drop (delete the table).
then again create all the Linked tables, (TransferDatabase acLink).

 

[KitaYama]

i think was thinking that if you find the table in MsysObjects, drop (delete the table).
then again create all the Linked tables, (TransferDatabase acLink).

At present I'm doing something in that line. I run the function twice.
The first time some are added, some are refreshed. The next run, refreshes all.

The number of tables are not too much (only 214). So running time is really seconds.

The problem with your suggestion is that since all tables/views are added as new links, their string connection is not correct.
(Correction: not that the string connection is wrong, but contains un-necessary parts.)

thanks for suggesting.

 

[GPGeorge]

I've written a function that syncs a database to a sql server.

1. It connects to a sql server and opens a recordset of all tables and views in the sql server.
   If it finds a linked table/view in CurrentDB, re-links and refreshes the connection string.
   if it doesn't find a linked table/view, adds a new linked table/view.
2. And then deletes all other tables/views in CurrentDB.

stConnect = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=Server;DATABASE=MyDatabase;Trusted_Connection=Yes;Encrypt=no;"
Do
    tbl = rs.Fields("name")
    If DCount("[Name]", "MSysObjects", "[Name] = '" & tbl & "'") = 1 Then
        db.TableDefs(tbl).Connect = stConnect
        db.TableDefs(tbl).RefreshLink
    Else
        DoCmd.TransferDatabase acLink, "ODBC Database", stConnect, acTable, tbl, tbl
    End If
    DoEvents
    rs.MoveNext
Loop Until rs.EOF

My problem is: (it's not actually a problem. I simply need to know why)
Even though re-linking and adding linked tables/views are done with the same connection string, After running the function, Linked Table Manager shows two data source.
One contains the re-linked objects, the other contains added objects.

View attachment 118212

And if I check their connection string they are different.

Relinked object show:
View attachment 118215

Added objects show:
View attachment 118217

Both are working without problem.
Does anyone know why the connection strings are different?

Thanks.

The connections strings reflect the actual concatenations used to put each connection together if you used VBA, or the way they were created originally, via the External Data Wizard. In other words, looking at the two Connection strings, you can see differences in them. One uses "DATABASE=MyDatabse" and the other uses "DATABASE=Reception"

In fact, as long as you keep the connection string valid, you can add or modify it here as well. For example, where it says APP=Microsoft Office, you could edit that to APP=MySpecialAccessProject, or whatever strikes your fancy. Obviously, since they involve two different databases, these specific connections can't be merged. However, if you ended up with the different connections to the same database, you could consolidate them by copying one over the other in the Connection string box.

You could also rename each of them from plain vanilla "SQL" to something like "MySQLServerDatabase4MySpecialAccessProject";, whatever strikes you as appropriate. Or maybe just "MyDatabse" and "Receiption", to reflect the two different databases being linked might be better.

I always do the latter because I find it annoying to see multiple connections all with a generic name that doesn't tell me which data source is actually involved in that connection.

 

[Pat Hartman]

If you link a table using the new external dialog, you get the connection string from the ODBC connection you defined. The other one comes from what you have in your relinking code. I would make my relinking code match the ODBC connection to avoid confusion.

Also, rather than you maintaining a table with tabes/views, I would use a query of the system tables to get the tables/views so you (or your successor) don't have to remember to update your hard-coded list.

 

[KitaYama]

If you link a table using the new external dialog, you get the connection string from the ODBC connection you defined. The other one comes from what you have in your relinking code. I would make my relinking code match the ODBC connection to avoid confusion.

I think you just answered my question. I didn't know the ODBC connection string is different with what I had in my code.
Thanks.

rather than you maintaining a table with tabes/views, I would use a query of the system tables

I don't have a table to maintain a list of tables and views.
I open a connection to sql server and then open a recordset from sql server system files to get a list of views and tables I have there.
The count of tables and views are high and I will never trouble myself to manage a table that will be used only for re-linking purpose.

The list of tables can be maintained from:

"SELECT name FROM sys.tables WHERE type = 'U'"

And the list of views can be created by:

SELECT name  FROM sys.views ORDER BY name;

 

[Pat Hartman]

That's great. It just sounded like you were maintaining the list of items that needed to be relinked