[RESOLVED] Updating fields in a database

[BeKay]

First, before i say anything really absurd, i'm just starting with visual basic, so i don't know that much about it.
Ok so here is the deal, i'm creating a database in MS Access 2002, and i have several independent fields, that once you click a button are updated into a table. Something like i write "bob" in a text box, and when i press a button "bob" is added to my table.
Now i managed to get this to work, but after developing a bit more the datbase it stoped working for some reason. Then i tested in a new batabase, with just a table with 2 fields, and a simple form, and it didn't worked either, so i guess there's something wrong with the code i'm using.
I saw 2 different tutorials on how to do this, neither of them work, so i'll post both here:

Private Sub cmd1_Click()
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
 
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Table1")
 
   rst.AddNew
   rst("test").Value = txt1
   rst.Update
End Sub

Private Sub cmd1_Click()
 
 
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    
 
    rst.Open "Table1", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
    If rst.Supports(adAddNew) Then
        rst.AddNew
        rst.Fields("test") = "txt1"
        rst.Update
    End If
    
 
    rst.Close
    Set rst = Nothing
    
End Sub

So any ideas why it doesn't work (despite it being working before)?

[DKenny]

You need to open the recordset with a dynamic cursor if you want to write back to the DB. In both these cases you are using a Static cursor.

If you change adOpenStatic to adOpenDynamic in the second procedure, you should be able to write to the table. You also don't need the IF statement.

Private Sub test()
 
 
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    
 
    rst.Open "Table1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    rst.AddNew
    rst.Fields("test") = "txt1"
    rst.Update
 
    rst.Close
    Set rst = Nothing
    
End Sub

[KGComputers]

Hi,

Most programmers prefer using ADO instead of DAO if your project is vb6 (frontend).You may ignore the first code and instead follow DKenn'ys code.You may use DAO if your code is for MS access only (VBA).

[BeKay]

Thanks for the answers, but it still isn't working, here is the exact code i'm using:

Option Compare Database
Option Explicit
 
Private Sub cmdContinuar_Click()
 
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    
    rst.Open "Semana", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    rst.AddNew
    rst.Fields("data_inicio") = txtDInicial
    rst.Fields("data_fim") = txtDFinal
    rst.Update
 
    rst.Close
    Set rst = Nothing
    
End Sub

"Semana" is the name of the table.
"data_inicio" and "data_fim" are 2 date fields in the table. The table also has a increment(sp?) fiel named ID.
"txtDInicial" and "txtDfinal" are two independent text box, wich are also formatted to have the date type.
Reading the last post, my database is MS Access only, so does it make a big difference if i use ADO instead o DAO? If so how do i had a dynamic cursor using DAO?

[humbertob]

You are forgetting the most important part: Declaring the connection

Private cn As ADODB.Connection

and: setting the connection before creating the recordset

Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= c:\database\access_database.mdb"
    cn.Open

the first chunk of code goes on the General section of your VB project and the second chunk of code goes on the form_load section.

[BeKay]

Ok so 2 problems now, first i get a error when i open the form. My access version is in portuguese so i'll try to translate the best way i can:

"The expression On Load that you introduced has a definition of the property of the event reported the following error: There was a problem while Microsoft Access was comunicating with the OLE server or the ActiveX control
* the expression cannot result in the name of a macro, the name of a user defined function or [event procedure]
* probablly there was a error evaluating the function, procedure or macro"

Second thing i noticed that i need to point to the location of the database in my hard drive to make the conection, would this work if the database was located in a local server and was to be accessed from several computers in the network?

Again here is my code now:

Option Compare Database
Option Explicit
Private cn As ADODB.Connection
 
Private Sub cmdContinuar_Click()
 
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    
    rst.Open "Semana", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    rst.AddNew
    rst.Fields("data_inicio") = txtDInicial
    rst.Fields("data_fim") = txtDFinal
    rst.Update
 
    rst.Close
    Set rst = Nothing
    
End Sub
 
Private Sub Form_Load()
 
Set cn = New ADODB.Connection
 
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= C:\Documents and Settings\sergio\Desktop\bd1.mdb"
    cn.Open
    
End Sub

i also tried to put "Data Source= [C:\Documents and Settings\sergio\Desktop\bd1.mdb]" do the the path having a space, but it gave the same error.

Thanks for all the replies so far

[BeKay]

sorry had to bump this, any one has any ideas?

[d3gerald]

as what i have noticed in your sdo code above, you dont have an open connection. Perhaps you declared it somewher as public. make sure it is open and try your code again.

[RobDog888]

CurrentProject.Connection is the open connection.

Note, this is being done in Access VBA.

[BeKay]

i've been reading posts and tutorials on the forum and still aint working
i changed the code to the following:

Option Compare Database
Option Explicit
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
 
Private Sub cmdContinuar_Click()
    
    With rst
        .AddNew
        .Fields("data_inicio") = txtDInicial.Text
        .Fields("data_fim") = txtDFinal.Text
        .Update
    End With
 
 
    
