Visual Basic :: Updating Single Record In MS Access Database

Updating A Record In Access
Hi CodeFreaks!!!

All i want to do is update a simple record in my access database but i keep getting
the error "primary key already exists blah, blah....".

I know the record exists....i want to update it!!!!

Im obviously not doing something to allow me to overwrite the previous record
but i havent seen anything on the forums that has helped me yet.

Here is my bit of code:

If validateFlag = True Then
     On Error GoTo errorcode
     Set addEditedTitleConn = New ADODB.Connection
     addEditedTitleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:HeartlandProject est1.mdb;Persist Security Info=False"
     addEditedTitleConn.Open

     ' retrieve recordset
     Set addEditedTitleRS = New ADODB.Recordset



     addEditedTitleRS.ActiveConnection = addEditedTitleConn
     addEditedTitleRS.Open "HeartlandStock", addEditedTitleConn, adOpenStatic, adLockOptimistic
     addEditedTitleConn.BeginTrans

     addEditedTitleRS.Fields("Title_Number") = CInt(txtTitleNumber.Text)
     addEditedTitleRS.Fields("Artist") = txtArtist.Text
     addEditedTitleRS.Fields("Title") = txtTitle.Text
     addEditedTitleRS.Fields("Format") = txtFormat.Text
     addEditedTitleRS.Fields("Release_Type") = txtReleaseType.Text
     addEditedTitleRS.Fields("Heading") = txtHeading.Text
     addEditedTitleRS.Fields("Style") = txtStyle.Text
     addEditedTitleRS.Fields("Grading") = txtGrading.Text
     addEditedTitleRS.Fields("Details") = txtDetails.Text
     addEditedTitleRS.Fields("Price") = txtPrice.Text
     addEditedTitleRS.Fields("Supplier") = txtSupplier.Text
     addEditedTitleRS.Fields("Company") = txtCompany.Text
     addEditedTitleRS.Fields("Catalogue_Number") = txtCatalogueNumber.Text

     addEditedTitleRS.Update ' save changes
     addEditedTitleConn.CommitTrans
     ' close recordset
     addEditedTitleRS.Close

     Set addEditedTitleRS = Nothing
     Set addEditedTitleConn = Nothing

     MsgBox "Your edited record has been entered!!", , "Edited Record Accepted"

Title_Number is the primary key.

Any help appeciated.
Koddy

Updating 2tables In Database Based On New Record In Datagrid
in my datagrid i have allowed for updates. the datagrid is bound to an adodc, whose datasource is a SQL statement (ie not a table in the access database). the datagrid contains 3 columns - 2 from the products table in the database and 1 from a related price table. how do i reference these 3 fields (for an insert SQL statement) when adding and saving a new record (row) in the datagrid? if you need any additional information, please let me know! any help would be greatly appreciated!

Updating Current Record In Access
Hi

I am running VB5 with Access2000. The problem I am experiencing is editing the current record in the database.

My code is below. The first part of the code populates a form that has text boxes and combo boxes. The records are searched for either by serial number or asset tag number. Once the required record is displayed I want to be able to edit it and write the changes back to the database. The problem I am having is that when I go to the cmdUpdate subroutine to update my changes, the recordset - recset - is either at the BOF or EOF - I have put in a line to test for this.
The error line is in the cmdUpdate_Click subroutine and the error is there is no curent record. This obviously means there is no record to modify.

My question is: How do I grab the current record for editing?

Thanks for any help!!

Chris

