Access 2000/VB Experts

[mpm32]

I created a DB that needs to be put on our network drive here at work.  When I move the DB to the shared drive, some of the functionality that I created does not work from others pc's. 

I am assuming this is because I had to add in some references when I coded for a password routine that I wrote to allow access to a restricted part of the DB (updates).  I have created other DBs in the past and have not had this problem moving them to the server.  The only difference is the new references.

Can anyone help me?  Is there some code I can use so that I force the references on the other pc's and or the server?

Oh and the DB works when I access it from the server on my pc so I am assuming that the references need to be forced to the individual pc's.

[cdbrown]

I've been doing a lot of vba coding lately for excel and have come across the problem of file paths but will need to see some of the code especially surrounding these files you are referencing - pm if you'd rather not have the code out in public.

[mpm32]

I don't mind posting it here since it's code that I found and modified.  I can't write code but I can sure 'borrow' and modify.

I found and tried to use this exactly as found, I thought that if I made a button that the users had to push it would force the refs;

Code: [Select]

Function FixUpRefs()
    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim intX As Integer
    Dim blnBroke As Boolean
    Dim strPath As String

    On Error Resume Next

    'Count the number of references in the database
    intCount = Access.References.Count
 
    'Loop through each reference in the database
    'and determine if the reference is broken.
    'If it is broken, remove the Reference and add it back.
    Debug.Print "----------------- References found -----------------------"
    Debug.Print " reference count = "; intCount

    For intX = intCount To 1 Step -1
      Set loRef = Access.References(intX)
      With loRef
        Debug.Print " reference = "; .FullPath
        blnBroke = .IsBroken
        If blnBroke = True Or Err <> 0 Then
          strPath = .FullPath
          Debug.Print " ***** Err = "; Err; " and Broke = "; blnBroke
          With Access.References
            .Remove loRef
            Debug.Print "path name = "; strPath
            .AddFromFile strPath
          End With
        End If
       End With
    Next
     '''Access.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
   
  Set loRef = Nothing
 
  ' Call a hidden SysCmd to automatically compile/save all modules.
  Call SysCmd(504, 16483)
End Function


Function AddRefs()
    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim intX As Integer
    Dim blnBroke As Boolean
    Dim strPath As String

    On Error Resume Next
 
    'Loop through each reference in the database
    'Add all references
    Debug.Print "----------------- Add References -----------------------"
   
          With Access.References
            .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
            .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6.dll"
            .AddFromFile "C:\Program Files\Microsoft Office\Office\msacc9.olb"
            .AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll"
            .AddFromFile "C:\Program Files\Common Files\System\ado\msado25.tlb"
            .AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll"
            .AddFromFile "C:\WINNT\System32\stdole2.tlb"
            .AddFromFile "C:\WINNT\System32\scrrun.dll"
          End With
     
  ' Call a hidden SysCmd to automatically compile/save all modules.
  Call SysCmd(504, 16483)
End Function

It didn't work but in the immediate window when I ran the code, this came up;

----------------- References found -----------------------
 reference count =  8
 reference = C:\Program Files\Common Files\System\ado\msador15.dll
 reference = C:\Program Files\Common Files\System\ado\msado26.tlb
 reference = C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
 reference = C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
 reference = C:\WINNT\system32\stdole2.tlb
 reference = C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.DLL
 reference = C:\Program Files\Microsoft Office\Office\MSACC9.OLB
 reference = C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

The problem is I don't know what to do with this information.

I guess I do not understand where the references need to be.  When I create a DB, are the references stored on my PC or with the DB?  I am assuming that they get stored on my PC since in my situation above, I can't get full functionallity of the DB from the server on others PCs.

I'm not really good at this yet, I can't write code but if I find some that might work, I can usually use that and modify it to work.

Thanks in advance for any help that you can give me.

[cdbrown]

So the first function code you posted works out how many references are in the database, in your case it found 8.  Checks if they exist on the system and then display the path if it exists or the broken link.
This is commented out for some reason??
'''Access.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"

2nd Function adds specified file and filepaths to the database.  I'm not sure whether it actually checks if they exist before adding the reference.

The code looks like an example of using functions to add references and then another function to test if the references exist.  If your pc has a specified network name/address and you share those files you could change the
.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
to suit.

But I'm really not sure what the purpose of the functions are?  Can you post the macro which calls these functions?

edit - had a look at some other access example codes and they also use functions - I'm used to seeing sub () which get Functions to do things

[mpm32]

I do believe that the references were missing from the server that I put the DB on.  I think I did fix it.  I created a file with all of the reference .dll, .olb and .tlb files that I copied from my PC.  I then put that file on the server and picked the references making sure that they pointed to the server file I created, not my drive.  It seems to work ok.

[NightGod]

Been a while since I did any DB programming, but I believe the short answer is "they don't have Access installed on their computers and you referenced files that normally are part of Access".

The microsoft newsgroups kick large amounts of ass for things like this, BTW. Bunch of true gurus watch those things.

[SirPoonga]

Yeah, best place to ask these questions is microsoft.public.access and microsoft.public.access.multiuser newsgroups.  You can use google groups (and star your topic to watch for replies).