Greebles or network changes? (1 Viewer)

[snow-raven]

I have a really hard time sometimes figuring out why code that has worked for years suddenly stops working for apparently fundamental reasons without my making any changes to the database.

For this one, I have a form code that essentially copies table data from one database to another while arranging things to make sure that all of the child table contents are linked to the correct parent table items. It goes through a bunch of checks related to database versions and other QC and has worked perfectly for me for three years, but all of a sudden the starting import target file selection and table import steps aren't working because it doesn't like how I call my database name.

Here's the relevant code at the moment:

'--------------------Select database to import contents-----------------------------------
Const msoFileDialogFilePicker As Long = 3
Dim fDialog As Object
Dim varFile As Variant
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      .InitialFileName = CurrentProject.Path & "\"
      .AllowMultiSelect = False
      .Title = "Please select the database for transfer"
      .Filters.Clear
      .Filters.Add "Access Databases", "*.ACCDB"
      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
      If .Show = True Then
         filename = Dir(.SelectedItems(1))
      Else
      End If
   End With
  
   Confirm = MsgBox("Import selected tables to database? (You will need to accept the next 20 warning messages because Microsoft is dumb.)", vbYesNo, "Import All?")

Set db = CurrentDb()
If Confirm = vbYes Then 'User chose 'Yes' to import
'    On Error Resume Next
'    DoCmd.SetWarnings False
    Call CheckDbVersion
    'Debug.Print dbVersion
    If dbVersion >= 3.34 Then
        'Debug.Print filename
        '---------------DATABASE INFO--------------------------------------------------
        DoCmd.TransferDatabase acImport, "Microsoft Access", filename, acTable, "Database_Info", "TempDatabase_Info", False 'Project intervals import

I now get an error that says my destination doesn't exist, which is loco because this is an acImport, it shouldn't exist yet. However, if I replace filename with the literal path string (Y:\somefolders\Sample_Database_23458 Project Name.accdb), the imports work as expected.

So, right now filename is typically ending up something like "Sample_Database_23458 Project Name.accdb". I can't recall whether it included the full filepath originally, which I think should be the outcome from my msoFileDialogFilePicker, yes?

A couple of things that have changed on the backend:
- At some point I standardized all of my system's connected drives to lettered drives (from \\someserver\PROJECTS\2023 to J:\PROJECTS\2023) for important reasons that I can't remember,
- Our IT recently transitioned us to a new server that is different in ways beyond my realm of expertise,
- And they've even more recently made some security changes that interfere with things like whether I need a security certificate to allow some macros.

Could something like this be the reason my filename variable is no longer sufficient, or have I misunderstood the file-picker routine for three years DURING WHICH IT UNACCOUNTABLY WORKED PERFECTLY?!?!?!

Thanks for any support you can throw my way on a day I did not plan to spend troubleshooting...

 

[MajP]

It either never worked or you put Dir in front. You need a full path don't you?

 Debug.Print Dir(.SelectedItems(1)) & " With Dir"
           Debug.Print .SelectedItems(1) & " No Dir"
           FileName = .SelectedItems(1)

Example23.accdb With Dir
C:\Users\John\Downloads\Example23.accdb No Dir

 

[snow-raven]

It either never worked or you put Dir in front. You need a full path don't you?

I swear it's been working for months, but it's hard to argue that it shouldn't have. Luckily, fixing it also solved today's fun Microsoft Trust Certificate issues, which miraculously evaporated when I stepped through it.

Thanks for your help!