SQL ODBC Update

[fabby1]

Hi

I have an Access database that connects to SQLServer via an ODBC link I can see the table fine.

I have a main form and a subform.

What I want to do is update the subform with the inputs from the user on the main form.

but I get an error

Private Sub AddRecord_Click()

    Dim rs As Object
    Dim rst As DAO.Recordset
    Dim StaffNum As Double
    
    StaffNum = Me.cmb_Search.Value

    Set rst = Me!SicknessMainFormSub.Form.RecordsetClone
    
 With rst
   .AddNew
  ' ...
  ' ...
  ' ...
 End With
   
End Sub

If I use

Set rst = Me!SicknessMainFormSub.Form.Recordset

I get one error and if I use

Set rst = Me!SicknessMainFormSub.Form.RecordsetClone

I get another

What should I use and can I update over an ODBC link

Thanks

Phil

 

[RoyVidar]

WHAT ERRORS ARE YOU GETTING - wouldn't you think that knowing the numbers and excact messages would help us understand what you're dealing with a little better? faq181-2886 #14 gives details making it easier to assist...

What recordsettype do you have? ADO or DAO? Is it an adp or mdb? Are you refering to the subform thru the subform control name (which might differ from the subform name as viewed in the database window)?

Test of recorsdettype:
[tt]dim rs as object
set rs=me.recordsetclone
if typeof rs is dao.recordset
msgbox "DAO"
else if typeof rs is adodb.recordset then
msgbox "ADO"
else
msgbox "Havent't the foggiest..."
end if[/tt]

- would require a reference to both DAO and ADO

If it is ado, declare rst as ado, and perhaps use me.recordset.clone (clone method of the recordset), think that would be needed in Access 2000, but perhaps not later versions.

Find the correct subform reference - enter the controlsource of a control on the main form, and use the expression builder. Doubleclick thru forms, open forms, main form, subform and a control on the subform - the resulting reference will give the correct subform control name.

Have you ensured the sql-server table contains a unique index and/or perhaps a timestamp?

- again - more info, else it's only guesswork...

But, what is wrong with having the users enter the information in the subform in stead of the main form?

Roy-Vidar

 

[fabby1]

Roy

Thanks for your reply.

The subform's recordset was based on the matched STAFFNO so it would contain only say 7 records.

Would this be a problem when it comes to updating.

ANYWAY

I have changed it to the following, so that I can just see if I can write to the table.

Private Sub AddRecord_Click()

Dim dbsCurrent As Database
Dim rst As DAO.Recordset
    
Set dbsCurrent = CurrentDb

    Set rst = dbsCurrent.OpenRecordset( _
        "SELECT * from dbo_ABSENCE", dbOpenDynaset)

 With rst
  .AddNew
  !STAFFNO = G_STAFFNO
  !DAYS = Me.txtDays
  .Update
 End With
   
End Sub

This gets as far as .UPDATE and comes back with

Run Time  error '3146'
ODBC -- call failed.

I have the users input from the main for because I calculate behind the scenes formula b4 appending the results or thats what I will do when I can get it to update.

Thanks
Phil

 

[RoyVidar]

This is out of my league.

If you're able to retrieve data, but not update, my guess would be there's some user rights thingie on the sql server, do they have sql rights? If they have only read only, then...

Roy-Vidar

 

[fabby1]

Roy

certainly not out of your league, your where rite at the start there was a KEY that needed to be there.

Thanks

Roy

as ever your have come up with the goods.