• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

How can tables be detected in Access using VBA?

Hi Experts,

How can tables be detected in Access using VBA?

I have this code to link tables but I want to detect before doing this for each table. How can this be done?

Thanks
Option Compare Database
 
Public Sub Reconnect_Database_Table_Links()
 
 
Dim db As DAO.Database
Dim tbd As DAO.TableDef
 
Dim PathIs As String
 
'Detect path
PathIs = Application.CurrentProject.Path
 
Set db = CurrentDb
 
'FTT_BUDGETED
Set tbd = db.CreateTableDef(Name:="FTT_BUDGETED")
tbd.SourceTableName = "FTT_BUDGETED"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_Budget.mdb"
db.TableDefs.Append tbd
 
'FTT_ACTUALS
Set tbd = db.CreateTableDef(Name:="FTT_ACTUALS")
tbd.SourceTableName = "FTT_ACTUALS"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_FTT_Actuals.mdb"
db.TableDefs.Append tbd
 
 
'LOCATIONS
Set tbd = db.CreateTableDef(Name:="LOCATIONS")
tbd.SourceTableName = "LOCATIONS"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_Manager.mdb"
db.TableDefs.Append tbd
 
'TRANSPORT_MODES
Set tbd = db.CreateTableDef(Name:="TRANSPORT_MODES")
tbd.SourceTableName = "TRANSPORT_MODES"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_Manager.mdb"
db.TableDefs.Append tbd
 
 
'LANES
Set tbd = db.CreateTableDef(Name:="LANES")
tbd.SourceTableName = "LANES"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_FTT_Actuals.mdb"
db.TableDefs.Append tbd
 
'LANE_TRANSPORT_MODES
Set tbd = db.CreateTableDef(Name:="LANE_TRANSPORT_MODES")
tbd.SourceTableName = "LANE_TRANSPORT_MODES"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_FTT_Actuals.mdb"
db.TableDefs.Append tbd
 
 
 
'USERS
Set tbd = db.CreateTableDef(Name:="USERS")
tbd.SourceTableName = "USERS"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_Manager.mdb"
db.TableDefs.Append tbd
 
'CAL_YEAR
Set tbd = db.CreateTableDef(Name:="CAL_YEAR")
tbd.SourceTableName = "CAL_YEAR"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_Manager.mdb"
db.TableDefs.Append tbd
 
 
'CAL_MONTH
Set tbd = db.CreateTableDef(Name:="CAL_MONTH")
tbd.SourceTableName = "CAL_MONTH"
tbd.Connect = ";DATABASE=" & PathIs & "\SMC_Logistics_Manager.mdb"
db.TableDefs.Append tbd
 
 
 
 
End Sub

Open in new window

0
indyng
Asked:
indyng
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can attempt to open the table and trap the error which indicates it does not exist"

On Error Resume Nex
Currentdb.OpenRecordset("YourTableName")

If err.Number = 3078 Then
   ' Table does not exist
Else
  ' whatever
End If

mx
0
 
rockiroadsCommented:
or this way


    Dim adoConnection As ADODB.Connection
    Dim rstSchema As ADODB.Recordset
    Dim sTableToCheck as String
       
    Set adoConnection = New ADODB.Connection
   
    Set adoConnection = Application.CurrentProject.Connection
   
    sTableToCheck = "YoMX"
    Set rstSchema = adoConnection.OpenSchema(adSchemaTables)
    rstSchema.Find "TABLE_NAME = '" & sTableToCheck & "'"
    If rstSchema.EOF = False Then
        Debug.Print "Table Found"
    Else
        Debug.Print "Table Not Found"
    End If
    rstSchema.Close
    adoConnection.Close
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now