Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-04-2018, 01:00 AM   #16
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,401
Thanks: 123
Thanked 137 Times in 129 Posts
Lightwave will become famous soon enough
Re: ODBC SQL Server driver

My interpretation of this .

DSN is simply the name given to the small data store that indicates the type of database and the name of the database along with any passwords. It doesn't in itself contain text or functions that execute in anyway - if you could read a DSN file in a text editor it would only consist of a couple of lines of text, it is needed to be set up on each machine via an ODBC manager UNLESS you use the DSNless code which transfers that configuration from a file sitting on your computer (or into the registry) separate to your database to the VBA residing within your database. Either way drivers are always needed.

You can connect to MySQL and PostGres as well easily just need to install the drivers. Likewise if you were good enough you would be able to make up DSNless connections for those databases but only after installing the drivers.

Here's how to connect to PostGres from Access 2003
Connect MS Access to PostGres

and here's how to connect to MySQL from Access 2003
Connect MS Access to MySQL

Both involve hunting down a driver firstly

Connecting via DSN is a bad description better described as.

Use a DSN file to store the configuration required by specific database drivers to connect to an instance and database.

This confused the hell out of me initially

__________________

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

Last edited by Lightwave; 07-04-2018 at 02:29 AM.
Lightwave is offline   Reply With Quote
The Following 2 Users Say Thank You to Lightwave For This Useful Post:
mjdemaris (07-06-2018), Uncle Gizmo (07-04-2018)
Old 07-04-2018, 03:06 AM   #17
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,357
Thanks: 159
Thanked 1,707 Times in 1,677 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: ODBC SQL Server driver

I check for a specific linked tables and if not present automatically relink.
I maintain a local table of remote / local table names and their location, and can force a relink via an admin form.

Before that though I also check which SQL driver is loaded.
If you ever need to you can check for the driver that is installed and (in my case) point the user automatically to the network download path;

Code:
Public Function CheckSQLDriver()

    Dim arrEntryNames()
    Dim arrValueTypes()
    Dim strAsk As Variant
    Dim strFound As Variant
    Dim rPath As String
    rPath = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"

    Call EnumerateRegEntries(rPath, arrEntryNames, arrValueTypes)

    If Not IsEmpty(arrEntryNames) Then
        For Each strAsk In arrEntryNames
            If (InStr(strAsk, "SQL Server Native Client 11.0")) Then
                strFound = strFound & strAsk & ", "
            End If
        Next
    End If

    If (Len(strFound) = 0) Then
        
        MsgBox "You need to install SQL Driver Native Client 11 - Press OK to get it!" & vbCr & "The Database will now close." & vbCrLf & "Please restart the database once it is installed."
        
        CheckSQLDriver = False
        
        Application.FollowHyperlink Address:="\\MyNetworkPath\Users\Access\SOFTWARE\SQLDriver_ODBC_Ver11\sqlncli.msi", NewWindow:=True
       
    Else
        CheckSQLDriver = True
    End If

End Function

'============================================
Public Sub EnumerateRegEntries(strKeyPath, arrEntryNames, arrValueTypes)
    Const HKEY_CLASSES_ROOT = &H80000000
    Const HKEY_CURRENT_USER = &H80000001
    Const HKEY_LOCAL_MACHINE = &H80000002
    Const HKEY_USERS = &H80000003
    Const HKEY_CURRENT_CONFIG = &H80000005

    Dim objReg              As Object
    Dim strComputer         As String

    strComputer = "."
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\default:StdRegProv")

    objReg.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrEntryNames, arrValueTypes


End Sub
Not all my own code - found and twiddled with from the interwebs.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
mjdemaris (07-06-2018)
Old 07-04-2018, 11:18 AM   #18
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 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
Re: ODBC SQL Server driver

I thought it might be worth expanding on my previous answers.
My approach has much in common with that outlined by Minty

It is several years since I last used the wizard to setup file or machine data source ODBC connections 'manually' - I just have no reason to do so

I always use DSN-less connection strings as that was how I first learned to manage split databases for distribution to clients many years ago.

Until recently, I thought this was the standard approach and to me it is far easier than setting up ODBC connections manually on each machine. In fact it would be impossible to work any other way without visiting each of my clients' sites. I have several clients across the UK who I have never visited because it isn't necessary

