I have written some code that generates a Batch File from Excel (2003) to copy a Backend File and another db file. Then I call a macro in the db file that relinks it to the Backend file on the users desktop. This was to speed up the process of pulling data. If I step through it it's fine but when I run it the tables aren't linked. What am I missing other than an easier way to do this? Or, how can I make this work? Any help would be great!
Here is my code in the XL workbook.
Option Explicit
Sub GetBE()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim strBatFile As String
Dim strWorkHorsemdb As String
Dim strCopyTo As String
Dim struser As String
Dim strBEmdb As String
Dim strKillFile As String
Dim strpath As String
Dim db As Object
struser = Environ("USERNAME")
strCopyTo = ActiveWorkbook.Path & "\"
strKillFile = ActiveWorkbook.Path & "\Work Horse.mdb"
strWorkHorsemdb = ActiveWorkbook.Path & "\Work Horse.mdb"
strBEmdb = "C:\Documents and Settings\" & struser & "\Desktop\KCB_BE.mdb"
' sets the file name of the batch file to create
strBatFile = ActiveWorkbook.Path & "\CopyBE.cmd"
' creates the batch file
Open strBatFile For Output As #1
Print #1, "Echo Off"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "COPY ""\\xxxxxx\common\BDW\KCB_BE.mdb"" ""C:\Documents and Settings\" & struser & "\Desktop\"""
Print #1, "COPY ""\\xxxxxx\common\BDW\Utilities\Work Horse.mdb"" ""C:\Documents and Settings\" & struser & "\Desktop\"""
Print #1, ""
Close #1
' runs the batch file
Shell strBatFile 'This executes my Batch file
Application.StatusBar = "Verifying Local Tables..."
'Making sure Files are copied to the Desktop
Do Until FileThere(strWorkHorsemdb) = True
Loop
Do Until FileThere(strBEmdb) = True
Loop
Application.StatusBar = "Local Tables verified..."
Application.StatusBar = "Linking Local Tables..."
strpath = LCase(Environ("USERPROFILE"))
strpath = strpath & "\Desktop\Work Horse.mdb"
' Get a reference to Access
Set db = CreateObject("Access.Application")
'Open database...
db.OpenCurrentDatabase strpath
'Hide Access app...
db.Visible = False
db.DoCmd.RunMacro "M_LinkTables"
' Close the database.
db.CloseCurrentDatabase
'Quit Access.
db.Quit
'Free up memory
Set db = Nothing
Application.StatusBar = "Local Tables Linked and refreshed..."
End Sub
Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function
Here is the Function I have in my Access db (Work Horse.mdb) that is being called by the macro "M_LinkTables"
Option Compare Database
Option Explicit
Public Function RelinkTables()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
Dim NewPathname As String
Dim struser As String
struser = LCase(Environ("USERNAME"))
NewPathname = "C:\Documents and Settings\" & struser & "\Desktop\KCB_BE.mdb"
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Function
Here is my code in the XL workbook.
Option Explicit
Sub GetBE()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim strBatFile As String
Dim strWorkHorsemdb As String
Dim strCopyTo As String
Dim struser As String
Dim strBEmdb As String
Dim strKillFile As String
Dim strpath As String
Dim db As Object
struser = Environ("USERNAME")
strCopyTo = ActiveWorkbook.Path & "\"
strKillFile = ActiveWorkbook.Path & "\Work Horse.mdb"
strWorkHorsemdb = ActiveWorkbook.Path & "\Work Horse.mdb"
strBEmdb = "C:\Documents and Settings\" & struser & "\Desktop\KCB_BE.mdb"
' sets the file name of the batch file to create
strBatFile = ActiveWorkbook.Path & "\CopyBE.cmd"
' creates the batch file
Open strBatFile For Output As #1
Print #1, "Echo Off"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "COPY ""\\xxxxxx\common\BDW\KCB_BE.mdb"" ""C:\Documents and Settings\" & struser & "\Desktop\"""
Print #1, "COPY ""\\xxxxxx\common\BDW\Utilities\Work Horse.mdb"" ""C:\Documents and Settings\" & struser & "\Desktop\"""
Print #1, ""
Close #1
' runs the batch file
Shell strBatFile 'This executes my Batch file
Application.StatusBar = "Verifying Local Tables..."
'Making sure Files are copied to the Desktop
Do Until FileThere(strWorkHorsemdb) = True
Loop
Do Until FileThere(strBEmdb) = True
Loop
Application.StatusBar = "Local Tables verified..."
Application.StatusBar = "Linking Local Tables..."
strpath = LCase(Environ("USERPROFILE"))
strpath = strpath & "\Desktop\Work Horse.mdb"
' Get a reference to Access
Set db = CreateObject("Access.Application")
'Open database...
db.OpenCurrentDatabase strpath
'Hide Access app...
db.Visible = False
db.DoCmd.RunMacro "M_LinkTables"
' Close the database.
db.CloseCurrentDatabase
'Quit Access.
db.Quit
'Free up memory
Set db = Nothing
Application.StatusBar = "Local Tables Linked and refreshed..."
End Sub
Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function
Here is the Function I have in my Access db (Work Horse.mdb) that is being called by the macro "M_LinkTables"
Option Compare Database
Option Explicit
Public Function RelinkTables()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
Dim NewPathname As String
Dim struser As String
struser = LCase(Environ("USERNAME"))
NewPathname = "C:\Documents and Settings\" & struser & "\Desktop\KCB_BE.mdb"
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Function
