Problem linking to SQL tables in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Weird problem linking to SQL tables - if I create a project and connect to
the DB I can access tables fine - if I create Access DB & link to tables with
ODBC connection when I try to link to tables I get an error 3125 stating that
the primary key fields are not following standard naming conventions.
The SQL database is from our billing system - NDCLytec 2006
 
If you can't see the problem with the table or field names, look
also at the index names. Index names may also "include invalid
characters or punctuation [or be] too long"

(david)
 
I think you're correct, but the only environment where this happens - and, of
course, the only one I need it to work with - seems to be Access.
I can link to the tables in both Crystal Reports & SQL Admin Studio without
the syntax problem.

Chris

david@epsomdotcomdotau said:
If you can't see the problem with the table or field names, look
also at the index names. Index names may also "include invalid
characters or punctuation [or be] too long"

(david)

crm4094 said:
Weird problem linking to SQL tables - if I create a project and connect to
the DB I can access tables fine - if I create Access DB & link to tables with
ODBC connection when I try to link to tables I get an error 3125 stating that
the primary key fields are not following standard naming conventions.
The SQL database is from our billing system - NDCLytec 2006
Click to expand...
Click to expand...
 
Access has restrictions on valid names, and also implements
a very strictly standard compliant ODBC connection.

If the only problem is that the connection wizard is trying to
create an invalid alias for the table/field/index, then you may be
able to work around it by using standard VBA re-link code
(lots of examples available).

On the other hand, if the problem is that the ODBC driver is
presenting a name that does not meet ODBC specification,
then Access is just more strict, and there is nothing you can
do on the Access side.

When Access was created, it was a very flexible, powerful and
modern implementation: SQL Server had something like a 32
character name limit, 7 bit alpha/numeric characters only. But
MS made a strategic decision that Access and the native operating
system database support would not be enhanced, SQL Server
would be the high-end product.

(david)



crm4094 said:
I think you're correct, but the only environment where this happens - and, of
course, the only one I need it to work with - seems to be Access.
I can link to the tables in both Crystal Reports & SQL Admin Studio without
the syntax problem.

Chris

david@epsomdotcomdotau said:
If you can't see the problem with the table or field names, look
also at the index names. Index names may also "include invalid
characters or punctuation [or be] too long"

(david)

crm4094 said:
Weird problem linking to SQL tables - if I create a project and connect to
the DB I can access tables fine - if I create Access DB & link to
Click to expand...
tables
with
ODBC connection when I try to link to tables I get an error 3125
Click to expand...
stating
that
the primary key fields are not following standard naming conventions.
The SQL database is from our billing system - NDCLytec 2006
Click to expand...
Click to expand...
Click to expand...
 
I had a similar problem once and I rescued it on the SQL Server so that I
made views for all the tables with nonregular field names. In the views I
substituted the neregular field names with similar but regular field names.
And then I changed my links to the views instead of to the tables.

Ivan

crm4094 said:
I think you're correct, but the only environment where this happens - and,
of
course, the only one I need it to work with - seems to be Access.
I can link to the tables in both Crystal Reports & SQL Admin Studio
without
the syntax problem.

Chris

david@epsomdotcomdotau said:
If you can't see the problem with the table or field names, look
also at the index names. Index names may also "include invalid
characters or punctuation [or be] too long"

(david)

crm4094 said:
Weird problem linking to SQL tables - if I create a project and connect
to
the DB I can access tables fine - if I create Access DB & link to
tables with
ODBC connection when I try to link to tables I get an error 3125
stating that
the primary key fields are not following standard naming conventions.
The SQL database is from our billing system - NDCLytec 2006
Click to expand...
Click to expand...
Click to expand...
 
Back
Top