Access frontend can't save record to SQL server 1

[BMarkham]

I've got an Access front end database attaching to SQL server tables through a DSN connection. My table has 34 fields, 26 of which are nchar or nvarchar. Entering data from the table, I'm able to type in the first 17 fields without a problem. However, if I enter any information into fields 18 to 34, I get an ODBC call fail error.

When I look at design view of my attached table in Access, it's telling me that all my nchar and nvarchar fields have a size of 255 characters. This is not the case. Most are set between 8 to 50 characters.

I'm guessing this is the problem, that Access thinks that I've maxed out on my Access record size of 2000 characters.

Has anyone experienced this? Can anyone verify that this is what's going on?

Thanks

 

[allanon]

You did not state which SQL Server you are using. We had some issues with Access 97 maping to fields in SQL 7.0. SQL 7.0 and higher are unicode and some of the field types do not map to access field types.

 

[BMarkham]

Sorry. Access 97 attaching to SQL 2000.

 

[allanon]

Ok. I took a look at our databases and talked to the dba's. I recall that we tried to use nvarchar and they did not work well. Also, we had a really big problem with bit fields. If they were not initialized on the SQL side they often would crash access. Here is the character mapping we went with and that works well for us:

SQL Access
nvarchar not used
varchar text
bit yes/no
int number (long int)
money currency
float number (double)
datetime date/time
text memo

Hope this helps..

 

[BMarkham]

Thanks for the help.

The problem was with my SQL text fields. They were being mapped to Access as 255 character text fields (instead of memo fields.)

The simplest solution was actually decided for me. We're upgrading to office 2000. Access 2000 maps all the SQL fields to appropriate Access fields.

 

[allanon]

That would work. Some of us don't have that option.