Does query or Table exist already?

[ssecca]

If possible would somebody share a sample of how to determine if a query or linked table already exist in a database before being re=linked or created.

Thanks ssecca

 

[evalesthy]

Module:

Function IsTable(DbName As String, TName As String) As Integer
Dim db As Database
Dim Found As Integer
Dim Test As String
Const Name_Not_In_Collection = 3265

Found = False

On Error Resume Next
'If the database name is empty..
If Trim$(DbName) = "" Then 'If db name not specified in function call isTable("",xx)
'...set the db to current db
Set db = CurrentDb()
Else
'otherwise set db to specified open database
Set db = DBEngine.Workspaces(0).OpenDatabase(DbName)
'see if an error occurred
If Err Then
'ONE CAUSE OF BEING HERE IS IF _BE NOT FOUND (MAYBE ON REMOTE PC WITH ONLY FE)
MsgBox "modDTE could not find database to open: " & DbName
IsTable = False
Exit Function
End If
End If

'see if the name is in the Tables collection
Test = db.TableDefs(TName$).Name
If Err <> Name_Not_In_Collection Then Found = True

db.Close
IsTable = Found


End Function

Code to check for table:

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'Specify paths to frontend and backend
Set db = DBEngine.OpenDatabase(&quot;C:\NCTCComm\NCTC Subscriber_be.mdb&quot '<---SET THIS BEFORE DELIVERING
strPath = &quot;C:\NCTCComm\NCTC Subscriber_be.mdb&quot; '<----------SET THIS BEFORE DELIVERING

strFePath = &quot;C:\NCTCComm\NCTC Subscriber.mdb&quot; '<----------SET THIS BEFORE DELIVERING

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'Link #1 tbl ErrorLog table
If Not IsTable(strFePath, &quot;tbl ErrorLog&quot And IsTable(strPath, &quot;tbl ErrorLog&quot Then
bSuccess = CreateAttached(&quot;tbl ErrorLog&quot;, strPath, &quot;tbl ErrorLog&quot
If bSuccess = True Then
'REM MsgBox &quot;Developer Message #9:&quot; & vbcrlf & vbcrlf &&quot;'tbl ErrorLog' link established.&quot;, vbInformation
Else
MsgBox &quot;Attempt to link 'tbl ErrorLog' failed.&quot; 'Do something more here???
End If
Else
End If

 

[GenieTR]

By using a macro to test for linked tables, here is the code I found for reconnecting linked table.

Function Reconnect()
'**************************************************************
'* START YOUR APPLICATION (MACRO: AUTOEXEC) WITH THIS FUNCTION
'* AND THIS PROGRAM WILL CHANGE THE CONNECTIONS AUTOMATICALLY
'* WHEN THE 'DATA.MDB' AND THE 'PRG.MDB'
'* ARE IN THE SAME DIRECTORY!!!
'* PROGRAMMING BY PETER VUKOVIC, Germany
'* 100700.1262@compuserve.com
'* ************************************************************
Dim db As Database, source As String, path As String
Dim dbsource As String, I As Integer, J As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
'*************************************************************
'* RECOGNIZE THE PATH *
'*************************************************************

For I = Len(db.Name) To 1 Step -1
If Mid(db.Name, I, 1) = Chr(92) Then
path = Mid(db.Name, 1, I)
MsgBox (path)
Exit For
End If
Next
'*************************************************************
'* CHANGE THE PATH AND CONNECT AGAIN *
'*************************************************************

For I = 0 To db.TableDefs.Count - 1
If db.TableDefs(I).Connect <> &quot; &quot; Then
source = Mid(db.TableDefs(I).Connect, 11)
Debug.Print source
For J = Len(source) To 1 Step -1
If Mid(source, J, 1) = Chr(92) Then
dbsource = Mid(source, J + 1, Len(source))
source = Mid(source, 1, J)
If source <> path Then
db.TableDefs(I).Connect = &quot;;Database=&quot; + path + dbsource
db.TableDefs(I).RefreshLink
Debug.Print &quot;;Database=&quot; + path + dbsource
End If
Exit For
End If
Next
End If
Next
End Function

Just add it to the module section.

For Query table check under the help for information on detecting query table.

Hope this help.

 

[ssecca]

In the end I did it this way...

CODE SEGMENT from main module...

If existTable(&quot;dbo_PSC_ye_AvgeragesAndFixed_Data&quot = False Then
DoCmd.TransferDatabase acLink, &quot;ODBC Database&quot;, _
&quot;ODBC;DSN=CMSDEV;UID=sa;PWD=bulletpr00f;DATABASE=CMSDEV&quot;, acTable, &quot;_PSC_ye_AvgeragesAndFixed_Data&quot;, &quot;dbo_PSC_ye_AvgeragesAndFixed_Data&quot;, False, True
End If


Procedure in my Utilities Module that gets called...

Public Function existTable(tblName As String) As Boolean
Dim test As String
existTable = False

'see if the name is in the Tables collection
On Error Resume Next
test = CurrentDb.TableDefs(tblName).Name
'MsgBox Err.Number
If Err = 0 Then existTable = True
Err.Clear
End Function


Thanks for the help it got me in the right direction.