Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-06-2019, 11:07 AM   #16
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,292
Thanks: 115
Thanked 3,090 Times in 2,808 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Linking SQL Server tables with VBA

Sorry I can't offer much guidance either

I always delete all existing connections then loop through recreating the links
Even for 300+ linked tables on a network it typically takes no more than 30-45 seconds in total

As that approach works reliably and is very fast, I've stuck with that for the past 15 years

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-06-2019, 11:12 AM   #17
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,548
Thanks: 58
Thanked 1,430 Times in 1,411 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linking SQL Server tables with VBA

Quote:
Originally Posted by isladogs View Post
Sorry I can't offer much guidance either

I always delete all existing connections then loop through recreating the links
Even for 300+ linked tables on a network it typically takes no more than 30-45 seconds in total

As that approach works reliably and is very fast, I've stuck with that for the past 15 years
Okay, thanks! I'll have to give it a try. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-06-2019, 12:03 PM   #18
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,026
Thanks: 455
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Linking SQL Server tables with VBA

Here is the method I adapted. My app only has about 50+ tables and it only takes about 3 seconds for this whole routine to run:

http://etutorials.org/Microsoft+Prod...es+at+Runtime/

__________________
ďIt follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.Ē - with apologies to George Washington
NauticalGent is online now   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
Hello1 (11-06-2019)
Old 11-06-2019, 12:05 PM   #19
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,548
Thanks: 58
Thanked 1,430 Times in 1,411 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linking SQL Server tables with VBA

Quote:
Originally Posted by NauticalGent View Post
Here is the method I adapted. My app only has about 50+ tables and it only takes about 3 seconds for this whole routine to run:

http://etutorials.org/Microsoft+Prod...es+at+Runtime/
Hi John. Thanks for the link. That looks similar to Method 1 from Paul's link. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-06-2019, 12:09 PM   #20
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,548
Thanks: 58
Thanked 1,430 Times in 1,411 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linking SQL Server tables with VBA

Out of curiosity, I checked which method Doug was using in his famous article. It looks like I'm the only one doing it the way I'm doing, so maybe I am doing it wrong. I may have to make some changes now.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-06-2019, 12:15 PM   #21
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Linking SQL Server tables with VBA

I submit that if it works it can't be wrong.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-06-2019, 12:19 PM   #22
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,548
Thanks: 58
Thanked 1,430 Times in 1,411 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linking SQL Server tables with VBA

Quote:
Originally Posted by pbaldy View Post
I submit that if it works it can't be wrong.
Hi. Thanks! It does work, but I also don't want to be an exception. If MS didn't sanction it, they might change their minds later on, and I'll be left out. I don't want to be left out.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-06-2019, 12:27 PM   #23
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,292
Thanks: 115
Thanked 3,090 Times in 2,808 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Linking SQL Server tables with VBA

Well yes … in this context but thinking as I type ...
One reason for breaking the connection then relinking is that you know whether all links have been updated.
If you have a network disconnect whilst refreshing you might not update all links.
If you have a disconnect by the delete/replace method, some or all tables wouldn't be relinked

Of course, table level lookups, multivalue and attachment fields all "work" but like many of us I'd submit using any of them are a mistake
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-06-2019 at 01:49 PM.
isladogs is offline   Reply With Quote
Old 11-06-2019, 03:14 PM   #24
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 201
Thanks: 69
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Linking SQL Server tables with VBA

One question. On some servers for linking tables I use the SQL Server Native client 10.0 driver and on some 11.0, no where I saw that I use the "ODBC;Driver={SQL Server}..
What is the difference? Should I stick with the Native client driver and make an option button to select between 10.0 or 11.0, or is SQL Server driver fine for all?
Is the speed affected?
Hello1 is offline   Reply With Quote
Old 11-06-2019, 03:16 PM   #25
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Linking SQL Server tables with VBA

The author of this is very highly regarded in the Access world:

https://accessexperts.com/blog/2018/...-oledb-driver/
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following 3 Users Say Thank You to pbaldy For This Useful Post:
Galaxiom (11-06-2019), Hello1 (11-06-2019), NauticalGent (11-06-2019)
Old 11-06-2019, 03:18 PM   #26
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,548
Thanks: 58
Thanked 1,430 Times in 1,411 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Linking SQL Server tables with VBA

Quote:
Originally Posted by pbaldy View Post
The author of this is very highly regarded in the Access world:

https://accessexperts.com/blog/2018/...-oledb-driver/
I totally agree!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-06-2019, 03:48 PM   #27
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 201
Thanks: 69
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Linking SQL Server tables with VBA

