Visual Basic :: Update Syntax In Access 2003

Syntax Error While Useing Cascade Update For MS Access
Hi, Experts

I have One Table with three fields, which is given below.

aName text (20) - Primary Key
cName text (20) - Forign Key (used self Join) (Cascade Updated)
pName text (30)

now for above table i want to generate SQL Statement which will run through VB, i have written SQL statement for this, but if i include ON UPDATE CASCADE, then it is giving syntax error. How should i resolve this ??

Thanks in advance

Bhadresh Sangoi
Cell : +91-9892638621
email@removed
I'm careful not to confuse excellence with perfection.
Excellence, I can reach for; perfection is God's business

Syntax Error On Update Statement Vb6 Access 2000
hey guys,

im getting a syntax error on this update statement:

strUpdate = "UPDATE teams SET day='yes' WHERE tid=" & teamid
rsteams.open strupdate, dbcon

any ideas? im confused,
thanks

Reading/Import Data From Excel 2003 File And Write/export To Access 2003 Database
I'm developing Database System using MS Access 2003
As the old data are all in Excel format, I'd like to add a function into the system so that operator import old data from Excel file into the Access 2003. But i do not know how to write data into Access Database using dataset object

I have write the follwing code to read data from Excel file:

Code: Public Sub import_from_excel()
Dim conn_excel As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=c:
eport
eport_10102006.xls;Extended Properties=Excel 8.0")

sqlcommand = String.Empty
sqlcommand = "Select * from ['Multiple PN$']"
Dim excel_adaptor As New OleDbDataAdapter(sqlcommand, conn_excel)
Dim excel_DS As New DataSet
excel_adaptor.Fill(excel_DS)

'For testing
Dim excel_DT As New DataTable
Dim f_ed As New excel_data
f_ed.Show()
excel_adaptor.Fill(excel_DT)
f_ed.dg_excel.DataSource = excel_DT
'conn_excel.Open()
conn_excel.Close()

End Sub

Update ::: Syntax Error In Update Startment .... What Up ???
Dim insertSQL, updateSQL As String
Dim APP_NO As String
APP_NO = txtAppNo.Text

If UpdateOrSave = "nowupdate" Then
Set rsUpdate = New ADODB.Recordset

updateSQL = "UPDATE patents SET " & _
"[your, country, new_con, related, ftype, app_no, patent_no," & _
"fstatus, client, agent, ftitle, cref, aref, prior, mailed, filed," & _
"publish, issued, f1st, sexpires, expires, pcode, entered, modified," & _
"fby, f1, f2, p]VALUES '" & your & "','" & country & "','" & new_con & "'," & _
"'" & related & "','" & ftype & "','" & ChkString(APP_NO) & "','" & ChkString(patent_no) & "'," & _
"'" & fstatus & "','" & ChkString(client) & "','" & ChkString(agent) & "','" & ftitle & "'," & _
"'" & ChkString(cref) & "','" & ChkString(aref) & "','" & ChkString(prior) & "','" & ChkString(mailed) & "'," & _
"'" & ChkString(filed) & "','" & ChkString(publish) & "','" & ChkString(issued) & "','" & ChkString(f1st) & "'," & _
"'" & ChkString(sexpires) & "','" & ChkString(expires) & "' ,'" & pcode & "'," & _
"'" & entered & "','" & modified & "','" & fby & "','" & f1 & "'," & _
"'" & f2 & "','" & p & "' & WHERE [APP_NO] = '" & APP_NO & "';"
objAccessConnection.Execute (updateSQL)

Convert SQL Server SQL Syntax To MS Access Syntax
How can I convert SQL server SQL syntax to MS Access Syntax?
What is the differences, are there any conversion classes or modules I can
download.
I never used any SQL functions, just SELECTing, INSERTing, DELETEing and UPDATEing
and just the SQL function to get the latest added row, but that is not really a problem, I'll should be able to sort that out.

Thanx in advance
NL

Edited by - NLategan on 7/11/2004 9:15:21 AM

Update Syntax In VB
Hi!

I would like to update an Ingres Table (I_persons) connected using an ODBC driver from an SQL Server table (S_persons) using an OLEDB driver.

Lets say: I_person Table has the ff: columns namely name,address, tel. while S_persons has also the same.

