<div>
Add &quot;[&quot; and &quot;]&quot; to your SQL string 
 

<pre><code class="code-1 nolinks" id="code-20-43283665-1">Set rs = db.openrecordset(&quot;select * from [&quot; &amp; ActiveSheet.Name &amp; &quot;];&quot;)
</code></pre>
</div>

<div>
Hi, I still have same error using line below <pre><code class="code-1 nolinks" id="code-20-43283670-1">Set rs = db.openrecordset("select * from [" &amp; ActiveSheet.Name &amp; "];")
</code></pre>
</div>

<div>
Just to add to aikimark's answer a little bit, whenever you refer to a table name, field names with special characters, spaces, ... then you need to surround the name with square brackets. SELECT * FROM Contacts SELECT * FROM [Business Contacts] SELECT * FROM [Week8-13] because of this, and since you are pulling your name from a workbook where anything could be input, always add brackets. &nbsp;It is actually a good idea to always surround field, table names with brackets thus avoiding the issue altogether. SELECT * FROM [Contacts] SELECT [ID], [First Name], [Last Name] FROM [Contacts] SELECT * FROM [Business Contacts] SELECT * FROM [Week8-13] Also, it can be useful to use the QBE to build a sample query, switch to SQL View and look at the syntax of the SQL Statement generated, you will see bracketing around fiels/tables using special characters in their names. Also remember that - is a substraction sign, thus why you need the bracket to instruct Access to interpret the whole thing as the name of something rather than some mathematical operation. Even better, to avoid all of this is to follow the golden rule of naming objects, fields, variables, ... never use any special characters and stick to plain CamelCase alphabetic names. &nbsp;This will simplify your life in the long run. &nbsp;You may like to review <a href="https://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/" rel="ugc">https://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/</a>
</div>

<div>
Using a sheet as table source in a SQL statement has an additional rule. In most cases, the sheet name must have a dollar sign ($) as prefix. Thus try the following: 
 

<pre><code class="code-1 nolinks" id="code-20-43283675-1">Set rs = db.OpenRecordset(&quot;SELECT * FROM [$&quot; &amp; ActiveSheet.Name &amp; &quot;];&quot;)
</code></pre>
There are cases, where the dollar sign is not needed, but I cannot name those scenarios. So may code always prefixes the sheet name. And in the case of an error it retries without. 
 
EDIT: Oops. The dollar sign, if necessary, is a sheet name suffix.
</div>

<div>
<blockquote>I still have same error using line below<pre><code class="code-1 nolinks" id="code-20-43283677-1">Set rs = db.openrecordset("select * from [" &amp; ActiveSheet.Name &amp; "];")
</code></pre></blockquote>Does table 'Week8-13' exist? Create the query manually and swicth to SQL View and look over the difference in syntax to spot what you are missing and adjust your code accordingly.
</div>

<div>
My Excel import masks come in many cases with an sheet selector combo box. You can read the sheet names using ADODB: 
 

<pre><code class="code-10 nolinks" id="code-20-43283680-1">Option Compare Database
Option Explicit

