Linking SQL Server tables with VBA

[isladogs]

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]

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]

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 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]

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?

 

[Hello1]

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.

 

[theDBguy]

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]

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 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]

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]

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]

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]

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]

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]

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]

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]

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]

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!

 

[pbaldy]

No problem.

 

[bossjohnc]

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.