Pseudocode: Update I_persons table from S_persons where I_persons.name=S_persons and I_persons.address = S_persons.address set I_persons.tel = S_persons.tel

Can somebody translate this psuedocode into a correct VB syntax?

Thanks in advanced.

Sql UPDATE Syntax
Updating 5 fields in a single row of a db, I can get this to work updating 1 fields at a time, but it would be nice to do all 5 in one step

stupst = "UPDATE guide SET [guide].[views] = " + stview + ", SET [guide].[viewjono] = " + stviewjo + ", SET [guide].[viewwally] = " + stviewwa + ", SET [guide].[viewstevo] = " + stviewst + ", SET [guide].[viewshutts] = " + stviewsh + " WHERE [guide].[id]=" + stid + ";"

oConn.Execute(stupst)

the strings are in format to include the ' + value + ' so that
stview are equivalent to '2'
stviewjo are equivalent to '2'
stviewwa are equivalent to '3'
stviewst are equivalent to '1'
stviewsh are equivalent to '0'
so that...

....SET [guide].[view] = '2', SET .... is what is actually being excuted

I am not shure if I should seperate the 5 different "SET" lines by a comma or not

SQL UPDATE Syntax
This is quite strange, I have an UPDATE statement that, according to VB, has a syntax problem. However, it seems to match the other UPDATE statements that I am using in the program, which all work.

Here is the offending statement:

Code:
DbConn.Execute "UPDATE Users " & _
"SET Password='" & txtNewPass.Text & "' " & _
"WHERE Username='" & strUsername & "'"

What Should Be Sql Syntax For Update
Hi

I have to tables one is transaction and other is master, when i make entry in transction it should be add in Transaction file while updates Master file.
Master.stock = Master.stock+1

I want to know, what is the sql query syntax for update in above condition.

ASP Update Syntax!!!
Does this syntax look right?
thanks

Code:
sql = "Update Contacts Set Address='" & strAddress & "', City='" & strCity & "', State='" & strState & "', Zip='" & strZip & "', EmailAddress='" & strEmail & "', Company='" & strCompany & "', Department='" & strDepartment & "', PhoneNo='" & strPhoneNo & "', Extension='" & strExtension & "', FedexAcctNo='" & strFedexAcctNo & "', ContactType='" & strContactType & "', OtherYesNo='" & strOtherYesNo & "' Where ContactID =" & lngID & "'"

Help In Update Syntax
hi ,

i have a form in which there are text fields. these text fields show information form a access database.
i also have an update button. the code in this syntax is "adodc1.recordset.update" . when i run the program it shows me an error message saying "s_no cannot be updated"
the "s_no" is the primary key in the database. and i need to get this button working , someone please help.

reply me to virajdeoram@hotmail.com

Syntax For Update
Please Help me.

what is the syntax for the "update" statement.

thank you.

Jay

ADO 2.7 RecordSet Update Syntax
This statement is not functioning properly:

Code:
rs.Update [MyField], False
It returns the error of:

Quote:

Compile Error:

External name not defined.

It then points to this code upon debugging:

Code:
rs.Update [MyField], False
I know this field exists in the recordset, I can access it. Why is it that I cannot update a field with a value of False when using this statement?

Edit:
This was the best I could come up with on MSDN:

http://msdn.microsoft.com/library/de...dmthupdate.asp

SQL Update Syntax Error
greetings, a very simple line with hopefully a very simple problem

Update Main SET (ProductName = '1',Category = '1',CostPrice = '1',ProfitPercent = '1') WHERE (ProductCode = '1' and Supplier = '1')

obviously, MAIN is my table in MSaccess 2000. all fields are text type and im using vb 6 service pack 5.

all help would be greatly appreciated
regards

Syntax Error - Update
please check my update statement..

rsGetsubjects (access query)

Code:
if maxstud= studnum then
conn1.Execute ("update rsGetsubjects!fld_closed = vbchecked ")
end if
thanks...

SQL Update Syntax Error
I can't figure out whats wrong with this statement....