Private Sub cmdSearch_Click()

    If optAssetTag = True Then
        SQL = "SELECT * FROM BaseData order by AssetTag"
        Set recset = db.OpenRecordset(SQL, dbOpenDynaset, False, dbOptimistic)
        TagItem = cboAssetTag.Text
    ElseIf optSerialNo = True Then
        SQL = "SELECT * FROM BaseData order by SerialNumber"
        Set recset = db.OpenRecordset(SQL, dbOpenDynaset, False, dbOptimistic)
        TagItem = cboSerialNumber.Text
    End If

    rt.MoveFirst

    While Not recset.EOF
        For i = 0 To 0
        If TagItem = recset!AssetTag Then
            txtAssignee.Text = recset!Assignee & ""
            cboSerialNumber.Text = recset!SerialNumber & ""
            txtComment.Text = recset!comment & ""
            txtMake.Text = recset!Make & ""
            txtType.Text = recset!Type & ""
            txtModel.Text = recset!Model & ""
            txtRam.Text = recset!Ram & ""
            txtHardDrive.Text = recset!HardDrive & ""
            txtProcessor.Text = recset!Processor & ""
            txtOS.Text = recset!OS & ""
            txtBuyDate.Text = recset!DatePurchased & ""
            txtStatus.Text = recset!Status & ""
            txtDeptID = recset!deptID & ""
            txtLocID.Text = recset!locid & ""

                If recset!incycle.Value = True Then
                    chkInCycle.Value = 1
                Else
                    chkInCycle.Value = 0
                End If

                If recset!Verified.Value = True Then
                    chkVerified.Value = 1
                Else
                    chkVerified.Value = 0
                End If

        ElseIf TagItem = recset!SerialNumber Then
            txtAssignee.Text = recset!Assignee & ""
            cboAssetTag.Text = recset!AssetTag & ""
            txtComment.Text = recset!comment & ""
            txtMake.Text = recset!Make & ""
            txtType.Text = recset!Type & ""
            txtModel.Text = recset!Model & ""
            txtRam.Text = recset!Ram & ""
            txtHardDrive.Text = recset!HardDrive & ""
            txtProcessor.Text = recset!Processor & ""
            txtOS.Text = recset!OS & ""
            txtBuyDate.Text = recset!DatePurchased & ""
            txtStatus.Text = recset!Status & ""
            txtDeptID = recset!deptID & ""
            txtLocID.Text = recset!locid & ""

                If recset!incycle.Value = True Then
                    chkInCycle.Value = 1
                Else
                    chkInCycle.Value = 0
                End If

                If recset!Verified.Value = True Then
                    chkVerified.Value = 1
                Else
                    chkVerified.Value = 0
                End If

        End If
        Next i

    recset.Bookmark = recset.Bookmark

    recset.MoveNext

   Wend

   Call GetLocation(txtLocID.Text)
   Call GetDepartment(txtDeptID.Text)

   On Error Resume Next


End Sub

Private Sub GetLocation(locid As String)
      On Error Resume Next

    SQL = "Select distinct l.Location, b.locid from Location as l, BaseData as b Where b.LocID = l.LocID And l.LocID = " & locid

    Set Location = db.OpenRecordset(SQL)

    With Location
        If Not .EOF Then
            txtLocation.Text = IIf(IsNull(!Location), "", Trim(!Location)) & ""
            .Close
        End If
    End With

End Sub
Private Sub GetDepartment(deptID As String)
    On Error Resume Next

    SQL = "Select distinct l.Description, b.deptid from Department as l, BaseData as b Where b.DeptID = l.DeptID And l.DeptID = " & deptID

    Set Department = db.OpenRecordset(SQL)

    With Department
        If Not .EOF Then
            txtDepartment.Text = IIf(IsNull(!Description), "", Trim(!Description)) & ""
            .Close
        End If
    End With

End Sub

Private Sub cmdUpdate_Click()

    If recset.EOF Or recset.BOF Then
         MsgBox "1"
    End If

    Code:If txtComment.Text <> recset("comment") Then
        recset.Edit
            recset!comment = txtComment.Text
        recset.Update
    End If

End Sub

I Need Some Codes For Adding, Deleting, Updating A Record In ORACLE Database
I need some codes for Adding, Deleting, Updating a record in ORACLE database with VB as Front-End.

I just need codes for the above.

Thank you

Storing Data In And Recalling The Data From A Single Database Record
hi guys...
I am still working on the same system but on another part of it.

I need to know how I can have some numbers in text boxes or other controls, so that when the app is closed, the numbers get stored in a database record, or some other data sheet, and then get called back up wheb the program is reopened

I actually have access to some code that was writting for another part of this system, but I don't understand a lot of it

Anyway... I need to know how to have these numbers stored automatically when the app is clsed, and then recalled automatically next time the app is opened.

Thank you so much

Happieman

Networking A Single User VB6 Application (using ACCESS Database)
Can anyone give me any hints on how I would go about converting a single-user VB project (using an MS Access database) into a networkable system?

Is it simply a matter of locking records (or recordsets) each time they are accessed?

I'm sure this can't be too much of a complicated problem. Any help and hints are greatly appreciated

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!

Trying To Input Single Record Number To Textbox And Display Individual Record
Have an input textbox "txtEnterRecNum_LostFocus()", trying to get it so that the user can enter a single record number (1-10) and then THAT one record displays in another textbox "txtDisplayIndividual.Text"

