Linking SQL Server tables with VBA (1 Viewer)

pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:33
Joined
Aug 30, 2003
Messages
36,125
I submit that if it works it can't be wrong. ;)
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,467
pbaldy said:
I submit that if it works it can't be wrong. ;)
Click to expand...
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. :(
 
isladogs

isladogs

MVP / VIP
Local time
Today, 20:33
Joined
Jan 14, 2017
Messages
18,213
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
 
Last edited:
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
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?
 
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
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.
 

Attachments

  • CurrentODBCDrivers.png
    CurrentODBCDrivers.png
    20.1 KB · Views: 106
isladogs

isladogs

MVP / VIP
Local time
Today, 20:33
Joined
Jan 14, 2017
Messages
18,213
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
 
Last edited:
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
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
 
isladogs

isladogs

MVP / VIP
Local time
Today, 20:33
Joined
Jan 14, 2017
Messages
18,213
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
 
Last edited:
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
Im trying to extract the database name from the table link string. What would be the best way?
Code: 
DRIVER=ODBC Driver 17 for SQL Server;SERVER=PCName\SQLEXPRESS;UID=username;PWD=password;APP=2007 Microsoft Office system;DATABASE=DatabaseSQL;
So I need the "DatabaseSQL". Im not sure if the order of the connection string will always be like this, but anyways I would start with finding the word "DATABASE" because its always the same.
I want to use this to save the current database names in a table before I run the procedure to relink the tables.

Another thing Im trying to find is a way to read the available databases on the SQL Server, so I can list them in a combobox on a form.
I did find the query but not sure how to do the actual connecting and running the query. I mostly see ADO being used, any way with DAO?
Code: 
"SELECT name" & vbCr & _
                "FROM [master].[dbo].[sysdatabases]" & vbCr & _
                "where name not in " & _
                "('master','tempdb','model','msdb','pubs')"
Or maybe to read the content of the folder where I keep the databases but I guess I would have trouble accessing that folder with VBA or no? Edit: probably a bad idea with the folder, path is most probably always different
 
isladogs

isladogs

MVP / VIP
Local time
Today, 20:33
Joined
Jan 14, 2017
Messages
18,213
1. I do this in the reverse order. Store the database name (& other properties) in a table then use that to build the connection string. If you look at my example you can see how I do so.

However to do it your way, use at string variable strConnectionString for the entire string then use the Mid and InStr function searching for "DATABASE=".

I've never done the second part of your question and personally don't see the point. If you reverse the approach as suggested above you will have a list of available databases in your link types table. Otherwise you probably need to use a passthrough query in Access. I expect someone will have code for doing this online
 
Last edited:
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
Whats confusing me is what if I want to change this link:
Code: 
DRIVER=ODBC Driver 17 for SQL Server;SERVER=ServerName\SQLEXPRESS;UID=username;PWD=password;APP=2007
Microsoft Office system;DATABASE=[B]DatabaseSQL[/B];
with this one
Code: 
DRIVER=ODBC Driver 17 for SQL Server;SERVER=MyPCName\SQLEXPRESS;UID=username;PWD=password;APP=2007
Microsoft Office system;DATABASE=[B]DatabaseSQL_Test[/B];
Now if I use the code from the first example which pbaldy shared, and lets say in the current links besides the DatabaseSQL I have fwe links with a DatabaseSQL2 database. When I run the code it would remove all the links, even the DatabaseSQL2 and cause an error because I dont have the tables needed for DatabaseSQL2 in my DatabaseSQL_Test.
So I was thinking to grab the databases from the current links in my .accdb which would be DatabaseSQL and DatabaseSQL2 and add them to a table tblDatabasesSQL.
Structure of the tblDatabasesSQL would be something like this.
Column name_______Data type______Primary key?______Required?
DatabaseSQLName____Text50___________Yes_____________Yes
DriverName__________Text70___________No_____________Yes

I would have 2 records in it,
DatabaseSQL________Native client 10.0
DatabaseSQL2_______Native client 10.0

After this I would make a popup form with a comboobx1 ("Current database") containing database names from tblDatabasesSQL and another combobox2 ("New database") which would contain the available databases in the SQL Server.
On the form I would chose DatabaseSQL in combobox1 and DatabaseSQL_Test in combobox2. Now on a button click I would run the code to relink the tables like in pbaldys first post but I would remove only the links which contain the DatabaseSQL database and replace them with new ones which would contain DatabaseSQL_Test database.

If I would replace the current links with a link which contains the same database name like the current one I wouldnt have any troubles because I tested it and it worked fine with that little modification I posted. However, when I work at my PC sometimes I use the same databases like on the workstations but I rename them like adding _Test or something similar.
I did check your example but to be honest I have trouble figuring it out still, still too complex for me :eek:
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,467
Hi. I could be wrong, but that requirement seems to be only applicable while you're developing. If so, rather than reinvent the wheel, perhaps you could try this add-in instead.
 
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
Looks like its not compatible with Access 2007 :(
 
isladogs

isladogs

MVP / VIP
Local time
Today, 20:33
Joined
Jan 14, 2017
Messages
18,213
From the link web page
To deal with this situation, I’ve a LTM for Access and SQL Server (will be expanded to other data sources in the future) which can be used by all versions of Access starting at 2007.
Click to expand...
 
H

Hello1

Registered User.
Local time
Today, 22:33
Joined
May 17, 2015
Messages
271
This is what I get when trying to open with Access 2007 (attachment)
 

Attachments

  • LTMError.PNG
    LTMError.PNG
    6.6 KB · Views: 119
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,467
Hello1 said:
This is what I get when trying to open with Access 2007 (attachment)
Click to expand...
Hi. I suggest you contact the author for clarification. Or, I could let him know.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,467
Okay, I sent him an email. Let's see what he says.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,467
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.
 
You must log in or register to reply here.

Similar threads

N
Solved Failing to save change made from MS Access Front End to SQL Server Cloud
Replies
4
Views
146
nector
N
J
Create DSN-less connection to SQL Server
2 3
Replies
48
Views
1,992
isladogs
isladogs
J
Check for presence of an SQL Server
2
Replies
20
Views
812
JohnPapa
J
B
VBA problem relating to SQL linked table
Replies
6
Views
534
CJ_London
C
E
Problem with linking
Replies
4
Views
343
ellenr
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom