Linking SQL Server tables with VBA (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 17:22
Joined
Jan 14, 2017
Messages
18,186
Hi
Just to confirm what the developer Dale Fye said.
I tried the 32-bit version of the LTM add-in using a VM with A2007 (which only comes in a 32-bit version)
It worked perfectly without errors.
Certainly worth investigating further
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Hi. The developer told me the 32-bit Office version of the add-in should work in Access 2007. Did you, by any chance, try to install the 64-bit Office version? Please double-check. Thanks.
In addition, Dale told me you can contact him directly if you're still having any issues. If you need his email address, we can give it to you via PM (don't want spam bots to harvest it here). Or, you can contact him through his website. Good luck!
 

Hello1

Registered User.
Local time
Today, 19:22
Joined
May 17, 2015
Messages
271
I tried both versions but same error. On access 2010 it did install without problems but got the same error which he shows in his post when trying to run it.
I will maybe give it another try later, for now will try to finish what I started. I dont have much left to do, just want to read available databases on the server and write existing databases from the current links into a table. Will probably try tonight
Thanks for your effort and help! :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
I tried both versions but same error. On access 2010 it did install without problems but got the same error which he shows in his post when trying to run it.
I will maybe give it another try later, for now will try to finish what I started. I dont have much left to do, just want to read available databases on the server and write existing databases from the current links into a table. Will probably try tonight
Thanks for your effort and help! :)
Hi. Sorry to hear that. Colin didn't have any issues when he tried it. Maybe there's something else going on. Please keep us posted, if you get a chance to work on it some more. Good luck!
 

isladogs

MVP / VIP
Local time
Today, 17:22
Joined
Jan 14, 2017
Messages
18,186
I also tried it with my default version, A2010. Again no problems.

Going back to an earlier point....
I know I'm repeating myself but one of the big advantages of DSN-less connections is that you don't need to configure each workstation separately. But that's only true if each workstation has the ODBC driver specified in the connection string. That won't be the case if you use Driver 17 for SQL Server so you will need to install that on each workstation. Do you really need the
extra Functionality provided with that new driver?
 
Last edited:

Hello1

Registered User.
Local time
Today, 19:22
Joined
May 17, 2015
Messages
271
Sorry, that was my test string and I forgot to change it. It will actually be the Native client 10.0 but as I mentioned earlier, one machine has the Native client 11.0 so I will probably keep the driver version in a table with the server name, so I will grab it from there.

BTW: how can I completely uninstall the custom LTM from access 2010?
When I go the way the author describes when updating the LTM I still see it in the AddIns. I did a mistake when I installed it the first time in access 2010, I run the .accde from my dowloads folder instead AppData/..../AddIns

Edit: I checked now and it seems its uninstalled completely, I will try it again now

Still same error (attachment)
I will try later on my laptop where I have a clean installation of windows, just have to add office.
 

Attachments

  • LTMErrorAfterInstalling.PNG
    LTMErrorAfterInstalling.PNG
    5.2 KB · Views: 107
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Hi. As I said earlier, the author/developer (Dale) is willing to work with you, if you contact him directly. If you want his email address, send me a PM.
 

isladogs

MVP / VIP
Local time
Today, 17:22
Joined
Jan 14, 2017
Messages
18,186
I wonder whether the Native Client is backwards compatible.
In other words I wonder whether the PC with Native Client 11.0 will still be OK if you use 10.0 for all workstations. Certainly worth checking.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
I wonder whether the Native Client is backwards compatible.
In other words I wonder whether the PC with Native Client 11.0 will still be OK if you use 10.0 for all workstations. Certainly worth checking.
My thought on that is, and it could be wrong, if the Connection String says "11.0" and the computer it's running on has 10.0 installed, it will probably fail. Just a guess though...


Edit: Or, did I misunderstand your question? Was I reading it backwards? If the Connection String says "10.0" but 11.0 is installed, would it still work? I wonder too...
 

isladogs

MVP / VIP
Local time
Today, 17:22
Joined
Jan 14, 2017
Messages
18,186
My question was whether using the 'lowest common denominator' (NC 10.0) will work on all machines. Same idea as developing in the lowest versions of Access used by your clients

The reverse won't work.
 

Hello1

Registered User.
Local time
Today, 19:22
Joined
May 17, 2015
Messages
271
Unfortunately its not, I tried it with the links having in it the Native Client 10.0 while the workstation only has the Native Client 11.0 driver. It caused an error in the linking process, cant remember exactly the description. However, when I changed the link string to Native Client 11.0 it linked the tables without errors.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Unfortunately its not, I tried it with the links having in it the Native Client 10.0 while the workstation only has the Native Client 11.0 driver. It caused an error in the linking process, cant remember exactly the description. However, when I changed the link string to Native Client 11.0 it linked the tables without errors.
Hi. Interesting. It's good to know. I wouldn't have expected it, so thanks for the update. But just to confirm, you had 11 in the connection string, but the computer only has 10 installed, and it worked, correct?
 

Hello1

Registered User.
Local time
Today, 19:22
Joined
May 17, 2015
Messages
271
Other way around. I had 10 in the connection string and the computer has only 11 installed. That way it didn't work. When I updated the connection string to 11 it did work.

I didn't have the chance to test what you wrote. 11 in string, 10 being the only driver on computer.
 

isladogs

MVP / VIP
Local time
Today, 17:22
Joined
Jan 14, 2017
Messages
18,186
DBG
I don't believe that Hello1 said that.

Hello1
Can you let us know what the error description was when you used NC10.0 on the 11.0 machine.

Also have you tried connecting with the default SQL Server driver that all machines should have
 

Hello1

Registered User.
Local time
Today, 19:22
Joined
May 17, 2015
Messages
271
Alright, I will make a screenshoot next time I try it. No, I didn't try the default SQL Server driver, I will try that one too, I suppose that one should work. I finished my form and code for the connection string so I will have to do some testing in the upcoming days and I will also test the mentioned above.

Edit: one maybe interesting thing I had on the machine I tried the code was after running the relinking code successfully the tabes, forms.. were opening much slower than with previous connections. However, then I realized I didn't write the whole SQL Server name in the connection string, just the computer name, so that's probably the reason. But I realized that later, when I already finished that short testing.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Other way around. I had 10 in the connection string and the computer has only 11 installed. That way it didn't work. When I updated the connection string to 11 it did work.

I didn't have the chance to test what you wrote. 11 in string, 10 being the only driver on computer.
Okay. Thanks for the clarification. It would be nice to know what happens if you could test having 11 in the code but only10 installed on the computer.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 10:22
Joined
Aug 30, 2003
Messages
36,118
Okay. Thanks for the clarification. It would be nice to know what happens if you could test having 11 in the code but only10 installed on the computer.

It fails as I would expect. RTE 3151, ODBC connection failed.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
It fails as I would expect. RTE 3151, ODBC connection failed.
Hi Paul. That's what I expected too, as I mentioned in Post #49, but I just didn't have a way to test it. Thanks for confirming it. Cheers!
 

bossjohnc

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

Users who are viewing this thread

Top Bottom