Some Added Records Not Showing When Updating An Access Database

Added Records Not Showing In Recordset
I have a problem where i add a client record to a recorset through vb, it adds to the database but wont show in the recordset when scrolling through vb.

Heres the code I am using:


VB Code:
Private Sub Form_Load()                 Set cnDetails = New Connection        Set comDetails = New Command        Set rsDetails = New Recordset        Dim strDetails As String    'Determines the provider and the connection to    'the specified database.    cnDetails.Provider = "Microsoft.Jet.OLEDB.4.0"    cnDetails.ConnectionString = "A:VBPRO_Compacted.mdb"     'Opens the connection    cnDetails.Open     comDetails.ActiveConnection = cnDetails     'SQL statement    strDetails = "SELECT tblClient.C_Number, tblClient.C_F_Name, tblClient.C_L_Name, tblClient.C_Street, tblClient.C_Town, tblClient.C_County, tblClient.C_Phone, tblClient.C_Age, tblClient.C_Preferences, tblClient.C_Gender, tblClient.Notes, tblStaff.S_F_Name FROM tblStaff INNER JOIN tblClient ON (tblStaff.S_Number = tblClient.C_Prefered_Stylist)Order By tblClient.C_Number Asc "     rsDetails.Open strDetails, cnDetails, adOpenStatic, adLockOptimistic        'Calls the BindData funtion    BindData  End Sub   Private Sub cmdAdd_Click() 'Allows the user to add a new record'to the list of clients.'Hides the various text/Combo boxes.    txtFName.Locked = False    txtLName.Locked = False    txtStreet.Locked = False    txtTown.Locked = False    cboCounty.Locked = False    txtPhoneNo.Locked = False    txtPreferences.Locked = False    cboStylist.Locked = False    cboGender.Locked = False    cboAge.Locked = False    cmdHistory.Visible = False    cmdAddSave.Visible = True    fraControls.Visible = False    cmdBack.Visible = True         rsDetails.AddNew  End Sub  Private Sub cmdAddSave_Click()rsDetails.Update     MsgBox "Record Added Successfully", vbInformation, "Client Record Added"End Sub

---

Records Not Added To Access 2000
Trying to add records to MS Access 2000 database using ADO from VB 6.0. Every thing is working fine but the users are complaining some records are not added. When I tried to add the record it is adding. Not getting any error message even though error handler is there to check for cn.errors when the users add it. Any ideas?

No speific action is required from the user. Code is part of a OK command button in the application. I am opening the connection and doing a addnew for the record set and finally update to add the record.

Could you please let me know how to find the number of concurrent users setting in access 2000 so that I can find out the number of users allowed to use the database.

---

Combo Box Selection Not Showing Records From Database Properly.
HI guys i had my software working like a dream.

was about to start the next phase when its all went $%^& shaped.

The following code worked

Code:
Option Explicit
Private Sub form_load()
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    
    Set adoConn = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
    
    adoConn.Open "DSN=Test; UID=Admin; PWD=rezq; DBQ=C:dbsmartlaunch.db"
    
    '[bruger-infoid]-infoID,[bruger-fornavn]-firstname,[bruger-efternavn]-surname,[bruger-foesdag]-age,
    '[bruger-adresse]-address,[bruger-postnr]-zipcode,[bruger-email]-email,[bruger-tlf]-telephone
    '[bruger-mobil]-mobile,[bruger-koen]-sex,[bruger-by]-city,[bruger-personalnumber],[bruger-personalnumberverified]
    With adoRS
        .Open "SELECT [bruger-infoid] FROM [tbl-brugerinfo] WHERE [bruger-infoid] Is Not Null", adoConn, adOpenForwardOnly, adLockOptimistic
           .MoveFirst
        Do Until .EOF
            If Not IsNull(adoRS![bruger-infoid]) Then cbousername.AddItem adoRS![bruger-infoid]
            .MoveNext
        Loop
        .Close
    End With

Private Sub cbousername_click()
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
        'declares connections
    Set adoConn = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
        'set conections
    adoConn.Open "DSN=Test; UID=Admin; PWD=rezq; DBQ=C:dbsmartlaunch.db"
        'connects to the database
    Dim sUserID As String
    Dim sSQL As String
    sUserID = cbousername.Text
        'declares settings and the combobox selection

    If IsNumeric(sUserID) Then
       sSQL = "SELECT [tbl-brugerlogin].[bruger-lastlogin] as LLName,[tbl-brugerlogin].[bruger-oprettet] as ACName,[bruger-fornavn] AS FName,[bruger-efternavn] AS LName,[bruger-adresse] as AdName,[bruger-by] as CName,[bruger-postnr] as ZName,[bruger-personalnumber] as TName,[bruger-mobil] as MName,[bruger-email] as EName,[bruger-alder] as AgName,[bruger-foesdag] as BName,[bruger-personalnumberverified] as VName,[bruger-koen] as SName " & _
             "FROM [tbl-brugerinfo],[tbl-brugerlogin] " & _
             "WHERE [bruger-id] = " & CLng(sUserID)
    Else
      ' msgbox "non-numeric entry [" & sUserID & "]"
        Exit Sub
    End If
        'selects records from the database from cbo selection by user
        
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!FName) Then
            txtfirstname.Text = "<Null>"
         Else
            txtfirstname.Text = adoRS!FName
         End If
      End If
      .Close
    End With
        'To Veiw Firstname of User
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!LName) Then
            txtlastname.Text = "<Null>"
         Else
            txtlastname.Text = adoRS!LName
         End If
      End If
      .Close
    End With
        'To Veiw lastname of User
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!AdName) Then
            txtaddress.Text = "<Null>"
         Else
            txtaddress.Text = adoRS!AdName
         End If
      End If
      .Close
    End With
        'to veiw address of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!CName) Then
            txtcity.Text = "<Null>"
         Else
            txtcity.Text = adoRS!CName
         End If
      End If
      .Close
    End With
        'to view city of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!ZName) Then
            txtzipcode.Text = "<Null>"
         Else
            txtzipcode.Text = adoRS!ZName
         End If
      End If
      .Close
    End With
        'to view zipcode of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!TName) Then
            txttelephone.Text = "<Null>"
         Else
            txttelephone.Text = adoRS!TName
         End If
      End If
      .Close
    End With
        'to view telephone of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!MName) Then
            txtmobile.Text = "<Null>"
         Else
            txtmobile.Text = adoRS!MName
         End If
      End If
      .Close
    End With
        'to view mobile of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!EName) Then
            txtemail.Text = "<Null>"
         Else
            txtemail.Text = adoRS!EName
         End If
      End If
      .Close
    End With
        'to view email of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!AGName) Then
            txtage.Text = "<Null>"
         Else
            txtage.Text = adoRS!AGName
         End If
      End If
      .Close
    End With
        'to view age of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!BName) Then
            txtbirthday.Text = "<Null>"
         Else
            txtbirthday.Text = adoRS!BName
         End If
      End If
      .Close
    End With
        'to view age of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
            chkverified.Value = adoRS!VName
      End If
      .Close
    End With
        'to view verified phone number of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!BName) Then
            txtsex.Text = "<Null>"
         Else
            txtsex.Text = adoRS!SName
         End If
      End If
      .Close
    End With
        'to view verified phone number of user
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!ACName) Then
            lblac.Caption = "An Error Has Occured, No Account Creation Data!!"
         Else
            lblac.Caption = adoRS!ACName
         End If
      End If
      .Close
    End With
        'to view account creation details
    With adoRS
      .Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
      If Not .EOF Then
         If IsNull(adoRS!LLName) Then
            lbllastlogin.Caption = "An Error Has Occured, No Login Data!!"
         Else
            lbllastlogin.Caption = adoRS!LLName
         End If
      End If
      .Close
    End With
        'to view last login details of user
    
    If txtsex.Text = "1" Then
        chkMale.Value = vbChecked
        chkfemale.Value = vbUnchecked
        Else
        If txtsex.Text = "0" Then
            chkfemale.Value = vbChecked
            chkMale.Value = vbUnchecked
        End If
    End If
    
    If Not IsDate(lblac) Then
        MsgBox "Sorry! That is not a valid date or Date/Time.", vbCritical & vbOKOnly, "Error"
        Exit Sub
    End If
    
    Dim AddYear As Long
    AddYear = DateAdd("m", 12, lblac)
    lblexpire = CDate(AddYear)
    
    Dim YearExpire As Long
    YearExpire = DateDiff("d", Now, lblexpire)
    lblc = YearExpire
    
    If InStr(YearExpire, "-") = 1 Then
        lblAccountExpire = "This account expired " & YearExpire & " days ago!"
        lblAccountExpire.BackColor = vbRed
    Else
        lblAccountExpire = "This will expire in " & YearExpire & " days!"
        lblAccountExpire.BackColor = vbGreen
    End If
