How to run vba for another databse (1 Viewer)

Zandyboy · Jan 23, 2019

Hello,

I am tasked with providing code for backup of linked tables. These tables are supposed to be saved in a new database but as a local file.
I have the code for transferring the linked tables into a new database.

Sub BackUp()

Dim dTime As Date

On Error Resume Next
dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
If Err.Number <> 0 Then Exit Sub

Do Until Time >= dTime
DoEvents
Loop

MsgBox "Time to create a backup"

Dim sFile As String, oDB As DAO.Database

sFile = CurrentProject.Path & "" & Format(Date, "m-d-yy") & ".accdb"
If Dir(sFile) <> "" Then Kill sFile

Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
oDB.Close

Dim oTD As TableDef

DoDmd.Hourglass True
For Each oTD In CurrentDb.TableDefs
If Left(oTD.Name, 4) <> "MSys" Then
DoCmd.CopyObject sFile, , acTable, oTD.Name
End If

Next oTD
DoCmd.Hourglass False

MsgBox "Backup is stored in the same folder"

End Sub

I have the code for changing linked tables into local tables:

Sub convertLinkedToLocal()
On Error Resume Next
Dim db As DAO.Database, td As DAO.TableDef
Set db = CurrentDb
' Check each table definition
For Each td In db.TableDefs
' If a linked table (source is outside of the current db) . . .
If td.SourceTableName <> "" Then
' Select the table and convert to local
DoCmd.SelectObject acTable, td.Name, True
DoCmd.RunCommand acCmdConvertLinkedTableToLocal
End If
Next

End Sub

My Question is: How do i run the seconde code (for transferring linked into local tables) from my source databse for my backup databse.
I want to do this so i do not have to touch my backup database at all.

sneuberg · Jan 23, 2019

While this not a complete answer to your question you might be able to use the shell command to do this. This would open the new database. You would have to copy an autoexexec and a module to it that would do whatever you want done before doing this. Below is some code that I used to open another database.

Code:

Private Sub RunUpdater_Click()

