Linking SQL Server tables with VBA

[theDBguy]

My apologies for the thread bump here. I've been using this method to link backend SQL tables on several DB applications for probably over 10 years now. Recently, every link seems to take about 3 or 4 seconds to establish. Is anyone else seeing the same?

Not good when there are 100 tables!

I'm seeing this over a variety of locations. I think a recent update did something.

Hi @bossjohnc. Welcome to AWF!

I don't have a whole lot of ODBC linked tables, so I have practically no experience to comment on the issue you are experiencing. Good luck!

 

[NauticalGent]

If you relink a table from the navigation pane, is there a noticeable delay as well?

 

[bossjohnc]

If you relink a table from the navigation pane, is there a noticeable delay as well?

Great question. Just tried, and no.

That's interesting. Continuing to step through and examine...

 

[Minty]

What version of SQL Server are you using and which driver?

 

[nectorch]

Just compose the ODBC DSN less like below then you are done!

DRIVER={HDBODBC};UID=myUser;PWD=myPassword;SERVERNODE=myServer:30015

 

[bossjohnc]

What version of SQL Server are you using and which driver?

Using 2019 here, 2016 at a separate place with the same issue. ODBC v17.

Actually, I think manually linking may have a delay too. It's very hard to tell. I've just run through the code and put a timestamp/tablename debug.print statement in, and every table consistently takes one second to link in (so above was a guess/exaggeration).

This takes considerably longer than it used to. 100 tables = 100 seconds = user gone for coffee

 

[bossjohnc]

Just compose the ODBC DSN less like below then you are done!

DRIVER={HDBODBC};UID=myUser;PWD=myPassword;SERVERNODE=myServer:30015

I'm using:

strSQLConn = "ODBC;Driver={ODBC Driver 17 for SQL Server}; Server=MyServer; Database=MyDB;Trusted_Connection=Yes;"

so SSO for authentication, but looks ok (and has worked for at least a decade bar some not recent tweaks for TLS) otherwise.

 

[Minty]

Maybe the SQL server is taking the time to authenticate?
Has it moved or has the AD been moved to the cloud or similar?

 

[bossjohnc]

Maybe the SQL server is taking the time to authenticate?
Has it moved or has the AD been moved to the cloud or similar?

This is happening in multiple places, different ADs with different clients and different servers (and different implementations).

It seems to have been happening since update 2109 14430.20270 for Access. At this point it only seemed to affect compiled (accde) dbs. Any user with 2108 or earlier was ok running in the same environment.

Now accdbs seem to be affected as well.

I just wondered if anyone else has the issue, or could test (if you have a similar setup).

 

[NauticalGent]

In the event that you have to simply live with it, have you considered giving the user something to look at while the sub is running - splashscreen, progress meter, etc.?

 

[bossjohnc]

In the event that you have to simply live with it, have you considered giving the user something to look at while the sub is running - splashscreen, progress meter, etc.?

The procedure has always run behind a splashscreen, so users know to wait. It only runs once on launch, so it's not the end of the world I suppose, but a shame this now takes so much longer.

 

[Minty]

I'm not sure of your setup, but do you need to relink all the tables every time?

You could have a flag in a system table that you could set to enforce a relink when you made changes to the backend.
Other wise don't bother?

 

[bossjohnc]

I'm not sure of your setup, but do you need to relink all the tables every time?

You could have a flag in a system table that you could set to enforce a relink when you made changes to the backend.
Other wise don't bother?

Not a bad thought... I use a 'version' table to compare server vs client and copy over a new interface if this changes so the same process would cover it. I guess I could do away with the initial linking process entirely. It was useful though, as I could make backend design changes without too much consideration.

 

[pbaldy]

My data recently got pushed from an on premise SQL Server to a cloud SQL Server (against my wishes). Suddenly my DSNless code was taking an unacceptable amount of time, from a couple of seconds to 30 or more for apps with a lot of tables. I went the route of comparing the connection string of the existing linked table to what the connection string in my code would result in, and only re-linking if different.