The table name you entered doesn't follow System object-naming rules (1 Viewer)

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Dear Friends,

I'm getting "The table name you entered doesn't follow TOSS System object-nameing rules." error when executing below code in MS Access using VBA.

strSelectSQL = " SELECT SU.CUSTOMER, SU.ISIN_NO, SU.TURNOVER, SU.SOURCE, MAP.OVERVIEW, ADV.SUB_ADV " & _
" FROM tbl_Details_YTD AS SU, tbl_Mapping AS MAPPING, tbl_Advisor AS ADV " & _
" WHERE SU.SOURCE = " & gsSource & " AND SU.REPORTING_PERIOD = " & Me!txtReportingMonth & " AND SU.GROUP_CODE=MAPPING.Mapping_Code And ADV.RM_CODE=SU.RM "
Set rsTmp = gsTOSSDatabase.OpenRecordset(strSelectSQL)
strFile = Me.Output_Dir & "\" & Format(Now(), "yyyymmdd") & Format(Now(), "hhmm") & "_TOSS_" & Format(Me.txtReportingMonth, "mmm") & Format(Me.txtReportingMonth, "yy") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strSelectSQL, strFile, True

I have data in different database (TOSS.mdb file) and code in different database (TOSSProg.mdb file) that's why I can't use Query while exporting data into excel. I can't create / keep query in (TOSSProg.mdb file).


Thanks
Ria
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Jan 20, 2009
Messages
12,849
Re: The table name you entered doesn't follow System object-nameing rules

You have tbl_mapping aliased as MAPPING but appear to have referred to it in the first line as MAP.

BTW
Format(Now(), "yyyymmdd") & Format(Now(), "hhmm")

can be replaced with:
Format(Now(), "yyyymmddhhnn")

Similarly the formatting of ReportingMonth.
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Hi Galaxiom,

Thanks for the reply. Actually I have changed the ALIAS but error ("The table name you entered doesn't follow TOSS System object-nameing rules.") is displaayed while executing below line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strSelectSQL, strFile, True

Thanks
Ria
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Jan 20, 2009
Messages
12,849
Re: The table name you entered doesn't follow System object-nameing rules

It does seem an obscure message.
Just a wild guess but maybe table names can't be aliased in an export.
 

MSAccessRookie

AWF VIP
Local time
Today, 04:43
Joined
May 2, 2008
Messages
3,428
Re: The table name you entered doesn't follow System object-nameing rules

Hi Galaxiom,

Thanks for the reply. Actually I have changed the ALIAS but error ("The table name you entered doesn't follow TOSS System object-nameing rules.") is displaayed while executing below line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strSelectSQL, strFile, True

Thanks
Ria

The value of strFile is the most likely candidate for causing the error message, but put a breakpoint at the DoCmd.TransferSpreadsheet line, and evaluate the contents of both strFile and strSelectSQL. By doing this, you might be able to see something that will help you determine the problem.
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Dear Friends,

Thanks for the reply. Please find the value I'm getting for the variables:

strFile: J:\TOSS New MIS Database\2011\Feb 2011\Reports\201102091553_TOSS_2011-01_1553.xls

acExport: 1

strSelectSQL: SELECT SU.CUSTOMER, SU.ISIN_NO, SU.TURNOVER, SU.SOURCE, MAP.OVERVIEW, ADV.SUB_ADV
FROM tbl_Details_YTD AS SU, tbl_Mapping AS MAP, tbl_Advisor AS ADV
WHERE SU.SOURCE = 'TOSS' AND SU.REPORTING_PERIOD = 201101 AND SU.GROUP_CODE=MAP.Mapping_Code And ADV.RM_CODE=SU.RM

Can we use SQLStatement instead of TableName in Export syntax?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strSelectSQL, strFile, True

Regards
Ria
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Sep 12, 2006
Messages
15,614
Re: The table name you entered doesn't follow System object-nameing rules

i think a table can't have a period/dot in the name. Maybe its a slash.

Try and create the table manually, and see what error you get - then you will know.
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Hi Dave,

I'm not using tablename while exporting the data. Data is extracted using SQL and that data I want to export.

Hope this is clear.

Regards
Ria
 

PeterF

Registered User.
Local time
Today, 09:43
Joined
Jun 6, 2006
Messages
295
Re: The table name you entered doesn't follow System object-nameing rules

From the help:The method TransferSpreadsheet does not accept a SQL string. Only table names and query names are accepted.
Save your SQL string as a Query then it should work.
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Hi Peter,

Thanks for the reply. I can't save this as a query becuase table does not exist in this database. This .mdb file contains only program and another .mdb file contain tables, data, queries etc.

Thanks
Ria
 

PeterF

Registered User.
Local time
Today, 09:43
Joined
Jun 6, 2006
Messages
295
Re: The table name you entered doesn't follow System object-nameing rules

Hi Ria,

