DoCmd.RunSQL statement error. MS Jet DB not able to find table

[rushitshah]

Hello People,
I am very new with VBA coding.

I am trying an application in that I am doing following steps.

1. Converting excel spreadsheets in to tables
2. Merging all those tables in to one result table columnwise, means the tables can have same or different columns, so if they have same column names then data will be copied in one column or if they have different columns then new column will be appended in result table.
3. running different queries on the result table.

This is the code I am doing..

there is a combobox on the form that has parameter names.
and text box on the form that allows to enter cyclename, basically like March'05.
so from March'05 code will find all the existing tables that includes march'05 in their names and merge them togather and save it as a result table March'05. Then I want to run queries on that table for different parameters.
There will be a column in March'05 result table parm_nm.

But I am getting error in docmd.runsql statement. It says Microsoft jet database engine cannot fine input table or query "March'05" Make sure that it exists or name is spelled correctly.

But table is created in the database first when I hit Create button. But it is not able to detect its existance, so I tried refreshdatabasewindow in between before running the query. But it is also not working.
Please help with this.
Thanks..

_____________________________
Private Sub Create_Click()
Dim dbT As DAO.TableDef
Dim dbD As DAO.Database
Dim dbF As DAO.Field
Dim dbNewField As DAO.Field
Dim dbMerge As DAO.TableDef
Dim cycName As String, tblName1 As String, tblName2 As String
Dim tTable As TableDef

Dim sTables
Dim iCntr As Long
Dim sSQL As String

Dim sTabString As String
cycName = Me.PCycle

Set dbD = CurrentDb
For Each dbT In dbD.TableDefs
If dbT.Name Like "*" & cycName & "*" Then sTabString = sTabString & " " & dbT.Name ' you could add the square brackets here and remove them from other code, for cleanliness
Next

sTables = Split(Mid(sTabString, 2), " ", -1, vbTextCompare)


Set dbD = CurrentDb
DoCmd.CopyObject , Me.PCycle, acTable, sTables(0)

Set dbMerge = dbD.TableDefs(Me.PCycle)
For iCntr = 0 To UBound(sTables)
For Each dbF In dbD.TableDefs(sTables(iCntr)).Fields
On Error Resume Next
Set dbNewField = dbMerge.CreateField(dbF.Name, dbF.Type, dbF.Size)
With dbNewField
.AllowZeroLength = dbF.AllowZeroLength
.DefaultValue = dbF.DefaultValue
.Required = dbF.Required
.ValidateOnSet = dbF.ValidateOnSet
.ValidationRule = dbF.ValidationRule
.ValidationText = dbF.ValidationText
End With
dbMerge.Fields.Append dbNewField
On Error GoTo 0
Next
Next
Set dbNewField = Nothing
DoCmd.SetWarnings False
'On Error GoTo MergeTables_Error
For iCntr = 1 To UBound(sTables)
sSQL = ""
For Each dbF In dbD.TableDefs(sTables(iCntr)).Fields
sSQL = sSQL & ", [" & dbF.Name & "]"
Next
sSQL = Mid(sSQL, 3)
sSQL = "INSERT INTO [" & Me.PCycle & "] (" & sSQL & ") SELECT " & sSQL & " FROM [" & dbD.TableDefs(sTables(iCntr)).Name & "]"
'sSQL = "INSERT INTO &me.tblName1& (" & sSQL & ") SELECT " & sSQL & " FROM [" & dbD.TableDefs(sTables(iCntr)).Name & "]"
DoCmd.RunSQL sSQL
'sSQL = "INSERT INTO ]" & dbMerge.Name & "] (" & sSQL & ") SELECT " & sSQL & " FROM [" & dbD.TableDefs(sTables(iCntr)).Name & "]"
'DoCmd.RunSQL sSQL
Next

RefreshDatabaseWindow

Dim strSQL As String
Set dbD = CurrentDb

strSQL = "SELECT t.* " & _
"INTO ['" & Me.Parameters & "'] " & _
"FROM ['" & Me.PCycle & "'] t " & _
"WHERE t.parm_nm = '" & Me.Parameters & "';"

DoCmd.RunSQL strSQL

End Sub

 

[Bat17]

looks like it could be the same problem as this one

http://www.access-programmers.co.uk/forums/showthread.php?t=92676&highlight=collection

Peter