MS Access project with SQL Server

Author

Invalid Field Name

stephanie.harrell@stateauto.com

2005-10-27, 8:32 am

I have an Access project that I created from an existing Access
database. SQL is 2000, Access is both 2000 and 2003.

The existing Access tables had field names like [IDF DATA(1)],
[IDF DATA (2)], [IDF DATA (3)]. Those names are ok in SQL as long as
you use the brackets around them. The user wanted to add a few more,
so in design view, she copied the line and inserted it below. She
changed the name to [IDF DATA(4)] - using the brackets - and saved.
She received the error message

The name 'IDF DATA (4)' is invalid because it is empty, contains
invalid characters, or contains more than 128 characters.

I don't understand why the old names are ok, but adding a new one gives
the error. I can go in to SQL Server Enterprise manager and copy the
column.

Any thoughts anyone? I'd really like from my user to be able to do
this without my involvement and without giving her Enterprise Manager.

Bill Edwards

2005-10-27, 8:32 am

From Access Help:

"In Microsoft Access, names can be up to 64 characters long and can include
any combination of letters, numbers, spaces, and special characters except a
period (.), an exclamation point (!), an accent grave (`), and brackets
([ ])."

<stephanie.harrell@stateauto.com> wrote in message
news:1130164678.822285.242670@g44g2000cwa.googlegroups.com...
>I have an Access project that I created from an existing Access
> database. SQL is 2000, Access is both 2000 and 2003.
>
> The existing Access tables had field names like [IDF DATA(1)],
> [IDF DATA (2)], [IDF DATA (3)]. Those names are ok in SQL as long as
> you use the brackets around them. The user wanted to add a few more,
> so in design view, she copied the line and inserted it below. She
> changed the name to [IDF DATA(4)] - using the brackets - and saved.
> She received the error message
>
> The name 'IDF DATA (4)' is invalid because it is empty, contains
> invalid characters, or contains more than 128 characters.
>
> I don't understand why the old names are ok, but adding a new one gives
> the error. I can go in to SQL Server Enterprise manager and copy the
> column.
>
> Any thoughts anyone? I'd really like from my user to be able to do
> this without my involvement and without giving her Enterprise Manager.
>

Robert Morley

2005-10-27, 8:32 am

This is somewhat of a guess, but try adding the field without the []'s...I
think SQL Server will realize it needs them and add them on its own.

Rob

<stephanie.harrell@stateauto.com> wrote in message
news:1130164678.822285.242670@g44g2000cwa.googlegroups.com...
>I have an Access project that I created from an existing Access
> database. SQL is 2000, Access is both 2000 and 2003.
>
> The existing Access tables had field names like [IDF DATA(1)],
> [IDF DATA (2)], [IDF DATA (3)]. Those names are ok in SQL as long as
> you use the brackets around them. The user wanted to add a few more,
> so in design view, she copied the line and inserted it below. She
> changed the name to [IDF DATA(4)] - using the brackets - and saved.
> She received the error message
>
> The name 'IDF DATA (4)' is invalid because it is empty, contains
> invalid characters, or contains more than 128 characters.
>
> I don't understand why the old names are ok, but adding a new one gives
> the error. I can go in to SQL Server Enterprise manager and copy the
> column.
>
> Any thoughts anyone? I'd really like from my user to be able to do
> this without my involvement and without giving her Enterprise Manager.
>

Sylvain Lafontaine

2005-10-27, 8:32 am

The SQL-Server: yes but the table design tools in ADP: no (with or without
[] or "). This is one of the numerous known (and old) bugs of ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:%23s2H2vm2FHA.3588@TK2MSFTNGP15.phx.gbl...
> This is somewhat of a guess, but try adding the field without the []'s...I
> think SQL Server will realize it needs them and add them on its own.
>
>
> Rob
>
> <stephanie.harrell@stateauto.com> wrote in message
> news:1130164678.822285.242670@g44g2000cwa.googlegroups.com...
>
>

Robert Morley

2005-10-27, 5:30 pm

Ah, thanks for the info, Sylvain. Guess I'd simply never come across this
problem before.

Thanks again,
Rob

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OpRE7nn2FHA.1276@TK2MSFTNGP09.phx.gbl...
> The SQL-Server: yes but the table design tools in ADP: no (with or without
> [] or "). This is one of the numerous known (and old) bugs of ADP.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF

Sponsored Links