<div>
What is used: Excel VBA or Access VBA?
</div>

<div>
Within VBA (Excel). To validate it against Access DB.
</div>

<div>
Here is a revision <pre><code class="code-10 nolinks" id="code-20-43284204-1">Public Function TblExists(ByVal strTableName As String) As Boolean
Dim blnExists As Boolean
Dim searchResult
blnExists = False
&nbsp;
searchResult = DLookup("Name", "MSysObjects", "Name = '" &amp; strTableName &amp; "' AND Type = 1")
If Len(Nz(searchResult, vbNullString)) = 0 Then
Else
blnExists = True
End If
TblExists = blnExists
End Function



</code></pre>
</div>

<div>
Dlookup is an Access specific function. To use it from within Excel you must have an Access Object declared, on which it is working. E.g. <pre><code class="code-1 nolinks" id="code-20-43284213-1">Dim app as New Access.Application
App.OpenCurrentDatabase(YourFilePath)
If Not IsNull(App.DLookup("Name", "MSysObjects", "Name = " &amp; Chr(34) &amp; strTableName &amp; Chr(34) &amp; " AND Type = 1")) Then TblExists = True
</code></pre>&nbsp; Alternatively if you already have a connection established, you can use that to query the MSysObjects table.
</div>

IT Supervisor

John Tsioumpris

Gustav Brock

<div>
John, It is same error per attached <a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559420/1h.png" rel="ugc">1h.png</a>
</div>

1h.png

<div>
I missed the Excel parameter...Anders gave the solution
</div>

<div>
Can you help to issue attached<a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559421/1i.png" rel="ugc">1i.png</a>, when running the file? 
</div>

1i.png

<div>
I would use my own CurrentDb() as he wants to do more with that database, e.g. something like: 
 

<pre><code class="code-1 nolinks" id="code-20-43284256-1">Option Explicit

Public Sub Test()

 MsgBox TableExists(&quot;T1&quot;)
 ReleaseReferences ' Call once your done.

End Sub
</code></pre>
with 
 

<pre><code class="code-10 nolinks" id="code-20-43284256-2">Option Explicit

Private Const DATABASE_FILENAME As String = &quot;C:\Temp\Tests\Db1.accdb&quot; ' Can also be a read from a cell or an explicit parameter.

' Microsoft Office 12+ Access database engine Object Library&quot;
' Or use late binding.
Private m_CurrentDbEngine As DAO.DBEngine
Private m_CurrentDb As DAO.Database

Public Property Get CurrentDb() As DAO.Database

 If m_CurrentDb Is Nothing Then
 Set m_CurrentDbEngine = New DAO.DBEngine
 Set m_CurrentDb = m_CurrentDbEngine.OpenDatabase(DATABASE_FILENAME)
 End If
 
 Set CurrentDb = m_CurrentDb
 
End Property

Public Sub ReleaseReferences()

 If Not m_CurrentDb Is Nothing Then
 m_CurrentDb.Close
 Set m_CurrentDb = Nothing
 End If

 If Not m_CurrentDbEngine Is Nothing Then
 Set m_CurrentDbEngine = Nothing
 End If

End Sub

Public Function TableExists(ByVal CTableNAme As String) As Boolean
 
 Const NO_ERROR As Long = 0
 
 On Local Error GoTo 0

 Dim TableDef As DAO.TableDef

 Err.Clear
 Set TableDef = CurrentDb.TableDefs(CTableNAme)
 TableExists = (Err.Number = NO_ERROR And Not TableDef Is Nothing)
 Set TableDef = Nothing
 
End Function
</code></pre>
</div>

<div>
Escape the parameter to avoid SQL injection and use DCount() and &nbsp;Boolean expressions instead of If, I think it's better readable. And as we need to escape normally often, use a function: 
 

<pre><code class="code-10 nolinks" id="code-20-43284258-1">Option Explicit

Private Const DATABASE_FILENAME As String = &quot;C:\Temp\Tests\Db1.accdb&quot; ' Can also be a read from a cell or an explicit parameter.

