HOWTO: Use ADO to Query a Table with a Space in Its Name (189683)

MORE INFORMATION

If you want to query the Order Details table in the Access Northwind (NWind.MDB) database that ships with many products using ADO, you need to use code like the following:

   rsOrderDetails.Open "[Order Details]", cnNWind, adOpenStatic, _
                   adLockReadOnly, adCmdTable
				

You can also use the back quote if you need to use the table name with spaces. For example:

   select * from `Order Details`.
				

NOTE: You can only use the back quote in Access. A standard quote will not work.

This information also applies when using the ADO Data Control that ships with Visual Basic 6.0.


SQL Server 7.0 does support columns names with spaces.

       table1 -------- table name
       ff cc  -------- column name
       char(10)-------- column type
				

In ISQL ( aka query analyzer) you can use select [ff cc] from table1

        insert will occur via
        insert into table1([ff cc]) VALUES('xx')
				

If you execute the statement

        set quoted_identifier on 
				

You can use

        select 'ff cc' from table1
				

You can create a table that have column names with spaces like:

        create table table1([col1 xx] integer)
				

A standard quote will not work. Back quotes do not currently work with back end as SQL Server 7.0.