Have coded it to singlely accept one number (for example it is set at number 5), that is ok ... having problems expanding to determine if it's 1,2,3,4,5,6,7,8,9 or 10.

I've played with Do While, Do Until, Ten episodes of IF's (that is long and hokey), tried OR's, tried i <=10, etc., but am not coding correctly as nothing has been successful.

(1) How would I change the following single acceptance code below, to FIRST accept numbers 1,2,3,4,5,6,7,8,9 or 10 and THEN display that particular record?

Code:
Private Sub txtEnterRecNum_LostFocus()
Dim i As Integer
i = 0
Open GetAppPath & "mytext.txt" For Input As #1
Do Until EOF(1)
i = i + 1
Input #1, SampleOutput
If i = 5 Then
txtDisplayIndividual.Text = SampleOutput
Exit Do
End If
Loop
Close #1
End Sub
(2) When it does work for one number, the record displays only if one clicks inside another object/input textbox ... How to make it work no matter where the user clicks on the form?

(3) And how to make it work by pressing ENTER key on the keyboard? (maybe this one is too complicated?)

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

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

Using ADODB.Record To Pull A Single Record From A Recordset
I am attempting to pull a single record from a recordset to pass to a function for populating a chart. What is the best method to do this. I am having a problem opening the record from the recordset.

Dim rsChartData As ADODB.Recordset
Dim rData As ADODB.Record

... Get the recordset from a storedprocedure via a command object

Set rData = New ADODB.Record
Set rData.Source = rsChartData
rData.Open rsChartData

I am getting an error "Object or provider is not capable of performing requested operation."

If anyone can help with this it would be appreciated. Also, if there is a better way to pass the record, other than each individual field as a variable, please plass that on.

Thanks

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 ????

Some Added Records Not Showing When Updating An Access Database
Ok, Here's the Skinny...

I am writing an application that pulls a list of names off of a
server, it runs a command prompt with a batch file, and outputs
to a text file. (There is an erlier post I put on here last week
regarding that portion of it.)

The problem is that when VB reads through the file and adds the
records, it appears to skip some of the records in the begginning
(appearing to be a random amount) I pasted the code below,
any ideas?

below is the code that creates the file

VB Code:
Private Sub Timer2_Timer() Dim ListCMD Dim BatchFile Dim ListLoc Dim lPid As Long Dim lHnd As Long Dim lRet As Long Timer2.Enabled = False BatchFile = App.Path & "atch.bat" ListLoc = App.Path & "list.txt" ListCMD = "c:" & vbNewLine & "cd " & vbNewLine & "cd " & App.Path & vbNewLine & "telalertc.exe -host jorma -list destinations > list.txt" Open BatchFile For Append As #1 Print #1, ListCMD Close #1 BatchFile = "cmd.exe /c " & Chr(34) & BatchFile & Chr(34) lPid = Shell(BatchFile, vbHide) If lPid <> 0 Then lHnd = OpenProcess(SYNCHRONIZE, 0, lPid) If lHnd <> 0 Then lRet = WaitForSingleObject(lHnd, dwMilliseconds) CloseHandle (lHnd) Timer3.Interval = 3000 Timer3.Enabled = True End If End If End Sub

Below is the code that reads the file

VB Code:
Sub ReloadFullList() Dim matrixdb As Database Dim SQLcmd Dim ListFile Dim BatchFile Dim log Set matrixdb = OpenDatabase(Form1.MatrixDBLocation) ListFile = App.Path & "list.txt" BatchFile = App.Path & "atch.bat" With Form1.Adodc2 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Form1.MatrixDBLocation & ";Mode=ReadWrite;Persist Security Info=False" .RecordSource = "SELECT * from tblPagers" .Refresh End With SQLcmd = "DELETE * from tblpagers" WriteSQLLog (SQLcmd) matrixdb.Execute SQLcmd Open ListFile For Input As #1 Do Until EOF(1) Line Input #1, log If InStr(1, log, "error", vbTextCompare) Then GoTo ConnectError Exit Sub Else: End If Loop Close #1 Open ListFile For Input As #1 Line Input #1, log Line Input #1, log Do Until EOF(1) Line Input #1, log log = Mid(log, 2, Len(log) - 2) With Form1.Adodc2.Recordset .AddNew !pagers = log .Update End With Loop Form1.Adodc2.Recordset.MoveFirst Close #1 Form1.List1.Clear Do Until Form1.Adodc2.Recordset.EOF = True Form1.List1.AddItem Form1.DataGrid2.Text Form1.Adodc2.Recordset.MoveNext Loop Form1.Adodc2.Recordset.MoveFirst Kill BatchFile Kill ListFile MsgBox "Jorma Full List Update Complete, Dont Forget to Push to Server When done.", vbOKOnly + vbInformation, "Jorma List Update Complete" Unload Form4Exit SubConnectError: Close #1 MsgBox "There was an error connecting to Jorma to retreieve the full list, please try again later." & vbNewLine & vbNewLine & "If the problem persists, please contact your System Administrator", vbOKOnly + vbInformation, "Potential Jorma Issue" Unload Form4 Kill BatchFile Kill ListFileEnd Sub

