Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Writing record to MSAccess DB
#1
Hi- I used QM to pull some info from a work website and format it, and now I am trying to insert same data into an MsAccess mdb database. But the available help and conversations on the forum aren't enough to get me up and running, given my knowledge of SQL and Database accessing.

Can you provide a clear, real-code example of accesing an Access database that:

1. Opens the database
2. Checks 1 field to ensure record is not created already, and if it has been, updates the record
3. If no record exists per #1, adds a record to the database
4. Closes the database

I have tried a couple dozen variations given what I can find via available QM resources, but the examples provided currently give me syntax errors every time.

I appreciate any help that can be provided.

PS the current examples don't provide field names, so I assume the field info is passed sequentially per the DB schema, is that correct? If so, if you only need to initially populate the first 4 fields, for example, can you leave off the trailing, empty fields, or do you have to represent them (i.e. with '','', etc)? That is one point I am unclear on.


Thanks
Again
#2
real quick, here's what i do.
i create the query in access and then use qm to hit that query, if the results are blank then the record doesnt exist. you might be able to do the same thing with adding a record by making an append query as well....no SQL needed :wink:
An old blog on QM coding and automation.

The Macro Hook
#3
Code:
Copy      Help
;Database: My Documents\db1.mdb
;Table: TestTable
;Fields: Name (text), Email (text), Age (number)


;will insert or replace these values
str n="Test"
str e="a@b.c"
str a=100

;connect
Database db7
str connString=db7.CsAccess("$personal$\db1.mdb")
db7.Open(connString)

;;create test table
;db7.Query("DROP TABLE TestTable"); err ;;drop if exists
;db7.Query("CREATE TABLE TestTable(Name CHAR, Email CHAR, Age NUMBER)")


;if record exists, update values
str sql.format("UPDATE TestTable SET Email='%s', Age=%s WHERE Name='%s'" e a n)
if(db7.Query(sql)=0) ;;returns the number of affected records, so it will be 0 if the record does not exist
,;insert new record
,sql.format("INSERT INTO TestTable (Name,Email,Age) VALUES('%s','%s',%s)" n e a)
,db7.Query(sql)

;disconnects automatically
#4
By default, changes in database are saved automatically. If macro does series of changes, and an error occurs in the middle, macro will end and database will be updated partially, which is not good in many cases. Here is example of saving changes explicitly:

Code:
Copy      Help
;Database: My Documents\db1.mdb
;Table: TestTable
;Fields: Name (text), Email (text), Age (number)


;will insert or replace these values
str n="Test"
str e="a@b.c"
str a=100

;connect
Database db7
str connString=db7.CsAccess("$personal$\db1.mdb")
db7.Open(connString)

;begin transactions (series of changes that must be explicitly saved and can be undone)
db7.conn.BeginTrans
;Now changes will not be saved unless you call db7.conn.CommitTrans. You can call db7.conn.RollbackTrans to undo changes at any moment. These functions also end the transaction.

;;create test table
;db7.Query("DROP TABLE TestTable"); err ;;drop if exists
;db7.Query("CREATE TABLE TestTable(Name CHAR, Email CHAR, Age NUMBER)")


;if record exists, update values
str sql.format("UPDATE TestTable SET Email='%s', Age=%s WHERE Name='%s'" e a n)
if(db7.Query(sql)=0) ;;returns the number of affected records, so it will be 0 if the record does not exist
,;insert new record
,sql.format("INSERT INTO TestTable (Name,Email,Age) VALUES('%s','%s',%s)" n e a)
,db7.Query(sql)

;save changes
db7.conn.CommitTrans

;disconnects automatically
#5
Many thanks, some good ideas and great examples; exactly whatI was looking for. Thanks again.
Ryshyn
#6
Hi guys - I used the examples you provided me in my last forum request with some success - I can get the DB to update when a record exists in the DB. But when it goes to INSERT a new record, I keep getting the error:


Error (RT) in Macro56: 0x80040E14,
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

I don't see where that can be, it all seems to match. Here is what I have; (The first section pulls the data from a web page, and works fine; just including it for continuity)

I also have an issue that the
if (db7.Query(sql))
Always seems to return -1 whether a record exists or not. I changed it to
if (db7.Query(sql)) = 0
and that seems to work ok; just mentioning it to give all the facts...

My code follows. Any suggestions would be appreciated.

Note: I have 14 fields in the actual database-this query only populates 4 of them. Do I have to account for the remaining (blank) fields in the INSERT query in some way?)

Thanks in advance