End Sub




This worked like a dream, user selected the ID from a combobox and the text boxes displayed the results it grabbed from the database.

Now it grabs the first record and displays it no matter which ID is selected.

But the set of dates display the correct dates for the ID selected.

Its got me dumbfounded.

I think I may have typed something worng somewhere and its causing it.

Please help.

Big Al



Edited by - Big Al Inc on 2/4/2006 6:37:12 PM

---

Updating Records In A Database
Can someone help me out with this code?

Dim sFile As String
Dim db As Database
Dim rs As Recordset
With dlgCommonDialog
.DialogTitle = "Choose member picture"
.CancelError = False
.Filter = "BMP Files (*.BMP)|*.BMP"
.ShowOpen
sFile = .FileName
End With
frmMembers.Image1.Picture = LoadPicture(sFile)
frmMembers.lblPicturePath.Caption = sFile
Set db = OpenDatabase(App.Path & "Members.mdb")
Set rs = db.OpenRecordset("MemberInfo", dbOpenTable)
rs.Edit
rs!PicturePath = sFile
rs.Close

Gid.

PS. What I am trying to do is update the DB with the path to the NEW picture chosen.

---

Updating Records In A Database
Can someone help me out with this code?

Dim sFile As String
Dim db As Database
Dim rs As Recordset
With dlgCommonDialog
.DialogTitle = "Choose member picture"
.CancelError = False
.Filter = "BMP Files (*.BMP)|*.BMP"
.ShowOpen
sFile = .FileName
End With
frmMembers.Image1.Picture = LoadPicture(sFile)
frmMembers.lblPicturePath.Caption = sFile
Set db = OpenDatabase(App.Path & "Members.mdb")
Set rs = db.OpenRecordset("MemberInfo", dbOpenTable)
rs.Edit
rs!PicturePath = sFile
rs.Close

Gid.

PS. What I am trying to do is update the DB with the path to the NEW picture chosen.

---

Updating Records In A Database
Subject: Updating Records in a database

Category: ActiveX

Hello, I have a vb interface for an Access Database. I have a listbox called lstContacts that I use as the search field.
I use the function below to update a current database field. But whenever I run the code, the program freezes. Can anyone let me know what might be wrong with it? Thanks



Function SaveRecords5()
Dim dbs as Database
Dim RSContacts as Recordset
set dbs = OpenDatabase("C:Program FilesDevStudioVBmm.mdb")
set RSContacts = dbs.OpenRecordset("Contact", dbOpenDynaset)

'Saves records
With RSContacts


Do Until .EOF
If lstContacts.List(lstContacts.ListIndex) = RSContacts!LastName then
.Edit
!LastName = lblLastname.Text
!FirstName = lblFirstname.Text
!Address! = lblAddress.Text
!PhoneHome = lblHome.Text
!PhoneCell = lblCell.Text
!Company = lblCompany.Text
!PhoneWork = lblWork.Text
!Pager = lblPager.Text
!Fax = lblFax.Text
!Email = lblEmail.Text
!WebPage = lblWebpage.Text
.Update
End If

Loop
End With
RSContacts.Close
dbs.Close
lstContacts.Refresh
End Function

---

Updating Records In An Oracle Database
My code:


Code:
Private Sub cmdEdit_Click()
Dim objDB As New ADODB.Connection
Dim strConnectionString As String
Dim strSQL As String

Dim strID As String
Dim FeedbackID As String
Dim Name As String

FeedbackID = txtFeedbackID
Name = txtName

strConnectionString = "Driver={Oracle in OraHome92};ConnectString=mylsi;UID=scott;PWD=tiger;"
objDB.Open strConnectionString

If UpdateFeedbacks.lvwFeedbacks.SelectedItem.Selected = True Then
If MsgBox("Are you sure you want to edit the details of this feedback?", vbOKCancel) = vbOK Then


strID = UpdateFeedbacks.lvwFeedbacks.SelectedItem.Text

MsgBox FeedbackID

strSQL = "UPDATE FEEDBACK SET NAME=Name WHERE FEEDBACKID = '" & strID & "' "
objDB.Execute strSQL

End If
End If
End Sub
Is there something wrong with my SQL syntax?

---

Updating And Adding New Records To A Database
I've been trying to add a new record to a database using the statement

data1.recordset.addnew

The path to the database is correct, but I get the message "Object variable or with block variable not set." I'm using 6.0 and Access (2000). Any thoughts?

---

Error With Updating Database Records
Hi

I am trying to edit existed records. but it doesnt update i tried it to debug it debugged perfectly. but doenst update records.

code given below

rs.Open "select * from billauto", cn, adOpenDynamic, adLockBatchOptimistic
'If MsgBox("Are you sure to save records", vbYesNo, "Save") = vbYes Then
rs.Update
Else
MsgBox "you can do it", vbOK
End If
rs.Close

waiting for reply.
Thanx a lot

---

Inserting Or Updating Records In A Database
Hi guys. I was just wondering how to insert/update records in a database without querying the database. Please help me. Thanks in advance guys.

---

How Can Updating A Data In Excel Updating A Database In Access
I´d like to update data in excel imediately after its update in the access database. but How can I do this?

:S

Any suggestions I'll be appreciated

---

Updating All The Records Of A Database That Meet Some Criteria
I'd like to change all the null fields of my database because those create problems in my program (the text property of a textbox cannot be set to null '^^) how can do it with an sql execution?

---

