SQL Backend Issues

[nevets2001uk]

We have an access database here split into a frontend and backend system. I've just migrated the backend to SQL and relinked the tables in the front end.

All seems fine with most of the forms. Data is being read from the SQL database without a problem.

However I'm getting a number of issues that I can't for the life of me solve.

The front end has a button that allows the user to add a new record. It uses the code...

DoCmd.GoToRecord,, acNewRec

Clicking this results in the error "You can't go to the specified record. You may be at the end of the recordset"

So I tried opening the linked table directly and at first I can see the data and the fields. However I try to view the last record and I then get the error...

"The field is too small to accept the amount of data you are attempting to add. Try inserting or posting less data"

Then all the fields show as #Name?

A couple of suggestions I found where to change all smalldatetime fields to simply datetime, which I've done and also to change the text type (ex. memo field in access) to varchar(8000). Neither of these have corrected the problem. I can't understand why I can view the data at first but move to later records causes a problem. In SQL the last records all look okay and I can add a record manually.

Any suggestions on this issue would be most welcome as I've tried all the ideas I can think of!!

Cheers,

Steve G (MCP)

 

[nevets2001uk]

Additionally...

I have noticed that in the access when viewing the linked table directly (double clicking on it) I can scroll quite a way down the data before the error crashes in.

After the error it allows me to see all of the rows with the #Name? in all the fields. However the row count listed is 10172 whereas a rowcount in SQL shows a total of 16754 rows in the table.

Not sure if this has any baring on the problem?

Cheers,

Steve G (MCP)

 

[davejam]

not sure about the whole problem, but just a quick idea:

make sure your table has a key, maybe an incrementing field.

I know in the past i came up against problems when trying to add records to a none keyed table, access doesn't always have the best error messages.

not entirely sure about the #name?, how are the fields named?

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[nevets2001uk]

Thanks for the suggestion.

I had the same thought and went through ensuring that each field had a PK that was an identity field.

Ad for the #Name? issue, it's the data that's displaying like that after scrolling down the data in the linked table.
A example segment looks like

TransNo ClientID ClientOrderNo

1 #Name? #Name? #Name?
2 #Name? #Name? #Name?
3 #Name? #Name? #Name?

Cheers,

Steve G (MCP)