To manage the external links I have 2 local tables:
1. tblTableLinkTypes - lists details of all connections to external databases
2. tblTableLinks - lists all the linked tables and the external databases for each

Typically, my FE databases will have links to 2 or more external databases
a) a SQL Server and/or Access BE datafile
b) an Access configuration datafile (used to store settings for each client e.g. school/company)

Some apps also have links to Excel files and/or csv files

However, many have more link types. The screenshot shows the relink tables form for a database with 8 external links (including 2 web databases - now deprecated) though in this example not all are in use at any time



The next screenshot shows a form where details for each link type can be edited



As you can see, this shows 35 records as it includes all link details for each client

Another form is used to edit the details of each linked table.



In this case there are 147 linked tables
The largest database has about 330 linked tables - almost all are in the SQL datafile
If linked tables are added/deleted or renamed e.g. to manage new features in a version update, this is easily handled using this form

Before apps are distributed to clients via my website, all links are removed from the FE.
For a new install, clients are guided through the process of adding link types (SQL or Access) and the form in the first screenshot is used to relink all tables.
Details of the link types for that client are added to the Access configuration file and are automatically emailed to me for inclusion in future updates

For a version update, the link types details are recovered from the config file and the links recreated.

Relinking takes less than 30 seconds for the largest database of around 330 tables

Of course, the relinking needs to be done on one PC only and the new/updated FE is then distributed to users' workstations without any additional configuration needed on each user's workstation. From memory, I have only once needed to assist a client with obtaining new SQL Server drivers. Normally, the required drivers are already installed on the client PC so the process is brainless

To me, this is so straightforward that I find it hard to understand why it isn't the standard approach used by all developers. If there is an easier way of managing split databases for use with with many clients each having multiple workstations, I would like to know about it!

HTH
Attached Images
File Type: png EditTableLinks.PNG (23.5 KB, 103 views)
File Type: png RelinkTablesForm.PNG (62.1 KB, 104 views)
File Type: png ManageTableLinksForm.PNG (99.6 KB, 101 views)

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

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.

Last edited by isladogs; 07-05-2018 at 06:30 AM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
mjdemaris (07-06-2018)
Old 07-05-2018, 09:18 AM   #19
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: ODBC SQL Server driver

I see, that makes sense if the runtime version uses the "SQL Server" driver.
At this time, I do not use runtime. I remember attempting to use it earlier, but for some reason it did not work well. All of our machines currently have a full Office version, now...but if I could get a runtime to work and connect without additional drivers...
I may look into that, especially when I want to use tablets - this would be great for taking inventory and receiving incoming freight at the other end of the plant.
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-06-2018, 12:30 PM   #20
mjdemaris
Working on it...
 
Join Date: Jul 2015
Location: Washington State, USA
Posts: 362
Thanks: 80
Thanked 7 Times in 7 Posts
mjdemaris is on a distinguished road
Re: ODBC SQL Server driver

While typing my last post, I did not notice there were two pages to this discussion, which is why it doesn't seem to flow with the convo.
Colin,
your management system looks nice. Do you also have a way of:
1) seeing who is currently online,
2) kicking users to update the BE
3) notifying users that the update is complete and available for use?

We have about 45 machines that I would like to use a system like yours to manage the connections, and FE/BE updates.

From what I gather, it sounds easier to use DSN-less connections, use code to determine driver installed, and use tables to store the remote/local table names and target path (backend tables).

I need to ponder on this for a bit.

I definitely would like some kind of management/admin form(s).
__________________
Life is not a problem to be solved, but a reality to experience.

Soren Kierkegaard
mjdemaris is offline   Reply With Quote
Old 07-06-2018, 01:02 PM   #21
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 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
Re: ODBC SQL Server driver

Quote:
Originally Posted by mjdemaris View Post
Colin,
your management system looks nice. Do you also have a way of:
1) seeing who is currently online,
2) kicking users to update the BE
3) notifying users that the update is complete and available for use?

We have about 45 machines that I would like to use a system like yours to manage the connections, and FE/BE updates.

From what I gather, it sounds easier to use DSN-less connections, use code to determine driver installed, and use tables to store the remote/local table names and target path (backend tables).

I need to ponder on this for a bit.