Updating Records In Access DB Using ADO
I have a successful open connection to an Access DB using ADO. I can successfully run a SELECT query to get info from the DB. But I wrote the following statement to attempt to update a record and I am getting an error saying "No value given for one or more required parameters"

Set MyRecSet = MyConn.Execute("UPDATE games SET g_name = strGameName, g_password = 'No' WHERE g_id = index")

Is it a problem with my SQL syntax or is there a different format for running UPDATE commands using ADO?

---

Updating/Deleting Records/Acess 2000 Database
Hi all,

I hope somebody can help me with this issue I am having. This is basically the last most complicated part of my project.

I have a form with a MSFlexGrid. On the MsFlexGrid, I populate data from an Access 2000 database. The user has the ability to edit,delete, and add more records to the MSFlexGrid.

When a record is updated or added, I am doing an insert or update to the databse; however, when the user deletes the record, since I do not have it on the MSFlexGrid, I do not have a way of referencing the deleted record to the databse to delete it.

Can anybody give me an idea on how to delete the record from the database once is not present on the MSFlexGrid.

I want to delete the records after the user finishes deleting all the records he/she wants. For example, if I had records 1,2,3,4 and records 1 and 2 were deleted, when the user clicks save those records will be deleted from the database.

Thanks in advance.

Jose

---

Updating Records From VB Application To Internet Web Server Database
Dear All,

I have one data entry form in VB application.

I have some fields cmbcity, txtarea, txtdesc and database field is ACity, AArea, ADesc in Table Plot.
I am adding these details using VB form but Now I wants to add these details to the web Server Database.
I want to update these records simultaneously to the web server SQL Database also.
I want to upload these records on the web server SQl Database.
Can Any one help me in this!!
Its a bit tricky ... can any one give a hint or a small code help

Any Help is appreciated

Thanks in advance

---

SQL Database Table Not Updating Some Records From Ms Excel (xls) File
I am updating SQL Database Table from Excel file but some of the records are not updating the table and error is displaying 'data type mismatched.'
Please resolve.

---

Access 2000 Updating Records
I have one table with unique records Tbl_AcctRef, I have another table that it joins with with about 200,000 records. I need to update each record with data from the other table, it is taking way too long the wayI have it set up. Any Ideas?

[code/]
MySql1 = "Select SVCARRID, acctfmt, GROUPFMT, SUBACCFMT, SVCARRLBL from Tbl_AcctRef "


Set db = CurrentDb
Set rst1 = db.OpenRecordset(MySql1)

Do While Not rst1.EOF
Criteria = rst1.Fields(0).Value

If Criteria Like "*ATI*" Then

Else

MySql2 = "Select SVCARRID, MainAcctNum, GrpNum, SubAcctNum, AcctType From " & TableName & " where SVCARRID = " & Chr(34) & Criteria & Chr(34)

MsgBox (MySql2)
Set rst2 = db.OpenRecordset(MySql2)
Do While Not rst2.EOF
Main = rst1.Fields(1).Value
Grp = rst1.Fields(2).Value
AcctType = rst1.Fields(4).Value
SubAcct = rst1.Fields(3).Value

rst2.Edit
rst2.Fields(1).Value = Main
rst2.Fields(2).Value = Grp
rst2.Fields(3).Value = SubAcct
rst2.Fields(4).Value = AcctType
rst2.Update

rst2.MoveNext
Loop
End If
rst1.MoveNext
Loop

Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing
[/code]

Thanks

---

Updating A Batch Of Records In Access
Hello all,
I had an access database that I riddled with vba code. I decided to cut out the middle man and write it purely in VB6. Basically this bit of the code is to update all records in access that have the value of "Box***" where *** is the box number.
I was able to translate all the other code from vba, however the following just won't work - please help!


Code:Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim dbsArchiveBoxLogTest As DAO.Database
Dim qdfChangeTitles As DAO.QueryDef
Dim strSQLUpdate As String
Dim strOld As String
Dim strNew As String

   Set dbsArchiveBoxLogTest = CurrentDb

   strSQLUpdate = "PARAMETERS [Old Title] Text, [New Title] Text; " & _
                  "UPDATE ArchiveBatches SET DocStatus = [New Title] WHERE " & _
                  "BoxNo = [Old Title]"
        
   'Create the unstored QueryDef object.
   Set qdfChangeTitles = dbsArchiveBoxLogTest.CreateQueryDef("", strSQLUpdate)

   'Prompt for old title.
   strOld = InputBox("Enter Box Number To Destroy:")

   'Prompt for new title.
   strNew = InputBox("Enter DocStatus:", , "Destroyed")

   'Set parameters.
   qdfChangeTitles.Parameters("Old Title") = strOld
   qdfChangeTitles.Parameters("New Title") = strNew

   'Invoke query.
   qdfChangeTitles.Execute
    
   'Refresh the DocStaus field
    Requery
   
    
Exit_Command1_Click:
    Exit Sub

Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
    
End Sub

I'm a big newbie and my VB skills as weak! so can anyone see what's wrong with this? I'd really appreciate some help as this has been nagging me for ages now.

Thanks

The-Don



Edited by - The-Don on 12/1/2005 5:26:03 PM

---

Inserting/Updating Records Using Access (SOLVED)
I checked out the write ups that are in the Access part of this forum. They have helped me a lot but I am running into a problem. I checked my Access DB and in the fields middlename and suffix (Required is set to no) so data can be in the field or not.

Here is the problem. I click on the AddPerson button (The table includes in this order: ssn, fname, mname, lname, suffix, signed) For this ssn, fname, and lname have to be entered. mname and suffix is optional. When I click on the ok button I get an error becuase nothing is entered in the txtMName.text How do i set up the paramaters to allow data or null? Here is the code I am using.

Code:
' This example inserts a new record

    On Error GoTo ErrorHandler
    Const strLcQUERY_NAME As String = "query1"
    Const strLcQUERY_PARAMETER1_ssn As String = "ssn"
    Const strLcQUERY_PARAMETER2_fname As String = "fname"
    Const strLcQUERY_PARAMETER3_mname As String = "mname"
    Const strLcQUERY_PARAMETER4_lname As String = "lname"
    Const strLcQUERY_PARAMETER5_suffix As String = "suffix"
    Const strLcQUERY_PARAMETER6_signed As String = "signed"

    Dim cmd As ADODB.Command: Set cmd = New ADODB.Command
    Dim prm As ADODB.Parameter
    Dim temp As Integer
    temp = 1

    With cmd
' Attach the command to a connection
        .ActiveConnection = mconStr