UPDATE [REPORTING] SET [EMPNAME] = 'JOE BLOW', CLASS = 'FOREMAN', WEEKENDING = #1/9/2005#, PROJECTNAME ='COMPANY PROJECT', CHECK# = '50118', STWAGE = 37.25, OTWAGE = 55.88, FEDTAX = 302, FICA = 113.99, STATE = 95, SDI = 16.09, VACATION = 104.3, HEALTH = 195.2, PENSION = 200, TRAINING = 14, FUNDADMIN = 6.8, UNIONDUES = 48.43, TRAV/SUBS = 0, SAVINGS = 0, OTHER = 44.8, EXEMPTIONS = S-0, NETPAID = 808.19, GROSSTP = 1490, GROSSAP = 1490, TOTALDEDUCTIONS = 681.81, PAYROLL# = '29', T/F = 2, MILEAGE = 0, STMONDAY = 8, STTUESDAY = 8, STWEDNESDAY = 8, STTHURSDAY = 8, STFRIDAY = 8, STSATURDAY = 0, STSUNDAY = 0, TOTALSTHOURS = 40, OTMONDAY = 0, OTTUESDAY = 0, OTWEDNESDAY = 0, OTTHURSDAY = 0, OTFRIDAY = 0, OTSATURDAY = 0, OTSUNDAY = 0, TOTALOTHOURS = 0 WHERE [ID] = 1

I've tried it without the brackets for the table and initial field name. I've tried it without spaces before and after the = signs. I checked it out and the fields without single quotes are all numeric. All the ones with single quotes are string, the only field that is date has ## around it....

What gives!

Update Syntax Error
Greetings:

In use ADO 2.7, Access 2000, VB6;

Can someone shed some light on what is wrong with the following code syntax; I'm getting:

Error -2147217900; Source: Microsoft JET Database Engine;
Description: Syntax error in UPDATE statement."

Code:

Set adoConn = New ADODB.Connection
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\MegaoneService.mdb; Persist Security Info=False "

With adoConn
.Open ConnectionString
.BeginTrans
.Execute "UPDATE Prospects set " & _
" (ProspectCompanyName = '" & Trim$(txt_company_name) & "', " & _
" ProspectDepartment = '" & Trim$(txt_department) & "', " & _
" ProspectLastName = '" & Trim$(txt_last_name.Text) & "', " & _
" ProspectFirstName = '" & Trim$(txt_first_name.Text) & "', " & _
" ProspectAddress1 = '" & Trim$(txt_address_1.Text) & "', " & _
" ProspectAddress2 = '" & Trim$(txt_address_2.Text) & "', " & _
" ProspectCity = '" & Trim$(txt_city.Text) & "', " & _
" ProspectState = '" & Trim$(Combo_States.Text) & "', " & _
" ProspectZipcode = '" & Trim$(txt_zipcode.Text) & "', " & _
" ProspectGender = '" & Trim$(Combo_Gender.Text) & "', " & _
" ProspectVoicePhone = '" & Trim$(txt_work_phone.Text) & "', " & _
" ProspectVoiceExtension = '" & Trim$(txt_extension.Text) & "', " & _
" ProspectFaxPhone = '" & Trim$(txt_fax_phone.Text) & "', " & _
" ProspectCellPhone = '" & Trim$(txt_cell_phone.Text) & "', " & _
" ProspectHomePhone = '" & Trim$(txt_home_phone.Text) & "', " & _
" ProspectEmailAddress = '" & Trim$(txt_email_address.Text) & "', " & _
" ProspectStatus = '" & Trim$(Combo_Status.Text) & "', " & _
" ProspectGroup = '" & Trim$(Combo_Group.Text) & "', " & _
" ProspectReferredBy= '" & Trim$(Combo_Referral.Text) & "', " & _
" ProspectNextContactDate = '" & CDate(txt_next_date) & "', " & _
" ProspectTransDate = #" & strdate & "#, " & _
" ProspectLastUserToUpdate = '" & Txt_User_Name & "', " & _
" ProspectLastUpdateDateTime = #" & Now & "#, " & _
" WHERE ProspectID = " & CInt(txt_prospect_no) & " ) "
.CommitTrans
.Close
End With

Set adoConn = Nothing
Clear_Variables
Exit Sub

As usual I thank you guys in advance,

Donald

Update Syntax In SQL7 ??
Hi,
I am familiar with oracle but new to sql7
so pls advise
I have a database with only two fields
citycode and cityname.

I have combo1.text = citycode and
text1.text = cityname
then I give the command ( just rs.update works in ORACLE)
rs.update ( what is the SET command in sql7 update)
( do I have to give the SET word after UPdate....)
I get an error
too many rows update error

