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

P

PeterF

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

What's the proplem with your code is that you make a query for a remote database without a direct link. As I don't know a way to execute the transferspreadsheet command in the remote database this code wil probably always fail.
As a workaround you could create a temporary linked table to your remote database in the one where your code is so you can work with a local query.
 
V

vbaInet

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

Or simply move the code to where the tables and queries are.

Or connect to the database and use the database object for creating the query so it points to that database.
 
R

ria_arora

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

Hi vbaInet,

Please help to throw some light on your second option ("connect to the database and use the database object for creating the query so it points to that database.")

How to create the object and how to use that object? Please help me to pass some example / code if you have.

Thanks and regards
Ria
 
P

PeterF

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

Something like the below code should work. I copied your SQL and export strings that need some refining only to show what goes where, the actual connection will work.
Code: 
Function ConnectToRemoteDB()
Dim PathAndFilenameRemoteDB As String
Dim RemoteTableName As String
Dim tmpLocalTableName As String
Dim tmpLocalTable As TableDef
Dim tmpLocalQueryName As String
Dim tmpLocalQuery As QueryDef

Dim strSQL As String

PathAndFilenameRemoteDB = "[COLOR="red"][here the full file and path name of the remote db][/COLOR]"
tmpLocalTableName = "[COLOR="Red"][Here a temporary local table name (can be the same as the remote)][/COLOR]"
RemoteTableName = [COLOR="red"]"[Here the remote table name][/COLOR]"

'create a temporary linked table
Set tmpLocalTable = CurrentDb.CreateTableDef(tmpLocalTableName)
With tmpLocalTable
.Connect = ";DATABASE=" & PathAndFilenameRemoteDB
.SourceTableName = RemoteTableName
End With
CurrentDb.TableDefs.Append tmpLocalTable

'Create a temporary Query to export (if there's a saved one skip this
strSelectSQL = " SELECT SECU.CUSTOMER, SECU.TRADE_NO, SECU.BUY_SELL FROM tbl_Secu_Details_YTD AS SECU"
Set tmpLocalQuery = CurrentDb.CreateQueryDef(tmpLocalQueryName, strSelectSQL)
CurrentDb.QueryDefs.Append tmpLocalQuery

'Do the actual export
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True




Set tmpLocalTable = Nothing
Set tmpLocalQuery = Nothing


'delete the temporary linked table and query
CurrentDb.TableDefs.Delete tmpLocalTableName
CurrentDb.QueryDefs.Append tmpLocalQueryName

End Function
 
R

ria_arora

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

Hi Peter,

Thanks a lot for the help. As now data is coming for 3 tables so SQL is fetching data from 3 tables so I'm updating the code something like this:

Code: 
    Dim RemoteTableName_Secu As String
    Dim RemoteTableName_Map As String
    Dim RemoteTableName_Advisor As String
    
    Dim tmpLocalTableName_Secu As String
    Dim tmpLocalTableName_Map As String
    Dim tmpLocalTableName_Advisor As String
    
    Dim tmpLocalTableDef_Secu As TableDef
    Dim tmpLocalTableDef_Map As TableDef
    Dim tmpLocalTableDef_Advisor As TableDef
    
    Dim tmpLocalQueryName As String
    
    Dim tmpLocalQueryDef_Secu As QueryDef
    Dim tmpLocalQueryDef_Map As QueryDef
    Dim tmpLocalQueryDef_Advisor As QueryDef

        strSelectSQL = " SELECT SECU.CUSTOMER, SECU.TRADE_NO, SECU.BUY_SELL, SECU.FRONT_OFFICE, SECU.SECURITY_NO, SECU.SECURITY_NAME, SECU.ISIN_NO, SECU.TRADE_DATE, SECU.MATURITY_DATE, SECU.TRADE_CCY, SECU.NOMINAL, SECU.TURNOVER, SECU.VALUE_DATE, SECU.INCOME_IN_TRADE_CCY, SECU.INCOME_IN_BASE_CCY, SECU.INCOME_IN_TRADE_CCY/SECU.FX_RATE AS AMOUNT_EUR, SECU.FX_RATE, SECU.REPORTING_PERIOD, SECU.TRADE_INPUT_DATE, SECU.RM, SECU.SECUCASH_SOURCE, SECU.PRODUCT_GROUP_CODE, MAPPING.GMT_OVERVIEW, ADV.SUB_GMT " & _
                        " FROM " & tmpLocalTableName_Secu & " AS SECU, " & tmpLocalTableName_Map & " AS MAPPING, " & tmpLocalTableName_Advisor & " AS ADV " & _
                        " WHERE SECU.SECUCASH_SOURCE = 'SGBC' AND SECU.REPORTING_PERIOD = " & txtReportingMonth.Value & " AND SECU.PRODUCT_GROUP_CODE=MAPPING.Matrix And ADV.DBT_RM_CODE=SECU.RM "

I'll update you after finishing the code. Once again thanks a lot Peter for all the help.

Regards
Ria
 
V

vbaInet

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

ria_arora said:
Please help to throw some light on your second option ("connect to the database and use the database object for creating the query so it points to that database.")