' Create the parameters which will be used
' by the query to insert
' ssn, data type, parameter type, length of parameter, value of parameter
        Set prm = .CreateParameter(strLcQUERY_PARAMETER1_ssn _
                                  , adVarChar _
                                  , adParamInput _
                                  , Len(Me.txtSsn.Text) _
                                  , Me.txtSsn.Text _
                                  )
        Call .Parameters.Append(prm)

        'amount, data type, parameter type, length of parameter, value of parameter
        Set prm = .CreateParameter(strLcQUERY_PARAMETER2_fname _
                                  , adVarChar _
                                  , adParamInput _
                                  , Len(Me.txtFName.Text) _
                                  , Me.txtFName.Text _
                                  )
        Call .Parameters.Append(prm)
        
        'month, data type, parameter type, length of parameter, value of parameter
        Set prm = .CreateParameter(strLcQUERY_PARAMETER3_mname _
                                  , adVarChar _
                                  , adParamInput _
                                  , Len(Me.txtMName.Text) _
                                  , Me.txtMName.Text _
                                  )
        Call .Parameters.Append(prm)

        'year, data type, parameter type, length of parameter, value of parameter
        Set prm = .CreateParameter(strLcQUERY_PARAMETER4_lname _
                                  , adVarChar _
                                  , adParamInput _
                                  , Len(Me.txtLName.Text) _
                                  , Me.txtLName.Text _
                                  )
        Call .Parameters.Append(prm)
        
        'suffix, data type, parameter type, length of parameter, value of parameter
        Set prm = .CreateParameter(strLcQUERY_PARAMETER5_suffix _
                                  , adVarChar _
                                  , adParamInput _
                                  , Len(Me.txtSuffix.Text) _
                                  , Me.txtSuffix.Text _
                                  )
        Call .Parameters.Append(prm)
        
        'signed, data type, parameter type, length of parameter, value of parameter
        Set prm = .CreateParameter(strLcQUERY_PARAMETER6_signed _
                                  , adInteger _
                                  , adParamInput _
                                  , Len(temp) _
                                  , Me.chkSigned.Value _
                                  )
        Call .Parameters.Append(prm)
        
' Tell the command that it is about to execute a Query
        .CommandType = adCmdStoredProc
' Tell the command the name of the query to be executed
        .CommandText = strLcQUERY_NAME
' Execute the query
        .Execute
    End With
    GoTo OverError
    
ErrorHandler:
    MsgBox (Err.Description)
    GoTo OverError

OverError:

End Sub
 

lost on this one



Edited by - epatterson78 on 9/12/2003 6:13:01 AM

---

Problems Updating, Deleteing, Adding Records In Access DB Using VB
I cannot for the life of me add, delete or update fields in a database without getting a whole load of errors.
Does anyone have any examples on how to do this?
or can anyone give me some code to show me?

These are the errors I get

Insufficent base table information for updating or refreshing (when updating, adding etc)

Row cannot be located for updating. Some values may have changed since it was last read.(when moving through records)

operation is not allowed in this context (when Deleteing)

Though if I delete the first record it deletes fine (just wont delete another record without erroring)
The other 2 errors are intermittent

I have tryed it by binding the controls to the datasource

I am also trying it by not binding the fields and using like the following
txtFields(0) = IIf(IsNull(DBrec!Name), "", DBrec!Name)
txtFields(1) = IIf(IsNull(DBrec!race), "", DBrec!race)
txtFields(2) = IIf(IsNull(DBrec!Profession), "", DBrec!Profession)

but if I try to do anything I get the insufficent base table infomation error.

Thanks for any help in advance

---

Textfile Records And Access Database Records Matching
how do i do matching whith using looping on textfile records and database records? if the records in textfile and access database are not match then it will have a output on a.txt and the output is retreive from textfile

the bugs is, there are no output (it suppose to have output)

please help ... thanx


VB Code:
Do While Not RS.EOF     strFileNoDB = CatchNull(Trim(RS("FILE_NO").Value), "Null")    strBenCdDB = CatchNull(Trim(RS("BEN_CD").Value), "Null")    strBenNameDB = CatchNull(UCase(Trim(RS("BEN_NAME").Value)), "Null")    strBenICPassDB = CatchNull(Trim(RS("IC_PASSP_B").Value), "Null")    strGuardNameDB = CatchNull(UCase(Trim(RS("GUARD_NAME").Value)), "Null")    strGuardICPassDB = CatchNull(Trim(RS("IC_PASSP_N").Value), "Null")        strBenICPassDBHy = Replace(strBenICPassDB, "-", "")    strGuardICPassDBHy = Replace(strGuardICPassDB, "-", "")        strFileOutputDB = strFileNoDB & strBenCdDB & strBenNameDB & strBenICPassDBHy & strGuardNameDB & strGuardICPassDBHy            If Left(ARBFile, 2) = "01" Then        strTxnYear = Mid(ARBFile, 17, 4)        strTxnMonth = Mid(ARBFile, 15, 2)        strTxnYMJ = strTxnYear & strTxnMonth    End If     SeqNum = SeqNum + 1    strSeqNum = CStr(SeqNum)        For x = 0 To aIndex        If Left(ARBFiles(x), 2) = "02" Then         If Len(strSeqNum) = 1 Then            SeqNumJ = strSeqNum & "         "        ElseIf Len(strSeqNum) = 2 Then            SeqNumJ = strSeqNum & "        "        ElseIf Len(strSeqNum) = 3 Then            SeqNumJ = strSeqNum & "       "        ElseIf Len(strSeqNum) = 4 Then            SeqNumJ = strSeqNum & "      "        ElseIf Len(strSeqNum) = 5 Then            SeqNumJ = strSeqNum & "     "        ElseIf Len(strSeqNum) = 6 Then            SeqNumJ = strSeqNum & "    "        ElseIf Len(strSeqNum) = 7 Then            SeqNumJ = strSeqNum & "   "        ElseIf Len(strSeqNum) = 8 Then            SeqNumJ = strSeqNum & "  "        ElseIf Len(strSeqNum) = 9 Then            SeqNumJ = strSeqNum & " "        ElseIf Len(strSeqNum) = 10 Then            SeqNumJ = strSeqNum        End If              strFileNo = Mid(ARBFiles(x), 51, 8)                strTxnStatus = "N"                strBenCd = Mid(ARBFiles(x), 59, 2)        If Len(strBenCd) = 1 Then            strBenCdJ = strBenCd & "   "        ElseIf Len(strBenCd) = 2 Then            strBenCdJ = strBenCd & "  "        End If        strBenCdC = Trim(strBenCd)                strBenName = UCase(Mid(ARBFiles(x), 61, 60))        strBenNameJ = Trim(strBenName)                strBenICPass = Trim(Mid(ARBFiles(x), 15, 12))        strBenICPassJ = strBenICPass & "   "                strGuardName = UCase(Mid(ARBFiles(x), 165, 80))        strGuardNameJ = Trim(strGuardName)                strGuardICPass = Trim(Mid(ARBFiles(x), 141, 12))        strGuardICPassJ = strGuardICPass & "   "                strAmount = Mid(ARBFiles(x), 271, 13)                strAmountCent = Right(strAmount, 2)        strAmountDecimal = "." & strAmountCent     If Left(strAmount, 11) = "00000000000" Then        strAmountRinggetFilterJ = "0"        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "                  "    ElseIf Left(strAmount, 10) = "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 3)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 1)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "                  "    ElseIf Left(strAmount, 9) = "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 4)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 2)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "                 "    ElseIf Left(strAmount, 8) = "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 5)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 3)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "                "    ElseIf Left(strAmount, 7) = "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 6)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 4)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "               "    ElseIf Left(strAmount, 6) = "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 7)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 5)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "              "    ElseIf Left(strAmount, 5) = "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 8)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 6)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "             "    ElseIf Left(strAmount, 4) = "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 9)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 7)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "            "    ElseIf Left(strAmount, 3) = "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 10)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 8)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "           "    ElseIf Left(strAmount, 2) = "00" And Left(strAmount, 3) <> "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 11)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 9)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "          "    ElseIf Left(strAmount, 1) = "0" And Left(strAmount, 2) <> "00" And Left(strAmount, 3) <> "000" And Left(strAmount, 4) <> "0000" And Left(strAmount, 5) <> "00000" And Left(strAmount, 6) <> "000000" And Left(strAmount, 7) <> "0000000" And Left(strAmount, 8) <> "00000000" And Left(strAmount, 9) <> "000000000" And Left(strAmount, 10) <> "0000000000" And Left(strAmount, 11) <> "00000000000" Then        strAmountRinggetFilter = Right(strAmount, 12)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 10)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "         "    Else        strAmountRinggetFilter = Right(strAmount, 13)        strAmountRinggetFilter1 = Left(strAmountRinggetFilter, 11)        strAmountRinggetFilterJ = strAmountRinggetFilter1        strAmountCombine = strAmountRinggetFilterJ & strAmountDecimal        strAmountJ = strAmountCombine & "        "    End If       strFileOutputJ = strFileNo & strBenCdC & strBenNameJ & strBenICPass & strGuardNameJ & strGuardICPass        If strFileOutputDB <> strFileOutputJ Then        strARBFileContent = SeqNumJ & strFileNo & strTxnStatus & strBenCdJ & strBenName & strBenICPassJ & strAmountJ & strTxnYMJ        Print #2, strARBFileContent    ElseIf strFileOutputDB = strFileOutputJ Then        Exit Do    End If        End If        Next x     RS.MoveNext    Loop

