see if table exists then link it

  • Thread starter Thread starter LGarcia
  • Start date Start date
L

LGarcia

Hi All,
I have an EntryDB that contains links to tables in MyDataDB. I need to look
in MyDataDB for "tblAllNutr". If this table exists I then need to link it
to my EntryDB.
I use this code to evaluate if a table exists but it only looks at currently
linked tables. Can I modify this code to look in MyDataDB?

Public Function DoesObjectExist(ObjectName$)
On Error Resume Next
Dim Found_Object, Find_Object As String

Found_Object = -1
Find_Object = CurrentDb.TableDefs(ObjectName$).Name
If Err = 3265 Or Find_Object = "" Then
Found_Object = 0
End If

DoesObjectExist = Found_Object

End Function

I use this code to return the full path of MyDataDB:
CurrentDb.TableDefs("tblIntakeCV").Connect
MyDataDB is only used as an example. The true name of this DB is usually
project based.
"tblIntakeCV" will always reside in this DB but this is not true for
"tblAllNutr".

Hope someone can help!
Thanks,
LGarcia
 
I have an EntryDB that contains links to tables in MyDataDB. I need to look
in MyDataDB for "tblAllNutr". If this table exists I then need to link it
to my EntryDB.
I use this code to evaluate if a table exists but it only looks at currently
linked tables. Can I modify this code to look in MyDataDB?
Click to expand...

You can return the names of all "local" tables in another database file using
the following sql:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects IN 'c:\MyData\MyData.mdb'
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like
"MSYS*") AND ((MSysObjects.Type)=1));

You will need to replace 'c:\MyData\MyData.mdb' with the proper path and
filename for the desired data file. You should be able to open a recordset based
on this sql statement and step through the rows to verify/perform your linking.
 
LGarcia said:
Hi All,
I have an EntryDB that contains links to tables in MyDataDB. I need
to look in MyDataDB for "tblAllNutr". If this table exists I then
need to link it to my EntryDB.
I use this code to evaluate if a table exists but it only looks at
currently linked tables. Can I modify this code to look in MyDataDB?

Public Function DoesObjectExist(ObjectName$)
On Error Resume Next
Dim Found_Object, Find_Object As String

Found_Object = -1
Find_Object = CurrentDb.TableDefs(ObjectName$).Name
If Err = 3265 Or Find_Object = "" Then
Found_Object = 0
End If

DoesObjectExist = Found_Object

End Function

I use this code to return the full path of MyDataDB:
CurrentDb.TableDefs("tblIntakeCV").Connect
MyDataDB is only used as an example. The true name of this DB is
usually project based.
"tblIntakeCV" will always reside in this DB but this is not true for
"tblAllNutr".

Hope someone can help!
Thanks,
LGarcia
Click to expand...

You could use the following function to return the full path to your
back-end "data" .mdb file"

'----- start of code for fncDataDBName -----
Function fncDataDBName() As String

' Return the name (including path) of the "back-end" database
containing the data
' this application is working with. If the data tables are local,
not linked, return
' the name of the current database.

' Note: this function relies on the fact that table tblIntakeCV will
always exist as
' a linked table in the front-end.

Static strDBFileName As String

If Len(strDBFileName) = 0 Then
strDBFileName = CurrentDb.TableDefs("tblIntakeCV").Connect
If Left$(strDBFileName, 10) = ";DATABASE=" Then
strDBFileName = Mid$(strDBFileName, 11)
Else
strDBFileName = CurrentDb.Name
End If
End If

fncDataDBName = strDBFileName

End Function

'----- end of code for fncDataDB-----

Then you could modify your code to check for the existence of a table in
the back-end database, like this:

'----- start of code for fncTableExistsInBackend -----
Public Function fncTableExistsInBackend ( _
TableName As String) _
As Boolean

On Error GoTo Err_Handler

Dim db As DAO.Database

Set db = _
DBEngine.Workspaces(0).OpenDatabase(fncDataDBName())

If db.TableDefs(TableName).Name = TableName Then
fncTableExistsInBackend = True
End If

Exit_Point:
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
Exit Function

Err_Handler:
If Err.Number <> 3265 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Function
'----- end of code for fncTableExistsInBackend -----
 
Back
Top