Pls advise the SET command in update of SQL7

thnks

UPDATE Syntax Question
Hi,

I'm trying to update multiple fields using the UPDATE function but cannot get it to work.

strSQL = "UPDATE Estimates SET [Project Name]= '"
strSQL = strSQL & item.Subject
'strSQL = strSQL & "','"[Location]= '"
'strSQL = strSQL & item.Location
strSQL = strSQL & "'WHERE [EstimateID] = " & item.ID

conDB.Execute strSQL

The above command successfully updates the Project Name but when I take the comment off lines 3 & 4, it will not update the Location along with the Project Name.

All these "s and 's are confusing. My goal is to have it to update multiple fields.

TIA

Recordset Update Syntax Error!
Hi,

I have discovered that using the Driver={Microsoft Visual FoxPro Driver} with ADO2.5 on a Foxpro2.6 table that uses I guess key words specifically "Desc" (of course short for Description) as a Feild name (Desc must be confused with Order by Desc I guess). So when I use either of below update methods I get an Syntax error on update,

rs!Desc ='Bloody Update'
rs.update
Update Table Set Desc='Bloody Update' Where Criteria

Funny enough I can use rs.addnew and add a whole Record, though I would have to delete the old record first, and as Foxpro
doesnt really delete(just flag record as deleted until Pack) I prefer not to do this!
Changing the Feild name isn't an option.

Any suggests please?

David

P.S Feild "Desc" is a Memo feild though I wouldn't think Type would matter

Syntax Error In Update Statement
Hey all,

Been trying to find out where the syntax is bad and can't find it. Can fresh eyes find it!

Code:
Set CMDUPDATE.ActiveConnection = CNN
CMDUPDATE.CommandText = "UPDATE Presses SET " & _
"[Collector]='" & cboCollector.Text & "', " & _
"[presses]=" & cboPresses.Text & ", " & _
"[Type]='" & txtType.Text & "', " & _
"[Unit]='" & txtUnit.Text & "', " & _
"[Internal_ID]=" & txtInternalID.Text & ", " & _
"[displayhigh]=" & txtDisplayHigh.Text & ", " & _
"[displaylow]=" & txtDisplayLow.Text & ", " & _
"[high]=" & txtHigh.Text & ", " & _
"[low]=" & txtLow.Text & ", " & _
"[Green]=" & txtGreen.Text & ", " & _
"[red]=" & txtRed.Text & ", " & _
"[Dropbelow]=" & txtDropBelow.Text & ", " & _
"[PressGroup]='" & txtPressGroup.Text & "', " & _
"[PressGroupSeq]=" & txtPressGroupSeq.Text & ", " & _
"WHERE [Collector]='" & MAINT_COLLECTOR & "' AND [presses]=" & MAINT_PRESS

RSTUPDATE.Open CMDUPDATE, , adOpenDynamic, adLockBatchOptimistic

SQL Update Statement Syntax Error
I am having toruble with the below vb sql update statement. All the field names are correct and the fields are all text fields. Does anyone know what im missing? I have been looking at this for so long now it must be something simple to do with characters such as ' and "
-----------------------
DoCmd.RunSQL "update tblTransStatementSource set UnitsOFF = AdvisorUnitsOFF, set CreditAUD = AdvisorCommissionAUD"
-----------------------
(the code is all in one line in the project - no _ used in the statement)
Please help...

Thanks
Scott

Syntax Error In Update Statement
I am getting a "syntax error in update statement" error message. Can someone please take a look at my code and help me fix it. The syntax seems pretty good to me. I dont understand where i went wrong.

sSql = "UPDATE Organization o SET (o.TextZip = '" & TextZip.Text & "') WHERE o.OrganizationID = '" & TextOrgID.Text & "'"

myRecordset.Open sSql, myConnection, adOpenDynamic, adLockOptimistic

Syntax Error In UPDATE Statement - Pls Help
I would be very grateful if anyone could shed any light on this error message:

Run-time error -2147217900
Syntax error in UPDATE statement.

Below is detailed the code:

