Using a period in table name 1

[rtshort]

I have created an App with VB to create tables, enter info in the tables, and edit the tables. When a user created a table with a period in the name (3.4 Litre) and went to add parts to the table it threw a SQL error "invalid table name" (and listed the table name).

To add parts I first check with an SQL Statement to see if the part already exists since more than one person input data daily. I use the query "Select * from TableName where PartName = '" & txtPartName.text" If none exist I use the AddNew method in ADO to add the info. It will work with anything except a period in the table name. (at least everything I've tried)

This aroused my curiosity so I opened the SQL Query Analyzer and selected all of the table names from the SysObjects. I copied the table name and pasted it in the analyzer, added "select * from" in the front, and put the table name in brackets []. When I ran the query it worked. I also did the same with an InsertInto statement. It added the information into the field like it should.

NOW the kicker. I opened the table and tried to add information into it manually and it gave me the same error that my VB App did. "Invalid table name" (tablename)

What's the deal? Rob
Just my $.02.

 

[rtshort]

Someone here has to have an opinion? Rob
Just my $.02.

 

[tlbroadbent]

Which version of SQL Server are you running?

I have SQL 2000 and MSDE 2000 installed. I've been testing table names with embedded periods. I've used Query Analyzer, Enterprise Manager, ISQL and OSQL to create and drop tables. I've been able to insert, delete, and update the tables with each tool.

Access 2000 will not allow the period in the name when creating a new table from the design window. Access will delete a table with a period in the name. I can create, drop and update a table with a period in the name by issuing SQL commands using RunSQL.

I cannot update a table with a period in the name in the datasheet view. Access returns a non-updateable result.

I haven't tried using an ADO connection. I suspect that I might encounter the same problem as you have in VB. ADO, ODBC or the SQL Driver may be displaying the error rather than SQL Server itself.

I don't know if there is a work around available for ADO. Perhaps an MDAC upgrade will do the trick. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[rtshort]

I'm using SQL Server 7.0. I don't think it is ADO because I can go into Enterprise Manager and open the table and put info in and go to a new record and I get the same error. The new record is not added.

I think I'll just leave the period out.

Thanks Terry. Rob
Just my $.02.

 

[benbotektips]

This one statement,
"I copied the table name and pasted it in the analyzer, added "select * from" in the front, and put the table name in brackets []. When I ran the query it worked."
saved me from an hour of aggravation. I couldn't figure how to get the table merged with another that didn't have a period. Previously, others worked by qualifying the table name, or enclosing in quotes "". Don't understand why the rules would be different for 'insert into...select' than 'select...from', where the ""quotes work.
In this vein, are there other idiosynchracies for table and/or column names, that gerenrate the unhelpful 'invalid object name' error message? Assuming I can't rename the table or column, what else is there to look out for with weird characters in table or column names?