Solved File Location Not Matching Up Issue

[EzGoingKev]

Good morning all.

I have some code that updates some local tables from one Access db into another.

The source db is replaced with a new version monthly. The current file name is "VCdb_AUGUST_2021.accdb".

I added "VCdb_" to the table names in the destination db.

This is the code:

Option Explicit

Function UpdateVCdbTables()

On Error GoTo ErrorHandler

DoCmd.Hourglass (True)
DoCmd.SetWarnings (False)

Dim filePath As String: filePath = Dir("C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\VCdb_*.accdb")

    Dim TU As TableDef
        For Each TU In CurrentDb.TableDefs
            If TU.Name Like "VCdb_*" Then
                DoCmd.RunSQL "DELETE * FROM " & TU.Name
                DoCmd.RunSQL "INSERT INTO " & TU.Name & " SELECT * FROM " & Mid(TU.Name, 6) & " IN " & filePath
            End If
        
    Next TU

      
Error_Exit:
    Exit Function

ErrorHandler:
    MsgBox Error$
    Resume Error_Exit


End Function

When I run this I am getting the following error message : "Could not find the file 'C:\Users\EzGoingKev\VCdb_AUGUST_2021.accdb'. I am not sure what is going on here. "C:\Users\EzGoingKev\" is not the location I specified as the filePath but "VCdb_AUGUST_2021.accdb" is the correct file name though.

Can anyone help me with this?

 

[MarkK]

You have typos. Add an Option Explcit statement, and in the VBA IDE, 'Require Variable Declaration.'

 

[EzGoingKev]

You have typos. Add an Option Explcit statement, and in the VBA IDE, 'Require Variable Declaration.'

The typos were in the post because I realized I could simplify the code after looking at it here. I fixed them in my post.

I changed it from Option Compare to Option Explicit.

I do not know what "in the VBA IDE, 'Require Variable Declaration." means.

 

[MajP]

 

[EzGoingKev]

I checked off the "Require Variable Declaration" but I am still getting the same error message.

 

[bastanu]

Can you please try this:

filePath = "C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\" & Dir("C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\VCdb_*.accdb")

 

[EzGoingKev]

Can you please try this:

filePath = "C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\" & Dir("C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\VCdb_*.accdb")

I had thought of that. Trying that gave me an error message that had to do with my FROM clause.

 

[bastanu]

Because I don't think you have the syntax right. IN is used for the target table, not the source. Please try this (with the modified filepath variable from post #6):

DoCmd.RunSQL "INSERT INTO " & TU.Name & " SELECT * FROM [" & filePath & "].[" & Mid(TU.Name, 6) & "];"

Cheers,
Vlad

 

[EzGoingKev]

Vlad, the combination of your two things did it.

The interesting thing with the INSERT statement is I use one in a macro to do something similar and I had to write it that way for it to work. That db is on a server so IDK if that is has anything to do with it.

Pat - I want to keep the monthly names.

Thanks for the help guys.

 

[bastanu]

You're very welcome, good luck with your project!

I think if you look closely at the macro you'll see that the IN refers to the external database hosting the target table, not the source.

Cheers,

 

[EzGoingKev]

No. The IN in the macro's SQL statement refers to the db where the source table is. I run that macro at least once a month every month. It has worked 100% for two years.