Using VBA? [My THINKING] to LINK a BE

[GPGeorge]

The J Street Relinker has served a lot of developers very well over many years.

Here's the copyright notice for it in the version I use. Note the starting date.

'--------------------------------------------------------------------
'
' Copyright 1995-2009 J Street Technology, Inc.
' www.JStreetTech.com
'
' This code may be used and distributed as part of your application
' provided that all comments remain intact.
'
' J Street Technology offers this code "as is" and does not assume
' any liability for bugs or problems with any of the code. In
' addition, we do not provide free technical support for this code.

 

[mloucel]

The J Street Relinker has served a lot of developers very well over many years.

Here's the copyright notice for it in the version I use. Note the starting date.

'--------------------------------------------------------------------
'
' Copyright 1995-2009 J Street Technology, Inc.
' www.JStreetTech.com
'
' This code may be used and distributed as part of your application
' provided that all comments remain intact.
'
' J Street Technology offers this code "as is" and does not assume
' any liability for bugs or problems with any of the code. In
' addition, we do not provide free technical support for this code.

Thank you Sir.,

 

[mloucel]

For many years I've included code in front end databases' unbound opening form which checks to determine if the current links are valid. I have a number of different versions of this for different contexts. The attached zip archive includes a RefeshLinksSingle_07.accdb file which illustrates the simplest version, which assumes that the back end is a single Access .accdb file.

To use this in your database you'd add the code from the demo to your front end's opening form. This must either be unbound or bound to a local table in the front end. If you are currently using a form bound to a table in the back end as the opening form, then you'd need to add a little unbound form similar to that in the demo, with a Continue button or similar to open the current bound opening form.

You'd also need to add the BackEndLocation table, the frmUpdate_Links form and the basBrowse module to your front end.

When a front end file with invalid links is opened, the frmUpdate_Links form will open at start-up, allowing the user to browse to a back end file to refresh the links. This can be tested in the demo by making a copy of any .accdb file, and adding a linked table from it, using the frmAddLinkedTable form. Then move the linked file to a new location and open the demo again. To change from one back end to another the frmUpdate_links file can be opened at any time, provided none of the linked tables are currently in use.

Thank you Ken, I have checked also what Colin sent me, so far I think your approach is simpler and easy to follow, I am trying both codes, but I suspect that with a little bit of tweak I can work with your code, I want to avoid, completely any interaction with the EU, I need the code to do it automatically without any interaction, I will check both codes nevertheless, but I really like the simplicity of your code, is easy to follow and I believe easy to adapt it to my needs.

Thank you.

Maurice

 

[Ken Sheridan]

I want to avoid, completely any interaction with the EU, I need the code to do it automatically without any interaction

If you know the paths to the alternative back ends, then it would be an easy matter to set the tabledef objects' CONNECT property at start-up to that appropriate to the current user based on their Windows login name. You'd just need a table of all authorised user names related to a table of connect strings. The user's login name can be obtained with the GetUser function from the following module:

' module basGetUser
Option Compare Database
Option Explicit

Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
   lpBuffer As String, nSize As Long) As Long

Public Function GetUser() As String

   Dim strBuffer As String
   Dim lngSize As Long, lngRetVal As Long
  
   lngSize = 199
   strBuffer = String$(200, 0)
  
   lngRetVal = GetUserName(strBuffer, lngSize)
  
   GetUser = Left$(strBuffer, lngSize - 1)

End Function

 

[mloucel]

If you know the paths to the alternative back ends, then it would be an easy matter to set the tabledef objects' CONNECT property at start-up to that appropriate to the current user based on their Windows login name. You'd just need a table of all authorised user names related to a table of connect strings. The user's login name can be obtained with the GetUser function from the following module:

' module basGetUser
Option Compare Database
Option Explicit

Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
   lpBuffer As String, nSize As Long) As Long

Public Function GetUser() As String

   Dim strBuffer As String
   Dim lngSize As Long, lngRetVal As Long
 
   lngSize = 199
   strBuffer = String$(200, 0)
 
   lngRetVal = GetUserName(strBuffer, lngSize)
 
   GetUser = Left$(strBuffer, lngSize - 1)

End Function

Thank you Ken.

 

[isladogs]

The J Street Relinker has served a lot of developers very well over many years.

Here's the copyright notice for it in the version I use. Note the starting date.

'--------------------------------------------------------------------
'
' Copyright 1995-2009 J Street Technology, Inc.
' www.JStreetTech.com
'
' This code may be used and distributed as part of your application
' provided that all comments remain intact.
'
' J Street Technology offers this code "as is" and does not assume
' any liability for bugs or problems with any of the code. In
' addition, we do not provide free technical support for this code.

George
Just for info, there is also a newer version of the J Street Relinker app

 

[mloucel]

George
Just for info, there is also a newer version of the J Street Relinker app

Yes I got that one as well, thanks .

 

[GPGeorge]

George
Just for info, there is also a newer version of the J Street Relinker app

Good point. I used that reference primarily to point out it's been around a long time, 30 years.

I actually don't use it much, if at all, anymore because most of my personal databases have SQL Server back ends.

 

[isladogs]

Good point. I used that reference primarily to point out it's been around a long time, 30 years.

I actually don't use it much, if at all, anymore because most of my personal databases have SQL Server back ends.

If I had known about it 20+ years ago, I would probably have used the J Street Relinker rather than create my own version.
Having said that, creating code for yourself is enormously beneficial both in terms of enhancing skills and personal satisfaction