Linked tables

[rjoshi2]

(1) How can I check to see if the linked tables are working correctly when the database is opened?
(2) If there is a problem with the linked tables then I would like to delete and recreate the linked table.
(3) Is there a better way to make sure the linked tables are always working then the one I have come up with? Any help would be appreciated. (I already have a way to for the user to open a dialog box and point the database that has the tables. What I am trying to due is hardcode the linked table if they cannot be opened to a default setting that should work.)

Thank You
rjoshi2

Here is my code for part 2 of my question:

Function hardCoded()
Dim deleted As String
Dim todayDate As Date
Dim currentDay As Integer
'Get system's date
' todayDate = Date
' Extracting Day from todayDate
' currentDay = Day(todayDate)
DoCmd.SetWarnings False

'If currentDay = 9 Or currentDay = 18 Or currentDay = 27 Then
'hard coded table links
'If the directory is change where the data file is kept
'Then the path has to be change to reflect the new location

deleted = "DROP TABLE [Core Data Dump]"
DoCmd.RunSQL deleted
DoCmd.TransferDatabase acLink, "Microsoft Access", "\\Psc2\Data\Ocio\SHARED\Acquisitions\Back end of PSC IT Acquisition Log\PSC IT Acquisition Data File.mdb" _
, acTable, "Core Data Dump", "Core Data Dump"

deleted = "DROP TABLE [PSC IT Acquisition Log]"
DoCmd.RunSQL deleted
DoCmd.TransferDatabase acLink, "Microsoft Access", "\\Psc2\Data\Ocio\SHARED\Acquisitions\Back end of PSC IT Acquisition Log\PSC IT Acquisition Data File.mdb" _
, acTable, "PSC IT Acquisition Log", "PSC IT Acquisition Log"

deleted = "DROP TABLE [tblObjClsDesc]"
DoCmd.RunSQL deleted
DoCmd.TransferDatabase acLink, "Microsoft Access", "\\Psc2\Data\Ocio\SHARED\Acquisitions\Back end of PSC IT Acquisition Log\PSC IT Acquisition Data File.mdb" _
, acTable, "tblObjClsDesc", "tblObjClsDesc"

deleted = "DROP TABLE [tblObjects]"
DoCmd.RunSQL deleted
DoCmd.TransferDatabase acLink, "Microsoft Access", "\\Psc2\Data\Ocio\SHARED\Acquisitions\Back end of PSC IT Acquisition Log\PSC IT Acquisition Data File.mdb" _
, acTable, "tblObjects", "tblObjects"

deleted = "DROP TABLE [tblObjectsInSystem]"
DoCmd.RunSQL deleted
DoCmd.TransferDatabase acLink, "Microsoft Access", "\\Psc2\Data\Ocio\SHARED\Acquisitions\Back end of PSC IT Acquisition Log\PSC IT Acquisition Data File.mdb" _
, acTable, "tblObjectsInSystem", "tblObjectsInSystem"

DoCmd.SetWarnings True
'End If
End Function

 

[Guest_imported]

An easy way to check the status of a linked table would be to try a simple query on it in your code. If you get an error, trap it and use the error code to determine the nature of the problem.

Also, with regard to your code I would be inclined to rewrite this as a function that takes two parameters (source database and table name) and then call that function five times rather than step through the same sequence of lines five times. The code might look something like

Function fctnRelink(strDb as String, strTbl as string)
  DBEngine(0)(0).Execute "DROP TABLE [" & strTbl & "];"
  DoCmd.TransferDatabase acLink, "Microsoft Access", strDb, acTable, strTbl, strTbl
End Function

 

[rjoshi2]

Mr. P I tried your code but I keep getting an error the compiler is looking for an equal sign when I call the fctntlink. Could look at my code and tell me what I am doing wrong.

Thank You,
rjoshi2

My code:

Private Sub Form_Load()
Dim strDB2 As String
Dim strTbl2 As String
'To Hide the Database Window
DoCmd.RunCommand acCmdWindowHide
myCommandBar 'create a commandbay

'strDB2 = "PSC IT Acquisition Data File"
strDB2 = "\\Psc2\Data\Ocio\SHARED\Acquisitions\Back end of PSC IT Acquisition Log\PSC IT Acquisition Data File.mdb"

strTbl2 = "Core Data Dump"

fctnRelink(strdb2, strtbl2)
End Sub

Function fctnRelink(strDB As String, strTbl As String)
DBEngine(0)(0).Execute "DROP TABLE [" & strTbl & "];"
DoCmd.TransferDatabase acLink, "Microsoft Access", strDB, acTable, strTbl, strTbl
End Function

 

[mp9]

You don't need the brackets around the parameters, i.e. in your form load code replace

fctnRelink(strdb2, strtbl2)

with

fctnRelink strdb2, strtbl2