This means that there's problably no other option then to change your sql to a table make query. Eport the created table and delete it after the export.
Your strSQL has to be changed to:
Code:
strSelectSQL = " SELECT SU.CUSTOMER, SU.ISIN_NO, SU.TURNOVER, SU.SOURCE, MAP.OVERVIEW, ADV.SUB_ADV [COLOR="Red"]INTO tmpTableName[/COLOR] " & _
" FROM tbl_Details_YTD AS SU, tbl_Mapping AS MAPPING, tbl_Advisor AS ADV " & _
" WHERE SU.SOURCE = " & gsSource & " AND SU.REPORTING_PERIOD = " & Me!txtReportingMonth & " AND SU.GROUP_CODE=MAPPING.Mapping_Code And ADV.RM_CODE=SU.RM "
Your actual export changed to:
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, [COLOR="Red"]tmpTableName[/COLOR] , strFile, True
[COLOR="Red"]docmd.DeleteObject acTable ,tmpTableName[/COLOR]

I'm not sure if quotes are needed for the tablename in the delete command.
If you do this often a regular compact and repair is needed.
 

vbaInet

AWF VIP
Local time
Today, 08:43
Joined
Jan 22, 2010
Messages
26,374
Re: The table name you entered doesn't follow System object-nameing rules

No need to make-table. You can "make"-query though:
Code:
    If DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
        DoCmd.DeleteObject acQuery, "qryTemp101"
        CurrentDb.QueryDefs.Refresh
    End If
    
    CurrentDb.CreateQueryDef "qryTemp101", "SELECT ..."
    CurrentDb.QueryDefs.Refresh
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True
    DoCmd.DeleteObject acQuery, "qryTemp101"
A much better way would be to have the query already created and for the criteria reference textboxes that have the values in them. This way you don't have to create a query and delete it afterwards. The WHERE part of the sql statement in the query will look something like this:
Code:
...
WHERE SU.SOURCE = [Forms]![SomeFormName]![txtgsSource] AND SU.REPORTING_PERIOD = [Forms]![SomeFormName]![txtReportingMonth] AND [COLOR=Red][B]SU.GROUP_CODE = MAPPING.Mapping_Code And ADV.RM_CODE=SU.RM[/B][/COLOR]
Also, you change the red bit to an INNER JOIN between SU and Mapping (via Group_Code <-> Mapping_Code) and between SU and ADV (via RM <-> RM_Code).
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Dear vbaInet (VIP)

Getting error at below line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True

Error:
The Microsoft Jet database engine cannot find the input table or query 'tbl_Details_YTD'. Make sure it exists and that its name is spelled correctly. :mad:

strSelectSQL = " SELECT SECU.CUSTOMER SECU.BUY_SELL FROM tbl_Details_YTD AS SECU"

If DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
DoCmd.DeleteObject acQuery, "qryTemp101"
CurrentDb.QueryDefs.Refresh
End If

CurrentDb.CreateQueryDef "qryTemp101", strSelectSQL
CurrentDb.QueryDefs.Refresh

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True
DoCmd.DeleteObject acQuery, "qryTemp101"

Regards
Ria
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Dear Peter,

I tried to implement the solution you provided that is also throwing the error:

Dim tmpSecucashTableName As String
tmpSecucashTableName = "tmpSecucashTable"

strSelectSQL = " SELECT SECU.CUSTOMER, SECU.BUY_SELL FROM tbl_Details_YTD AS SECU INTO tmpSecucashTable "

strFile = Me.Output_Dir & "\" & Format(Now(), "yyyymmdd") & Format(Now(), "hhmm") & "_Secucash_M2M_Comparision_" & Left(txtReportingMonth.Value, 4) & "-" & Right(txtReportingMonth.Value, 2) & "_" & Format(Now(), "hhmm") & ".xls"

DoCmd.SetWarnings False

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tmpSecucashTableName", strFile, True

Getting error at this point
Run-time error '3078':
The Microsoft Jet database engine could not find the object 'tmpSecuTable'. Make sure the object exists and that you spell its name and the path name correctly.

Any idea what's wrong?

Thanks and regards
Ria
 

vbaInet

AWF VIP
Local time
Today, 08:43
Joined
Jan 22, 2010
Messages
26,374
Re: The table name you entered doesn't follow System object-nameing rules

Like the error says, it doesn't exist.

Is tbl_Details_YTD the name of the table or this tbl Details YTD?

Does the sql statement run in a query? I would imagine it doesn't.
 

PeterF

Registered User.
Local time
Today, 09:43
Joined
Jun 6, 2006
Messages
295
Re: The table name you entered doesn't follow System object-nameing rules

If you don't run your SQL string it won't create a temp table.

As I think vbaInet's idea is the better way I would go with the query.
How do you connect to the "TOSS.mdb", by linked tables or do you make the connection in VBA?
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Dear vbaInet,

tbl_Details_YTD the name of the table.

Please find below the code:

Private Sub cmdBtnRunSecuSG_Click()
On Error GoTo ERR_HANDLER