So if I understood well I want to use the ODBC Driver 17 for SQL Server, I dont use ADO?
So my connection string would be something like this?

Code:
strConnectionString = "ODBC;Driver={ODBC Driver 17 for SQL Server};" & _
        "DATABASE=" & DatabaseName & ";" & _
        "SERVER=" & ServerName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & PWD & ";"
I put in attachment a screen of my current drivers.
Attached Images
File Type: png CurrentODBCDrivers.png (20.1 KB, 8 views)
Hello1 is offline   Reply With Quote
Old 11-06-2019, 04:01 PM   #28
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,292
Thanks: 115
Thanked 3,090 Times in 2,808 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Linking SQL Server tables with VBA

Are you using the latest features in sql server that require the latest drivers?
I ask as if you use that driver, you may need to install it on each workstation.

If you don't need it, I would stick with the older drivers that each workstation running Access will definitely have
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-06-2019 at 10:56 PM.
isladogs is offline   Reply With Quote
Old 11-06-2019, 04:15 PM   #29
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 201
Thanks: 69
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Linking SQL Server tables with VBA

Most SQL Servers are 2008, one is 2012 i think. All the workstations with SQL Server 2008 use SQL Server Native client 10.0, the exact versions I dont know. The 2012 is only giving me headache because it uses SQL Server Native client 11.0 and because of it I have to somehow modify my code to use that driver and not SQL Server Native client 10.0..
Would it be wise to maybe use the Left function to get the current driver from one of the SQL linked tables, so I dont have to select manually?
But thinking now, a popup form with the option button for which driver to use wouldnt be that of a pain either because I dont plan to run the code on a startup of a form.
I will have to think about it heh
Thanks
Hello1 is offline   Reply With Quote
Old 11-06-2019, 04:34 PM   #30
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,292
Thanks: 115
Thanked 3,090 Times in 2,808 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Linking SQL Server tables with VBA

As my Access applications which have SQL Server BEs are used by a number of different clients with varying versions of both Access and SQL Server, I not only develop in the lowest version of Access in use by my clients but also deliberately use older drivers which will be available to each client.

If you aren't using the latest features, I would do the same.
Ben's advice refers to those who do need or will need the latest features.
But of course those clients will have what is required for that.
My experience is different which is why I asked.

I've never needed to go through hoops quite like you are describing.
But my code does allow for different drivers when building the connection strings.
Code:
'############################################
' GetConnectionString 04/08/2010
'   Used to create a connection string
'############################################
Function GetConnectionString(strLinkType, _
                            strLinkServer, _
                            strLinkDatabase, _
                            strLinkUsername, _
                            strLinkPassword) As String
        
    Select Case strLinkType 'Get the link type to determine the connection string
#If SQL_DRIVER = "Native" Then
        Case "SQL"
            GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=SchoolDataAnalyser;" & _
                                    "UID=" & Nz(strLinkUsername, "") & ";" & _
                                    "PWD=" & Nz(strLinkPassword, "")
        Case "SQL-Trusted"
            GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=SchoolDataAnalyser;" & _
                                    "Trusted_Connection=yes;"
#ElseIf SQL_DRIVER = "MDAC" Then
        Case "SQL"
            GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=SchoolDataAnalyser;" & _
                                    "UID=" & Nz(strLinkUsername, "") & ";" & _
                                    "PWD=" & Nz(strLinkPassword, "")
        Case "SQL-Trusted"
            GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=SchoolDataAnalyser;" & _
                                    "Trusted_Connection=yes;" & _
                                    ""
#End If
        Case "Access"
            If Nz(strLinkPassword, "") <> "" Then
                GetConnectionString = "MS Access;" & _
                                    "PWD=" & Nz(strLinkPassword, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
            Else
                GetConnectionString = "MS Access;" & _
                                    "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
            End If
        Case Else
            GetConnectionString = ""
    End Select

End Function
Hope that helps

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-06-2019 at 04:43 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Hello1 (11-06-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Linking Access tables to a remote SQL Server ken1921 General 3 01-04-2016 08:10 AM
Question Linking SQL Server Tables Eddie Mason General 3 08-30-2012 04:56 PM
Linking SQL Server tables reginab Tables 0 05-03-2010 06:50 AM
Linking SQL Server Tables to Access via ADO AlexTeslin Modules & VBA 15 09-11-2008 12:41 PM
Linking tables to BE using server name, possible? mattkorguk Tables 9 01-03-2008 07:58 AM




All times are GMT -8. The time now is 08:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World