Public Sub ExcelLoadSheetNames(AFileName As String, ASheetComboBox As Access.ComboBox, Optional ADefaultSheetName As String = &quot;&quot;)

 Const adSchemaTables As Long = 20
 Const CONNECTION_STRING As String = &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES';&quot;
 
 On Local Error GoTo LocalError

 Dim cnn As Object 'ADODB.Connection
 Dim rs As Object 'ADODB.Recordset
 
 Dim CurrentSheetName As String
 Dim FoundDefaultSheetName As Boolean
 
 ASheetComboBox.Enabled = False
 ASheetComboBox.RowSource = &quot;&quot;
 ASheetComboBox.RowSourceType = &quot;Value List&quot;
 If Len(Trim(AFileName)) &gt; 0 And Len(Dir(AFileName)) &gt; 0 Then
 FoundDefaultSheetName = False
 Set cnn = CreateObject(&quot;ADODB.Connection&quot;)
 cnn.Open Replace(CONNECTION_STRING, &quot;{0}&quot;, AFileName)
 Set rs = cnn.OpenSchema(adSchemaTables)
 Do While Not rs.EOF
 CurrentSheetName = rs.Fields(&quot;table_name&quot;).Value
 If Left(CurrentSheetName, 1) = &quot;'&quot; And Right(CurrentSheetName, 1) = &quot;'&quot; Then
 CurrentSheetName = Mid(CurrentSheetName, 2, Len(CurrentSheetName) - 2)
 End If
 
 If Right(CurrentSheetName, 1) = &quot;$&quot; Then
 CurrentSheetName = Left(CurrentSheetName, Len(CurrentSheetName) - 1)
 End If
 
 ASheetComboBox.AddItem CurrentSheetName
 FoundDefaultSheetName = FoundDefaultSheetName Or (CurrentSheetName = ADefaultSheetName)
 rs.MoveNext
 Loop

 rs.Close
 Set rs = Nothing
 cnn.Close
 Set cnn = Nothing
 ASheetComboBox.Value = IIf(FoundDefaultSheetName, ADefaultSheetName, ASheetComboBox.ItemData(0))
 ASheetComboBox.Enabled = True
 End If
 
 Exit Sub
 
LocalError:
 ' Your error handler.
 MsgBox &quot;ExcelLoadSheetNames&quot;
 
End Sub
</code></pre>
</div>

<div>
FYI (DB file and Excel file) <a href="https://1drv.ms/x/s!Ai8CrEskdewXvwPP8sr_QRAuLLeQ?e=kzGS5q" rel="ugc">https://1drv.ms/x/s!Ai8CrEskdewXvwPP8sr_QRAuLLeQ?e=kzGS5q</a> <a href="https://1drv.ms/u/s!Ai8CrEskdewXvwJry99MPfM5CUy0?e=fiERBZ" rel="ugc">https://1drv.ms/u/s!Ai8CrEskdewXvwJry99MPfM5CUy0?e=fiERBZ</a>&nbsp; I do not know why mentioned error happens when opening Excel file.
</div>

<div>
Okay, as the code is in Excel: 
 
Please rephrase your question. What is the use-case and scenario here? Why using record sets at all instead of using native Excel VBA? 
 
 
p.s. always use Option Explicit as the first directive in your VBA code. Make it the default by checking Variable declaration required under Tools / Options in the VBA IDE. And for posting code, make sure that it compiles. And also removing dead code is in most scenarios, cause it makes reading code harder.
</div>

<div>
Stephen, How to resolve current issue with table name, when opening Excel file?
</div>

<div>
I don't know, cause I don't understand what you're trying to do. Thus I've asked that you rephrase your question.
</div>

<div>
'Week8-13' is existing table name in DB file. I do not know why issue happens.
</div>

<div>
Which command/proc are you currently trying to get working? On a side note, you truly need to get in the habit of always declaring Option Explicit at the top of all your modules and adding Error handling to all your procedures.
</div>

<div>
When I open the database there is no such table. &nbsp;There is only 1 non-system table and it is named T1.
</div>

<div>
FYI, the $ goes at the end of the sheet name.
</div>

IT Consultant

Peter Chan

<div>
<div class="content wysiwyg-content fr-view">
Hi, Why is there error <a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559344/1j.png" rel="ugc">1j.png</a> due to this line <pre><code class="code-1 nolinks" id="code-10-29215082-1">Set rs = db.openrecordset("select * from " &amp; ActiveSheet.Name &amp; ";")
</code></pre> while ActiveSheet.Name is having value like "Week8-13"? 
</div>
</div>

1j.png


Hi,
Why is there error
1j.png
due to this line
Set rs = db.openrecordset("select * from " & ActiveSheet.Name & ";")
while ActiveSheet.Name is having value like "Week8-13"?

Issue to table name

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.