The part where it skips two lines is the header for "connecting to,
pulling list" blah blah, I dont need that in the database, all I need
is the names.

The text file is complete, but the names in the database seem to
start at a different part of the list, sometimes 3 names down,
sometimes 20 names down... it makes absolutely no sense to
me, I even set up a timer to have it wait 3 seconds before it
reads the list, but that doesnt seem to make a difference either!!
Any suggestions would be greatly apperciated

Get Access Database Record
I have an existing MSACCESS Database named StampBase.
My VB project has a form titled ACD with a text box Text11.
When a record is displayed on the form Text11 indicates the CatalogNo.
My form contains a command box titled "Find Record".
I want to be able to select the "Find Record" command and have Text11 accept a CatalogNo value.
I then want to use this catalog number value to find and display the proper record from my database.
Im fairly new to Visual Basic and sure could use some help with this (Creating the code).
Thanks in advance for any help.
greenweiney

Can't Add A New Record To An Access Database
I am a novice when it comes to ADO. I am working with VB 6.0 and Access 2000. I would have opened the Access Database but I can't addnew. I get error "3251" -not supported by the provider.

Here is my connection code

    Dim db              As ADODB.Connection
    Dim i               As Integer

    Set db = New ADODB.Connection

    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data         Source=C:My DocumentsSuzanneD & T DatabaseCommissions & Expenses - 2000.mdb;Persist Security Info=False"

    Set mrsAdvisors = New ADODB.Recordset
    Set mrsCommission = New ADODB.Recordset

    With mrsAdvisors
        .ActiveConnection = db
        .CursorType = adOpenDynamic
        .Open Source:="Advisors"
    End With

When I go mrsAdvisors.supports(adAddNew) I get false. I have no idea how to make it true!!  Help.

Thanks

Access Database Add Record
Hello

How do you check to see if a record exist in access database from vb then add the record

Cheers

Jason

Bookmarking A Record In An Access Database
Lets say I wanted to assign a bookmark to the first record in my database, how would I go about doing that?

Adding New Record To MS Access Database
Hi, I'm fairly new to vb and need some assistance with adding new data to adatabase.

Here is my attempted code:

Code:
Private Sub cmdAdd_Click()

Dim newAllProductsRow As AllProDataset.AllProductsRow
newAllProductsRow = AllProDataset1.AllProcucts.newAllProductsRow()

newAllProductsRow.Forename = txtForename
newAllProductsRow.Surname = txtSurname
newAllProductsRow.AssetTag = txtAssetTag
newAllProductsRow.Product = cboProd
newAllProductsRow.Make = txtMake
newAllProductsRow.Model = txtModel
newAllProductsRow.SN = txtSN
newAllProductsRow.Site = txtSite

Me.AllProDataset.AllProducts.Rows.Add (newAllProductsRow)
Me.AllProducts.Update (Me.AllProDataset.AllProducts)

End Sub

It comes up with the error 'user-defined type not defined' but do not know how to solve the problem. Could anyone HELP PLEASE??

Adding A Record To Access Database
Do i need to declare a command object and recordset object when adding to an access database?

here is addition code:
With grs
.AddNew 'adding new record
.Fields("Customer_ID") = strCustomerID
.Fields("First_Name") = txtFName.Text
.Fields("Surname") = txtSurname.Text
.Fields("Address_1") = txtAddress1.Text
.Fields("Address_2") = txtAddress2.Text
.Fields("Address_3") = txtAddress3.Text
.Fields("County") = txtCounty.Text
.Fields("AreaCode") = txtAreaCode.Text
.Fields("Number") = txtNumber.Text
.Fields("Mileage") = txtMileage.Text
.Update
End With

