Linking SQL Server tables with VBA (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,357
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

Ignore List Poster Boy
Local time
Today, 07:54
Joined
Apr 27, 2015
Messages
6,280
If you relink a table from the navigation pane, is there a noticeable delay as well?
 

Minty

AWF VIP
Local time
Today, 11:54
Joined
Jul 26, 2013
Messages
10,354
What version of SQL Server are you using and which driver?
 

nectorch

Member
Local time
Today, 13:54
Joined
Aug 4, 2021
Messages
41
Just compose the ODBC DSN less like below then you are done!

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

bossjohnc

New member
Local time
Today, 11:54
Joined
Nov 25, 2021
Messages
7
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

New member
Local time
Today, 11:54
Joined
Nov 25, 2021
Messages
7
Just compose the ODBC DSN less like below then you are done!

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

I'm using:
Code:
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

AWF VIP
Local time
Today, 11:54
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 11:54
Joined
Nov 25, 2021
Messages
7
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

Ignore List Poster Boy
Local time
Today, 07:54
Joined
Apr 27, 2015
Messages
6,280
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

New member
Local time
Today, 11:54
Joined
Nov 25, 2021
Messages
7
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

AWF VIP
Local time
Today, 11:54
Joined
Jul 26, 2013
Messages
10,354
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

New member
Local time
Today, 11:54
Joined
Nov 25, 2021
Messages
7
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

Wino Moderator
Staff member
Local time
Today, 04:54
Joined
Aug 30, 2003
Messages
36,118
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.
 

Users who are viewing this thread

Top Bottom