sqlSQL = "UPDATE [db1_Full_Store_Details] SET db1_Full_Store_Details[stock_level] = " + CStr(sqlRS.Fields(17)) + " "
sqlSQL = sqlSQL + "WHERE db1_Full_Store_Details.[store_ID] = " + CStr(sqlRS.Fields(3)) + " "
sqlSQL = sqlSQL + "AND db1_Full_Store_Details.[ref_id] = " + CStr(sqlRS.Fields(4)) + " "
sqlConn.Execute sqlSQL

Syntax Error In Update Statement
do i miss anything in my statement:

Code:
Private Sub cmd_drop_Click()

schedid = Val(txt_schedid)
conn1.Execute (" update tbl_stud_mlist set fld_remarks = '" & "Officially Dropped" & "' where fld_stud_no = '" & txt_studno & "'" & _
"and fld_sched_id = '" & schedid)

End Sub

it says syntax error in string in query expression 'fld_stud_no = ' " & txt_studno & " ' " & _
" and fld_sched_id = ' " schedid

Syntax Error In Update Query- Help Out
okay, this is actually similar to a previous post - same way that i'm doing - get data from mother table then saves updated data to childtable...

here's a query which is returning a syntax error:

Code:
SelSql2 = "UPDATE tblPayrollInfo SET SalaryLoan_Pay =" & Text2(0).Text & ", PolicyLoan_Pay=" & Text2(1).Text & "," _
& "Premium_Pay =" & Text2(2).Text & ", PhilHealthMed = " & Text2(3).Text & "," _
& "GSIS = " & Text2(4).Text & ",Desc1 = '" & Text2(5).Text & "', ODed1 = " & Text2(6).Text & ",Desc2 = '" & Text2(7).Text & "'," _
& "ODed2= " & Text2(8).Text & ", Desc3= '" & Text2(9).Text & "', ODed3 = " & Text2(10).Text & ",OptionalPrem= " & Text2(11).Text & ",Tax = " & Text2(12).Text & "," _
& "RATA = " & Text2(13).Text & ", PERA =" & Text2(14).Text & ", ACA =" & Text2(15).Text & "," _
& "Allowance=" & Text2(16).Text & ", GrossPay =" & Text2(17).Text & ", Deduction = " & Text2(18).Text & ", NetIncome=" & Text2(19).Text & " Where EmpInfo = " & Combo1.Text & " AND PayDate = #" & Text1.Text & "#"

like in the previous code, i think it's the part of the query with the date that is causing the error...

any idea again?

2 ?s: Update Syntax And Required Fields
hi all.
1st: DBCon.Execute "UPDATE LoginTable SET Password='" & fldNewPassword & "' WHERE Username='" & fldUsername & "'"

why does this give me a syntax error, what is the correct syntax?
ive tried numerous variations, and all i managed to do was change the error when i put 's around the LoginTable. it then changed to a "incomplete query clause" or something.

2nd: how do i set a field's required property to false? vb seems to ignore this because i set many fields to blank, but when i attempt to do so from an mdb editor i am using, it raises an error about the field being required. and i dont know how to make it unrequired.

thx for any help

Syntax Error In Update Statement
Guys... I'm trying to update data in a recordset. First I use the REPLACE INTO query but I got an error saying "INSERT, SELECT, UPDATE and DELETE statements are expected" or something like that. So I guess REPLACE INTO is not acceptable. I used the UPDATE query in my code and I got a syntax error. I don't know where I messed up, care to tell me? Please help me

Code:
Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:user.mdb;"
Conn.Open
Set recset = New ADODB.Recordset
recset.CursorType = adOpenKeyset
recset.LockType = adLockOptimistic
Set recset = Conn.Execute("UPDATE members SET Password = 'txtnew.Text' WHERE Password = 'txtold.Text' ")
recset.Update
Conn.Close
where txtnew.Text contains the new password and the txtold.Text contains the old password.

Syntax Error In UPDATE Statement
hi,

I'm trying to update my Access database with the UPDATE command, but for some reason it keeps saying "synxtax error in UPDATE statement". I've searched on this forum, and even went to W3Schools.com to double-check the command. All the values are numeric, so I don't need quotes, and the CursorType and LockType seem to be fine too. Below is the procedure to connect to the database:

Code:
Private Sub SQL_Execute(Command As String)
Set Conn = New ADODB.Connection
Set RS = New ADODB.Recordset

Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & File
RS.Open Command, Conn, adOpenStatic, adLockOptimistic
End Sub
All of the above variables (Conn, RS & File) are public variables.

