How to check Table exists in MS Access through VBscript

Question

I have a vbscript that trying to create the table in MS Access DB, but i want to make it like if the table is exists, then it will direct proceed to enter data without need of creating the table.

What can i do to check the existing of the table is created or not?

My code as below, it will not proceed to insert data if the table is already exists.

'Constants
'Const adOpenStatic = 3
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adCmdTable = &H0002

Set objConn = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

'Connect Primary DB
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "C:\AIS_Workfolder\Reference\DB\" & "AIS_DataDB.mdb"

'Open Connection
objConn.open connStr

'Create table
objConn.Execute "CREATE TABLE " & "test_table" & "(" & _
  "[ID] COUNTER ," & _
  "[Field1] TEXT(255) ," & _
  "[Field2] TEXT(255) ," & _
  "[Field3] TEXT(255) ," & _



objRecordSet.Open "test_table", objConn, adOpenDynamic, adLockOptimistic, adCmdTable

    objRecordSet.AddNew
      objRecordSet("Field1").value = "testing123"
      objRecordSet("Field2").value = "testing123"
      objRecordSet("Field3").value = "testing123"

Possible duplicate of [can we list all tables in msaccess database using sql?](http://stackoverflow.com/q/2629211/1630171). — Ansgar Wiechers, Nov 24 '15 at 09:05

score 1 · Answer 1 · answered Mar 24 '17 at 16:38

Or like this one:

Function TableExists(strTableName)
    Dim RStmp
    TableExists = true
    on error resume next
    RStmp=Conn.Execute("SELECT * FROM ["&strtablename&"]")
    If Err.Number <> 0 Then TableExists=false
    on error goto 0
End Function

score 0 · Answer 2 · answered Jan 05 '16 at 15:30

Use a function like this:

If TableExists("test_table") Then
  ' Take action here
Else    
  ' Create table here
End If    

Function TableExists(TabletoFind)
    TableExists = False
    Set adoxConn = CreateObject("ADOX.Catalog")
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open(ConnStr)
    adoxConn.ActiveConnection = objConn
    IsThere = False
    For Each Table in adoxConn.Tables
        If LCase(Table.Name) = LCase(TabletoFind) Then
            IsThere = True
            Exit For
        End If
    Next
    objConn.Close
    Set objConn = Nothing
    Set adoxConn = Nothing
    If IsThere Then TableExists = True
End Function

How to check Table exists in MS Access through VBscript

2 Answers2