SQL as backend and Access as frontend - Add new not working

[wz]

I am a beginner with SQL and the business that I work for wishes to increase dramatically our Access databases. So....we are wanting to use SQL for backend only.

I used the migration assistant (2005) and now all the tables sit in SQL.

Everything seems to be working well with the linked tables in Access. I can edit & run all my reports - except...
when I want to ADD a new customer (a form with 4 pages & sub forms - that works perfect when entirely in Access) - now it will not let me ADD a new customer with address, etc.

error: ODBC - call failed (cannot insert the value NULL into column...

error: The linkmasterfields property setting has produced this error: The object doesn't contain the Automation object 'tbl...

I created a view in SQL that is the same as my querry & I get this error:
No row was updated.
The data was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: View or function 'view_vEnterData' is not updatable because the modification affects multiple base tables.

Any help? Ideas? suggestions?

 

[mrdenny]

You'll want to go back to the method you were using first. The table and column that you got the error message about. Are you trying to put a value into that field? Is it supposed to be putting a value in automatically?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[SQLSister]

Do you have a primary key on each of the tables? You must have that if you are to add records when linked to SQL Server.

"NOTHING is more important in a database than integrity." ESquared

 

[wz]

Dear me! did I say I was new to SQL....

Mrdenny: I am tring to place a new record in to several tables and yes the "customer number (primary key)" is added automatically (with linked tables) when the end user adds a new customer. - atleast it did (in Access07)

SQLsister: yes they all have primary keys -

FYI...I tried to have a separate form in which the end user adds the NEW customer number (so they are not on the data entry screen with all the sub forms & pages, etc). and I receive this error: View or function 'View_... is not updatable because the modification affects multiple base tables.

 

[mrdenny]

That error is to be expected. You can't write to a view, if the write needs to access more than one table.

Check the properties of the table in the SQL Server, and make sure that the IDENTITY property of the field which used to be an autonumber field got turned on.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[wz]

mrdenny: thanks for the info - I thought I could write to a view. I guess I need to take a course on SQL.

As far as autonumber. I only have one table with autonum & the Identity is yes.

 

[mrdenny]

You'll probably want to use SQL Profiler to see what the actual error message on the SQL Server is. Access can often mangle the error messages.

Check for a local SQL Server users group. They are pretty much everywhere. They will usually meet once a month, and will have all sorts of good info.

They are usually free, or like $5 a meeting (to help cover the cost of the pizza, and soda). Also look for local Code Camps (the users group can help with this). Code Camps are free, and are weekend long conferences chock full of local speakers.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[markros]

The NY SQL Server group is great. I didn't really explore UG once I moved to a new state.

 

[SQLSister]

View or function 'View_... is not updatable because the modification affects multiple base tables.

You can udate from a view but can only affect one base table per insert statment, so there is really no reason to update from a view. Rewrite your updates to update the base tables directly. You will need one update statement for each base table. Since you will probably want this in a transaction (so if one update fails, they all do), I would suggest using a stored proc to do the updates. Look in Books Online for information on transactions and stored procs.

"NOTHING is more important in a database than integrity." ESquared