Dim rsTmp As DAO.Recordset
Dim strSelectSQL As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim strFile As String
Dim varStatus As String
Dim strSelectSQLWhere As String
Dim tmpSecuTableName As String
tmpSecuTableName = "tmpSecuTable"

If IsNull(txtReportingMonth.Value) Then
MsgBox "Please enter REPORTING PERIOD!", 16, "Import Secu for SG"
Exit Sub
End If
varStatus = SysCmd(acSysCmdSetStatus, "Generating Secu Month to Month Comparision, please wait...")

strSelectSQL = " SELECT SECU.CUSTOMER, SECU.TRADE_NO, SECU.BUY_SELL FROM tbl_Secu_Details_YTD AS SECU"

Set rsTmp = gsCnPDatabase.OpenRecordset(strSelectSQL)

strFile = Me.Output_Dir & "\" & Format(Now(), "yyyymmddhhmm") & "_Secu_M2M_Comparision_" & Left(txtReportingMonth.Value, 4) & "_" & Right(txtReportingMonth.Value, 2) & "_" & Format(Now(), "hhmm") & ".xls"
DoCmd.SetWarnings False
'==============================
If DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
DoCmd.DeleteObject acQuery, "qryTemp101"
CurrentDb.QueryDefs.Refresh
End If
CurrentDb.CreateQueryDef "qryTemp101", strSelectSQL
CurrentDb.QueryDefs.Refresh
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True
DoCmd.DeleteObject acQuery, "qryTemp101"
'==============================

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tmpSecuTableName, strFile, True
DoCmd.DeleteObject acTable, tmpSecuTableName
rsTmp.Close
Set rsTmp = Nothing

DoCmd.SetWarnings True
Exit_Err_Handler:
Exit Sub
ERR_HANDLER:
MsgBox Err.Description
DoCmd.Hourglass (False)
varStatus = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass (False)
Resume Exit_Err_Handler
End Sub

Just want to highlight one point, my VBA code is in one database (e.g. CnPProg.mdb) and data / tables, queries are in another database (e.g. CnPDB.mdb). That's why I need to make the connection to execute the Select SQL using below code:
Set rsTmp = gsCnPDatabase.OpenRecordset(strSQL)

and looks like it's not able to create Query Defination.

I'm really stuck over here.

Regards
Ria
 

ria_arora

Registered User.
Local time
Today, 16:43
Joined
Jan 22, 2011
Messages
56
Re: The table name you entered doesn't follow System object-nameing rules

Dear vbaInet,

tbl_Details_YTD the name of the table.

Please find below the code:

Code:
Private Sub cmdBtnRunSecuSG_Click()
On Error GoTo ERR_HANDLER

Dim rsTmp As DAO.Recordset
Dim strSelectSQL As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim strFile As String
Dim varStatus As String
Dim strSelectSQLWhere As String
Dim tmpSecuTableName As String
tmpSecuTableName = "tmpSecuTable"

If IsNull(txtReportingMonth.Value) Then
MsgBox "Please enter REPORTING PERIOD!", 16, "Import Secu for SG"
Exit Sub
End If
varStatus = SysCmd(acSysCmdSetStatus, "Generating Secu Month to Month Comparision, please wait...")

strSelectSQL = " SELECT SECU.CUSTOMER, SECU.TRADE_NO, SECU.BUY_SELL FROM tbl_Secu_Details_YTD AS SECU"

Set rsTmp = gsCnPDatabase.OpenRecordset(strSelectSQL)

strFile = Me.Output_Dir & "\" & Format(Now(), "yyyymmddhhmm") & "_Secu_M2M_Comparision_" & Left(txtReportingMonth.Value, 4) & "_" & Right(txtReportingMonth.Value, 2) & "_" & Format(Now(), "hhmm") & ".xls"
DoCmd.SetWarnings False
'==============================
If DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
DoCmd.DeleteObject acQuery, "qryTemp101"
CurrentDb.QueryDefs.Refresh
End If
CurrentDb.CreateQueryDef "qryTemp101", strSelectSQL
CurrentDb.QueryDefs.Refresh
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True
DoCmd.DeleteObject acQuery, "qryTemp101"
'==============================

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tmpSecuTableName, strFile, True
DoCmd.DeleteObject acTable, tmpSecuTableName
rsTmp.Close
Set rsTmp = Nothing

DoCmd.SetWarnings True
Exit_Err_Handler:
Exit Sub
ERR_HANDLER:
MsgBox Err.Description
DoCmd.Hourglass (False)
varStatus = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass (False)
Resume Exit_Err_Handler
End Sub

Just want to highlight one point, my VBA code is in one database (e.g. CnPProg.mdb) and data / tables, queries are in another database (e.g. CnPDB.mdb). That's why I need to make the connection to execute the Select SQL using below code:
Set rsTmp = gsCnPDatabase.OpenRecordset(strSQL)

and looks like it's not able to create Query Defination.

I'm really stuck over here.

Regards
Ria
 

Users who are viewing this thread

Top Bottom