The command is:

Code:
SQL_Execute "UPDATE Settings SET Top = " & Me.Top & ", Left = " & Me.Left & " WHERE Port = " & COM.CommPort
When I replace SQL_Execute with MsgBox, I get this: UPDATE Settings SET Top = 3405, Left = 3810 WHERE Port = 1
Again, this looks fine to me, but it keeps saying there's a synxtax error.
I've tried to use the INSERT INTO command, but that fails too. Reading (with the SELECT command) works fine though.

Can anyone please help me with this? Thanks in advance.

Syntax Error In Update Statement
hi,when try to update database have this error message appear
"Syntax error in Update statement".The database is ms.access.

this is the current code:

Code:
Private Sub cmdmatch_Click()
Dim cn As ADODB.Connection 'this is the connection
Dim rs As ADODB.Recordset 'this is the recordset
Dim strsql As String

Set cn = New ADODB.Connection
cn.Open "Provider = 'Microsoft.Jet.OLEDB.4.0';" & "Data Source = " & App.Path & "overtime.mdb;"

strsql = "update overtime set match=1 From OverTime, attendance Where OverTime.overtimedate = attendance.overtimedate And OverTime.employeeno = attendance.employeeno"
Debug.Print strsql
cn.Execute strsql

End Sub

please help.thanks!

Syntax Error In UPDATE Statemnet
Hi,
I'm new to the form, I'm using Access and VB6 to develop some sort of a job tracking program. I'm facing a problem when trying to update a table using the syntax below:

Code:
1. Private Sub cmdOK_Click()
2. con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & vbCrLf & _
3. "DataSource=" & App.Path& "db.mdb;Persist Security Info=False"
4. con.Open
5. strSQL = "UPDATE tblnew SET "
6. strSQL = strSQL & "Type = " & cbmType.Text & ","
7. strSQL = strSQL & " Status = " & cbmStatus.Text & ","
8. strSQL = strSQL & " Priority = " & cbmPrio.Text & ","9.
9. strSQL = strSQL & " DateOut = " & txtDateOut.Text & ","
10. strSQL = strSQL & " Summ = " & txtSumm.Text & ","
11. strSQL = strSQL & " ElapsedTime = " & txtElapsed.Text & ","
12. strSQL = strSQL & " UpdateDate = " & Now & ""
13. strSQL = strSQL & " WHERE ID = " & lbID.Caption
14. con.Execute strSQL
15. con.Close
16. Set con = Nothing

17. End Sub

Each time this code is excuted, a syntax error in the update statement message pops up. Would anyone please help me with this.

Syntax Error In Update Statement
kindly help. i wonder what is my error in UPDATE statement.

here's my error:
-2147217900 Syntax error in UPDATE statement.

Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "URLOrderform.mdb"
    cn.CursorLocation = adUseClient


    stUpdate = ("UPDATE tblRegistration Set [Record] = '" & txtRecord.Text & "',[Orderdate]= '" & DTPicker1.Value & "', " & _
            "[OrderID] = '" & txtOrderID & "',[Ordernum]= '" & txtOrderNum & "',[CompID]='" & txtLicID & "'," & _
            "[LicKey]= '" & txtLicKey & "',[LicName]='" & txtLicName & "',[LicLname]='" & txtLName & "'," & _
            "[Address]= '" & txtAdd & "',[myemail]='" & txtEmail & "',[Orderstat]='" & cboOrderStat & "'," & _
            "[Price]= '" & txtPrice & "'," & _
            "WHERE [Record] = '" & txtRecord & "'")

    cn.Execute (stUpdate)

Edited by - edd_hills on 5/15/2007 7:23:04 PM

Syntax Error In Update Statement
Dim updateSQL As String
updateSQL = "Update Product Set " & _
" Description = '" & txtDesc.text & "', " & _
" Where Code = '" & txtCode.Text & "'"
Set RS = Conn.Execute(updateSQL)

This statement executes fine when i test it in access but here, it returns the above error. I have checked that all the fields are the same as that in the database and the textboxes match aswell. Neither txtcode or txtDesc are bound to any component. What could the problem be? if you need any more information, please let me know, any help would be greatly appreciated!!