End Sub
 
Private Sub Form_Load()
   
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= \bd1.mdb"
    cn.Open
    
    Set rst = New ADODB.Recordset
    rst.Open "Semana", cn, adOpenDynamic, adLockOptimistic
    rst.MoveFirst
   
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
    cn.Close
    rst.Close
    Set rst = Nothing
    Set cn = Nothing
 
End Sub

still gives the same error, and doesn't work. I noticed that if i move the private variables on to the cmd click function, the error is gone, but it still doesn't do what i want. Any1 can give me any links about doing this with DAO?
Also is there a way for me to test if the connection to the database/table is actually being done properlly?

[simon ong]

Option Explicit
Private cn As New ADODB.Connection
Private rst As New ADODB.RecordSet
 
Private Sub cmdContinuar_Click()
    
    With rst
        .AddNew
        .Fields("data_inicio") = txtDInicial.Text
        .Fields("data_fim") = txtDFinal.Text
        .Update
    End With
End Sub
 
 
Private Sub Form_Load()
 
    Dim Connectionstring As String
    
    Set cn = New ADODB.Connection
    Set rst = New ADODB.RecordSet
    rst.CursorLocation = adUseClient
    
    'Make Sure your Database Path is correct
    Connstring = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\bd1.mdb;Persist Security Info=False"
       
   
    cn.Open Connstring
    
    'Assuming Semana is a Database Table form bd1.mdb
   
    rst.Open "Semana", cn, adOpenStatic, adLockOptimistic, adCmdTable
    
    txtDInicial.Text = rst.Fields("data_inicio")
    txtDFinal = rst.Fields("data_fim")
    
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
   cn.Close
   rst.Close
   Set rst = Nothing
   Set cn = Nothing
 
End Sub
 
Private Sub RSTMoveNxt_Click()
rst.MoveNext
If rst.EOF Then rst.MoveLast
txtDInicial.Text = rst.Fields("data_inicio")
txtDFinal.Text = rst.Fields("data_fim")
End Sub
 
Private Sub RstMovePrev_Click()
rst.MovePrevious
If rst.BOF Then rst.MoveFirst
txtDInicial.Text = rst.Fields("data_inicio")
txtDFinal.Text = rst.Fields("data_fim")
 
End Sub

This shuould Work.

[RobDog888]

You may want to open your recordset with adOpenKeyset instead.

rst.Open "Semana", cn, adOpenKeyset, adLockOptimistic, adCmdTable

[BeKay]

Ok now using simon code i get a "variable not defined error" and it jumps to the App.path part :S

[RobDog888]

Thats because in Access there is no App.Path. Use ...

Application.CurrentDb.Properties(0).Value

[BeKay]

stil not working

i get the error i mentioned above whenever i load/unload the form, wich makes me think it has to do with the connection string:

The expression On Load that you introduced has a definition of the property of the event reported the following error:
There was a problem while Microsoft Access was comunicating with the OLE server or the ActiveX control
* the expression cannot result in the name of a macro, the name of a user defined function or [event procedure]
* probablly there was a error evaluating the function, procedure or macro

what i've now:

Option Compare Database
Option Explicit
Private cn As New ADODB.Connection
Private rst As New ADODB.Recordset
 
Private Sub cmdContinuar_Click()
    
    With rst
        .AddNew
        .Fields("data_inicio") = txtDInicial.Text
        .Fields("data_fim") = txtDFinal.Text
        .Update
    End With
    
End Sub
 
Private Sub Form_Load()
 
    Dim Connstring As String
    
    Set cn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    
    Connstring = "Provider = Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Documents and Settings\sergio\Desktop\bd1.mdb; Persist Security Info=False" & _
    "Application.CurrentDb.Properties(0).Value"
       
   
    cn.Open Connstring
 
    
    rst.Open "Semana", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    txtDInicial.Text = rst.Fields("data_inicio")
    txtDFinal = rst.Fields("data_fim")
    
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
 
   cn.Close
   rst.Close
   Set rst = Nothing
   Set cn = Nothing
 
End Sub
 
Private Sub RSTMoveNxt_Click()
 
    rst.MoveNext
 
    If rst.EOF Then rst.MoveLast
 
    txtDInicial.Text = rst.Fields("data_inicio")
    txtDFinal.Text = rst.Fields("data_fim")
 
End Sub
 
Private Sub RstMovePrev_Click()
 
    rst.MovePrevious
 
    If rst.BOF Then rst.MoveFirst
 
    txtDInicial.Text = rst.Fields("data_inicio")
    txtDFinal.Text = rst.Fields("data_fim")
 
End Sub

i tought this would be easier :P

[BeKay]

Finally it's fixed, i forgot that I had changed the form properties so that it wouldn't allow addin new records.. sorry for wasting every1 time
Still thanks for your help