VBA syntax error "... cannot find input table..." (1 Viewer)

EEH_Help

Member
Local time
Today, 09:21
Joined
Feb 15, 2020
Messages
32
Good morning... I'm running into a syntax error. I'm sure the fix is simple (i.e., changing order of single or double-quotes). Any suggestions would be appreciated.

Please see the From / To below:

From (this works fine):
Code:
Set rs_Before = db.OpenRecordset("Table_1", dbOpenDynaset)
Set rs_After = db.OpenRecordset("Table_2", dbOpenDynaset)

To (this is where I'm getting the syntax error):
Code:
Dim TableBefore As String
Dim TableAfter As String

TableBefore = "Table_1"
TableAfter = "Table_2"

Set rs_Before = db.OpenRecordset("' & TableBefore & '", dbOpenDynaset)
Set rs_After = db.OpenRecordset("' & TableAfter & '", dbOpenDynaset)

Currently, I'm getting a syntax error "MS-Access DB engine cannot find the input table or query...". How should the last 2 "Set rs" lines be modified so that I can pass the values "Table_1" and "Table_2" into the db.OpenRecordset statements?

Thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:21
Joined
May 7, 2009
Messages
13,691
remove the quote:

Code:
Dim TableBefore As String
Dim TableAfter As String

TableBefore = "Table_1"
TableAfter = "Table_2"

Set rs_Before = db.OpenRecordset(TableBefore, dbOpenDynaset)
Set rs_After = db.OpenRecordset(TableAfter, dbOpenDynaset)
 

EEH_Help

Member
Local time
Today, 09:21
Joined
Feb 15, 2020
Messages
32
Wow... I figured it was a simple one.

Thank you so much for your assistance, arnelgp. I appreciate it! :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:21
Joined
Feb 19, 2002
Messages
32,787
arnel gave you the correct answer but to explain the syntax for a situation where you actually need to embed the single quote:

Set rs_After = db.OpenRecordset("'" & TableAfter & "'", dbOpenDynaset)

You must enclose the string you wish to concatenate. So you surround the single quote with double quotes.

SomeField = "'" & Me.SomeControl & "'"
 

EEH_Help

Member
Local time
Today, 09:21
Joined
Feb 15, 2020
Messages
32
Pat - thank you for the additional information.... I appreciate.

Arne's solution works great, but I can definitely see the need for Concannon at some point. In such case, surrounding the double-quotes with single quotes will come in handy.

Again, thank you for providing the additional info.

Cheers!
 

Users who are viewing this thread

Top Bottom