Broken References when switching back and forth in MS Access 07-16

[oxicottin]

Ok, I have a database at work on our server and only one person is on it at a time and only about 5 people use it total and the odds of two people on it at the same time are slim to none and everyone here has 2007 MS access. Now one of the employees got a new PC with a upgraded version of Access 2016 and when they open the database it changes the references to 16.0 Object Library from 12.0 and it works on that machine then when the users on the machines with the 2007 access open the DB none of the querys work ect because of the broken reference to Object Library 12.0 and Irun a debug and im missing for some reason Microsoft Outlook 12.0 Object Library. Once I uncheck the broken 16.0 broken reference and get the Microsoft Outlook 12.0 Object Library everything works in 2007 again until the user in access 2016 opens it again.

Here is my question, can I clear broken references and install Microsoft Outlook 12.0 Object Library using VBA On Load of the DB? eventually we all will have the 2016 but that wont be for another year or so...

Thanks,

Thanks,
SoggyCashew.....

 

[dhookom]

Isn't your application split into front-end and back-end files with everyone getting their own copy of the front-end?

You can typically use "late binding" to avoid this type of issue.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Duane it wasnt split and i tried to split it and had a user with a copy of 2016 on thier PC open it and it worked then I went on my PC which has 2007 and it had the broken reference 16.0. This was checked on the be



Thanks,
SoggyCashew.....

 

[MajP]

As mentioned, late binding should work, but you really should run all shared DBs as split for performance reasons, compatibility, and to avoid corruption. It is very easy to corrupt a DB in this manner even if it is unlikely that 2 or more people are in at the same time.

 

[oxicottin]

Ok the split and putting the FE on the PC got rid of the reference issue now I have a different issue. My database uses images from a folder that was always with the database and now that the FE is on the desktops and the BE is separate I don't get images using the function below...

Public Function GetCurrentPath() As String

         'Gets path of current DB
           GetCurrentPath = Application.CurrentProject.Path '& "\"
           
End Function

Thanks,
SoggyCashew.....

 

[dhookom]

The path is data and should be stored in a table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

If the files are always stored in the same folder as the backend tables then you can do it as follows

Public Function GetBackendPath(LinkedTableName As String) As String
     Dim connection As String
     Dim DB_PathAndFile As String
     connection = CurrentDb.TableDefs(LinkedTableName).Connect
     DB_PathAndFile = Mid(connection, 11)
     GetBackendPath = Left(DB_PathAndFile, InStrRev(DB_PathAndFile, "\"))
End Function

Pass in the name of one of the linked tables to the function.

 

[oxicottin]

Thank you!

Thanks,
SoggyCashew.....

 

[MajP]

BTW you can make that function more generic. You first would check if it is a linked table or not
If Len(connection) > 0 Then it is linked
Then add an if then. If it is a linked table do what I showed, if not not use your original function for current path.

 

[oxicottin]

Majp, I couldn't get your example to work for the images, the below worked... Whats the diference?

Public Function GetCurrentPath() As String

'Gets path of current BE table txtVWI, Used  all images
'Put images in with BE file

    Dim strFullPath As String
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs("tblVWI").Connect, 11)
    GetCurrentPath = Left(strFullPath, InStrRev(strFullPath, "\"))
End Function

Thanks,
SoggyCashew.....

 

[dhookom]

I still wouldn't ever assume the path was going to be related to the location of some other file. Someone will come along and expect the image files to move to a totally different shared location. All you should have to do is change the value of one field in one table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oxicottin]

Do you guys have a easy to use auto updater for if I want to update the database? Right now I have the BE on server in a folder named VWI Backend and I have the FE in a folder named VWI FE and I have a master copy of the FE on my PC. Now lets say I want to update the BE and distribute how can I do this the easy way? Please explain I watched some videos and downloaded examples and im not getting it... I tried tonys auto updater and sent an email and it installs a shortcut of the FE on your desktop and im not sure if that was correct because from what I understand the FE is a copy of the DB not a shortcut..

Thanks,
SoggyCashew.....

 

[Darrylles]

I always define a 'standard', 'generic' folder structure for all of my MS Access applications.
E.g. Always:
/app_name

/data​

/supp - (supplementary data files)​

/hlp - (help documents)​

/doc - (documentation)​

/tec - tech spec​

/bus - business spec​

etc. etc.
That way, EVERY application can determine EXACTLY where specific network file objects reside simply from either the FE or BE and using 'CurrentProject.Path' - regardless of the individual application.
This also ensures that 100% of all documentation / data is found in one location.

D