Solved Possible for excel to connect .accdb if file name changes?

April 22, 2020 at 06:39:30
Specs: Windows 10
Hi all,

I tried looking online but couldn't find anything on this. Basically, I have an Excel spreadsheet where I pulled data from an Access database using the "From Access" button and following the instructions. The database was imported successfully, I added the macros that were needed in the spreadsheet, and making changes in the .accdb data and refreshing works fine too.

However, it seems the accdb file's name is supposed to change, which means connectivity cannot be maintained. The accdb file's name format is simply "Stuff Access Database [Month] [Day]", for example "Stuff Access Database March 23". Is there a way to maintain the connectivity by, say, checking only if the file has "Stuff Access Database" as a prefix, rather than the whole name? No other file in the directory has this same prefix. I understand this is a long shot, but thought I'd try asking any way.

Thanks!


See More: Possible for excel to connect .accdb if file name changes?

Reply ↓  Report •

✔ Best Answer
April 24, 2020 at 13:03:11
I played around a bit, found a sample accdb file to play with and tried some stuff. Since I obviously don't know your work process, I'll just toss this out and maybe we can tweak it to make it work.

Let's say you want to connect to db named "Stuff Access Database" but those darn users keep changing it to "Stuff Access Database May 1" then "My Stuff Access Database May 15", etc.

What if you connected to a db called "My Stuff Access Database" which was always a copy of the most recent version of "Stuff Access Database [Month] [Day]"?

If you ran this macro (with the proper path and file name, of course) before your query or before a refresh, you'd always be connected to a copy of the most recent version.

The following code will Copy the file that starts with "Stuff Access Database" and overwrite the existing "My Stuff Access Database" file:

Sub CreateMyStuff()

Dim fname As Variant
Dim myPath As String

'Find db file using * Wildcard

  myPath = "D:\Users\user_name\rest-of-path\"
  fname = Dir(myPath & "Stuff Access Database*")

'Copy and Rename file

  If fname <> "" Then
    FileCopy myPath & fname, myPath & "My Stuff Access Database" & ".accdb"
  End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
April 22, 2020 at 06:50:44
I can't help with anything directly Access related, but I may be able to help from the Excel side.

How is the accdb accessed. Is it done via a macro? If so, could you post that section of code?

Another thought: Maybe a macro could be used to rename the most current accdb file to a "known name", connect to that, then rename the file back to whatever it's supposed to be?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
April 22, 2020 at 13:00:13
Thank you DerbyDad03 for the reply!

No macro is used for connecting, just a simple and straight forward process. The accdb is initially connected by opening a new sheet in Excel, and in the ribbon, going to Data - Get External Data - From Access. From there, I just browse to the accdb, pick the table I need, and Excel loads it up in table format.

Basically, from this point onwards, I have a spreadsheet which has the information I need. I plan to then use this file as a tracker, applying some macros to it for viewing the data (simple ones like hiding certain rows and columns) and saving it. If I go to Data - Connections, I can see the filename in the Workbook Connections window. If I click Refresh All, the data is updated based on changes made to the table in the accdb file.

The problem of course is that the file name changes from time to time. An easy workaround would be to simply not change the file name. But it`d be better if the connection could be maintained to the same file, even if the file name were to change.

Your suggestion intrigues me, but I`m not sure how that would work. Would the macro run from within excel, grab the original name, rename the accdb to the known name, connect to a filename which will have the known name, then rename said file with the original that was stored? I`d love to try this out, but are there any guides you`d recommend to read up on to implement this?

Thanks!

message edited by mmh9119


Reply ↓  Report •

#3
April 22, 2020 at 17:48:23
re: "The problem of course is that the file name changes from time to time."

As I implied, I know next to nothing about Access, so I have to ask:

Why does the file name change from time to time? Is that a function of Access? Are users saving the database under a new name? Something else? That what intrigues me. ;-)

If I knew that answer, maybe that would lead my thinking in one way or another.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
April 23, 2020 at 06:48:57
Ah, apologies.

Yes, a user manually changes the file name of the database from outside Access. There is no new copy made, meaning that the same file is used. Hope that clears things a bit. :)


Reply ↓  Report •

#5
April 24, 2020 at 13:03:11
✔ Best Answer
I played around a bit, found a sample accdb file to play with and tried some stuff. Since I obviously don't know your work process, I'll just toss this out and maybe we can tweak it to make it work.

Let's say you want to connect to db named "Stuff Access Database" but those darn users keep changing it to "Stuff Access Database May 1" then "My Stuff Access Database May 15", etc.

What if you connected to a db called "My Stuff Access Database" which was always a copy of the most recent version of "Stuff Access Database [Month] [Day]"?

If you ran this macro (with the proper path and file name, of course) before your query or before a refresh, you'd always be connected to a copy of the most recent version.

The following code will Copy the file that starts with "Stuff Access Database" and overwrite the existing "My Stuff Access Database" file:

Sub CreateMyStuff()

Dim fname As Variant
Dim myPath As String

'Find db file using * Wildcard

  myPath = "D:\Users\user_name\rest-of-path\"
  fname = Dir(myPath & "Stuff Access Database*")

'Copy and Rename file

  If fname <> "" Then
    FileCopy myPath & fname, myPath & "My Stuff Access Database" & ".accdb"
  End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
April 27, 2020 at 08:21:19
After some fiddling around and trial and error, its working correctly!

Also, this is probably obvious to you, but was something of note to me. Just to see what would happen if I created another file with the same name and see which one it copied, it copies the one that is alphabetically/numerically ranked from first to last. So between Stuff Access Database June 2 and Stuff Access Database June 22, the macro will choose to copy the one with the June 2 suffix.

Thanks for all your help DerbyDad03, I've got something quite useful in my hands now!

message edited by mmh9119


Reply ↓  Report •

#7
April 27, 2020 at 09:53:55
I was aware that the macro would find the first file in the folder, but I wasn't concerned because you said this:

"There is no new copy made, meaning that the same file is used"

I assumed (always dangerous) that the folder would only contain one file that started with "Stuff Access Database", not multiple "Stuff Access Database" files with different date suffixes.

If multiple files gets to be an issue, you should be able to edit one of the following macros to loop through the folder and find the "Stuff Access Database" with the most recent "created" or "modified" date. I haven't written anything that loops and compares dates, but I'm confident that it could be done. As written, the following macros simply check a single file.

Sub GetDateCreated()

    Dim oFS As Object
    Dim strFilename As String

    'Put your filename here
    strFilename = "D:\rest-of-path\Stuff Access Database.accdb"

    'This creates an instance of the MS Scripting Runtime FileSystemObject class
    Set oFS = CreateObject("Scripting.FileSystemObject")

    MsgBox strFilename & " was created on " & oFS.GetFile(strFilename).DateCreated

    Set oFS = Nothing

End Sub

Sub GetDateCreatedOrModified()

    Dim strFilename As String

    strFilename = "D:\rest-of-path\Stuff Access Database.accdb"

    MsgBox strFilename & " was created or modified on " &  FileDateTime(strFilename)

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
April 28, 2020 at 06:55:11
Ah, I see.

Indeed, though I don't expect the number of files to increase in the immediate future, this could easily change.

Thanks again for sharing your knowledge!


Reply ↓  Report •

Ask Question