---

Showing The Latest Item Added To A ListBox
Hi,
I have a listbox to which I'm adding new items. The listbox is high enough to show 6 items without having to use the scroll bar.

When the listbox gets more than 6 items, I want it to scroll automatically to the bottom of the list to show the latest items without having user to use the scroll bar.
How do I do this?

Thanks.

---

Updating An Access Database In Vb6
I have a database named acesar.mdb with many tables one of which is called "userfile". Within that table is a field called "expDate" which is a date field in date/time format. I want to have a routine which adds one year to each record (member) of that table for that field. Can someone help me with this? The event will be triggered by a "special" password which I have already done. Thanks.

---

Updating Access Database
HOW DO I WRITE A PROGRAM IN VISUAL BASIC TO UPDATE A ACCESS DATABASE

---

Updating An ACCESS 97 DataBase...
Hi !!

Here's the problem...

I've got a project running with a Access97 DB and I'd like to upgrade it regularely from a *.txt file... is it possible ? and do I have to convert the *.txt into Access (*.mdb) first ?
I'm lost... How can I do ?

Thank you

COSIDUS

---

Updating Access Database
Hi

Making the conversion from VB6 to VB.NET has been extremely difficult for me. Everytime I think that I have things working right, something else appears. Here's the latest.

My code to update a database is (I thought) very simple as shown here

        dataAdapter.Update(dataSet,"Table")

Unfortunately, this results in an error message that reads "Update requires a valid UpdateCommand when passed a DataRow collection with modified rows."

I have tried to find this message in Microsoft's KB and in the help files but can't and hove no clue as to the cause. My code is identical to the code in the tutorial I am working with.

Can anyone help a very frustrated novice?



 

---

Updating Access Database Using VB6
Hi,

I have a quite confusing question. I am writing a Visual Basic 6.0 program that interacts with two databases. One is an existing database created by someone else. This database has Access forms for the user to fill out. One of the fields on these forms is a lookup text field. In my program, I am trying to update this field. However, it will not allow me to update this field because it is a lookup text field. Is there any possible way around this?

Thanks!

Rooey

---

Updating Access Database From Web!
I have a large Access database (price-catalog) on-line. At the moment I update it by using the web-query in MS Excel. I have included these queries to my self made VB macro that acquires the data from 5 different web sites, cleans the data and compines them to one large database. Then I convert it to Access DB and upload it to my site. (users can now search the database to find the lowest price)
This is pretty complicated task to do each day. So, I was wondering that is it possible to automate this process?
Is it possible to write a script that does this work for me (to schedule the update, executed at nights) so that I don't have to do anything? Just check that the process has been done right.

Or can you suggest some other ways to do this. My site works like Pricesearch.com.

Any suggestions are welcome!

---

Updating DataSource With Newly Added Rows In DataSet
I am trying to convert a C++ program into VB.Net (in an attempt to learn VB). I am having trouble writing changes I made to a DataSet back to the Access2002 DB. I get a runtime error: "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" Below is the area of the code that is throwing the exception:

Code:
Dim szQuery As String = "Select * From Employee;"
g_Utilities.db.Connect(g_szDBPath)
Dim dscmd As New OleDbDataAdapter(szQuery, g_Utilities.db.cnConnection)
Dim ds As New DataSet()
dscmd.Fill(ds, "Employee")
Dim dtResult As DataTable = ds.Tables.Item("Employee")
Dim rowNew As DataRow = dtResult.NewRow()
'code adding new row here

'add the new row to the table results & update teh table in DB
dtResult.Rows.Add(rowNew)
dscmd.SelectCommand = New OleDbCommand(szQuery, g_Utilities.db.cnConnection)

Dim cmdbld As OleDbCommandBuilder = New OleDbCommandBuilder(dscmd)

dscmd.Update(ds, "Employee") 'this is where the exception is thrown

g_Utilities.db.Disconnect()

frmTempEmpData.Close()
Does anyone know what I need to do to get this to work. I have been going over the MSDN help on this topic as well as a few books and I am doing what they say. I am probably missing some finer detail or something. But I can't figure it out.

Any help is appreciated!!

Thanks

---

Access Database Updating SLOW
HI... Im using VB6 with Crystal Reports9. I seem to have a problem when it comes to displaying the Information in a Table on my Report. Before i load the report, i first Populate my Database Table with Records, and then the report will use this Table's Records to display the information on the screen using the report viewer.

My Problem is: The last few records of the table is not displayed, because when the report is loaded, the records arent updated to the Table yet, when i look inside the table it;s not their, only about 2s after the update, then the reports will show. So it looks like when i add a record and close my database Table, it takes about 2s before the last few records are visable, only after 2s when i load the report, then all the data shows. Here is my connectionstring info:

Code:
Dim sConn as stirng
dim mConnection as new ADODB.Connection

'Create The Connection String
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Database.mdb" & ";Persist Security Info=False"
mConnection.Open sConn

This is how i add a record to the table.As you can see i do close the connection to the table, but the data is only visable inside the table after a few seconds, and this is not good, because the report is instantly loaded after this code is code, by then the records arent visable yet.

Code:
Private Sub SaveOtherFieldsToDatabase()
Dim SubReportOtherFieldsTable As New ADODB.Recordset

'Calculate the Net Salary that must be shown at the bottom of the Salary Report
dbSalaryTotal = (dbTripsTotal + dbYardworkTotal) - (dbDeductionsTotal + dbInsurance)

With SubReportOtherFieldsTable