; Activate Browser
act "Maxthon"
;Search for appropriate HTML to extract
MSHTML.IHTMLElement el=HtmlFind3("TABLE" "" "[]<TABLE cellSpacing=0 cellPadding=0 width=''100%'' bgColor=#e1e1e1 border=1><TBODY>[]<TR>[]<TD vAlign=top>[]<TABLE cellSpacing=0 cellPadding=2 width=''100%'' border=0>[]<TBODY>[]<TR>[]<TD vAlign=top>[]<TABLE cellSpacing=2 cellPadding=2 border=0>[]<TBODY>[]<TR>[]<TD align=right><FONT face=Arial,Helvetic" win("Nexicore Services - Maxthon Browser") 0 4 0x28)
str thetext=el.innerText

str s = el.innerText

; Tokenize & Extract data for DB Input
ARRAY(str) arr = s
nt = tok(s arr 3 ", ()" 8 arr2)
str CN=arr[0]
CN.findreplace("Customer Name:" "")
str SA=arr[1]
str SA
SA.findreplace("Street Address:" "")
str SA2=arr[2]
SA2.findreplace("Street Address 2:" "")
str CY=arr[3]
CY.findreplace("City:" "")
str ST=arr[4]
ST.findreplace("State:" "")
str ZP=arr[5]
ZP.findreplace("Zipcode:" "")
str CT=arr[6]
CT.findreplace("Country:" "")
str PH=arr[7]
PH.findreplace("Phone 1:" "")
str fp=PH
str PH1 PH2 PH3
PH1.get(fp 0 3)
PH2.get(fp 3 3)
PH3.get(fp 6 4)
PH.from("(" PH1 ") " PH2 "-" PH3)
str SN=arr[8]
SN.findreplace(" Service Notification:" "")
str Service_Co=arr[9]
Service_Co.findreplace("Service Company:" "")
str Service_Co_Short
Service_Co_Short.get(Service_Co 0 1)
str Ticket_No=arr[10]
Ticket_No.findreplace("PO #:" "")
str AS=arr[11]
AS.findreplace("Assigned To:" "")
str PSD=arr[12]
PSD.findreplace("Part Shipped Date:" "")
str Customer_Address
Customer_Address.from(CN "[]" SA " " SA2 "[]" CY ", " ST " " ZP "[]" PH)
str Comments.from("This is a " Service_Co " Warranty Call" "[]" "This Job has not yet been scheduled")

; Begin SQL actions
;Database: s:\spt\PactechDatabase\CIS.mdb
;Table: ptdb
;Fields: Ticket_No (Text) Service_Co (Text) Customer_Address (Text) Comments (Text)

;will insert or replace these values
str t=Ticket_No
str c=Service_Co
str a=Customer_Address
str m=Comments

;connect
Database db7
str connString=db7.CsAccess("s:\spt\PactechDatabase\CIS.mdb")
db7.Open(connString)
db7.conn.BeginTrans

;record exists?
str sql
sql.format("SELECT * FROM ptdb WHERE Ticket_No='%s'" t)
if (db7.Query(sql)) = 0 ;;yes
,out db7.Query(sql)
,;update record
,sql.format("UPDATE ptdb SET Service_Co='%s', Customer_Address='%s',Comments='%s' WHERE Ticket_No='%s'" c a m t)
else ;;no
,;insert record
,sql.format("INSERT INTO ptdb VALUES('%s','%s','%s','%s')" t c a m)
db7.Query(sql)
db7.conn.CommitTrans
#7
I am sorry, Query cannot be used with SELECT. Then it always returns -1. I changed the code.

Quote:I have 14 fields in the actual database-this query only populates 4 of them. Do I have to account for the remaining (blank) fields in the INSERT query in some way?)

Yes. Example:

Code:
Copy      Help
;Fields: Ticket_No (Text) Service_Co (Text) Customer_Address (Text) Comments (Text)
;...

,;insert record
,sql.format("INSERT INTO ptdb (Ticket_No, Service_Co, Customer_Address, Comments) VALUES('%s','%s','%s','%s')" t c a m)
,;remaining fields will be empty
,


When posting QM code, please copy it using menu Edit -> Other formats -> Copy BBCode. Then it will be colored, which is easier to read.
#8
Yes, that did it. Thanks truly for your (and everyone's) suggestions - and, btw for your response time, which is by leaps and bounds faster than any forum I have ever sought help in. (and for qm, an amazing tool)

Ryshyn


Forum Jump:


Users browsing this thread: 1 Guest(s)