SQL table does not update?

D

Dean J Garrett

We have an Access 2002 front-end with a SQL 2000 Server back-end. The code
below executes without an error, but nothing is updated in the table. Can
anyone tell why?

Thanks!

---------------------------------------------------
Public Const CONNECTSTRING = "Provider=sqloledb;User
ID=dodah; pwd=grump; Data Source=HOME\SQL; Initial
Catalog=TestFront"
consql.ConnectString = CONNECTSTRING
consql.open

Dim rsOrder As New ADODB.Recordset

cSQL = "SELECT top 1 * FROM tblOrderItems
ORDER BY TransactionID DESC"
rsOrder.CursorLocation = adUseClient
rsOrder.Open cSQL, consql, adOpenDynamic,
adLockOptimistic

rsOrder.AddNew
rsOrder!OrderType = cHeldOrderType
rsOrder!InventoryID = Me!txtInventoryId
rsOrder!CustomerId = rsSource!AccountId
rsOrder!QuantityOrdered =
rsSource!QuantityOrdered
rsOrder!QuantityShipped =
rsSource!QuantityOrdered
rsOrder!Status = "Invoiced"
rsOrder!statusdate = Date
rsOrder!EnterDate = Date
rsOrder!ReceiveDate = Date
nTransact = rsOrder!TransactionID
rsOrder.Update

rsOrder.Close

No error. But the table does not update.
 
R

Ron Weiner

Dean

I do not see how the code you supplied could have worked at all. I do not
see where you are instantiating the Connection object. The connection
Object does NOT have a "ConnectString" property, but does have a
"ConnectionString" property. Also note that the new records ID will not be
available to you until after you perform the rsOrder.Update.

Here is some code that does work in my environment. Change the
CONNECTSTRING const, Sql Statement in strSql, and field names to match your
environment and you should be good to go.

Ron W

Public Sub TestIt()
Const CONNECTSTRING = "Provider=sqloledb;User ID=DirLogin; pwd=MYPassword;
Data Source=WorksRiteSrv3; Initial Catalog=Directions"

Dim conSql As ADODB.Connection
Dim rsOrder As ADODB.Recordset
Dim strsql As String, lngID As Long

Set conSql = New ADODB.Connection
Set rsOrder = New ADODB.Recordset

conSql.ConnectionString = CONNECTSTRING
conSql.Open

strsql = "SELECT top 1 * FROM tblAsmt ORDER BY AsmtID DESC"
rsOrder.CursorLocation = adUseClient
rsOrder.Open strsql, conSql, adOpenDynamic, adLockOptimistic

rsOrder.AddNew
rsOrder!DocID = 22
rsOrder!DocType = "qwerty"
rsOrder!AsmtDefID = 123456
rsOrder!AsmtName = "Name"
rsOrder.Update
lngID = rsOrder!AsmtID

Debug.Print "Added New Record " & lngID

rsOrder.Close
conSql.Close
Set rsOrder = Nothing
Set conSql = Nothing
End Sub
 
Top