UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Rename An Access Database To The Single Table In It, 2007 probably    
 
   
stevep
post Mar 2 2019, 06:11 AM
Post#1



Posts: 91
Joined: 9-November 14



I am using WalMarts Retail Link for data extractions. There is an option to export to Access in the definition of the extraction. I am not sure the version but I suspect it is 2007 because the other choices are Excel 2007 and Excel .xls

Example

Running Retail Link report "S08 Whs Stock" exports to some randomly named accdb file in the browsers download folder. This randomly named accdb file contains a single table the same as the report name which is S08 Whs Stock.

I would like to change the randomly named accdb ( call it SomeRandomName.accdb) to the name of the table so this database becomes S08 Whs Stock.accdb

Am I better to do this in VBS (how?) or have some helper Access DB with some VBA do it (how)?

I assume I would have to use a file picker dialog to open the accdb, loop through the tabledefs, extract the table name and rename the original database. Or copy to a new db which has the desired name and delete the original.

Actually, there are 8 reports that have to be processed this way and the resulting 8 db with 8 tables are imported to a single 9th db (RetLink.accdb) for the actual users. But I want to learn this renaming process anyway for the ad hoc reports I sometimes use.
This post has been edited by stevep: Mar 2 2019, 06:20 AM
Go to the top of the page
 
June7
post Mar 2 2019, 02:12 PM
Post#2



Posts: 655
Joined: 25-January 16



Review https://www.devhut.net/2010/09/27/ms-access-vba-rename-file/

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Doug Steele
post Mar 2 2019, 03:33 PM
Post#3


UtterAccess VIP
Posts: 22,181
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'll just point out that you cannot rename the .accdb file while it's open, which means that your code needs to exist in another database, open the external database through code, determine the table name, then close the external database before you can rename it.

Not particularly difficult to do, but just something to keep in mind!

Good luck. smile.gif

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
June7
post Mar 2 2019, 04:48 PM
Post#4



Posts: 655
Joined: 25-January 16



I expect a VBScript can run the rename code. But then how to execute the VBScript? Perhaps set a Desktop shortcut to the .vbs file?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Doug Steele
post Mar 2 2019, 05:03 PM
Post#5


UtterAccess VIP
Posts: 22,181
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Much as I like VBScript (and yes, you could create a desktop shortcut to run it), I don't believe you'd be able to use the Windows File Open dialog to allow the user to select the database. I'd probably create a small Access application that did the work (perhaps even with tables to store the details of what work it did when, just in case some sort of audit trail was required).

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
DanielPineault
post Mar 2 2019, 06:55 PM
Post#6


UtterAccess VIP
Posts: 6,719
Joined: 30-June 11



Here something to get you going

CODE
Dim oAccess
   Dim db
   Dim tdf
   Dim sTdfName
   Dim sOriginalDb
   Const sDbPath = "C:\Users\Daniel\Desktop\"
   Const sDbOrignalName = "Database"
   Const sDbFileExtension = "accdb"
   Const sDbLockFileExtension = "laccdb"
  
   sOriginalDb = sDbPath & sDbOrignalName & "." & sDbFileExtension
      
   set oAccess = CreateObject("Access.Application")
   oAccess.OpenCurrentDatabase sOriginalDb
   Set db = oAccess.CurrentDB
  
   For Each tdf In db.TableDefs
    if Left(tdf.Name, 4) <> "MSys" Then sTdfName = tdf.Name
   Next
      
   oAccess.Quit
   Set tdfNothing = Nothing
   Set db = Nothing
   Set oAccess = Nothing
  
   Do while FileExists(sDbPath & sDbOrignalName & "." & sDbLockFileExtension)
    PauseScript
   Loop
   Call RenameFile(sOriginalDb, sDbPath & sTdfName & "." & sDbFileExtension)
  
  
   Sub PauseScript()
    dteWait = DateAdd("s", 1, Now())
    Do Until (Now() > dteWait)
    Loop
   End Sub
  
   Function FileExists(sFile)
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    If oFSO.FileExists(sFile) Then
     FileExists = True
    End If
    Set oFSO = Nothing
   End Function
  
   Function RenameFile(sOrignalFile, sNewFile)
    Dim oFSO
    Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
    oFSO.MoveFile sOrignalFile, sNewFile
    Set oFSO = Nothing
   End Function

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Mar 2 2019, 07:05 PM
Post#7


UtterAccess VIP
Posts: 6,719
Joined: 30-June 11



I should mention that the above was originally designed as a vbscript. As Doug already pointed out you can't rename an active db. It could also be integrated within any VBA compatible program (Excel, Access, ...).

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
June7
post Mar 3 2019, 02:16 AM
Post#8



Posts: 655
Joined: 25-January 16



Here is another method to allow user navigation and selecting file.
CODE
Set wShell=CreateObject("WScript.Shell")
Set oExec=wShell.Exec("mshta.exe ""about:<input type=file id=FILE>" & _
      "<script>FILE.click();new ActiveXObject('Scripting.FileSystemObject').GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);</script>""")
sFileSelected = oExec.StdOut.ReadLine
MsgBox sFileSelected
Set oFSO = Nothing

This post has been edited by June7: Mar 3 2019, 02:21 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Mar 3 2019, 03:44 AM
Post#9



Posts: 655
Joined: 25-January 16



Can't edit previous post any more so here is revision to suggested code. Might want to add error handler in case user cancels the file dialog.
CODE
Set wShell=CreateObject("WScript.Shell")
Set oExec=wShell.Exec("mshta.exe ""about:<input type=file id=FILE>" & _
      "<script>FILE.click();new ActiveXObject('Scripting.FileSystemObject').GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);</script>""")
sFileSelected = oExec.StdOut.ReadLine
Dim oFSO
Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
If InStr(sfileSelected, ".accdb") > 0 Then
    oFSO.MoveFile sFileSelected, "C:\somepath\S08 Whs Stock.accdb"
End If
Set oFSO = Nothing

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 09:54 AM