Private m_CurrentApplication As Access.Application

Public Property Get CurrentApplication() As Access.Application

 If m_CurrentApplication Is Nothing Then 
 Set m_CurrentApplication = New Access.Application ​
 m_CurrentApplication.OpenCurrentDatabase(DATABASE_FILENAME)
 End If
 ​
 ​Set CurrentApplication = m_CurrentApplication
 ​
End Property

Public Sub ReleaseReferences()

 If Not m_CurrentApplication Is Nothing Then
 m_CurrentApplication.CloseCurrentDatabase
 Set m_CurrentApplication = Nothing
 End If

End Sub

Public Function SqlQuoteStr(ByVal CString As String, ByVal Optional CDelimiter As String = &quot;'&quot;) As String

 ​SqlQuoteStr = CDelimiter &amp; Replace(CString, CDelimiter, CDelimiter &amp; CDelimiter) &amp; CDelimiter

End Function

Public Function TableExists(ByVal CTableName As String) As Boolean

 ​TableExists = (CurrentAccess.DCount(&quot;Name&quot;, &quot;MSysObjects&quot;, &quot;Name = &quot; &amp; SqlQuoteStr(CTableName) &amp; &quot; AND Type = 1;&quot;) &gt; 0)

End Function
</code></pre>
</div>

<div>
Thanks Stephen. What should be acTable declared, per attached issue?<a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559423/1j.png" rel="ugc">1j.png</a>
</div>

1j.png

<div>
Using Access.Application requires the at you reference the Microsoft Access 12+ Object Library.
</div>

<div>
Sorry, do you see my screenshot in above? What should be acTable declared as?
</div>

<div>
Yes. Did you read my <a href="https://www.experts-exchange.com/questions/29215186/Issue-to-DLookup-in-VBA.html?anchorAnswerId=43284260#a43284260" rel="ugc">post above</a>? 
 
When having that reference, then it is resolved automatically. 
And you can use the object browser to lookup that value. E.g. 
 
<a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559426/Capture.PNG" target="_blank" title="Capture.PNG (69 KB)"><img alt="Capture.PNG" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2021/05_w19/800_1559426/Capture.PNG" /></a>
</div>

Capture.PNG

<div>
Hi, How to correct attached error&nbsp;<a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559427/1k.png" rel="ugc">1k.png</a> due to last line below? <pre><code class="code-10 nolinks" id="code-20-43284278-1">Public Function TblExists(ByVal para_db As String, ByVal CTableNAme As String) As Boolean
&nbsp;
&nbsp; Const NO_ERROR As Long = 0
&nbsp;
&nbsp; On Local Error GoTo 0


&nbsp; Dim TableDef As DAO.TableDef


&nbsp; Err.Clear
&nbsp; Set TableDef = para_db.TableDefs(CTableNAme)
&nbsp; TableExists = (Err.Number = NO_ERROR And Not TableDef Is Nothing)
&nbsp; Set TableDef = Nothing
&nbsp;
End Function


&nbsp; &nbsp; Dim Adb As Object, acTable As Object


&nbsp; &nbsp; Dim Adb As Object, acTable As Object
&nbsp; &nbsp; Set Adb = CreateObject("Access.Application"): Call Adb.OpenCurrentDatabase(F1)
&nbsp; &nbsp; Adb.Visible = False
&nbsp; &nbsp;
&nbsp; &nbsp; If TblExists(Adb, ActiveSheet.Name) Then



</code></pre>
</div>

1k.png

<div>
You're mixing strategies. My first post uses DAO only. The second post use Access as proposed by Anders. 
 
As long as you're developing your solution, use early binding and the references. This will give you the answer to your question automatically. Adb is not of type DAO.Database. 
 
p.s. as you haven't told us much about your context. I strongly recommend not to handle the application or database like you do.
</div>

<div>
Hi, I used db instead below (to TblExists). But how to adjust with statement below? <pre><code class="code-10 nolinks" id="code-20-43284356-1">&nbsp; &nbsp; Dim db As DAO.Database
&nbsp; &nbsp;
&nbsp; &nbsp; Set db = DBEngine(0).OpenDatabase(F1)
&nbsp; &nbsp;
&nbsp; &nbsp; 'db.Close
&nbsp; &nbsp;
&nbsp; &nbsp; If TblExists(db, ActiveSheet.Name) Then
&nbsp; &nbsp; &nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; With Adb
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DoCmd.SetWarnings False
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '.DoCmd.RunSQL s0
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .DoCmd.DeleteObject acTable, ActiveSheet.Name
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DoCmd.SetWarnings True
&nbsp; &nbsp; &nbsp; &nbsp; End With
&nbsp; &nbsp;
&nbsp; &nbsp; End If


&nbsp; &nbsp; &nbsp; &nbsp; With Adb
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DoCmd.SetWarnings False
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '.DoCmd.RunSQL s0
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .DoCmd.CopyObject , ActiveSheet.Name, acTable, "t1"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'DoCmd.SetWarnings True
&nbsp; &nbsp; &nbsp; &nbsp; End With



</code></pre>
</div>

<div>
<blockquote>
But how to adjust with statement below?
</blockquote>
By choosing one strategy and implementing it. You are still mixing plain DAO with full Access automation.
</div>

<div>
As most of my code is not using Dao.Database, can I adjust TblExists to use Adb instead, which is declared like <pre><code class="code-1 nolinks" id="code-20-43284380-1">&nbsp; &nbsp; Dim Adb As Object, acTable As Object
&nbsp; &nbsp; Set Adb = CreateObject("Access.Application"): Call Adb.OpenCurrentDatabase(F1)



</code></pre>
</div>

<div>
Test sample (sligtly modified Ste5an's code) 
Update references according to your office version 
<a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559453/test.xlsm" target="_blank" class="file-inline" title="sample (20 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>test.xlsm</a>
</div>

test.xlsm

<div>
Als, Thanks a lot. Can you share the way to run query <pre><code class="code-1 nolinks" id="code-20-43284508-1">select * into [Week8-13] from t1;
</code></pre> based on definition below you're using <pre><code class="code-1 nolinks" id="code-20-43284508-2">&nbsp; &nbsp; Set m_CurrentDbEngine = New DAO.DBEngine
&nbsp; &nbsp; Set m_CurrentDb = m_CurrentDbEngine.OpenDatabase(Cells(1, 2))



</code></pre>
</div>

<div>
Try 
CurrentDB.Execute &quot;select * into [Week8-13] from t1&quot;
</div>

<div>
Hi, Can you help to error "Invalid operation" due to last line below? <pre><code class="code-10 nolinks" id="code-20-43284780-1">Option Explicit
Private mDbEngine As DAO.DBEngine
Private mCurrDB As DAO.Database
...
&nbsp; &nbsp; Do While True
&nbsp; &nbsp; &nbsp; &nbsp; RowID = RowID + 1
&nbsp; &nbsp; &nbsp; &nbsp; If Trim(ActiveSheet.Cells(RowID, 1).Value) = "" Then
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Exit Do
&nbsp; &nbsp; &nbsp; &nbsp; End If
&nbsp; &nbsp; &nbsp; &nbsp; s0 = "insert into t1 values (" &amp; CStr(RowID) &amp; ",'" &amp; Trim(ActiveSheet.Cells(RowID, 1).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 2).Value) &amp; "','" &amp; _
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trim(ActiveSheet.Cells(RowID, 3).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 4).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 5).Value) &amp; _
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 6).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 7).Value) &amp; "','" &amp; _
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trim(ActiveSheet.Cells(RowID, 8).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 9).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 10).Value) &amp; "');"
&nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; Set rs = mCurrDB.openrecordset(s0)