.Open "SELECT * FROM SubReport_Otherfields", mConnection, adOpenDynamic, adLockOptimistic, adCmdText
.AddNew

!fldTotalSalary = dbSalaryTotal
!fldInsuranceAmount = dbInsurance
!fldDateFROM = DateFROM
!fldDateTO = DateTO

.Update
.Close

Set SubReportOtherFieldsTable = Nothing

End With

End Sub





This is code i use in my form_load event to show my report

Code:
'*** Set and LOAD THE REPORT ***
Set Report = New EmployeeSalaryReport
Report.DiscardSavedData
Screen.MousePointer = vbHourglass
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault
CRViewer91.Zoom (87)

---

Searching And Updating Database Access
Hey there,

I'm not too sure on how to go about this i would like to

1) Search the database
2) if a matching computername is found in the database
3) overwrite the computername with a new info

Thus far my codes are as such

Rs.Find "PC"

Do While Rs.EOF <> True
Debug.Print "PC Name: "; Rs!PC
Count = Count + 1
Mark = Rs.Bookmark
Rs.Find "PC", 1, adSearchForward, Mark
Loop

How do i continue from here

thank you very much
cheers!

---

Problems Updating MS Access Database
I have the following code. I am retreiving data from two tables in a database, but want to update one of the fields in one of the tables. It's not liking the TempDyna.Edit code. Any ideas on how to get this to update my field properly?

TempSQL$ = "SELECT T980_DD350.B1A_CONT_NR_TX, T980_REPORT_ID.SENT_DT FROM T980_DD350, T980_REPORT_ID WHERE T980_DD350.K_ID = T980_REPORT_ID.K_ID AND T980_REPORT_ID.K_ID = " & Kid
Set TempDyna = MyDb.OpenRecordset(TempSQL, dbOpenDynaset)
DBEngine.Idle dbFreeLocks
If TempDyna.BOF = False And TempDyna.EOF = False Then
TempDyna.Edit
TempDyna.LockEdits = False
TempDyna("SENT_DT") = Format(Now, "MM/DD/YYYY")
TempDyna.Update
End If
Call dfClose(TempDyna)

---

Vb Error Updating With Access Database
Good Afternoon.


I am posting this again. Usually I receive a response very quick, but since there were 2 responses due to me posting on the wrong site, maybe it got overlooked. I would really appreciate the help. Thanks in advance.

I get error "3251" (Object or provider is not capable of performing requested operation).

I am able to update Access table when I read sequentially thru the file;

sample code:

strSql = "Abends"

rstAbends.Open strSql, strConn1, adOpenKeyset, adLockOptimistic, adCmdTable

intFlag = 0

Do Until rstAbends.EOF Or intFlag = 1
If rstAbends!JesNumber = txtJesNum.Text Then

If (Len(txtTurnAround.Text) > 0) And
rstAbends!turnaroundaction <>
UCase(txtTurnAround.Text) Then
rstAbends!turnaroundaction =UCase (txtTurnAround.Text)
End If
intFlag = 1

rstAbends.Update


However, when I use the SQL statement to retrieve the record, (which is the correct way to do it since I know the key value!), I get the above mentioned error, which will not even allow me to perform an assignment statement to the field. strjesnumber does have a value.


rstAbends.Open strSql, strConn1, adOpenKeyset, adLockOptimistic, adCmdTable

Set rstAbends = olah_cndb.Execute("Select * from ABENDS WHERE jesnumber = '" & strjesnumber & "'")


'If Not IsNull(txtTurnAround.Text) Then
If (Len(txtTurnAround.Text) > 0) And rstAbends!turnaroundaction <> UCase(txtTurnAround.Text) Then
rstAbends!turnaroundaction = UCase(txtTurnAround.Text)
End If

rstAbends.Update


Thanks in advance

---

Problem Updating Access Database
Morning,

Have a problem where i am trying to update some tables in access from vb. What it does is Deletes thecontents of a table (WORKS), imports the contents of a csv file( DOESNT WORK SAYS THAT THE TABLE ALREADY EXISTS) and then moves the data from the import table to the actual table it needs to be in(WORKS). What i need to do is to tell it to overwrite the table in the second query! The code is as follows......

Private Sub Form_Load()

Dim cmd As String
Dim sqldelete As String
Dim sqlmove As String
Dim sqlimport As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim database As String

Adodc1.Visible = False

sqldelete = "DELETE IMPORT.[Call start], IMPORT.[Call duration], IMPORT.[Ring duration], IMPORT.Caller, IMPORT.Direction, IMPORT.Called_number, IMPORT.Dialled_number, IMPORT.Account, IMPORT.Is_Internal, IMPORT.[Call ID], IMPORT.Continuation, IMPORT.Party1Device, IMPORT.Party1Name, IMPORT.Party2Device, IMPORT.Party2Name, IMPORT.Hold_Time, IMPORT.Park_Time FROM IMPORT;"
sqlmove = "INSERT INTO SMDR ( [Call start], [Call duration], [Ring duration], Caller, Direction, Called_number, Dialled_number, Account, Is_Internal, Continuation, Party1Device, Party1Name, Party2Device, Party2Name, Hold_Time, Park_Time )SELECT IMPORT.[Call start], IMPORT.[Call duration], IMPORT.[Ring duration], IMPORT.Caller, IMPORT.Direction, IMPORT.Called_number, IMPORT.Dialled_number, IMPORT.Account, IMPORT.Is_Internal, IMPORT.Continuation, IMPORT.Party1Device, IMPORT.Party1Name, IMPORT.Party2Device, IMPORT.Party2Name, IMPORT.Hold_Time, IMPORT.Park_Time FROM IMPORT;"
sqlimport = "SELECT * " & "INTO [IMPORT] " & "FROM [Text;database=C:Program FilesAvayaIP OfficeCCCDeltaServerSMDR_Output].[SMDR.csv]"


'create the connection
cmd = "provider=microsoft.jet.oledb.4.0;" & "data source=" & "c:calllogger2002.mdb"

'Establish the connection
Set cn = New ADODB.Connection
    With cn
        .ConnectionString = cmd
        .Open
    End With
'Ammend the records
Set rs = New ADODB.Recordset
    With rs
        .Open sqldelete, cn
        .Open sqlimport, cn ' This is the line that doesnt work!!
        .Open splmove, cn
    End With
Set rs = Nothing

cn.Close

Set cn = Nothing

End Sub

Any help anyone can offer will be greatly appreciated as this is the very last piece of the puzzle.

Thanks

Glen

---

Updating A Password To An Access Database
Hi,
   i am having a lot of difficulty getting a password top update, when it gets changed.
In the program that i have, there is an option to change your password. To do so you have to put in your current username and password which i can currently do. Then when the new password is entered and the confirmation password is entered and they are right i am trying to write to the database which i cant to.
Have tried many things is there an easy way to do it, i am using a data adapter and dataset to get things happening but this isnt working. Does anyoneone have any suggestions to help me out? or do you have any examples that may be of help?.
cheers
Dan

---

Updating The Value Of A Field In An Access Database...
I have a combo box, in a subform whose value list is based on a query. How can I make these values reflect the true result of the query without closing and reopening the main form.

