Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
OLE with Access
- Access 2000 Advanced DML Transactions
- Access Recordset to CSV File UDF
- Access RunCommand Constants
- ACCESS Table or Query Exists
- Access to Excel via SQL
- Access Visible Problem
- Adding Fields to an Existing Access Table
- Advanced DML Transactions
- Advanced SQL in the Jet 4.0 Provider Example
- Another OLE with Access Example
- Automating Access Tutorial
- Check if Access Table Exists
- Commit Rollback Begin Trans Updatebatch
- Control Access Forms
- Count Access Records
- Csv to Access
- Dealing with ACCESS 97 Field Types
- execute a macro in Access
- Execute a Public function in Access
- Export Access Report in Choice of Formats
- Export MDB to Flat File
- Find Read and Insert Data in Access
- Gather ACCESS Table and Column Information Into Excel
- Get Access Version That Created MDB File
- Get Column Data From an Access Database
- Get Record Count
- How to Use the OpenTable method
- Launch Access Macro from Commandline
- Loop thru a recordset in Access
- Office 2003 Macro Security Issue
- OLE and Access
- OLE Import a table into Access
- OLE Import Table into Access
- Open Access DB with DAO
- Pack an Access Database
- Query and Export Data from Microsoft Access
- Read Write Delete Tables from Access DB
- Repair MDB
- Retrieve Data in Access
- SQL UPDATE Fails with Access
- UDF to Return Record Count
- Updating Table From Filtered Recordset
- Ways to Get Data from and Access file without Access installed
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
SQL UPDATE Fails with Access
Question:
I am accessing an access database when I run my select statemet i reads from the database just fine but when I try to update it fails. I am using the same syntax as anohter program that I use just in anohter file but it still does not work. Any Sugestions???
;;Open the Databases
#DEFINESUBROUTINE dataopen()
;; Allocate SQL environment and handles
db=qAllocEnv()
if db==-1
errcode=qLastCode()
message("TM DB qAlloc Failed",errcode)
endif
tmdbc=qAllocConnect(db)
if tmdbc==-1
errcode=qLastCode()
message("TM DB Conn Alloc failed",errcode)
endif
;; Set opts for SQL Connections
qSetConnOpt(tmdbc, 103, 1, 0)
;;Connect to DBs
tmcode=qConnect(tmdbc,"tm","","")
If (tmcode != @qSuccess) && (tmcode != @qSuccessInfo)
Message("TM DB qConnect failed", tmcode)
Exit
Endif
#ENDSUBROUTINE
;Open and get some data from out db
#DEFINESUBROUTINE dataget()
tmstm=qAllocStmt(tmdbc)
tmSQL=StrCat("SELECT SeqNum,Time,Date,AP,CompanyName,Location,TM,Sales,Emp,Day FROM tm WHERE SeqNum=1")
tmget=QExecDirect(tmstm,tmSQL)
If (tmget != @qSuccess) && (tmget != @qSuccessInfo)
Message("TM DB Edit qExecDirect failed", tmget)
Exit
Endif
tmfet=qFetch(tmstm)
if tmfet==@qNoData
err=StrCat("Record for Appt SeqNum: ",seqnum," not found!!")
Message("Error",err)
Exit
endif
if (tmfet != @qSuccess) && (tmfet != @qSuccessInfo)
Message("TM DB Edit qFetch failed", tmfet)
Exit
Endif
getcode = qGetData(tmstm, 1, "seqnum", 80)
getcode = qGetData(tmstm, 2, "time", 80)
getcode = qGetData(tmstm, 3, "date", 80)
getcode = qGetData(tmstm, 4, "ap", 80)
getcode = qGetData(tmstm, 5, "compname", 80)
getcode = qGetData(tmstm, 6, "location", 80)
getcode = qGetData(tmstm, 7, "tm", 80)
getcode = qGetData(tmstm, 8, "sales", 80)
getcode = qGetData(tmstm, 9, "emp", 80)
getcode = qGetData(tmstm, 10, "dayweek", 80)
code=qFreeStmt(tmstm,1)
#ENDSUBROUTINE
#DEFINESUBROUTINE dataup(update)
tmstm=qAllocStmt(tmdbc)
try=0
q="'"
c=","
;sql="INSERT INTO tm (Time) VALUES (12:00)"
sql="UPDATE tm SET SeqNum='1',Date='123',Time='12:00',AP='P',CompanyName='acme',Location='norc',TM='twm',Sales='dk',Emp='5',Day='wed' WHERE SeqNum='1'"
;sql=StrCat(sql,q,rtime,q,c,"Date=",q,rdate,q,c,"AP=",q,rap,q,c,"CompanyName=",q,rcname,q,c,"Location=",q,rloc,q,c,"TM=",q,rtm,q,c,"Sales=",q,rsales,q,c,"Emp=",q,remp,q,c,"Day=",q,rday,q," WHERE SequenceNum=",q,seqnum,q)
:sql
set=qExecDirect(tmstm,sql)
If (set != @qSuccess) && (set != @qSuccessInfo)
if try==0
try=1
goto sql
endif
Message("TM DB qExecDirect failed", set)
Exit
endif
;code=qTransact(db,0,@qCommit)
code=qFreeStmt(tmstm,1)
break
default
;sql="SELECT notes,key FROM notes"
;notesget=qExecDirect(outstm,sql)
;notesF=qFetch(outstm)
;getcode=qGetData(outstm,1,"notes",255)
;getcode=qGetData(outstm,2,"key",2)
;code=qFreeStmt(outstm,1)
break
#ENDSUBROUTINE
Answer:
The function qError can be used to grab the error from the ODBC driver itself. Here is some revised code that should help track down the problem...
#DEFINESUBROUTINE dataup()
tmstm=qAllocStmt(tmdbc)
sql="UPDATE tm SET SeqNum='1',Date='123',Time='12:00',AP='P',CompanyName='acme',Location='norc',TM='twm',Sales='dk',Emp='5',Day='wed' WHERE SeqNum='1'"
set=qExecDirect(tmstm,sql)
if (set != @qSuccess) && (set != @qSuccessInfo)
err=qerror(hstmt,2)
message("qExecDirect Failed. Driver returned:",err)
Exit
endif
code=qFreeStmt(tmstm,1)
Return
#ENDSUBROUTINE
Run the above code then post the results here.
User Reply:
Here is what came back with37000 [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
Answer:
Apparently the Microsoft Access Driver doesn't like the syntax of that SQL statement. Check it very carefully.sql="UPDATE tm SET SeqNum='1',Date='123',Time='12:00',AP='P',CompanyName='acme',Location='norc',TM='twm',Sales='dk',Emp='5',Day='wed' WHERE SeqNum='1'"Does the above SQL string work in Access?
User Reply:
No go on that one...I dont think it should matter but the SeqNum Field is an AutoNumber field...But my select statement works fineAnswer:
Get the UPDATE command working from Access itself. Post that SQL string here.User Reply:
Ok. I figured it out! You cannot use "Date" or "Time" as a column names in update statement for Access it seems. I changed those two column names and it seems to be working!!!!!Article ID: W16599
File Created: 2005:02:18:12:21:36
Last Updated: 2005:02:18:12:21:36