What code do i need before i start adding these records?The connection to database is set up on form load.

Adding A Record To Access Database
i store one record in a collection .how to store them cell by cell to the databse and form one new record?

Adding A Record In An Access Database Using ASP && ADO
Help! Please please help!!
I'm trying to figure out how to add a record to a table in a database. I've got:
Code:
dim oRSu
set oRSu = Server.CreateObject("ADODB.Recordset")
oRSu.Open "users","DSN=fbquiz"
oRSu.AddNew

oRSu("Name") = sName
oRSu("Address") = sAddress
oRSu("Town") = sTown
oRSu("County") = sCounty
oRSu("Post_Code") = sPostCode
oRSu("Country") = sCountry
oRSu("EMail") = sEMail
oRSu("User_Name") = sUsername
oRSu("Password") = sPassword

oRSu.Update
set oRSu = Nothing
but I keep getting "The operation requested by the application is not supported by the provider." on the "oRSu.AddNew" line.
What am I doing wrong?

How Do You Access A Record From A Database According To Criteria
How is it possible to access certain records from a database and display it on a form.

Eg. 3 checkboxes, a text box and a command button are on a form. Each check box refers to a different record within the database.

When i select a check box and click on the cmdbutton, how do I display that particular recordset data in the text box?
Thanx

How To Print Last Record From Access Database?
Hi all,
I want to print last record only from the database. I'm using access 2000.
Let say after i enter new record into the database, i want to print the record. I'm trying using temporary table whereby the latest record will be stored there..but it dowsn't work. Any idea?
Thanx anyway..

How Ro Insert A Record In MS Access Database
Hello frds,
i have created a Databse in MS access through VB.
i also created a table and inserted a column in that table.
but now problem arises that how to insert the values in that columns!
i.e how to insert a record in that table
thanks in advance
bye

Adding Record To Access Database
Could someone please help me with a problem I have? I am trying to write a high score back to an Access 2 database using VB5. However, when it updates the DB it always updates the first entry! I've tried MoveLast, Addnew, Update (from fields on a hidden form - which do reflect the high score which I want to add - but I need some way of either adding a new row (with the data - as it does add the new row) or over writing the lowest entry in the database. There is no security applied to the database - but I really am a novice and have no idea where I am going wrong!
Any help very much appreciated.

TIA Nick

Nick

Show Access Database Record In New Form.
Hello, and thanks for any replies given to this post. It is much appreciated.

My problem is this, although im sure the cure is quite simple:

I am accessing an access database using ado and would like to open a record in a new form. Its an edit command basically,but in a new form. The problem is that it always comes up blank, or to an empty record. I just need to find out how to specify that Form2 should load the record that is currently being view in Form1.

Any ideas or comments are appreciated.

Thanks in advance!

Access Database Check For Existing Record
Delete me

Access Database Check For Existing Record
VBA noob here. Here is a little explanation.. I have a Main form (Main) and two subforms (controls and Analasys). I have two tables (Input and Master). I am checking a text file for a string, checking to see if the new string is the same as the last string, if not, write record to table Input. Then I want to see if that record exists in the master (field = MasterText). If so, do nothing, if not, write and set a flag on the form.