How to create the object and how to use that object? Please help me to pass some example / code if you have.
Click to expand...
Here's some aircode:
Code: 
    dim objApp as object
    
    set objApp = new access.application
    
    objApp.OpenCurrentDatabase "... path to database ..."
    
    with objApp
        If [COLOR=Red].[/COLOR]DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
            [COLOR=Red].[/COLOR]DoCmd.DeleteObject acQuery, "qryTemp101"
            [COLOR=Red].[/COLOR]CurrentDb.QueryDefs.Refresh
        End If
        
        [COLOR=Red].[/COLOR]CurrentDb.CreateQueryDef "qryTemp101", "SELECT ..."
        [COLOR=Red].[/COLOR]CurrentDb.QueryDefs.Refresh
        
        [COLOR=Red].[/COLOR]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True
        [COLOR=Red].[/COLOR]DoCmd.DeleteObject acQuery, "qryTemp101"
    
        [COLOR=Red].[/COLOR]CloseCurrentDatabase
    end with
    
    set objApp = nothing
objApp is the access object which allows you to open the database.

Notice the dots in red which indicate that it should use whatever the WITH block is pointing to.
 
R

ria_arora

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

Can I used variable for database name?

If use variable for database name my system is not responding once it reach below step.
Code: 
objApp.OpenCurrentDatabase (strCnPDatabaseName)[\code]
 
value of strCnPDatabaseName is 
c:\Documents and Settings\ria\My Documents\MyDoc\MS Access\CnP Utilities DB.mdb
 
But If I hard code this value then it works but again it break while exporting the data. It throw the below 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.
 
[code]
objApp.OpenCurrentDatabase "C:\Documents and Settings\ria\My Documents\MyDoc\MS Access\CnP Utilities DB.mdb[FONT=Times New Roman]"[/FONT]
[FONT=Times New Roman][\code][/FONT]
[FONT=Times New Roman][/FONT] 
[FONT=Times New Roman][code][/FONT]
[FONT=Times New Roman]    With objApp
        If DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = 'qryTemp101'") <> 0 Then
            DoCmd.DeleteObject acQuery, "qryTemp101"
            CurrentDb.QueryDefs.Refresh
        End If[/FONT]
[FONT=Times New Roman]        CurrentDb.CreateQueryDef "qryTemp101", " SELECT SECU.CUSTOMER, SECU.TRADE_NO, SECU.BUY_SELL FROM tbl_Secucash_Details_YTD AS SECU"
        CurrentDb.QueryDefs.Refresh
        
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryTemp101", strFile, True
        DoCmd.DeleteObject acQuery, "qryTemp101"
    
        CloseCurrentDatabase
    End With[/FONT]
[FONT=Times New Roman]    Set objApp = Nothing[\code][/FONT]
 
I checked query "qryTemp101" is created but when I double click the query it says
 
"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."
 
Coz tbl_Details_YTD does not exist in current DB, this table is in remote database.
 
Please help
 
Thanks
Ria
 
V

vbaInet

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

Ria, look at my code again and you will find that inside the With objApp block all the code lines have a dot (.) as the first character.

I even pointed it out to you here:
vbaInet said:
Notice the dots in red which indicate that it should use whatever the WITH block is pointing to.
Click to expand...
 
R

ria_arora

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

Thanks Dear vbaInet

I was doing something wrong that's why I was getting the error. And later I end up changing the code anyway somehow I managed to solve that.

Initially I was using
Code: 
    objApp.OpenCurrentDatabase "" & strCnPDatabaseName & ""[\code]
Instead of 
[code]
objApp.OpenCurrentDatabase "D:\Documents and Settings\Ria\MS Access Programs\CnP Utilities DB.mdb"[\code]

Is there anyway I can use variable instead of hardcoded value?

And there is another issue I want to pass the password while opening the database. Because remote database is password protected:

[code]
objApp.OpenCurrentDatabase "D:\Documents and Settings\Ria\MS Access Programs\CnP Utilities DB.mdb"

'******Something like this**********
    ' Create Microsoft Jet Workspace object.
    Set gCurWS = DBEngine.Workspaces(0)
    Set gsCnPDatabase = gCurWS.OpenDatabase(sCnPDatabaseName, False, False, "MS Access;PWD=" & sCnPDatabasePassword)[\code]

Million of thanks for the help

Regards
Ria
 
V

vbaInet

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

ria_arora said:
1. Is there anyway I can use variable instead of hardcoded value?

2. And there is another issue I want to pass the password while opening the database. Because remote database is password protected
Click to expand...
Code: 
    objApp.OpenCurrentDatabase [COLOR=Blue]strCnPDatabaseName[/COLOR], , "[COLOR=Red]...password here...[/COLOR]"
 
R

ria_arora

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

Thanks Dear.

Great solution! :cool:

Thanks a ton.

Regards
Ria
 
V

vbaInet

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

You're welcome.

ria_arora said:
Dear vbaInet,

Do you have any solution for the issue I posted on below link:

http://www.access-programmers.co.uk/forums/showthread.php?t=205121&page=1

Highly appreciate your solution for the above issue as well.

Regards
Ria
Click to expand...
You've got someone competent helping you already. Wait for his reply. But just to mention, if a database fails to open it will throw and error. If there's a password and you put in the wrong password, it will throw an error. Every error message has a unique error number. Trap the error using error handling.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom