Visual Basic :: Searching And Updating Database Access

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!

Searching Access Database
Hello,

I have an access database with 100,000+ records that I need to search through. I only need to work with the records that have a value in the account number field. Right now I have it going down the table and whenever it finds a row with an account number in it, it does the computations. This process only allows a few hundred records to be done every minute. I need to speed this up, but I have no clue how. Any help would really help me out

Code:
For i = 1000 To x 'x is number of rows in table

Adodc1.RecordSource = "SELECT * FROM tSaleTransactions Where cdeTrans='A" & i & "'"

Adodc1.Refresh

If Adodc1.Recordset.Fields("cdeCustCode") <> Empty Then
'does computations

Searching Access Database
Good morning! i'm connecting to an access97 database with ADO. i've got some searching options available, and they're working ok....they do find the first record that matches the search criteria. i'm wondering if there's a way to order the results so that if a user does a search for NY, it will display the first record for NY followed by the rest of the NY records.
anyone know how to do this? here's a sample of the search code
Code:
Private Sub mnuFindState_Click()
Dim pstrState As String
pstrState = InputBox("Enter the State *Format: NY", "Find")
If pstrState <> "" Then
    rsMerchants.Find "[State] = '" & pstrState & "'"
    If rsMerchants.EOF = True Then
        rsMerchants.MoveFirst
        rsMerchants.Find "[State] = '" & pstrState & "'"
    End If
End If
End Sub

Thanks!
Ryan

Searching An Access Database
ok i have an access database called modems, and in that database i have 2 fields (manufacturers, and names)

i created a program that i would like to be able to search for either on, in other words have 2 text boxes one to search names and the other to search manufacturers, then i would have a search button and when you hit search it would look at one of the boxes, not both, and then display the results in a list box below.
basically it is all set accept, i have no idea what i need to use for the search_click code????

does anyone have any ideas????

"who says you need wings to Phly"

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)

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

Searching An Access Database From Excel
Hello all,
I have yet another problem. I would like to automate Access from within an Excel workbook. I want to use an Excel userform to search an Access table, and fill in values within that Access database. I found this generic code on http://www.ExcelTip.com/:

