------ english version at the end ------------

Hi,

automatisches Reconnect von Access zum SQL Server

Idee dahinter: Ein automatischer Reconnect wird nur durchgeführt, wenn die als Test-Tabelle definierte Tabelle nicht richtig verbunden ist.

Der Name der Testtabelle wird in "PropInhalt" von _tblProperty als Text mit dem PropName "prp_SQLCheckTabelle" gespeichert

Diese Demo zeigt, wie man sich nach der korrekten Einrichtung einfach von sql Server wieder verbindet:
Diese Demo enthält:

- ReadMe Auto Relink to SQLServer.txt (diese Datei)

- TestConnect_SQL.mdb acc2000 Beispiel-MDB-Datei (gespeichert von 32 Bit 2013 Access accdb)

- crea_TestConnect.sql.txt Ein automatisch erstelltes SQL-Skript zum Erstellen einer Beispiel-Backend-MDF-Datei

WICHTIG: Bitte bearbeiten die

( NAME = N'TestConnect', FILENAME = N'C:\SQL_2017_Ex\Data\TestConnect.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TestConnect_log', FILENAME = N'C:\SQL_2017_Ex\Data\TestConnect_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Zeilen des Scripts passend zu der bei Ihnen verwendeten Systemkonfiguration.

Ich habe kein Backup verwendet, da mein System SQL Server Express 2017 ist und eine Wiederherstellung nicht abwärtskompatibel ist (soweit ich weiß)
aber dieses Skript sollte in fast jedem SQL Server ab 5 arbeiten ??

--------

Die folgenden Dinge müssen im Voraus festgelegt werden:

Erstellen Sie das Test-Back-End mit dem Skript.

MS Access:
Öffnen Sie zum ersten Mal die mdb mit Shift, oder Sie erhalten alle möglichen unangenehmen Fehlermeldungen.

Verbindungszeichenfolge
Für eine einfache Handhabung (sobald es eingerichtet ist) wird die verwendete Verbindungszeichenfolge gesplittet gespeichert.
Der Hauptteil ist in der Tabelle "Acc_SQL_Server" gespeichert
ACHTUNG - Es ist sehr wichtig, dass das letzte Zeichen immer ein ";" ist, wie dort gezeigt ...

Da ich die private Funktion atcnames (1) <Benutzername> oder atcnames (2) <pcname> aus Modul mdlVerbindeSQL verwende, kann ich "meinen eigenen" Server finden, der im Feld "Server_OBD" gespeichert ist, ansonsten wird der Kundenserver, Feld "Server_Kunde" verwendet.
Feld "Server" ist ein Ausgabefeld, in dem der tatsächlich verwendete Server automatisch gespeichert wird.

Ein App_Name wird in VBA über APPName erstellt
fAppName = "APP =" & atCNames (1) & "\" & atCNames (2) & "\" & Nz (DAOARRAY1 (5, 0)) <wobei 5 = das Feld Appname>
Loginname wird von diesem neu geschrieben
App_Name kann im SQL Server mittels "SELECT App_Name ()" verwendet werden

Last but not least: "ConnectionstringT1", die den ersten Teil der Verbindungszeichenfolge enthält

ODBC;Driver={SQL Server Native Client 11.0};Trusted_Connection=Yes;

Natürlich muss man diesen mit dem von Ihnen verwendeten Teil ersetzen ...

Der letzte Teil der Verbindungszeichenfolge ist der Datenbankname und die zu verbindende Tabelle.

Sie finden sie in der Tabelle "Acc_SQL_tblVerknüpfungstabellen"

Um eine Verbindung von verschiedenen SQL-Datenbank-Backends herstellen zu können, wird der Datenbankname in dieser Tabelle gespeichert.

Aus praktischen Gründen wird die Eigenschaft "prp_Standard_DBName" in der Tabelle _tblProperty in "PropInhalt" gespeichert.
Es enthält den Namen der Haupt-Back-End-Datenbank.
Dies wird verwendet für die

Funktion GetConnectionstring ()

Das finden Sie im Modul "mdlSonstiges4" Es bringt Ihnen den kompletten Verbindungsstrang unabhängig von einer verwendeten Tabelle.
 (aber deshalb vielleicht in einigen Fällen fehlerhaft). Ich persönlich benutze diese Funktion ziemlich oft.

Die Tabelle "Acc_SQL_tblVerknüpfungstabellen"

Um MS zu zitieren: >>> You can use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as Microsoft® SQL Server™, that does not already have an index. You do not need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. Creating a pseudo-index on a table that would ordinarily be read-only can be especially useful. <<<

tblName - Name wie in MSAccess angezeigt
tblName_Org - Ursprünglicher Name, der im SQL Server (Tabelle oder View) verwendet wird
jn - Wenn nicht Ja angekreuzt ist, wird die Tabelle überhaupt nicht verknüpft
Indexfkt - Indexanweisung (wie oben erwähnt) - Beispiel:
CREATE UNIQUE INDEX A_Detail_Prim ON qry_A_Details (DE_ID)
ID - automatisch erstellte ID-Nummer als PK
IDSort - Reihenfolge, in der die Verbindung erstellt wird (optional, wenn 0, dann wird ID verwendet)
Bemerkungen - Bemerkungen (optional / nicht verwendet)
DBName - DBName der Backend-Datenbank, in der die Tabelle gespeichert ist -
Wichtig: Der String >DATABASE = <name>; muss mit einem ";" enden, da als Teil von Connectionstring verwendet

Die Tabelle tbl_Connectionstring enthält als ID 1 nur den erzeugten ConnectionString (wie in GetConnectionString())
Und wird bei jedem Reconnect erstellt (wenn Tabellen geändert wurden oder das Makro Reconnect_BE (SQL)_Tables verwendet wurde)

Wenn alle Einstellungen korrekt sind, schließen und öffnen Sie die Datenbank erneut.

Das Autoexec-Makro führt nur die Funktion aus dem Modul mdl_Autoexec aus

Funktion f_Autoexec ()
checkconnectSQL
MsgBox "Tabellen sind korrekt verbunden"
Ende Funktion

Mit dem Makro "Reconnect_BE (SQL) _Tables" erzwingen Sie eine erneute Verbindung (auch wenn alles in Ordnung zu sein scheint)
zum Beispiel, wenn Sie die BE-Tabellen geändert haben ...

Es führt die Funktion =DatenMDBWechselSQL() im mdlVerbindeSQL-Modul aus.

All das "Heavy Lifting" erfolgt im mdlVerbindeSQL-Modul, mdlSonstiges4 und mdlSonstigesJasNein enthalten lediglich mehrere Helferfunktionen.
Ich verwende meistens die Funktion ArrFill_DAO_Acc, die eine Tabelle (Recordset / SQL String) in ein Array einfügt.
Kopieren Sie einfach die Kommentarzeilen am Anfang der Funktion (Dim ArrFill ... bis End If) in den eigentlichen Code und entfernen Sie dort die Kommentarzeichen ...

Das vollständige Beispiel finden Sie hier im Unterverzeichnis >SQL_Server Auto Relink tables queries to ACCESS<

-------------------------------------------------------------------------------------------------------------------------

Hi,

Automatic Reconnect from Sql Server BE to MSAccess

Idea: Only reconnect again, if one >test table< is not connected properly (unsuccessful open test)
The name of the test table is stored in "PropInhalt" of _tblProperty as Property with the PropName "prp_SQLCheckTabelle"

This demo will show how to easily reconnect from sql server, once set up properly:
This demo contains:

- ReadMe Auto Relink to SQLServer.txt (this file)

- TestConnect_SQL.mdb acc2000 Sample mdb file (saved from 32 bit 2013 Access accdb)

- crea_TestConnect.sql.txt An auto-created SQL script for creating a sample backend mdf-file

IMPORTANT: Pls edit the
( NAME = N'TestConnect', FILENAME = N'C:\SQL_2017_Ex\Data\TestConnect.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TestConnect_log', FILENAME = N'C:\SQL_2017_Ex\Data\TestConnect_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
lines according to your system

I didn't use a backup, because my system is SQL Server Express 2017 and a restore is not backwards compatible (as far as i know)
but this script should work on nearly each mdf starting from 8a ??

--------

The following things have to be set up in advance:

Create the Test Backend with the script.

MS-Access:
First time open the mdb with shift, or you'll get all sorts of nasty errormessages.

Connection string
For easy handling (once it is set up) the used connection-string is split stored.
The main part is stored within the table "Acc_SQL_Server"
PAY ATTENTION - It is most important that the last char always is a ";" as shown there ...

As i use the private function atcnames(1) <username> or atcnames(2) <pcname> from module mdlVerbindeSQL i can detect "my own" server which is stored in field "Server_OBD" otherwise the customer server, field "Server_Kunde" is used.
Field Server is an output field, it stores the actual used server automatically.

An App_Name is created in VBA via APPName
fAppName = "APP=" & atCNames(1) & "\" & atCNames(2) & "\" & Nz(DAOARRAY1(5, 0)) <where 5 = the field Appname>
Loginname is rewritten from that
App_Name can be used in SQLServer "SELECT App_Name()"

Last but not least "ConnectionstringT1" which contains the first part of the connectionstring

ODBC;Driver={SQL Server Native Client 11.0};Trusted_Connection=Yes;

Of course you have to replace it whith the connectionstring-part you use ...

The last part of the connection-string is the database-name and the table to connect.

You'll find them in table "Acc_SQL_tblVerknuepfungstabellen"

For beeing able to connect from varius SQL database backends, the database-name is stored within that table.

For convenience the Property "prp_Standard_DBName" of the table _tblProperty is stored in "PropInhalt"
It contains the Main Backend Database name.
This is used for

function GetConnectionstring()

Which you'll find in modul "mdlSonstiges4" It brings you the complete connectionstring independant of a used table.
(but therefore maybe incorrect in some cases). I personally use that function quite often.

The table "Acc_SQL_tblVerknuepfungstabellen"

To quote MS: >>> You can use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as Microsoft® SQL Server™, that does not already have an index. You do not need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. Creating a pseudo-index on a table that would ordinarily be read-only can be especially useful. <<<

tblName - Name as shown in MSAccess
tblName_Org - Original name, used in SQL Server (table or view)
jn - If not Yes, table is not linked at all
Indexfkt - Index statement (as mentioned above) - Sample:
CREATE UNIQUE INDEX A_Detail_Prim ON qry_A_Details (DE_ID)
ID - autocreated ID number as PK
IDSort - order in which the connection is created (optional, if 0, then ID is used)
Bemerkungen - remarks (optional / not used)
DBName - DBName of the backend database where the table is stored -
Important: String >DATABASE = <name>;< Ending with ; - as used as part of Connectionstring

The table tbl_Connectionstring as ID 1 just contains the the created ConnectionString (as in GetConnectionString() )
And is created on each connect (when tables changed)

If all settings are correct close and reopen the database.

The Autoexec Macro just execs the function

Function f_Autoexec()
checkconnectSQL
MsgBox "Tables are connected correctly"
End Function

found in Module mdl_Autoexec

You force a reconnect (even if everything seems OK) with macro "Reconnect_BE (SQL)_Tables"
for example if you changed the BE-tables ...

It execs the function =DatenMDBWechselSQL() found in mdlVerbindeSQL module

All the "heavy lifting" is done in mdlVerbindeSQL module, mdlSonstiges4 and mdlSonstigesJasNein just contain several helper funktions.
I mostly use the function ArrFill_DAO_Acc which puts a table (recordset / SQL String) into an array.
Just copy the comment lines at the beginning of the function (Dim ArrFill... to End If) in your actual code and uncomment them there...

You'll find the complete sample here in Subdirectory >SQL_Server Auto Relink tables queries to ACCESS<