SQL Server 2000 linked to Access 2000

Question

Post reply

SQL Server 2000 linked to Access 2000

Mithrandir

SSCrazy

Points: 2628

April 1, 2004 at 3:26 pm

#65120

Hi, I'm having a problem with a SQL Server view linked in Access:

CREATE TABLE dbo.MyTable(
emp_no INT not null primary key,
name VARCHAR(50) not null,
lastname VARCHAR(50) not null,
fullname AS (name + ' ' + lastname),
birthdate smalldatetime
)
CREATE VIEW dbo.vwMyView
AS
SELECT field1, name, lastname
FROM dbo.MyTable

This is OK, I create an user with select, insert, update & delete permission over the VIEW. I don't want to able him to access the table.

I do a test inserting records into the view from QA and all is Ok.

Then I create an ODBC with that user and create an Access 2000 DB/Project.

But when I open the view from Access I only can read data. Tells me "recordset not updateable".

¿How can I made the view Updateable from Access?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ebong SSC-Addicted Points: 419 More actions · Answer 1

When you create a link to Access, make sure you identify a PrimaryKey (unique identifier) for the SQL Server Table.

If you do not identify what will uniquely identify your records, Access will not let you update the trecord from withing Access.

It will however allow you to update it via the Query Window..

Mithrandir SSCrazy Points: 2628 More actions · Answer 2

Thanks Ebong, that's worked.

If I have the table as an ODBC linked table works fine.

If I have ADO direct server access with an Access Project I can't find how to declare the Row Id.

I'd prefer to use a Project instead a normal DB. You know how can I declare that row id using a project?

Ebong SSC-Addicted Points: 419 More actions · Answer 3

What is this RowID..

I would think you would only need to create Stored procedure, supply it with data in the form of a command object and execute it with your connection object..

i.e.

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

dim cmd1 as new ADODB.Command

cmd1.ActiveConnection = cn

cmd1.CommandType = adCmdStoredProc

cmd1.CommandText = "Procedure Name"

cmd1.Parameters.Refresh

cmd1Parameters(1).Value = 10

cmd1.Parameters(2).Value = 10

'if require returned values then use rs

i.e. Set rs = cn.Execute(cmd1)

'if you do not require returned values then just execute it

cmd1.Execute

Mithrandir SSCrazy Points: 2628 More actions · Answer 4

Thaks again.

I prefer to keep it simple and have all capabilities direclty to Access table/view object.

I think I will use ODBC to be capable to delcare the Primary Key (row id) in access.

kaeg Old Hand Points: 378 More actions · Answer 5

If you use Access Project, you shouldn't have the problem at all.

Anyhow, what field1 in the Create View statement stands for? Or it's a typo?

CREATE VIEW dbo.vwMyView

AS

SELECT field1, name, lastname

FROM dbo.MyTable

Try replacing it with emp_no and see if the problem disappeared.

Mithrandir SSCrazy Points: 2628 More actions · Answer 6

Thanks.

This Access incursion has been a little troubing. I'm, going to do it via web-based.

My skill is greater there 🙂

Robert Duncan Newbie Points: 5 More actions · Answer 7

I've tried this myself, but can't figure out how to make the recordset updateable.

I have a stored procedure that takes four parameters. I link this to a form in Access 2000 ADP file. On the form, the user will have a kind of search bar where they enter the info for the parameters. They can then click a button and see the results. They then have to be able to edit them.

I've tried this by setting the stored procedure as the form's recordsource, selecting a uniquetable, and attempting the set the inputpareters programmatically. This worked sort of. I did end up with the subset of data that I wanted and it was editable. But the input paremters caused a lot of unwanted dialog boxes. I could not figure out how to do this programatically.

So I thought I'd try setting the form's recordset programatically, as follows:

Dim cnn As ADODB.Connection

Dim cmd As ADODB.Command

Dim prm As ADODB.Parameter

Set cnn = CurrentProject.Connection

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = cnn

.CommandText = "sp_attEditHours"

.CommandType = adCmdStoredProc

Set prm = .CreateParameter("indate", _

adVarChar, adParamInput, 10, strDate)

.Parameters.Append prm

..... [more parameters] .....

Set Me.Recordset = cmd.Execute

Me.UniqueTable = "attend"

End With

Me.Refresh

This works quite well, but the form is not updatable. It says "The recordset is not updateable".

What can I do?

Thanks,

R. Duncan