How do I UPDATE a field in Access

[pochta]

I have been using VB6 to read data from Access. Now I want to write into the data base. I have set up a simple table with fields to test:

ID Name Number Mark

I have populated 5 records with random contents. In VB6 I have a form with one Command Button and one Text Box. The Text Box is bound to the Number field of the table. The following code is under the button:

Public Sub Command1_Click()


lnkData.CommandType = adCmdUnknown

lnkData.RecordSource = "UPDATE Test1 SET Test1.Number=123 WHERE ID=3"

lnkData.RecordSource = "SELECT Number FROM Test1 WHERE ID=3"

lnkData.Refresh


End Sub


The SELECT statement reads what is actually in the field but the UPDATE statement never changes it.

The routine was orginally Private. When I changed it to Public the Update worked the first time through. AArrrrrgh! It does not make sense.

Help!

 

[x508]

Are you using and ADODC control or some similar data control...If so then.......

If not so:

The easiest way to update a recordset is :

Build your connection, open your recordset and use

rs.fields("Number&quot = text1.text
rs.update

In any way if I can remember correctly, if you bind a textbox to a DB, whatever changes in that textbox should automatically be updated in the DB, not so?

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[CCLINT]

lnkData.ActiveConnection.Execute "UPDATE Test1 SET Test1.Number=123 WHERE ID=3",Options:=adCmdText + adExecuteNoRecords

lnkData.RecordSource = "SELECT Number FROM Test1 WHERE ID=3"

lnkData.Refresh


>if you bind a textbox to a DB, whatever changes in that textbox should automatically be updated in the DB, not so?

If you use Batch updating, (LockType = adLockBatchOptimistic and CursorLocation = adCUseClient) then the db only gets updated when the BatchUpdate is called.

 

[x508]

Ta CCLINT

I learn something new everyday!!

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[pochta]

Thanks for the help but all of the suggestions result in either compile time or run time errors in VB6.

I have narrowed the problem and can restate it. Here is the code using the Adodc1 for the connection link:

Global Adodc1 As Object

Public Sub PokeAccess()

frmSqlTest.Adodc1.CommandType = adCmdUnknown

frmSqlTest.Adodc1.RecordSource = "UPDATE Test1 SET Test1.Number=1234 WHERE ID=3"

frmSqlTest.Adodc1.Refresh 'Stops execution here

frmSqlTest.Adodc1.RecordSource = "SELECT Number FROM Test1 WHERE ID=3"

frmSqlTest.Adodc1.Refresh

The code runs and it does update the record in Access but it stops execution and gives me the error mesage "operation not allowed when object is closed". Oddly it does not give that message on the SELECT operation.

How do I "open" the object?

 

[CCLINT]

1. Look again at what was posted...It is NOT the same as yours.
2. You need to make sure the connection is made first.
Best is to use a connection object variable and open the connection with that, but you can also use the UPDATE on an opened ADODC connection.

 

[whitecane]

I use this code in all my access projects to create an ODBC connection to add to an Access data base then I use SQL put these functions in a module and call then at startup hope this helps you will need to insert your name of you database where mine is Student Record

Public Sub RegisterDatabaseX()
'This will create the DSN for the computer at install time
'This amy need another function to make it compatable with windows 98 and older

Dim dbsRegister As Database
Dim strDescription As String
Dim strAttributes As String
Dim errLoop As Error

'Build keywords string.
strAttributes = "DBQ=" & App.Path & "\" & "Student Record.mdb" & _
vbCr & "Description=" & "This is the DSN for Tom's Final Project" & _
vbCr & "OemToAnsi=No" & _
vbCr & "FIL=MSAcess;" & _
vbCr & "Server=Server1"


' Update Windows Registry.
On Error GoTo Err_Register
DBEngine.RegisterDatabase "StudentRecord", "Microsoft Access Driver (*.mdb)", _
True, strAttributes
On Error GoTo 0
Exit Sub
Err_Register:
'Notifiy user of any errors that result from
' the invalid data
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & _
vbCr & errLoop.Description
Next errLoop
End If

Resume Next


End Sub


Public Function GetWinDir() As String
' -- get the Windows Directory from Windows OS
Dim cTemp As String
Dim nTemp As Integer

cTemp = String$(255, 32)
nTemp = GetWindowsDirectory(cTemp, 255)
GetWinDir = Left$(cTemp, nTemp)

End Function

 

[CCLINT]

I strongly recommend not to use the Microsoft Access Driver, but use the JET driver instead...

 

[pochta]

Thanks!

I'm not an expert yet but with your help I have a solution to the problem.

Thanks again!