Problem with recordset name using string

[Nathan Bloomfield]

I am having difficulty with an attempt at setting a recordset based on
a string.

The code -- Set rsSource = CurrentDb.OpenR ecordset("'" &
strSourceTable & "'", dbOpenTable) -- results in the following error:

"The microsoft could not find the object "vCUSTMR". Make sure the
object exists and you spell its name and path name correctly."

The tables do exist and are spelled correctly, the recordset "rs" is
obviously working fine as it is extracting the correct data "CUSTMR"
from tblTolasFiles. I've tried adding "Tables!" to the string with no
success.

Any help will be much appreciated.
Dim strSourceTable As String
Dim strDestTable As String

UpdateFileCount = 0
TolasFileCount = 0
Dim rs As Recordset
Dim rsSource As Recordset
Dim rsDest As Recordset

Set rs = CurrentDb.OpenR ecordset("tblTo lasFiles", dbOpenTable)

strSourceTable = "v" & rs!TolasFilenam e.Value
strDestTable = "tbl" & rs!TolasFilenam e.Value

Set rsSource = CurrentDb.OpenR ecordset("'" & strSourceTable & "'",
dbOpenTable)
Set rsDest = CurrentDb.OpenR ecordset("'" & strDestTable & "'",
dbOpenTable)

[Terry Kreft]

Drop the quotes.

Set rsSource = CurrentDb.OpenR ecordset(strSou rceTable , dbOpenTable) --
results in the following error:

If ther eis the cghance that the table name contins spaces then

Set rsSource = CurrentDb.OpenR ecordset("[" & strSourceTable & "]" ,
dbOpenTable) -- results in the following error:

--
Terry Kreft
MVP Microsoft Access
"Nathan Bloomfield" <na************ **@hotmail.com> wrote in message
news:4b******** *************** ***@posting.goo gle.com...

I am having difficulty with an attempt at setting a recordset based on
a string.

The code -- Set rsSource = CurrentDb.OpenR ecordset("'" &
strSourceTable & "'", dbOpenTable) -- results in the following error:

"The microsoft could not find the object "vCUSTMR". Make sure the
object exists and you spell its name and path name correctly."

The tables do exist and are spelled correctly, the recordset "rs" is
obviously working fine as it is extracting the correct data "CUSTMR"
from tblTolasFiles. I've tried adding "Tables!" to the string with no
success.

Any help will be much appreciated.
Dim strSourceTable As String
Dim strDestTable As String

UpdateFileCount = 0
TolasFileCount = 0
Dim rs As Recordset
Dim rsSource As Recordset
Dim rsDest As Recordset

Set rs = CurrentDb.OpenR ecordset("tblTo lasFiles", dbOpenTable)

strSourceTable = "v" & rs!TolasFilenam e.Value
strDestTable = "tbl" & rs!TolasFilenam e.Value

Set rsSource = CurrentDb.OpenR ecordset("'" & strSourceTable & "'",
dbOpenTable)
Set rsDest = CurrentDb.OpenR ecordset("'" & strDestTable & "'",
dbOpenTable)

[nathanbloomfield]

Thanks Terry,

I tried this but results in error Runtime 3219 "Invalid Operation".
Any ideas?

[Terry Kreft]

Well I've just tried the following

Dim rsSource As DAO.Recordset
Dim strSourceTable As String

strSourceTable = "Table1"
Set rsSource = CurrentDb.OpenR ecordset(strSou rceTable, dbOpenTable)

and it works fine.

BTW, ignore the advice I gave about the square braces it's not needed fo
this operation, so ...

Dim rsSource As DAO.Recordset
Dim strSourceTable As String

strSourceTable = "Table1 Space In Name"
Set rsSource = CurrentDb.OpenR ecordset(strSou rceTable, dbOpenTable)

.... works just as well.
--
Terry Kreft
MVP Microsoft Access
<na************ **@hotmail.com> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. .

Thanks Terry,

I tried this but results in error Runtime 3219 "Invalid Operation".
Any ideas?