Errors saving Access2k ADP form info to SQL Server 2k

[jimbo62]

I have coded DAO to perform this operation to a local .MDB but ADO connection strings and working in the recordset have me stumped could anyone please point me in the right direction I have spent a few hours tryin all I could find on the subject of connecting and the like.

Here is the last code I tried and it gave me errors saying..

"The connection cannot be used to perform this action, it is either closed or invalid in this context"


''Save with ADO connection
''
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "vwFrozeworkdb"


With rst
rst.AddNew
rst!LOAISL = Me.txtLOAISL
rst!LOBAY = Me.txtLOBAY
rst!LOLEVL = Me.txtLOLEVL
rst!LOSKU = Me.TxtReason

rst.Close

Set cnn = Nothing
Set rst = Nothing


End With

Jimbo

 

[jimbo62]

After reviewing all of my code and research here is the working solution that I came up with. My thanks to those who may have read my post and if you need an answer to my question here it is.....


''Save Information entered on Form with ADO connection to
''a SQL Server Database Table for logging changes. Used on click event of a button


Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "WLOCB_FROZEN_WORK", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable


With rst
rst.AddNew
rst!LOAISL = Me.txtLOAISL
rst!LOBAY = Me.txtLOBAY
rst!LOLEVL = Me.txtLOLEVL
rst!LOSKU = Me.TxtReason



rst.Update
rst.Close

Set cnn = Nothing
Set rst = Nothing
End With

Thanks,



Jimbo