Dim strPath As String
Dim strCaller As String
Dim strShell As String
Dim strArgs As String
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "INSERT INTO [VersionTracker] (VersionName) VALUES ('Testing');"
db.Close
OpenTable
strArgs = Application.CurrentProject.Name & ";Z:\UpdaterDatabaseBackend\"
strPath = Application.CurrentProject.Path
strCaller = strPath & "\UpdateDatabase.accdb"
strShell = "MSAccess.EXE """ & strCaller & """" & " /cmd " & strArgs
AppActivate Shell(strShell, vbMinimizedNoFocus)
Application.Quit

End Sub

I'm curious though about why you just don't backup the entire database.

Zandyboy · Jan 23, 2019

Thanks for your reply I was hoping for someting easier. I thought the value of "Set db" could be changed.

I need the linked tables to be local so I dont lose the data when the Sources of the linked tables go down. Due to business we might not be able to connect to these sources for a while so I need the information locally.
When I use the backup function of access the linked tables stay linked so it's not an option.
My co worker wants to make easy backups by selecting one single macro that does it all so to say.

MajP · Jan 23, 2019

Code:

Private Sub TestOpen2()
     Const fileName = "DB2.ACCDB"
     Dim db2 As Object
     Dim db2Form As Access.Form
     Dim strPath As String
     strPath = CurrentProject.Path
     strPath = strPath & "\" & fileName
     Set db2 = GetObject(strPath, "Access.Application")
    'Run code in DB2
     db2.DoCmd.OpenForm "form1"
     Set db2Form = db2.Forms!Form1
     db2Form.OnClose = "[Event Procedure]"
End Sub

Zandyboy · Jan 23, 2019

Hi MajP,

this works almost perfect thank you so much.
Unfortunately I get a Runtime Error for the line

"db2.DoCmd.OpenForm "Form1"

Where exectly should I implement my code in yours? Sorry if these questions are obvious I'm very rusty in vba

MajP · Jan 23, 2019

Code:

"db2.DoCmd.OpenForm "Form1"

Sorry that is just an example of opening another DBs form.

Code:

Private Sub TestOpen2()
     Const fileName = "YourDBNameHere.ACCDB"
     Dim db2 As Object
     Dim db2Form As Access.Form
     Dim strPath As String
     strPath = CurrentProject.Path  'Or a real path. This is the same location
     strPath = strPath & "\" & fileName
     Set db2 = GetObject(strPath, "Access.Application")
    'Run code in DB2
     db2.runupdater
End Sub

theDBguy · Jan 23, 2019

Zandyboy said:
I am tasked with providing code for backup of linked tables.

Hi Zandyboy. If all you need is to create a backup copy of the backend data file, then maybe you can avoid all that by trying out this other approach.

Zandyboy · Jan 30, 2019

theDBguy said:
Hi Zandyboy. If all you need is to create a backup copy of the backend data file, then maybe you can avoid all that by trying out

Hi theDBGuy,

unfortunately the DB is still in progress. In the code I have to name specific names of the tables which is almost impossible since they change from time to time. The other method just scans all tables and exports them so it works better for me.

theDBguy · Jan 30, 2019

Zandyboy said:
theDBguy said:

Hi Zandyboy. If all you need is to create a backup copy of the backend data file, then maybe you can avoid all that by trying out

Hi theDBGuy,

unfortunately the DB is still in progress. In the code I have to name specific names of the tables which is almost impossible since they change from time to time. The other method just scans all tables and exports them so it works better for me.

Click to expand...

Hi. If you say so. Good luck!

Acropolis · Jan 30, 2019

Zandyboy said:
theDBguy said:

Hi Zandyboy. If all you need is to create a backup copy of the backend data file, then maybe you can avoid all that by trying out

Hi theDBGuy,

unfortunately the DB is still in progress. In the code I have to name specific names of the tables which is almost impossible since they change from time to time. The other method just scans all tables and exports them so it works better for me.

Click to expand...

To make life easier and not have to change the code all the time, why not create a table with all the table names in you want to backup and just loop through that as a record set, means you don't have to keep updating the code and worry about version etc. and you can very easily add/remove tables from your backup.

Zandyboy · Feb 6, 2019

Acropolis said:
Zandyboy said:

To make life easier and not have to change the code all the time, why not create a table with all the table names in you want to backup and just loop through that as a record set, means you don't have to keep updating the code and worry about version etc. and you can very easily add/remove tables from your backup.

Click to expand...

This actually sounds like a good idea. Unfortunately I do not have a lot of time for the project right now but will implement in the future. Thanks!

Db-why-not · Jun 22, 2020

I am trying to use this code in my database and I keep getting error message "Compile Error: User- defined type is not defined" when I run the module. Then it highlights the code db As DAO.Database. I'm still a VBA beginner so I don't know if there is something else I need to do with the code to work within my database.

Code:

Sub convertLinkedToLocal()
On Error Resume Next
 Dim db As DAO.Database, td As DAO.TableDef
 Set db = CurrentDb
 ' Check each table definition
 For Each td In db.TableDefs
   ' If a linked table (source is outside of the current db) . . .
   If td.SourceTableName <> "" Then
     '   Select the table and convert to local
     DoCmd.SelectObject acTable, td.Name, True
     DoCmd.RunCommand acCmdConvertLinkedTableToLocal
   End If
 Next
End Sub

theDBguy · Jun 22, 2020

Db-why-not said:
I am trying to use this code in my database and I keep getting error message "Compile Error: User- defined type is not defined" when I run the module. Then it highlights the code db As DAO.Database. I'm still a VBA beginner so I don't know if there is something else I need to do with the code to work within my database.

Code:

Sub convertLinkedToLocal() On Error Resume Next Dim db As DAO.Database, td As DAO.TableDef Set db = CurrentDb ' Check each table definition For Each td In db.TableDefs ' If a linked table (source is outside of the current db) . . . If td.SourceTableName <> "" Then ' Select the table and convert to local DoCmd.SelectObject acTable, td.Name, True DoCmd.RunCommand acCmdConvertLinkedTableToLocal End If Next End Sub

Hi. In the VBA window, go to Toos > References and post a screenshot of what you see.

Isaac · Jun 22, 2020

Zandyboy said:
I am tasked with providing code for backup of linked tables. These tables are supposed to be saved in a new database but as a local file.
I have the code for transferring the linked tables into a new database.

Assuming your database is split into FE and BE as it should be, why not just backup the back end database in the first place?

Gasman · Jun 22, 2020

pisorsisaac@gmail.co said:
Assuming your database is split into FE and BE as it should be, why not just backup the back end database in the first place?

Isaac, the first post was over 16 months ago.? I do not think you will get a reply?

Isaac · Jun 22, 2020

@Gasman
Yikes, I hadn't noticed that. Thank you for pointing that out to me.

Gasman · Jun 22, 2020

pisorsisaac@gmail.co said:
@Gasman
Yikes, I hadn't noticed that. Thank you for pointing that out to me.

Easily done. Old threads get resurrected by new(ish) members regularly.

Db-why-not · Jun 22, 2020

I tried to go to references by ut was greyed out and wouldn't let me open it. I have attached a screenshot of my code with the error message.

theDBguy said:
Hi. In the VBA window, go to Toos > References and post a screenshot of what you see.

theDBguy · Jun 22, 2020

Db-why-not said:
I tried to go to references by ut was greyed out and wouldn't let me open it. I have attached a screenshot of my code with the error message.

Hi. I mean, go to the code window without running your code.

How to run vba for another databse (1 Viewer)

New member

AWF VIP

New member

You've got your good things, and you've got mine.

New member

You've got your good things, and you've got mine.

I’m here to help

New member

I’m here to help

Registered User.

New member

Registered User.

I’m here to help

Lifelong Learner

Enthusiastic Amateur

Lifelong Learner

Enthusiastic Amateur

Registered User.

Attachments

I’m here to help

Similar threads

Users who are viewing this thread