</code></pre>
</div>

IT Consultant

Peter Chan

<div>
Yes, I have corrected it. Please see error like<a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559496/1m.png" rel="ugc">1m.png</a> due to last line below <pre><code class="code-10 nolinks" id="code-20-43284822-1">&nbsp; &nbsp; Do While True
&nbsp; &nbsp; &nbsp; &nbsp; RowID = RowID + 1
&nbsp; &nbsp; &nbsp; &nbsp; If Trim(ActiveSheet.Cells(RowID, 1).Value) = "" Then
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Exit Do
&nbsp; &nbsp; &nbsp; &nbsp; End If
&nbsp; &nbsp; &nbsp; &nbsp; s0 = "insert into [" &amp; ActiveSheet.Name &amp; "] values (" &amp; CStr(RowID) &amp; ",'" &amp; Trim(ActiveSheet.Cells(RowID, 1).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 2).Value) &amp; "','" &amp; _
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trim(ActiveSheet.Cells(RowID, 3).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 4).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 5).Value) &amp; _
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 6).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 7).Value) &amp; "','" &amp; _
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Trim(ActiveSheet.Cells(RowID, 8).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 9).Value) &amp; "','" &amp; Trim(ActiveSheet.Cells(RowID, 10).Value) &amp; "');"
&nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; mCurrDB.Execute s0, dbFailOnError
</code></pre> 
</div>