Thanks

---

Added Records Not Displaying
I have a problem with my recordset (coming from an access table through an ado connection object)
I can add a record from vb to access and it is added correctly to the access table, but ..HERE COMES THE PROBLEM... I cant view the record in Vb when i try to scroll through it, it is only the former recordset that is displayed.

(Sorry about the messiness of the code)

VB Code:
Private Sub cmdAdd_Click()  'Allows the user to add a new record'to the list of clients.'Hides the various text/Combo boxes.    txtFName.Locked = False    txtLName.Locked = False    txtStreet.Locked = False    txtTown.Locked = False    cboCounty.Locked = False    txtPhoneNo.Locked = False    txtPreferences.Locked = False    cboAge.Locked = False    cmdHistory.Visible = False    cmdAddSave.Visible = True    fraControls.Visible = False    cmdBack.Visible = True         rsDetails.AddNew 'rsDetails.Requery  End Sub  Private Sub cmdAddSave_Click()If txtFName.Text = "" Or txtLName.Text = "" Or txtStreet.Text = "" Or txtTown.Text = "" Or cboCounty.Text = "" Or txtPhoneNo.Text = "" Or cboAge.Text = "" ThenMsgBox "Please Complete the Missing Details", vbCritical, "Missing Details"ElsersDetails.Update  rsDetails.Requery     MsgBox "Record Added Successfully", vbInformation, "Client Record Added" End IfEnd Sub   Private Sub Form_Load()                 Set cnDetails = New Connection        Set comDetails = New Command        Set rsDetails = New Recordset        Dim strDetails As String        Dim rsStaff As Recordset        Dim comStaff As Command        Dim strStaff As String            'Determines the provider and the connection to    'the specified database.    cnDetails.Provider = "Microsoft.Jet.OLEDB.4.0"    cnDetails.ConnectionString = "A:VBPRO_Compacted.mdb"     'Opens the connection    cnDetails.Open     'comDetails.ActiveConnection = cnDetails     'SQL statement    strDetails = "SELECT tblClient.C_Number, tblClient.C_F_Name, tblClient.C_L_Name, tblClient.C_Street, tblClient.C_Town, tblClient.C_County,tblClient.C_Phone, tblClient.C_Age, tblClient.C_Preferences, tblClient.Notes FROM tblClient INNER JOIN History ON tblClient.C_Number = History.C_Number Order By tblClient.C_Number Asc "    ' "SELECT tblClient.C_Number, tblClient.C_F_Name, tblClient.C_L_Name, tblClient.C_Street, tblClient.C_Town, tblClient.C_County, tblClient.C_Phone, tblClient.C_Age, tblClient.C_Preferences, tblClient.Notes, tblStaff.S_F_Name FROM tblStaff INNER JOIN tblClient ON (tblStaff.S_Number = tblClient.C_Prefered_Stylist)Order By tblClient.C_Number Asc " '   ' .ActiveConnection = cnDetails   ' .CommandText = strDetails'End With 'Set rsDetails = comDetails.Execute      'rsDetails.Open strDetails, cnDetails, adOpenDynamic, adLockOptimistic   With rsDetails     .ActiveConnection = cnDetails     .CursorLocation = adUseClient     .CursorType = adOpenDynamic     .LockType = adLockOptimistic     .Open strDetails, cnDetails, adOpenDynamic, adLockOptimistic       End With            strStaff = "SELECT tblStaff.S_F_Name FROM tblStaff"       With rsStaff'     .ActiveConnection = cnDetails'     .CursorLocation = adUseClient    ' .CursorType = adOpenDynamic     '.LockType = adLockOptimistic     '.Open strStaff, cnDetails, adOpenDynamic, adLockOptimistic       End With'   Do While Not rsStaff.EOF   ' cboStylist.AddItem rsStaff.Fields("S_F_Name")    'rsStaff.MoveNext   ' Loop    'Calls the BindData funtion    BindData  End Sub

---

Error Updating Access Database Via VB Command
Good Afternoon.

I get error "3251" (Object or provider is not capable of performing requested operation).

I am able to update Access table when I read sequentially thru the file;

sample code:

strSql = "Abends"

rstAbends.Open strSql, strConn1, adOpenKeyset, adLockOptimistic, adCmdTable

intFlag = 0

Do Until rstAbends.EOF Or intFlag = 1
If rstAbends!JesNumber = txtJesNum.Text Then

If (Len(txtTurnAround.Text) > 0) And
rstAbends!turnaroundaction <>
UCase(txtTurnAround.Text) Then
rstAbends!turnaroundaction =UCase (txtTurnAround.Text)
End If
intFlag = 1

rstAbends.Update


However, when I use the SQL statement to retrieve the record, (which is the correct way to do it since I know the key value!), I get the above mentioned error, which will not even allow me to perform an assignment statement to the field. strjesnumber does have a value.


rstAbends.Open strSql, strConn1, adOpenKeyset, adLockOptimistic, adCmdTable

Set rstAbends = olah_cndb.Execute("Select * from ABENDS WHERE jesnumber = '" & strjesnumber & "'")


'If Not IsNull(txtTurnAround.Text) Then
If (Len(txtTurnAround.Text) > 0) And rstAbends!turnaroundaction <> UCase(txtTurnAround.Text) Then
rstAbends!turnaroundaction = UCase(txtTurnAround.Text)
End If

rstAbends.Update


Thanks in advance

---

Datagrid, Saving And Updating To Access Database
Hello

I am using a datagrid and l would like to update and save the contents to the access database. The customer would like to have an excel style grid, that they can navigate and edit details to it, then update. And also they like to be able to add a new record and add that to the database.

I am using the data environment, as l needed to print some reports (working ok)

Code so far.

Code:'updating
deCustomers.rsCmdCustomers.update

'saving
deCustomers.rsCmdCustomers.save


Many thanks in advance

Steve

---

Updating Single Record In MS Access Database
I have a form that loads records from a database and you can scroll through them. If you come upon a record that does not have a UserID filled in, I have coded the below method to create the user id and for it to populate the text field on the form with the new user id.

My problem is that I also want to update the record with the new userid, but when I try to do this i am getting an exception saying "Object reference not set to an instance of an object".

I'm not sure what I have done wrong, so any suggestions would be appreciated!

**Note
As soon as adapter1.UpdateCommand.CommandText = command1 is processed the exception is caught and it appears that command is nothing...??????

Code:
Private Sub RunMakeUserID()
        Dim command1 As String
        i = objCurrencyManager.Position
        firstName = objDataView.Item(i)("FirstName")
        LastName = objDataView.Item(i)("LastName")
        phoneExtension = objDataView.Item(i)("Extension")

        makeUserID = firstName.Substring(0, 1) + LastName.Substring(0, 1) + phoneExtension

        txtUserID.Text = makeUserID

        Dim connection1 As OleDb.OleDbConnection
        connection1 = New OleDb.OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB: Database L" & _
        "ocking Mode=1;Data Source=""F:435Lab6NorthwindNorthwind.mdb"";Jet OLEDB:Engine Type=5;Provider=""Micr" & _
        "osoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist secu" & _
        "rity info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Data" & _
        "base=False;Jet OLEDB:Create System Database=False;Jet OLEDB: Don't Copy Locale on" & _
        " Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet " & _
        "OLEDB:Global Bulk Transactions=1")

        Try

            connection1.Open()
            Dim adapter1 As New OleDb.OleDbDataAdapter
            command1 = ("Update Employees set UserID = '" & txtUserID.Text & "' where LastName = '" & txtLName.Text & "'")
            lblSQL.Text = command1
            adapter1.UpdateCommand.CommandText = command1

            adapter1.UpdateCommand.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            connection1.Dispose()
        End Try



    End Sub

