Solved File Location Not Matching Up Issue (1 Viewer)

EzGoingKev

Registered User.
Local time
Yesterday, 21:32
Joined
Nov 8, 2019
Messages
178
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:

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?
 
Last edited:

MarkK

bit cruncher
Local time
Yesterday, 18:32
Joined
Mar 17, 2004
Messages
8,178
You have typos. Add an Option Explcit statement, and in the VBA IDE, 'Require Variable Declaration.'
 

EzGoingKev

Registered User.
Local time
Yesterday, 21:32
Joined
Nov 8, 2019
Messages
178
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

You've got your good things, and you've got mine.
Local time
Yesterday, 21:32
Joined
May 21, 2018
Messages
8,463
declare.jpg
 

EzGoingKev

Registered User.
Local time
Yesterday, 21:32
Joined
Nov 8, 2019
Messages
178
I checked off the "Require Variable Declaration" but I am still getting the same error message.
 

bastanu

AWF VIP
Local time
Yesterday, 18:32
Joined
Apr 13, 2010
Messages
1,401
Can you please try this:
Code:
filePath = "C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\" & Dir("C:\Users\EzGoingKev\OneDrive - My Company Name\Access\@VCdb\VCdb_*.accdb")
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:32
Joined
Feb 19, 2002
Messages
42,981
I would not change the file name every month. Just replace the old version with the updated version. If you want to keep track of the version, add a table inside the database.
 

EzGoingKev

Registered User.
Local time
Yesterday, 21:32
Joined
Nov 8, 2019
Messages
178
Can you please try this:
Code:
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

AWF VIP
Local time
Yesterday, 18:32
Joined
Apr 13, 2010
Messages
1,401
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):
Code:
DoCmd.RunSQL "INSERT INTO " & TU.Name & " SELECT * FROM [" & filePath & "].[" & Mid(TU.Name, 6) & "];"
Cheers,
Vlad
 

EzGoingKev

Registered User.
Local time
Yesterday, 21:32
Joined
Nov 8, 2019
Messages
178
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

AWF VIP
Local time
Yesterday, 18:32
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Yesterday, 21:32
Joined
Nov 8, 2019
Messages
178
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.
 

Users who are viewing this thread

Top Bottom