Vbscript for Microsoft Access

[trevix]

SparOut:
Yes, more or less you need only

Code: Select all

tell Application "FileMaker Pro"
set QuestoID to ID of (create record of layout "List" of document "ASNomeDb" with data ASiDati)
end tell

If FIlemaker is open, it becomes the front window and gets updated on plain view.

mwieder:
sorry but I dont know Access enough to make it multiuser. I explored the menus with no result...Everytime in Access I have to reopen the table to see my new records.
Speaking of my typo, I noticed that Access does not require ";" at the end of the SQL query:
SQLString = "select * from TheTable where ID = NewID"
or
SQLString = "select * from TheTable where ID = NewID";
give the same result.
As a matter of fact it looks like I cannot put 2 query on the same call:
This (to do a new record and get back the auto ID generated by Acess):

Code: Select all

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"
SQLString = "INSERT INTO TheTable (name, team, Thedate, TheTime) VALUES ('newName', 'newTeam', 'newTheDate', 'newTheTime'); SELECT @@IDENTITY;" 
set rs = MyDB.Execute(SQLString)
result = rs(0)
set rs = nothing
MyDB.Close
set MyDB = nothing

does not works, with or without the ";" in any combination.
But this works:

Code: Select all

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"
SQLString = "INSERT INTO TheTable (name, team, Thedate, TheTime) VALUES ('newName', 'newTeam', 'newTheDate', 'newTheTime')"
set rs = MyDB.Execute(SQLString)
SQLString2 = " SELECT @@IDENTITY"
set rs = MyDB.Execute(SQLString2)
result = rs(0)
set SQLString = nothing
set rs = nothing
MyDB.Close
set MyDB = nothing

Is it Access ?
Where can I find a compreensive list of Sql calls that I can send from RunRev? Do I have to make them all up (find, update, AND & OR, working with dates, etc
Or can I do Dns less calls using transcript and I am just wasting my time with VBscript ?
Thanks for any input you can give me
Trevix

[trevix]

Since i did not get any reply, I worked hard on it and i came up with a collection of VBScript and SQL calls that I posted on Rev Online (programming/"AccessFromRunRev).
But I fell short on errror checking. For example, in the following script how can i get an error if the delete does not work ?

Code: Select all

Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"
SQLString = "DELETE from TheTable  where ID = NewtheID"        
' or to delete every record
   'SQLString = "DELETE from TheTable"
set rs = MyDB.Execute(SQLString)
set rs = nothing
MyDB.Close
set MyDB = nothing

Trevix

[mwieder]

Check out vbscript's "on error goto" and "on error resume next" statements for error handling.

on error goto oops
...
oops:
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description

...and you're right - Access doesn't allow two sql statements on the same line as far as I remember.

[trevix]

Yes, I did try but I am having problem on putting an exit before the label (oops:)
"Exit" does not seems to work and, on Runrev, I always get "execution error". What is the VBscript inside RunRev ? A sub ?

For example, trying to delete a non existing record ID, this does not work, no matter if I use exit sub, exit do, exit Property, etc:

Code: Select all

on error goto HandleError
Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"

SQLString = "DELETE from TheTable  where ID = NewtheID"       
 
set rs = MyDB.Execute(SQLString)
if err.number <> 0 then
return= "deleted"
end if
set rs = nothing
MyDB.Close
set MyDB = nothing
exit

HandleError:
return = "Error. Not deleted"
resume next

As I said I get "execution error" and I dont know if it is from RunRev, from VBscript, from SQL....(aaargh!)

[trevix]

Sorry.... But this does not work either:

Code: Select all

on error goto HandleError
Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"

SQLString = "DELETE from TheTable  where ID = NewtheID"       
 
set rs = MyDB.Execute(SQLString)
if err.number <> 0 then
result= "deleted"
end if
set rs = nothing
MyDB.Close
set MyDB = nothing
exit

HandleError:
result = "Error. Not deleted"
resume next

[trevordevore]

VBScript doesn't support 'on error goto' except to turn off error handling ('on error goto 0').

If you have to use On Error Resume Next and then use the Err object to check for errors.

Code: Select all

On Error Resume Next

' Do something that might cause an error.

if Err.Number > 0 then
   'show  Err.Description
end if

[mwieder]

Yep - Trevor's right about this. Got so used to the way VBA does this that I forgot how dumb vbscript is. Ignore my comment and do it the way Trevor suggests.

[trevordevore]

Except pretend I didn't start the second sentence with "If"

[mwieder]

rotfl

[trevix]

Thanks guys for your help...
I always get err.Number = 0, both if the record get deleted or does not exist.

I tried also a

Code: Select all

set rs = MyDB.Execute(SQLString, dbFailOnError) 

with no result

I looked hard for examples on the Web and I found this:

Code: Select all

set rs = MyDB.Execute(recordsAffected)

But I dont know how to claim what "should be 0 on error"....

With this second SQL statment, the err.num become "-2147217908" (!!!?)
I'm completely lost.
(Why the hell do I put myself in these things...)

Is it possible that my last hope is another

Code: Select all

SELECT * from TheTable where ID = NewTheID

It seems so redundant...

trevix

[trevix]

No answers...so here are my findings, calling a vbscript form RunRev:

this work:

Code: Select all

on error resume next
cosa= 5/0
if err > 0 then
result = "error: " & err
else
result = cosa
end if

dividing by 0 catch the error

but this ( if there is no record with such NewtheID) does not work:

Code: Select all

Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=The_Db_Path"
on error resume next
SQLString = "DELETE from TheTable  where ID = NewtheID"        
set rs = MyDB.Execute(SQLString) 

if err > 0 then
result = "error: " & err
else
result = "OK"
end if

set rs = nothing
MyDB.Close
set MyDB = nothing

I tried to put a "set rs = MyDB.Execute(SQLString, DbFailOnError)" but things dont change.
Is it like if the SQL code does not report any error to vbscript ?

Trevix

[mwieder]

What about if err < 0 ? Shouldn't that line read

if err <> 0 then

But in answer to your question, I'm not sure that "Delete from TheTable where ID = someIDThatDoesn'tExist" should throw an SQL error. If NewtheID doesn't match any existing records in the ID field then no records will be deleted. It's not an error as such, and I wouldn't expect the err variable to contain anything other than zero. This is different from a syntax error in the SQL statement or specifying a table that doesn't exist or some other similar things that would cause the SQL processor to complain.

[trevix]

Yeeaaaaa!

you were write: if the table does not exist it gives an error
Thanks so much