Update Record Source Syntax Problem
How do I make this work? When a button is clicked I want this to update... I think my SQL syntax is wrong...

VB Code:
Me.Octsub.Form.RecordSource = "SELECT tblOutput.NAME, tblOutput.HOTEL, tblOutput.COST, tblOutput.MONTH, tblOutput.NIGHTS, tblOutput.SHARING FROM tblOutput WHERE (((tblOutput.MONTH)="January"));"

Syntax For Insert, Update, Delete,Select
I am using vb 6.0 , ORacle 8.1.7 , ewin 2000,,,, on my home PC
I've connected db and my vb project and using ADODB

1- kindly tell me what is the syntax for the insert, update,select command (give example if possible. tell me how to show the selected record in the txt boxes of the form)
I knew Oracle as far as dml is concerned, but how to use in VB I dont know

2- I am hiving errors in this insert query
in my code window (when ever i open it ) it give me error
"compiler error expected end of ststement " and highlight area shown with red color
qry = "INSERT INTO wing (wing_id,principal_name,controller_name,email_add,phone_wing, fax_wing,wing_name) VALUES (' " & txt_frmwp_wingid & " ',' " & txt_frmwp_principal & " ' , ' " & txt_frmwp_controller & " ',' " & txt_frmwp_emailadd & " ',' " & txt_frmwp_phone& " ', ' " & txt_frmwp_fax & " ', ' " & txt_frmwp_blockname & " ' )"

If I ignore it and run the project it insert the values into table but give me the same error and highlight the above line completely.

kindly tell me how to devide a one stmt over multiple lines

follwing is the complete code I wrote for inserting the data
Code:
Private Sub cmd_frm_wing_profile_dd_Click()
' create a connection object
   Dim oconn As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   'this is using the Oracle provided driver
    oconn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=tps;" & _
           "User Id=system;" & _
           "Password=manager"

Dim qry As String
'qry = "INSERT INTO wing (wing_id,principal_name,controller_name,email_add,phone_wing, fax_wing,wing_name) VALUES (' " & txt_frmwp_wingid & " ',' " & txt_frmwp_principal & " ' , ' " & txt_frmwp_controller & " ',' " & txt_frmwp_emailadd & " ',' " & txt_frmwp_phone& " ', ' " & txt_frmwp_fax & " ', ' " & txt_frmwp_blockname & " ' )"
qry = "INSERT INTO wing (wing_id,principal_name,controller_name,email_add,phone_wing, fax_wing,wing_name) VALUES ('tt','principal','controller','emailadd','phone', 'fax', 'block' )"
Set rs = New ADODB.Recordset
rs.Open qry, oconn
     'rs.ActiveConnection = oconn
     'rs.Source = qrystring
     'rs.Open
oconn.Close
End Sub

Simple ADODB Update Syntax Error Issue
All,

This is a rather newbie question, but can anybody find where the Sytnax error in my Update statement is?

Dim PasswordConn As ADODB.Connection
Dim PasswordRecSet As ADODB.Recordset
Dim strUpdateuser As String
Dim strUpdatepass As String

Set PasswordConn = New ADODB.Connection
PasswordConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:programmingLBPP Password Utilitypasswordutility.mdb;"

PasswordConn.Open

strUpdateuser = txtUsername.Text
strUpdatepass = txtPassword.Text
PasswordConn.Execute ("Update PasswordUtility SET Password = strUpdatepass WHERE Name = strUpdateuser")

PasswordConn.Close

INSERT, UPDATE And DELETE With One Stored Procedure - Syntax Please?
Good day to everyone,

Aside from going into my books (that's right, I'm lazy) I just need some quick info.

I just need the general syntax for an INSERT, UPDATE and DELETE for a stored procedure. You see, I got four tables that I need to write to with one stored procedure. It was simple enough with the SELECT statement, I just used INNER JOINS, but I'm not seeing the idea of writing or deleting from these tables being quite so easy.

If someone can toss me a line, that would be great. I really don't want to have to try and look it up, SQL books are just crap (so vague).

cheers

SQL Update Statement Date/Time Syntax Problem
G'Day all,

Could somebody please help me sort out this statement.

Code:"UPDATE tblBookings SET Time = # " & initial & " # WHERE id = " & uid & " "

"Time" is a date/time field in an Access Database
Initial is set as a "Date" variable, its value "11.00.00AM"

