Vbscript for Microsoft Access

[trevix]

Hello everybody.
I'm trying to connect RunRev (version 2.9) to a simple local database made in M Access, everything on Window XP.
I know the argoment is rather complex for my knowledge, but I am not ready to give it up...specially after having seen how easy is to connect (on OSX with Applescript) to FileMaker.
My objective is to make the user (of my future application) easily able to search, modify and add records, without too messy ODBC setups or driver search.
I can easily start Access running this Vbscript:

Code: Select all

set AccApp = CreateObject("access.Application")
AccApp.Visible = True

But my code to connect, does not work:

Code: Select all

Set MyConn = CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\3vix\Desktop\db2.mdb"
MyConn.Execute "INSERT INTO Indirizzi (nome, cognome) VALUES ('roberto', 'trevisan'))" 
MyConn.Close

The name of the DB is "db2"; the name of the table is "indirizzi"

Any idea of why it does not work ?
(I'm running window under OSX with WMware fusion and hope this is not a problem...)
Thanks
Trevix

[Bernard]

I have never used vbscript from Rev, but here is a posting on how to use Excel via VBScript. The territory is close to what you are trying to do.

[Bernard]

oops, forgot to paste the URL:

http://www.sonsothunder.com/devres/revo ... iac001.htm

[mwieder]

Trevix- you've got a typo in your SQL statement. Try replacing the final parenthesis with a semicolon and I think that'll set things right.

Change

MyConn.Execute "INSERT INTO Indirizzi (nome, cognome) VALUES ('roberto', 'trevisan'))"

to

MyConn.Execute "INSERT INTO Indirizzi (nome, cognome) VALUES ('roberto', 'trevisan');"

[trevix]

I looked before to the use of Vbscript with Excel and it works flawless.
For what i understand to connect to access means setting a ODBC connection trough Vbscript (taking the burden from the user...)
Thanks mwieder, I will try your correction.

I wonder why, with so many request of connecting to commercial database s like FileMaker and Access, nobody yet published a compreensive guide on both Window and OSx on how to connect to them.
For my part I had to figure out the all Applescript stuff (with Filemaker on OSX) but of course I feel like I reinvented the wheel...

Trevix

[mwieder]

You can certainly work with odbc databases from within rev without having to resort to vbscript. Check out the documentation for the revopendatase command, for example. Trevore DeVore has also made a quite nice database library available that takes a lot of the pain out of managing the connections. But if you do want to go the vbscript route, I got your example to work by making that single char change, so you're on the right track.

[trevix]

Yeah, I knew that, but the idea was to give the User an application that can set-up by itself the ODBC connection, driver,etc without him messing out with the contropanel ODBC setup of Window XP.
Can I do that from RunRev ?

I understand that I cannot use VBscript with Access (or Filemaker) on Window the same way of Applescript with Filemaker on Osx.
Is this true ?
For example I know for sure that VBScript can only start Filemaker script, but cannot write or modify records.
As for Access, I dont know.
I tried anyway your change and i do not get an execution error anymore on the result. But still it does not modify the record...

Trevix

[trevix]

Ops...
it works but only if the db is closed or if I close and reopen the application (Access).
Am I missing something ? (I dont know very well Access)

Trevix

[Bernard]

if i understand you correctly, you're connecting to Access via ODBC. You change the record but this only takes effect when you close and re-open the connection to Access? Right?

If this is the case then I'm assuming that you need to 'commit' your data. If you don't know what 'commit' means in terms of SQL, then you need to find a basic tutorial on SQL/transactions. I'm guessing that Access is automatically committing your work when you close the connection. But that is really not an ideal way to be working with relational databases (although I think many might contest that Access is not truly relational, but since I've never used Access I have no opinion on the matter.

[SparkOut]

Can you explain where it is that the data is not being "refreshed" - ie, is it in your rev application that you can not see any of your edits until the Access database is closed and reopened? Or is it more that your users regular view in Access is not being updated to show the new record that you inserted via ODBC until they close the Access application and reopen it?
If the first (which doesn't look at all likely from the code you have shown) then it would be something to do with a "commit" problem as described. However, I believe that the <connection>.execute syntax is correct to commit the record changes made by the sql command, which also leads me to believe that this is not the issue.
I wonder if it is because the recordsets viewed by the users in the Access application were simply created before the new record was inserted via ODBC, and the cached recordset they're using just does not update automatically. I don't know how the Access DB has been designed but if the users in the Access DB are able to go to another form and then return to the form in question which refreshes their recordset view (ie force their form to make a new "SELECT ... " query), then that will probably be the reason.
I don't think that would even be a problem to do with Access, if you're using recordsets which don't automatically refresh then you have to design around that in whatever database you're using.

[trevix]

The second you said:
"...your users regular view in Access is not being updated to show the new record that you inserted via ODBC until they close the Access application and reopen it..."
It update only if, on Access, I close and reopen the table.
As you said, the recordset is not updated.
But the idea is that I would like to have Access refreshed after a ODBC call, in case someone is using Access on the same time (but inside the application).
I tried "MyConn.update" but it returns error. Are there other ways ?

One more thing (excuse me for not being willing to go trough tons of pages of ODBC manuals):
why this code:

Code: Select all

MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\3vix\Desktop\db2.mdb"

does not require me to go to the ODBC panel of Window and have to set a DNS ? I know it works but will it work on any Win PC ? Is there something similar for FileMaker ?(without having to load the ODBC driver,etc)
Thanks
Trevix

[SparkOut]

I tried "MyConn.update" but it returns error. Are there other ways ?

I doubt it, MyConn.execute is the correct way to execute the SQL and commit the database changes in a direct connection, as far as I know.
The way I see this thing is, that you're doing nothing wrong and you won't be able to solve it by doing anything from Rev. You've already created the record and the Access table is updated. It is down to the Access client to refresh the recordset in order to get a view of the new record. I suspect the Access database has not been produced with any kind of concurrency in mind.
If (say) there's a noticeboard somewhere and I look at it and note down the things of interest to me and go home to study them, then when you come and add another notice on the board, it will not be visible to me until I go back and take another look at the board myself. It's not realistic for you to pin a new notice up and then chase after me with a copy of the notice and follow me home, knock on my door and hand it over (and then chase after anyone else who may have looked at the noticeboard). If I (as the Access DB client) have a particular need to make sure that I have up to date knowledge, then I'll have to make more regular trips to view the noticeboard. I think that's the problem you're facing. You could maybe make a macro to refresh the recordset by forcing a new query every time some event happens in the Access client or on demand by the user clicking a button. I don't think you can force the Access client to have visibility of the new item by inserting it into its recordset from an external source.

One more thing (excuse me for not being willing to go trough tons of pages of ODBC manuals):
why this code:

Code: Select all

MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\3vix\Desktop\db2.mdb"

does not require me to go to the ODBC panel of Window and have to set a DNS ? I know it works but will it work on any Win PC ? Is there something similar for FileMaker ?(without having to load the ODBC driver,etc)
Thanks
Trevix

Any Windows PC that has MDAC components installed should work like that - although MDAC is now replaced by Windows DAC in Vista. According to Microsoft, "Windows DAC includes some changes to work with Windows Vista, but is almost entirely functionally equivalent to MDAC 2.8." (That last sentence lifted from Wikipedia). In other words, there may be a complication to overcome with Vista installations, but probably you should be OK.
I have no knowledge of a DSNless connection for Filemaker, but that's not to say there isn't someone who does. In fact, Timothy Huertas posted this (C# code for DSNless connection)

Code: Select all

String connectionString =
"DRIVER=DataDirect 32-BIT SequeLink 5.4;" +
"UID=userid;" +//your user id
"PWD=password;" +//your password
"PRT=2399;" +
"HST=10.127.55.22;" + //your ip here
"SDSN=dbname"; //your db name

on http://newsgroups.derkeiler.com/Archive ... 00553.html (back in 2006 but apparently the DataDirect drivers are the same ones with recent versions too). Being DSNless won't mean that you don't have to install the DataDirect drivers for the FileMaker ODBC connection though.

[trevix]

Thanks.
In my simple mind, everything works as with Applescript (on OSX) and Filemaker: two lines of code and your new record is done and updated visually on the screen...

Trevix

[SparkOut]

With FileMaker you are presumably working with found sets which will change and update the whole recordset on various operations, moving from one record to another, for example, may cause it to interrogate the set again. You could probably mimic the same sort of operation in Access, but I should think it might need to be designed in a different way. I don't know how major any changes could end up being. At the important points of navigation around the system, you would get Access to refresh the queries which populate its recordsets. Of course there's a tradeoff in speed and data movement with that approach if you're dealing with large sets.
With FileMaker are you actually inserting a record and having the screen update and the new record visible without touching anything at all?

[mwieder]

trevix-

I've been out of town for a week, so I've missed the conversation here.

MSAccess by default opens databases in single-user read-only mode. You can't make changes to a database while Access has control. You can change this, of course, but you have to do it at the Access end of things, either through a commandline option or by changing the database characteristics or (probably) by running a startup macro. Any of those things are beyond the scope of this thread.

Without MSAccess being in the picture, I can open databases and insert data without problems from runrev using your example (with the typo fixed). If you're going to try this in multiuser mode with Access getting to the database as well, I think you're going to have to manage the record locking yourself, both in Access and in your VBScript.