The write to the tables work fine, but the checking for duplicates doesn't work. BTW, I should mention that there is no user interaction other than to hit the start button on the controls form.
Heck, I am not sure I am even doing it correctly.. Any help would be greatly appreciated.
(I don't know if this should be in the office VBA integration or here, SRY)

Code:
Public Function CheckFile()
'On Error GoTo CHECKFILE_ERROR
Dim StFilename As String
Dim TStream As Object
Dim IntLoopCount As Integer
StFilename = Me.CurrentFile
Dim FSys As FileSystemObject
Set FSys = New FileSystemObject
IntLoopCount = 0
Dim tscount As Object
Dim count As Integer
Dim ts As Object
Dim i As Integer
Dim strTextline As String
Dim RsTestRecords As DAO.Recordset
Dim stQuery As String
Dim IsNew As Boolean
Dim rsclone As Recordset
Dim rstNewRecord As DAO.Recordset
Dim db As Database

'do work on the file
If FSys.FileExists(StFilename) Then
Set FSys = CreateObject("Scripting.FileSystemObject")
Set TStream = FSys.OpenTextFile(StFilename, 1)
'------------- Count How many lines we have ------------------------
Set tscount = FSys.OpenTextFile(StFilename, 1)
Do Until tscount.AtEndOfStream
tscount.ReadLine
count = count + 1
Loop
tscount.Close
' Read the contents of the file and output 2nd to last line. Doing so insures good read
Set ts = FSys.OpenTextFile(StFilename, 1)
For i = 1 To (count - 1)
Newline = ts.ReadLine
Next
'-----------------------------Add record---------------------------------------------------------
If Newline <> NewlineOld Then
Set db = CurrentDb()
LineCounter = LineCounter + 1
Set rsclone = Form!main.Analasys.Form.RecordsetClone '<---------- Problem starts here
rsclone.Find "MasterText = " & CleanLine(Newline)
If rsclone.RecordCount <= 0 Then 'the record is unique to master
'add to master table code <--------------------------------- Works from here on down
Set rstNewRecord = db.OpenRecordset("Master", dbOpenDynaset)
With rstNewRecord
.AddNew
!MasterText = CleanLine(Newline)
.Update
.Close
End With
Set rstNewRecord = Nothing
'mark as new code
IsNew = True
Else
IsNew = False
End If
Set rsclone = Nothing

Set rstNewRecord = CurrentDb.OpenRecordset("Input", dbOpenDynaset)
With rstNewRecord
.AddNew
!InputText = CleanLine(Newline)
!WasFirst = IsNew
.Update
.Close
End With
Newline = NewlineOld
Set rstNewRecord = Nothing
Set db = Nothing
Forms![main].Requery
Else
End If

Else
MsgBox "Suddenly, the file wasn't found. sup w/ that? "
Me.TG_IsActive = False
Exit Function
End If
'Set FSys = Null

CHECKFILE_EXIT:
Exit Function
CHECKFILE_ERROR:
MsgBox "CHECKFILE ERROR NUMBER " & Err.Number & ": " & Err.Description
Resume CHECKFILE_EXIT
End Function

Deleting A Record From An ACCESS Database - Where Have I Gone Wrong?
I'm trying to delete a record from an Access database. With the code below I get an "Invalid Use Of Property" error and VB highlights "MyRecSet" in the "MyRecSet = MyConn.Execute(strSQL)" line. I've been scratching my head now for some time with this issue. Can somebody point me to the light please. Much appreciated!!

- Cheers!

Code:
Private Sub CmdDelete_Click()
Dim ComboName2Delete As String

ComboName2Delete = frmMain.CmbComboTitle.Text

Dim MyConn As ADODB.Connection

Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "DBCombos.mdb"
MyConn.Open

Dim strSQL As String
Dim MyRecSet As ADODB.Recordset

strSQL = ("DELETE * FROM Combos WHERE [ComboName] = '" & ComboName2Delete & "'")

MyRecSet = MyConn.Execute(strSQL)

End Sub

Newbie Trying To Delete A Record/row From Access Database
Hi guys,

I am building this app where I am able to display records of trucks available from the database using a mshflexgrid. So far I have been successful in retrieving information such as getting eg: ALL trucks, trucks of type A and trucks of type B. The problem I have is with deleting a record/truck(where primary key is serial no). This is the section of code that I have and the error that I get is 'Data type mismatch in criteria expression'. I am not sure where I have gone wrong and could use any help/advice that you guys have to offer.
I have used 'serial_no = 3333' as an example. It is a valid record in the database. Alternatively I have tried to use myRecordset.Delete and it works by deleting records one by one from top down. I have little knowledge using databases so how would one manipulate myRecordset.Delete in my case if it's possible? Hope I am clear in what I am asking for. Thanks in advance!

Code:Private Sub btnDel_Click()
    Dim myCommand As New ADODB.Command
    Dim strSQL As String
    Dim srl_no As String

    'MsgBox MSHFlexGrid1.Row
    'srl_no = MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 2)
    'strSQL = "DELETE FROM Trucks WHERE Serial_No='3333'"
    'strSQL = strSQL & srl_no

    strSQL = "DELETE FROM Trucks WHERE Serial_No = '3333'"
    myCommand.CommandText = strSQL
    myCommand.CommandType = adCmdText
    myCommand.ActiveConnection = myConnection
    myCommand.Execute

    'MsgBox srl_no
    'myRecordset.Delete
    Set Me.MSHFlexGrid1.DataSource = myRecordset
    myRecordset.Requery
End Sub