---

Saving Records Added In MSHFlexGrid
I am using MSHFlexGrid to show a series of related records for a work order. Using a text box I could modify the cells and also add new rows and fill information. I am using Data Environment as the data link with MS-Access.

How do I save the modified and new added records?/

Please help

Regards
Varrey KM Rao

---

Error Updating Access Database Using Visual Basic
Option Explicit
Dim c As New adodb.Connection
Dim cn As New adodb.Connection
Dim cm As adodb.Command
Dim rsnew2 As New adodb.Recordset
Dim rsnew3 As New adodb.Recordset


Private Sub cmd_close_Click()
Unload.me
End Sub

Private Sub cmd_edit_Click()
rsnew3.Open "select * from company", c, adOpenDynamic, adLockOptimistic
If text1.Text = "" Then
MsgBox " enter the company name"
text1.SetFocus
rsnew3.Fields("text1") = text1.Text
End If
If text2.Text = "" Then
MsgBox " enter the company address"
text2.SetFocus
rsnew3.Fields("text2") = text2.Text
End If
rsnew3.Update
MsgBox " add new successful ", vbInformation, "successful"

End Sub

Private Sub Form_Load()
Dim cs As String
c.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:COMION.MDB;Persist Security Info=False")
rsnew2.Open "select * from company", c, adOpenDynamic
Me.text1.Text = rsnew2(2)
Me.text2.Text = rsnew2(3)

End Sub


I am getting message that "edit is successful", but its not updating in database..
What should i do ????

---

Error Updating Access Database Using Visual Basic
hi everybody
i am trying to access a database and update the changes..
Here is the code...

Code:
Option Explicit
Dim c As New adodb.Connection
Dim cn As New adodb.Connection
Dim cm As adodb.Command
Dim rsnew2 As New adodb.Recordset
Dim rsnew3 As New adodb.Recordset

Private Sub cmd_close_Click()
Unload.me
End Sub

Private Sub cmd_edit_Click()
rsnew3.Open "select * from company", c, adOpenDynamic, adLockOptimistic
If text1.Text = "" Then
MsgBox " enter the company name"
text1.SetFocus
rsnew3.Fields("text1") = text1.Text
End If
If text2.Text = "" Then
MsgBox " enter the company address"
text2.SetFocus
rsnew3.Fields("text2") = text2.Text
End If
rsnew3.Update
MsgBox " add new successful ", vbInformation, "successful"

End Sub

Private Sub Form_Load()
Dim cs As String
c.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:COMION.MDB;Persist Security Info=False")
rsnew2.Open "select * from company", c, adOpenDynamic
Me.text1.Text = rsnew2(2)
Me.text2.Text = rsnew2(3)

End Sub
I am getting message that "edit is successful", but its not updating in database..
What should i do ????

---

Can't See Added Records By Other Users With Dynamic Recordset
Hi,

Does anyone knows, how to see added records ( by other users ) in a recordset... I've tried all kind of combinaison with the recordset but nothing seems to work

Here's the code :
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = connexion
rs.CursorLocation = adUseServer
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.StayInSync = True
rs.Source = request
rs.Open

I am working with Access 2002 if that makes any difference

Could anyone help

Thanks and excuse my poor english, i'm french

---

Lock Records In Access Database Access :D
I'm working in an accounting software (if I'm translating OK from spanish )

Situation:
Access 2000 database.
One user is editing or deleting a record in one table.
Another user want to access the same record. I must lock the record for editing or dleting, but not for consulting, but in this case I have to notify the consultant this record is being editing by other user.

I don't know how to do this, thanks.

---

SQl To Access Database Records
I have an MsAccess DB created with Visual Data Manager.
It's a simple Address Book, I am trying to retrieve records based on a SQL. I have a TextBox on the form, I want to enter the first name of an address and have it find the record.
Here is my Sub cmdFind _Click ()Procedure
 frmViewAddress.AdoAddress.RecordSet =
"select * From tblAddress Where fldName" Like "txtFind*"
I get a compile error message saying "Expected AddNew, or Delete,or Update, or SELECT."
If I remove the Quotes before select and after fldName, the error message (with select highlighted) says "expected Procedure". What am I doing wrong?
The address name field in the database has this format, "John and Jane Doe"
The RecordSource Property in the Ado Control uses this SQL. select * From tblViewAddress
and it works fine.
any help will be a blessing. Thank You
Gary Swartz
gary35.wa@netzero.net

 

---

Adding Records To Access Database
I am trying to enter 5 columns into a database from 5 text boxes, then it will update a mshflexgrid on another form in run time. I figured out how to display the grid ok, but can't figure out how to update the database from the 5 textboxes. It doesn't seem to do anything when I click the Add cmdbutton on frmEnter. Attached is the project, this is for a school project.

Thanks,
Duane

---

Selecting Records In An Access Database Thru VB6
Ok I am trying to return some records from an Access database. For some odd reason this will not work. When I try to just return the BillToID it works, but not when I try to add the date of the invoice to print. Here is the SUB I am using, and the database layout.


Code:
Invoices
BillToID = Long Integer
DateOfInvoice = Date/Time

Code:
Sub PrintDaysInvoice(BillToID, BillDate)
'MsgBox "This feature is not yet working", vbInformation, "Invoices"
'Exit Sub

On Error GoTo ErrorPrintTodaysInvoice

MsgBox "BillToID-" & BillToID & vbCrLf & "BillDate" & BillDate
'Exit Sub
'Dim strFormLoadNumber
Dim A
Dim acNormal
If strDBOpen = 0 Then
OpenDatabase
End If
strSql = "SELECT LoadNumber"
strSql = strSql & " FROM Invoices"
strSql = strSql & " WHERE BillToID=" & BillToID
strSql = strSql & " AND DateOfInvoice=#" & BillDate & "#"
Set rs = my_Conn.Execute(strSql)

If rs.EOF Or rs.BOF Then
MsgBox "Can't find this invoice to print.", vbCritical, "Invoices"
Else
Do Until rs.EOF Or rs.BOF
strMessage = strMessage & vbCrLf & "LoadNumber-" & rs("LoadNumber")
'PrintInvoice rs("LoadNumber")
rs.MoveNext
Loop
MsgBox strMessage
End If
rs.Close
Set rs = Nothing
CloseDatabase

Exit Sub

ErrorPrintTodaysInvoice:
MsgBox Err.Description, vbCritical, "Invoices"
End Sub

---

Writting Records To Database (Access)
Need help on writting records to an Access database through vb code!

I have 3 variables with user input such as:
db is Customer

varCustomer

varAddress

varCity

---