To simplify the Access VBA 64bit issue, I've put a database online here that provides the key features to work in Access 32 as well as in Access 64bit. The database works in all versions between Access 2010 and Access 2019 (32 and 64bit).

PS: I've "spiced up" the form "frm_Font_Symbol_Wingdings" with a Fontsearch VBA ", I think now quite practical, the report is also available ...

The form _frmHlp_Sysinfo automatically saves the values per user in the tbl_SysInfo when exiting.

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. <<<

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:

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 stringFor 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 thatApp_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 MSAccesstblName_Org - Original name, used in SQL Server (table or view)jn - If not Yes, table is not linked at allIndexfkt - Index statement (as mentioned above) - Sample:CREATE UNIQUE INDEX A_Detail_Prim ON qry_A_Details (DE_ID) ID - autocreated ID number as PKIDSort - 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)

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...

The European GDPR is a good reason to convince a customer to change to SQL Server Express.There you can use the TemporalTables as an automatic generated Logging for personal data.(Two of my customers only because of that changed to SQL Server 2017 Express)

Beginning with 2016 you can use so called temporal tables. (= Automatic History for the records)When used in 2017 or later, then automatic a Retention Period is set

New Version available

Most things can be parametrisized. (Look into the text of the SP)

According to "my default" which is a) History Table is named: <tablenamne>_History and is created in same schema than "original" table.b) HistTbl_ValidFrom and HistTbl_ValidTo are the names of the two hidden mandatory date-fields c) SQL 2017 only: Retention Period is set in proc to 24 month

Just tested in 2017 Express Edition: When the temporal table is created, automatically a PK is added so that the *new* SQL 2017 functionality "History Retention" just works.This proc is set to a fixed 24 month retention time, but you can change that easily in proc.Proc: "History_Retention_Period = 24 MONTHs"

With this 3 Procs, the use of Temporal Tables is "a snap"

Permanent deletion: As you need to delete the two default-constrains for HistTbl_ValidFrom and HistTbl_ValidTo before you can delete the fields itself,the delete Proc only works, if the default-constrains are created with the Create proc, otherwise the constrain-names may be different.

PS: In SSMS the right mouse context menu changes drastically, if a table is "System-Versioned" as they call it.Hint: If you open the tree of the table, the first entry is the History table. (If not: Refresh)As this table is NOT "System-Versioned" it has nearly the "normal" right mouse context menu with at least the Select ScriptsIf you use that Select scripts and you just delete the "_History" in FROM then you have the Select for the "Main-Table"

The three scripts and some basic views (also newer version - including the 3 scripts) which I find extremely practical, can be found here in Onedrive in Subdirectory TemporalTables:

after nearly abundonning the very good keyboard / mouse set 5050 of MS - only and exclusively because of the "Shift key" in the German keyboard layout was too small (an impudence to design something like that ...), because every >Second >Word in >Caps looked like this - I decided on a radical solution:

Called the MS Shop by phone and ordered the same 5050 keyboard set but with US layout (which was not so easy over here in Germany). Parallel to this, i ordered.QWERTZ keyboard stickers (12 * 12) as well as a blank set (without imprint) as well as a 0.8 mm white ink pen, waterproof.Then I made my own keyboard by means of adhesive keys and software. The result is perfect for me.

Looking back, I could have saved myself the money for a new keyboard: For the 5050 following solution would have worked: "<" button converted as a Shift button, a new keyboard layout as described below and only painted the 3 missing characters on the keyboard with pen scripted by hand. (but that hint arrived too late...)..

Since finding and creating the optimal solution was relatively time-consuming (most of the time it took up searching and testing), I thought an "HowTo" could interest others too.

Here is the result:

1.) You have to create and activate your own keyboard layout (your own keyboard in the system)2.) Afterwards you can also disable the unspeakable Caps Lock button for this new layout3.) After putting the keyboard into operation, you have to adjust the hardware with keyboard stickers.4.) SharpKeys.exe does NOT help here (though always talked about).

After a long trial, I found out that you can only create additional AltGR keys using the older program of MS: Keyboard Layout Creator 1.4 (except for system-oriented hardcore programming freaks); but with this program it is very easy and fast:Download currently: https://www.microsoft.com/en-us/download/details.aspx?id=22339

Disadvantages of the program: Separate Numlock-Block not editable - Caps-Lock not editable.But you do not need those things for this task anyway.

If you think through the things properly, you have "only" to compensate the missing keys "<" ">" and "|", everything else does the German layout automatically for you.Therefore, I have these three keys distributed as follows:"<" on AltGr y ">" on AltGr x and "|" on AltGr to the right of the ß (s-Zet) key (the key with 'and `).(Then all the "US" characters are in an AltGr series)

Meaningful before: Notepad open and with <left Alt key>: 0060 the "<" then generates two lines lowerwith Alt 0062 the ">" and with Alt 0124 the "|" Characters (needed only because of working temporarily with a "wrong" layout).

After the installation and the first call of the program you load the German standard keyboard layout

(File / "Load existing Keyboard")German

You then see the standard German layout. Then turn on the check mark "Alt + Ctrl (AltGr)" on the left. Thus one sees only the level Altgr. Then you click on the letter box for which you want to create the character and enter the respective character there (here is possibly cut & paste from the text file helpful).

That's it with the creation itself, since the so-called Scans for the remaining keys (no matter what language) are all identical and the remaining keys are "German standard" ...We'll take care of the annoying CapsLock button later on, at this point no action makes sense.

So now comes the second part:

Project / "Test Keyboard Layout"

Setting up your own keyboard. Since one can easily mop the system with "scrap keyboards" by this step, if not planned carefully in beforehand, and these are not easy to remove, one should VERY thoroughly check the keyboard before doing the "final" creation step.

IMPORTANT: Under Project / Vaidate Layout -VERY IMPORTANT: The following question, whether the memory text and location should be checked again, in any case AGREEDThen a form opens in which you can enter the keyboard key name(up to 8 characters - system-wide unique) and the description (also system-wide unique)Another problem is that you can only generate a project with an existing key / description pair ONCE. MS does not overwrite an existing project, but closes with an error message !!!!!!!!!!!

After verifying and testing "your keyboard layout", the next step is: "Build DLL and Setup Package". Under the "Current working Directoty", a subdirectory is generated with the keyboard key as name and the setup is stored - if build was successful.

The next step is "Deploying" the setup on your own computer: Run Setup. Then a new standard keyboard with the above mentioned name (as description in system) is created.

The next step is to add your new keyboard and remove the "old" keyboard in the system under "Language" Language Options / Keyboards.