Code:
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:FolderNameDataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("A" & r).Value
.Fields("FirstName") = Range("B" & r).Value
.Fields("MI") = Range("C" & r).Value
.Fields("LastName") = Range("D" & r).Value
.Fields("Prefix") = Range("E" & r).Value
.Fields("Suffix") = Range("F" & r).Value
.Fields("MaintCo") = Range("G" & r).Value
.Fields("Installer") = Range("H" & r).Value
.Fields("Distributor") = Range("I" & r).Value
.Fields("Manufacturer") = Range("J" & r).Value
.Fields("Agency") = Range("K" & r).Value
.Fields("ContractDue") = Range("L" & r).Value
.Fields("StartDate") = Range("M" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

What that code does is copy information from an Excel worksheet to an Access table, but I am not sure how to search an Access database, or how to fill in the information in the Access database from an Excel userform.

I am perfectly willing to read over the basics of automating Access within Excel's VB capabilities, but I have not really been able to find any helpful web sites on the subject.

The reason I am asking about this is:

We are generating contracts through an Excel workbook. The first worksheet of the workbook is an input sheet, which fills in all of the pertinent information throughout the rest of the workbook (the body of the contract). We also use an Access database to track our contracts, so that we can schedule inspections, etc. I would like to give users the option of adding a record to our Access database when they finish generating the contract. We assign a job number to each client, so the user would enter the job number in an Excel userform. Then, I want Excel to search our Access database (all of the information is stored in an Access table) for that job number. If the job number exists, then Excel would initialize one userform to update the Access database record. If the job number does not exist, then Excel would initialize a different userform to add a record to our Access database.

I know that this is a very long post, and that I am asking an awful lot, but really all I am asking (at the moment) is that someone point me in a direction toward a free web site that will give me some tutorials on automating Access from within Excel VBA.

Searching An Access Database With VB Front
Hi,

I have written a VB script that handles a Database,

The database comprises of member details for a club,

I have a form that will add, edit, remove etc and will also cycle through details (the database does not appear as a list)

However I want to be able to "search" for members, and have their details appear on the form.

How can I do this? I am using VB6 and Access 2000

Searching An Access Database Using VB Form
Hi Out there,
I have created a VB App. for a museum - there is the imput form to update the database but i would like to have on that same form a search button where someone could enter at least one field into the form and by clicking the search button well retrieve the other data from that record.

Help anyone please

Searching An Access 2000 Database
Im making a lunch detention database program for my school and i need to search the database by ID number. I left you a copy of my program so you can fill in the search code.

Searching Keywords Access Database
How can i serach all fields in a table for Keywords?

keywords Example:
usb, mmc, SD, firewire

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

Searching An Access Database--from A 6 Month Newbie
I've been struggling with a problem for weeks now. I have a search box which I downloaded and am adapting to work with my Access 2000 database. It has well over 3000 records and I need to search using two different criteria. The search box does that by creating a SQL statement which queries my tables as I input the criteria. I have a "Create Query" button which saves the SQL statement created by the search under the name "Deposit Edit". The code is as follows:

Private Sub cmdCreateQDF_Click()
On Error GoTo ErrHandler
Dim db As Database
Dim qdf As QueryDef
Dim strName As String
   'first get a unique name for the querydef object
   strName = Application.Run("acwzmain.wlib_stUniquedocname", "Deposit Edit", acQuery)
   strName = InputBox("Please specify a query name", "Save As", strName)
   If Not strName = vbNullString Then
      'only create the querydef if user really wants to.
      Set db = CurrentDb
      Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
      qdf.Close
   Else
      'ok, so they don't want to
      MsgBox "The save operation was cancelled." & vbCrLf & _
         "Please try again.", vbExclamation + vbOKOnly, "Cancelled"
   End If
ExitHere:
   On Error Resume Next
   qdf.Close
   Set qdf = Nothing
   db.QueryDefs.Refresh
   Set db = Nothing
   Exit Sub
ErrHandler:
   Resume ExitHere
End Sub

I then have a buttlon called "Edit Deposit" which is tied to the following code:

Private Sub Edit_Deposit_Click()
On Error GoTo Err_Edit_Deposit_Click

    DoCmd.Close acForm, Me.Name
    Dim stDocName As String

    stDocName = "Deposit Edit"
    DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Edit_Deposit_Click:
    Exit Sub

Err_Edit_Deposit_Click:
    MsgBox Err.Description
    Resume Exit_Edit_Deposit_Click

End Sub

These codes work great the first time. But I can't get the Create Query section of the code to just replace the previous save so that it updates the information I'm searching for each time I make a search. Is what I'm trying to do possible? Or am I the victim of a pipe-dream?

Any advice is appreciated.

Maureen

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

Searching An MS Access Database And Posting The Results In A List Box
can anyone help?

i have a combobox with the list on field headings that the user will select,
and then in a textbox next to it, type the search word and then press the search button
which will search the database for the search word.

any ideas???

Regarding Searching By Date Range In Access W/oracle Database
Larry , thank you for your hlep, however I am still not getting it to respond.... I did correct the error you point out regarding the "bang" I missed.... so this is my issue again... if you guys could look it over for me once more....
( Oh and I did not understand the piece about time/date issue, Larry spoke of ... it's only a date I need it to respond too)
First of all...I am new at this site.... and new at VB.... so plz be gentle with me...
this is the code...
  I am using access 2000, and this is a front end to an Oracle on a I think a rs6000 unit... if that makes sense...
This is to collect data for a well information in a date range... all my other searches work so for...
thank you in advance
barry

Private Sub Command34_Click()
On Error GoTo Err_Command34_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim MyDate, MyCheck

    stDocName = "PV_WELL_DATA4"

    stLinkCriteria = "([PV_WELL_DATA.JOB_DATE]>=" & Me![From] & ") AND (" & "[PV_WELL_DATA.JOB_DATE] <= " & Me![To] & ")"
    MyDate = IsDate(Me![From])
    MyCheck = IsDate(Me![To])
    'MsgBox ("result of isdate = " & MyCheck)
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command34_Click:
    Exit Sub

Err_Command34_Click:
    MsgBox Err.Description
    Resume Exit_Command34_Click
End Sub

Code For Searching And Editting Ms Access Database Which Is Link By Ado
Hi i need to know how do i code a search for the database above using the text_change function.

Thanks!!

Edited by - OrionX_Chee_Chiew on 2/26/2003 4:53:29 PM

Access 2002 Database Search Problem: Searching Words With '
I'm using VB6 and ADO 2.7 to open an access 2002 database wich I created. I can normaly read and write values. I can browse it so the problem might not be in the database.

The problem is that when I search in a recordset for a word with a ' in it, it will generate an error. The error is generated here:

PHP Code:

rs2.Find "Nome_Ingles like '[" & FindName & "]' "

Thanks for your atention...

Updating An Access Table With A Table From Another Database
I'm trying to update a table using Access 97 and VB5 with an identical table in another database that contains changes- changes within the records, addtions & deletions of records. The update works as longs as there were no deletions or additions. The old table can't be replaced because of existing relationships. If the old table is deleted other information in different tables will also be deleted. Database replication is really not an option. Thanks.

Searching A Database Without Loading The Whole Database
I have a SQL database I am trying to search for just a few records at a time from. But when i pull over the datatable it loads the whole database. The database is so large it takes forever. What can i do just to pull the records I am searching for?

Thanks

ASP && Database Updating
I have a huge problem. Lets say that I have an online game made in ASP, if I want that the database should be updated every hour automatically, how should I do that?

Database Updating
how do i do the program which is allow the user to update the data in database ?I would like to provide an button "exit" to the user, if the user haven' t save their work,it will come out with a message that "Do you wish to save your changes?"how to make it that the program will check the work got any changes or not. and if it haven't save the work ,it will save it and update the data in database. anyone willing to help me and give some example code for it? thanks ...

Updating Database
I just started with programming a month or so ago, so I'm still very new to all of this. Here is my problem.

I have problems linking my database to my program. The database was created using Acess. I have 4 Ado controls on my form, one for the client table, one for the supplier table, one for the product table and one for the transaction table in my database. I can allready add or edit things in the client, supplier and product table, but I'm having problems with the transaction one. On the form I have a textbox (txtAmount) where I enter the amount of the product I'm buying or selling. Then there's two command buttons (cmdBuy and cmdSell) I can choose from depending if I'm buying or selling something. Just below that I've added another textbox (txtBalance) where it shows my current balance. Then in the click event of the two command buttons I've added something like this:

(I dimmed intBalance as an integer)

intBalance = intBalance + txtAmount.Text
txtBalance = intBalance

Up till here everything works fine, the balance gets updated on my form whenever I buy or sell something, but I want to save the balance that gets displayed in txtBalance to my database. I have a adodc control on my form called dcTransactions which is linked to my Transaction table in my database. One of the fields in the transaction table is balance and that's where I want to save the balance that gets displayed in txtBalance field. I tried using dcTransactions.recordset.update, but it doesn't work.

I want to save other things to my database as well, like the history of the clients and suppliers (what they bought or sold to me and for how much), but I'll probably be able to figure that out when I get the Balance working.

This is just for a project for college so please keep it very simple. Any kind of help will be much appreciated.

Updating Database
Anyone know of any utilities that can insert new data into access fields automatically??

No new tables, just additional entries into the fields

Updating Database
Hello World

Hi, Its me again. I have a local database which serves 4-6 computers. Every time there are changes with the table (e.g. modified by computer 1). How can I update the database automatically without clicking the Refresh button? All we know that every time we view all the list we need to open the connection and then close it again, right? So, how can I handle this problem? Please help. Urgent. Thanks

Updating To A Database.
What are the ado commands to update to a database, rather than adding or deleting a record? For instance, in a program that I'm writing, there is occasionally an error when not all the values have been written to the database, or they have not been written correctly and i'd just like to update the record.

The book that I have says that you can use adodc1.recordset.edit , but this causes an error saying that it's an invalid command. Is there another command that I need to use?

Thanks,
Ryan

Updating Database Value
I have a value stored as a value in a string in a textboy and a varaible Z. I want to take this new value for Z and update my database with it. I alreayd have declared the connection to my database the Myconn.Open, but when I try to run my prog I get 424 error obeject required.

This is the code I use on the button click

Private Sub Command3_Click()
z = Text6.Text
Text7.Text = z
MyConn.Open
MyConn.Execute ("UPDATE Customers SET Balance = '" & z & "' WHERE Pin = " & x & " and Account = '" & y & "'")
End Sub

Updating My Database
I'm using VB.net to update and pull from an access database. The UI has a combobox whose datasource is bound to the key of the database. I also have textboxes, datetimepickers, and a checkbox that are databound to the database, causing the data inside them to change when you pick a differnt item in the combobox. My problem that I am having is that I cant seem to pull information out of the display objects, and update the database.

here is the code that I am using currently I apologize for the cruddy looking code.

Dim clientrow As DataRow = ClientsDS1.Clients.NewClientsRow()
Dim clientrow3 As DataRow = ClientsDS1.Clients.NewClientsRow()
Dim clientrow2 As DataRow
clientrow3 = ClientsDS1.Clients.FindByName(ComboBox1.GetItemText(ComboBox1.Items.It em(ComboBox1.SelectedIndex)))
clientrow.Item("Name") = ComboBox1.GetItemText(ComboBox1.Items.Item(ComboBox1.SelectedIndex))
Dim some = ComboBox1.GetItemText(ComboBox1.Items.Item(ComboBox1.SelectedIndex))
If TextBox13.Text <> "" Then
clientrow.Item("BuisCom") = TextBox13.Text
End If
If TextBox5.Text <> "" Then
clientrow.Item("City") = TextBox5.Text
End If
If TextBox17.Text <> "" Then
clientrow.Item("Cphone") = TextBox17.Text
End If
If DateTimePicker1.Value.ToShortDateString <> "" Then
clientrow.Item("DOB") = DateTimePicker1.Value.ToShortDateString
End If
If DateTimePicker3.Value.ToShortDateString <> "" Then
clientrow.Item("DoBpm") = DateTimePicker3.Value.ToShortDateString
End If
If DateTimePicker4.Value.ToShortDateString <> "" Then
clientrow.Item("DoFs") = DateTimePicker4.Value.ToShortDateString
End If
If DateTimePicker5.Value.ToShortDateString <> "" Then
clientrow.Item("DoRec") = DateTimePicker5.Value.ToShortDateString
End If
If DateTimePicker6.Value.ToShortDateString <> "" Then
clientrow.Item("DoSale") = DateTimePicker6.Value.ToShortDateString
End If
clientrow.Item("Flowers") = CheckBox1.Checked
If TextBox16.Text <> "" Then
clientrow.Item("Hphone") = TextBox16.Text
End If
If TextBox12.Text <> "" Then
clientrow.Item("Recom") = TextBox12.Text
End If
If DateTimePicker2.Value.ToShortDateString <> "" Then
clientrow.Item("SDob") = DateTimePicker2.Value.ToShortDateString
End If
If TextBox6.Text <> "" Then
clientrow.Item("State") = TextBox6.Text
End If
If TextBox4.Text <> "" Then
clientrow.Item("Street") = TextBox4.Text
End If
If TextBox7.Text <> "" Then
clientrow.Item("Zip") = TextBox7.Text
End If
If TextBox7.Text <> "" Then
clientrow.Item("SName") = TextBox2.Text
End If
Dim nu
ClientsDS1.Clients.BeginLoadData()
ClientsDS1.Clients.RemoveClientsRow(clientrow3)
ClientsDS1.Clients.EndLoadData()
nu = OleDbDataAdapter1.Update(ClientsDS1)
ClientsDS1.Clients.BeginLoadData()
ClientsDS1.Clients.AddClientsRow(clientrow)
ClientsDS1.Clients.EndLoadData()
nu = OleDbDataAdapter1.Update(ClientsDS1)
ClientsDS1.Clear()
OleDbDataAdapter1.Fill(ClientsDS1, "clients")
OleDbDataAdapter2.Fill(ClientsDS1, "Agents")