1m.png

<div>
Hi John, Further to our message, I copy relevant query (from Excel) and run it in Access. I encounter attached message and chose Yes to go. But then I do not see record in Access table. You can see attached DB file if possible. Many Thanks &amp; Best Regards, Peter <a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559503/Db1.accdb" rel="ugc">Db1.accdb</a><a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559504/1o.png" rel="ugc">1o.png</a>
</div>

1o.png

Db1.accdb

<div>
<pre><code class="code-1 nolinks" id="code-20-43284981-1">INSERT INTO [Week8-13] ( ID,Trade, Vessel, Voyage, Leg, POR, [S/O NO FROM], [S/O NO TO], [Number to increment])
VALUES('51', "JTS", "YC3", 2, "S", "TW", 8500, 8599, 1;
</code></pre>
</div>

<div>
Hi, Must I put column names to query? It does stop to last line below insert into [Week8-13] values (3,'JTS','YC3','2','S','TWKSG','8001','8179','7','8008','8001 8002 8003 8004 8005 8006 8007'); insert into [Week8-13] values (4,'JTS','YC3','2','S','TWTXG','8200','8299','3','8203','8200 8201 8202'); insert into [Week8-13] values (5,'JTS','YC3','2','S','TWTYG','8500','8599','1','',''); I do not know why I cannot run last line above, against DB file.
</div>

<div>
Just do it in steps 
</div>

<div>
I put also column name to that, and still there is same problem to do insert.
</div>

<div>
You can't run last line because you have missing values 
</div>

<div>
Peter! Good sample (Excel and Access) could save us a lot of time. I see errors in data conversion, but I don't see your worksheet and can't check your query
</div>

<div>
If the SQL INSERT statement holds values for all the columns you don't need column names If you want to do a partial insert then you should use column names.
</div>

<div>
<div class="content wysiwyg-content fr-view">
Hi, How to resolve issue with DLookup to line below? I want to validate if table "Week8-13" is existing within Access database. If that is not existing, then further copy details of T1 table into table "Week8-13". <pre><code class="code-1 nolinks" id="code-10-29215186-1">Public Function TblExists(ByVal strTableName As String) As Boolean
TblExists = False
If Not IsNull(DLookup("Name", "MSysObjects", "Name = " &amp; Chr(34) &amp; strTableName &amp; Chr(34) &amp; " AND Type = 1")) Then TblExists = True
End Function



</code></pre>
</div>
</div>


Hi,
How to resolve issue with DLookup to line below? I want to validate if table "Week8-13" is existing within Access database. If that is not existing, then further copy details of T1 table into table "Week8-13".
Public Function TblExists(ByVal strTableName As String) As Boolean
TblExists = False
If Not IsNull(DLookup("Name", "MSysObjects", "Name = " & Chr(34) & strTableName & Chr(34) & " AND Type = 1")) Then TblExists = True
End Function

Issue to DLookup in VBA

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.