ODBC SQL Server driver

[isladogs]

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

 

[Pat Hartman]

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.

 

[isladogs]

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