Thanks

Scott

VBA/Access 2003 - Hiding Access Main Window
http://support.microsoft.com/default...S%3B167659#kb5

I found this website with information on how to hide an instance of Access when you start it up through Automation. I've been playing with the code trying to hide the instance of Access that I am running currently, instead of the one I start up.

Objective:
To hide the Access window behind the forms, reports, and everything you are using without having to open another instance of Access through automation.

I would really like to do this, any suggestions? Thanks, Dan~

Access 2000 To Access 2003 Question
Okay I have added a access 2000 database to my application, if I open the database in Access 2003 and save it in that version, will the file still load properlly in the application?

Thank you.

BlondeGirl

Access 97 Application To Access 2003 - What Concerns?
We have a large Access 97 application that we're planning to eventually replace. Until then, we want to keep it going with minimal changes. People with newer PCs and newer Office versions are having problems with this application, so we'd like to move to Access 2003.

This will be purely a "make it work" upgrade. We're not changing DAO to ADO, or enhancing functionality.

Some of the programs use third party OCXs.

Will this be a simple task, or should I expect problems? Any hints are greatly appreciated.

LB

Word 2003-VBA Update Specific Form Fields But Not Reset Other Fields
Good Afternoon,

I have a userform that pops up right when you open the template. When someone completed the userform the information the entered is placed in specific form fields within the document. If someone was to complete the rest of the form and then go back and update the information in the userform, the rest of the form fields would reset.

My question is, How can I make it so the userform updates the appropriate boxes but does not reset any other form fields?

The userform code I am using is as follows:

Code:
Option Explicit

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOK_Click()
' Prevent variable from being deleted
If txtDate = "" Then txtDate = " "
ActiveDocument.Variables("Date") = txtDate
If txtClientName = "" Then txtClientName = " "
ActiveDocument.Variables("Client Name") = txtClientName
If txtClientIDNumber = "" Then txtClientIDNumber = " "
ActiveDocument.Variables("Client ID Number") = txtClientIDNumber
' Update fields in document
ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Fields.Update
Unload Me
End Sub

Private Sub txtClientID_Click()

End Sub

Private Sub txtClientIDNumber_Click()

End Sub

Private Sub txtDate_Click()

End Sub

Private Sub Label1_Click()

End Sub

Private Sub UserForm_Initialize()
' Load variables into text boxes
On Error Resume Next
txtDate = ActiveDocument.Variables("Date")
txtClientName = ActiveDocument.Variables("Client Name")
txtClientIDNumber = ActiveDocument.Variables("Client ID Number")
End Sub

Any assistance on what I am doing wrong and how to fix it would be greatly appreciated!

Thanks,
SMOO

API's In VB For Access 2003
I am new to this site and have looked around for an answer to my problem; however I did not find one. So I apologize in advance if this topic has come up already, and if so please point me in the direction of said thread.

I am working with an Access 2003 database. The database is designed to hold IP addresses and various information about them. My project is to create a form that the administrator can use to go through each record and determine if that record is

A)The IP is still being used
B)The DNS name is correct
C)The subnet is correct.

At this point I have solved all 3 of those criteria. To determine if the IP is still active I use GetRTTAndHopCount from "iphlpapi.dll".

For the DNS I use GetHostByAddr from "wsock32.dll"

My first problem is that both of these take 6 seconds if they get no response. Ie. No response to the ping (GetRTTAndHopCount) or no DNS Name (GetHostByAddr) I have the program set up so worst case each bad record will only take 6 seconds, but I am wondering if there are better functions in other DLL’s that might help me, or am I just doomed to have a 6 seconds wait on my bad Records.

My second problem is rather minor. I am trying to put a progress bar on this form, as there are some 15,000 IP entries. I created a progress bar from Common Controls.and increment the .Value every time I get done with a record. If I take the bar out side of my code it works fine. But in my code it has issues. If I just click the GO button it runs fine, all is right in my little universe. However, if anything, the form, the desktop, anything is clicked the bar stops. The program continues and does its job, the bar just stops progressing. I have found that if I put a DoEvent in it will progress. But with having a 15,000 record and growing database I think DoEvent will slow it down Considerably, which is bad when you add that to the 6 seconds for each bad record.

Any help would be most appreciated
Thanks
-Souls