I definitely would like some kind of management/admin form(s).
In answer to the above
1. Yes - list of current users visible to adminstrators
2. Yes - users can be kicked out after a specified warning period when necessary. The system sends messages to all staff currently online and prevents new users logging in during the time needed for the update/system maintenance.
Updates to the SQL BE are normally done using a script. In many cases, this can be done without kicking users out. Otherwise, a scheduled task can often be done overnight
3. Yes - After running the update, a new message can be sent to inform users the system is available again

NOTE: a similar system is also used to update the BE from external data sources each night. This is done automatically using a scheduled task at e.g. 2am

I also have code to
a) inform admin users that a new version is available from my website
b) automatically download updated versions of the FE to end users from a network location

The above code is used in several of my school based apps and has been thoroughly tested over a number of years
I have published sections of my code in various places including this forum
However, I do not currently have a complete version available as a 'standalone utility' that I could upload.
It would take time to extract the code and so I would need to charge for my time.
If you want to discuss that further, please send me a PM or email me using the link in my signature line

45 machines is no problem. The system has been used for 200+ machines in more than one client school for over 10 years

I think it is MUCH easier to use DSN less connections which is why I always use that method. I regularly use it for connecting linked tables in Access, SQL Server, Excel spreadsheets & CSV files. I've also used XML files and more. I've never used PostgreSQL or MySQL but both should work fine AFAIK

Yet for whatever reason, it appears to be far less widely used than using the wizard to connect to ODBC data sources

After you've had a ponder, feel free to get back to me

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

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.

Last edited by isladogs; 07-06-2018 at 01:50 PM.
isladogs is offline   Reply With Quote
Old 07-06-2018, 03:20 PM   #22
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,967
Thanks: 13
Thanked 1,535 Times in 1,461 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: ODBC SQL Server driver

Quote:
but if I could get a runtime to work and connect without additional drivers...
If you review my earlier comments -- the default driver "SQL Server" will work IF and ONLY IF you are not using any data types that came into existence AFTER around the year 2000. This is a VERY OLD driver. As long as you have control over the data types of columns (dates in particular are a problem), then you won't have a problem. If you can't control the data types and someone has used a data type not supported by the default driver, then you have to locate the necessary driver and see that it is installed on ALL user computers. This has nothing to do with whether you are using the runtime or full version. The default driver is always installed with Access and you are on your own if you need something else.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-07-2018, 06:34 AM   #23
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,085
Thanks: 110
Thanked 2,739 Times in 2,500 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
Re: ODBC SQL Server driver

I've just had a look at the list of ODBC data sources on 2 Win 10 machines with Access 2010:

a) a tablet which doesn't have SQL Server
b) a workstation which also has SSMS 2014

The tablet has a list of basic drivers for Access/SQL Server/Excel/Oracle/Paradox/Text which is what Pat was referring to.
These are installed with Access (or perhaps by Windows as Galxiom suggests)

The workstation has all the above items plus three more for SQL Server (highlighted).



As I've never added any of these manually, these must have been added when SSMS was installed (as I originally suggested back in post #8)

The following shows the SQL Server items from my list of services



Looking again at my example connection string from post #8 may indicate I'm only using the basic SQL Server driver.
Whatever the reason, all my apps with SQL Server BE files always work 'out of the box' with no additional configuration on each workstation

As a side note, can anyone tell me what 'Treiber' stands for in the list of ODBC sources
Attached Images
File Type: png ODBC drivers.PNG (30.4 KB, 32 views)
File Type: png SQL Server Services.PNG (26.3 KB, 31 views)

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

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.

Last edited by isladogs; 07-07-2018 at 01:35 PM.
isladogs is offline   Reply With Quote
Reply

Tags
odbc , sql server

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC Driver 13.1 for SQL Server - new verson released Rx_ Modules & VBA 0 04-21-2018 02:12 PM
ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object TBC General 2 04-14-2011 09:46 AM
ODBC Driver KenHigg General 3 10-19-2006 11:19 AM
Access DB and ODBC SQL Server Driver cbe Tables 0 10-15-2004 05:41 AM
[ODBC Driver Manager] Data source name not found and no default driver specified cft Queries 2 04-16-2002 06:50 AM




All times are GMT -8. The time now is 04:05 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