Where does the Frontend store the Backend's address?

[raziel3]

I know there must be a system table or something that stores the Backend address. How do I find it?

The Problem:
The database being used, the Backend is stored on a server and the Frontend is on the user's PC. The user wants to add some stuff to a report, I am half way across the country but I do have copies of the Backend and Frontend on my computer.

Remote desktop/Anydesk etc is out of the question.

My solution (or if someone can suggest something better):
-Make the customizations on my copy of the Frontend
-Located the address of the User's Backend
-Physically type in the User's Backend address in my Frontend's copy
-Email my modified Frontend to the User.

 

[theDBguy]

Check the MSysObjects table.

PS. Sorry, the above answer was just after reading your original question. After reading the rest of your post, what I think you really need is an auto-relink code. Basically, it doesn't matter where the BE is stored. You make the changes on your copy and when the user gets the updated version, it automatically connects to their BE file, or at least prompts them to identify it.

You can't really just enter it as Access needs to physically connect to it to update that information.

 

[The_Doc_Man]

In a table, there is such a thing as the ".Connect" string property which, for a native Access linked back-end, will contain the full device, path, name, and file type of the file containing the named table. The linked table in this specific case will be established using the connection via Windows File Sharing protocols (SMB or Server Message Block). The name of the table linked in the front end will (I think MUST) match the name of the table actually contained in the back end.

Note that for a non-Access back end, table linking is FAR more complicated than that.

Your solution might be better if you could somehow establish an RDP-style solution. Remote Desktop would make this operation easy enough. The problem with e-mailed corrections is how you would do it and how you would apply it. You would do better to make your changes and then perform an "export to text" for the things you had to change.

Note that if you had to do a table change, you might have to learn about DDL, the data definition language, which allows you to edit an extant table from commands that could be executed by the SQL processor inside Access. If you ALSO have to change data in a table, you might have to dump that via a query to some useful format such as (but not limited to) a .CSV file for subsequent reimportation.

Access generally allows you to export AND IMPORT text files in several different formats and for different purposes.

You can export queries to text as an SQL string. So no problem deleting and reloading a text query. In fact, except in unusually complex queries, that SQL can be small enough to do a cut/paste operation.

You can export a form as text. Therefore you can delete a form and reload by importing the updated text file.

You can export a report as text. So you can delete and reload a report as previously mentioned.

You can export a module as text. So again you can delete and reload a module. Note that if this is a form or report's class module, that delete/reload operation would be extremely likely to break event links leading to the event-related code, thus forcing you to revisit the report and form event lists to reassert those event links.

To be honest, I never play with macros so I would have to guess that you can export macros to text.

 

[Pat Hartman]

This query will get you linked tables. The location of the BE .accdb is in the Database column

SELECT MSysObjects.Connect, MSysObjects.Database, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Connect) Is Null) AND ((MSysObjects.Type)=6));

 

[MajP]

Here is a demo relinker. There are other examples on the forum if you search.

Setting Database default paths

It really wasn't a big deal. I don't expect to be "spoon fed" with this stuff. Sometimes you just have to get in there and sort it! It was the modules that got me. I didn't realise until I got into the code that they were required. Not to worry though. It's a good piece of code.

www.access-programmers.co.uk

 

[raziel3]

Here is a demo relinker. There are other examples on the forum if you search.

Setting Database default paths

It really wasn't a big deal. I don't expect to be "spoon fed" with this stuff. Sometimes you just have to get in there and sort it! It was the modules that got me. I didn't realise until I got into the code that they were required. Not to worry though. It's a good piece of code.

www.access-programmers.co.uk

Do you have a 64 bit version?

Private Declare Function WinHelp Lib "user32.dll" Alias "WinHelpA" (ByVal hWnd As Long, ByVal _
lpHelpFile As String, ByVal wCommand As Long, ByVal dwData As Long) As Long

Error at this line

 

[theDBguy]

Maybe try the one from J Street as well.

Developer Downloads - J Street Technology

These free developer downloads are for our fellow developers – we hope you find them useful! If you use a download for commercial purposes all we ask is that you keep the J Street Technology references in the code. Are you stuck on your project? Need help with development? For mentoring and...

jstreettech.com

 

[gemma-the-husky]

If it's not clear from the above, there is no generic "back end location". Each table carries it's own information about the back end connection, so every table could be connected differently. If a table has a blank connect property/string, it's a local table and not in a different database.

 

[bastanu]

Here is a simple one that would relink the front-end to the back-end located in the same folder. So all you need is to have a local settings table (useful for many other things) that stores the name of the back-end (without the path) and has a Yes\No field to force the relinking.
Call this function from the first line of your AutoExec macro (you need a macro as calling from an event of a startup form might not work if the form is bound to a linked table).

Public Function vcRelinkFront_End()
If DLookup("RelinkOnLoad", "tblSettings") = False Then Exit Function
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strBE As String

strBE = DLookup("BackEndName", "tblSettings")
Set dbs = CurrentDb()
 
'we always attempt to relink the back-end in the current front-end folder
For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
     tdf.Connect = ";DATABASE=" & CurrentProject.path & "\" & strBE
     tdf.RefreshLink
    End If
Next tdf
dbs.Execute "UPDATE tblSettings SET RelinkOnLoad=False;", dbFailOnError
Set dbs = Nothing

MsgBox "DB - Front End has been relinked to " & CurrentProject.path & "\" & strBE, vbExclamation, "DAB DB"
End Function

Now all you need to to is set the RelinkOnLoad flag to True when you send the front-end and instruct the receiver to open it for the first time in the same network location where they have the back-end.

Cheers,

 

[GPGeorge]

Do you have a 64 bit version?

Private Declare Function WinHelp Lib "user32.dll" Alias "WinHelpA" (ByVal hWnd As Long, ByVal _
lpHelpFile As String, ByVal wCommand As Long, ByVal dwData As Long) As Long

Error at this line

You can modify 32 bit APIs to run under 64 bit Access; it' well worth learning how to do that.

 

[isladogs]

Do you have a 64 bit version?

Private Declare Function WinHelp Lib "user32.dll" Alias "WinHelpA" (ByVal hWnd As Long, ByVal _
lpHelpFile As String, ByVal wCommand As Long, ByVal dwData As Long) As Long

Error at this line

64-bit compatible version

Private Declare PtrSafe Function WinHelp Lib "user32" Alias "WinHelpA" (ByVal hwnd As LongPtr, ByVal lpHelpFile As String, _
     ByVal wCommand As Long, ByVal dwData As LongPtr) As Long