I have been asked to maintain an Access 2000 database which is a code front
end linked by ODBC to a SQL Server 2005 back end. (Originally the data was
in an Access database but was later moved to SQL Server 2005 by the Upsizing
tools.)
Some tables have column names which do not conform to the rules given in SQL
Books Online - there are field names with embedded spaces, first character
not a-z or A-Z, name contains '/' and two names which are T-SQL reserved
words. When the tables are viewed via Management Studio, these 'odd' names
are enclosed in square brackets.
The production system is working in this state, but when I try to set up a
test environment on a stand-alone PC, I cannot access these tables. If I try
to link them I get 'ODBC -- Call failed', and if I try to import them I get
error messages about various column names. I can only link to, or import
other tables which do not have these 'odd' field names. If I rename the
columns in SQL server then I can import or link them with no problem.
(My PC has the latest service packs for SQL Server 2005 and Office 2003, and
MDAC 2.8 SP1.)
Somehow the production system works with this data but my test PC doesn't.
What do I need to do on my test PC to use this data with the original 'odd'
column names?
How are you linking the tables? You can write DAO/VBA code to pass the
correctly-delimited table names.
Frankly, you'd be better off just changing the names to conform to
SQLS identifier rules. Having non-compliant names is just going to
cause more headaches down the road. Take the hit now before you've
deployed the app.
-mary
On Fri, 22 Jun 2007 07:54:04 -0700, didub
<didub@.discussions.microsoft.com> wrote:
>I have been asked to maintain an Access 2000 database which is a code front
>end linked by ODBC to a SQL Server 2005 back end. (Originally the data was
>in an Access database but was later moved to SQL Server 2005 by the Upsizing
>tools.)
>Some tables have column names which do not conform to the rules given in SQL
>Books Online - there are field names with embedded spaces, first character
>not a-z or A-Z, name contains '/' and two names which are T-SQL reserved
>words. When the tables are viewed via Management Studio, these 'odd' names
>are enclosed in square brackets.
>The production system is working in this state, but when I try to set up a
>test environment on a stand-alone PC, I cannot access these tables. If I try
>to link them I get 'ODBC -- Call failed', and if I try to import them I get
>error messages about various column names. I can only link to, or import
>other tables which do not have these 'odd' field names. If I rename the
>columns in SQL server then I can import or link them with no problem.
>(My PC has the latest service packs for SQL Server 2005 and Office 2003, and
>MDAC 2.8 SP1.)
>Somehow the production system works with this data but my test PC doesn't.
>What do I need to do on my test PC to use this data with the original 'odd'
>column names?
|||I agree that the column names should be changed to satisfy SQL naming rules,
and I plan to do that, but that is not my first priority.
This app. was created and deployed several years ago by someone who is no
longer with the company. On the live server and desktops it is working with
these invalid names.
My first objective was to install a copy of the system on my own PC so I can
take it apart and plan some major changes and bug fixes. So ideally I would
just like to get my test system working as the live system does.
My guess is that there are some ODBC related settings that I need to change.
Or perhaps there is something I need to do on the SQL Server database. I
have searched Knowledgebase and the forums but I haven't found anything
helpful yet.
"Mary Chipman [MSFT]" wrote:
> How are you linking the tables? You can write DAO/VBA code to pass the
> correctly-delimited table names.
> Frankly, you'd be better off just changing the names to conform to
> SQLS identifier rules. Having non-compliant names is just going to
> cause more headaches down the road. Take the hit now before you've
> deployed the app.
> -mary
> On Fri, 22 Jun 2007 07:54:04 -0700, didub
> <didub@.discussions.microsoft.com> wrote:
>
|||As I recommended earlier, write code so that you can specify the
delimiters. If you're using UI tools then you have no way of knowing
what the unspoken assumptions are - i.e., what is being passed to ODBC
and then the server. Another option is to put a Profiler trace on it
and see what the server is receiving from the client. It's hard to
tell from a distance what's actually going on under the covers with an
app, especially if someone else wrote it.
-mary
On Fri, 22 Jun 2007 15:09:01 -0700, didub
<didub@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I agree that the column names should be changed to satisfy SQL naming rules,
>and I plan to do that, but that is not my first priority.
>This app. was created and deployed several years ago by someone who is no
>longer with the company. On the live server and desktops it is working with
>these invalid names.
>My first objective was to install a copy of the system on my own PC so I can
>take it apart and plan some major changes and bug fixes. So ideally I would
>just like to get my test system working as the live system does.
>My guess is that there are some ODBC related settings that I need to change.
>Or perhaps there is something I need to do on the SQL Server database. I
>have searched Knowledgebase and the forums but I haven't found anything
>helpful yet.
>
>"Mary Chipman [MSFT]" wrote:
