As a Profesional Lurker of this site, i've tried to make a script to read a specific record in an Access database, change it, and then write it back.

In my quest for information I did some other topics I really liked.

I've seen all OLE examples (I liked the Merlin one ;-) )

I've also read the Perl Tutorial, which I can recommend to everyone.
http://www.netcat.co.uk/rob/perl/win32perltut.html

But those didn't help me solve my problem.

I saw the script where you can read all records in a database with using ODBC, but i couldn't change it to my needs.

Can anyone give me some pointers?
I would be really grateful.

TIA

Roger The Young (roughly translated)

Glad to have you "on-board" !

Ok - well there's a couple of ways we can approach this. Did you have a specific project (task) in mind - if so - can you decribe exactly what you want to build.

Need info like database schema and SQL queries and stuff like that ...

Or - we could work together on a simple database like an addressbook or emailbook - and we could build a simple demonstation script together - take it from start to finish sort-of-thing!

You're call !

Shawn (the old).

I'm honored that the Great Shawn is taking time for a humble junior member like me ;->
I'll describe the case as clear as I can.

The database is an Access97 database, there is one table named 'APP' with the following
record specifications

AppName Text 50 (unique)
LastUse Date/Time
Commandline Text 255
LocalSize Number Long Integer
Snapname Text 50
VINVersion Number Long Integer

What i'd like to do is:

Read a record by unique identifier (Appname), examine the variable 'Commandline' and
'Snapname' for a substring, change it, and write it back to the table.

The variable looks like '\\SERVERNR01\Application$' and I would like to change
the servername.

There are no SQL statements defined in the database.

The catch is, (aha, there's always a catch ;-) the workstations (all NT4.0) don't have
Access97 installed, but I don't know if this is important.

I hope I'm being clear enough for you. I'm anxiously waiting to see your magic.

Greetings,

Rogier de Jong
Roger the Young (roughly translated)

Just out of curiousity - are you planning on using this as part of a logon script - or as this some kind of user admin tool ?

In terms of "the catch" the workstations that don't have MSAccess97 installed - check their registry for the following keys...

HKEY_CLASSES_ROOT\DAO.DBEngine.35
HKEY_CLASSES_ROOT\DAO.DBEngine.36

Let me know if you find one of them -

As well, MSOffice97 can lay these down if told to do so during the initial install.

Shawn.

[This message has been edited by Shawn (edited 30 November 2000).]

This database is filled by an installation application. It remembers the server from wich it has installed an application. This is not a problem until we physically pick up the workstation and use it at another site in another town with it's own distribution server, because if something needs to be re-installed it connects to the old distribution server, thus the application is being installed over a WAN-connection. (some files are over 100Mb :-()

So yes, it's gonna be part of the login-script. I'm gonna give everyone the closest distribution-server depending on IP-address.

To answer your other question:

I found the key: HKCR\DAO.DBEngine.35

Greetings,

Rogier de Jong
Roger the Young (roughly translated)

[don't you hate it when your job get's in the way of important Kix business ?]

Got any of these ?

HKCR\ADODB.Connection
HKCR\ADODB.Recordset

Shawn.

p.s. we're talking MDAC now !

[This message has been edited by Shawn (edited 30 November 2000).]

I'm sorry, but I'm at home right now, so I can't check those reg-keys. I'll get back to you in about 12 hours ok?.

Talk to you later.

greetings,

Roger the Young

PS: if it helps, the workstations are all WNT4.0 sp3 or sp4, and they all have office95 (ancient isn't it?) installed.

Quickly though - if you want to read and write the data - you're going to need ADODB - if you just want to write - you can make do with DAO - the reason for this i'll explain tomorrow but basically, DAO is NOT KIX FRIENDLY !

Have a good one !

Shawn.

Our standard workstation don't have ADODB installed. But let's say for the sake of argument that they do.

I have a workstation that does have it installed, and i'm more curious to how it is done. If it works good, i can always advise the company to install ADODB (MS option pack?)

It is more fustrating for me that I know all the commands in kix except the OLE commands, so I'm really anxious to see your solution.

greetings,

Roger the Young

There's nothing better than starting the day with a strong cup of coffee and a little bit of kix scripting - eh ?

[it's amazing, coffee and kix scripting just seem to go together like - bacon and eggs - Sonny and Cher - VBS and Virus's]

Try running this script against your database - change the $source to your specifics...

Just want to make sure we're on the "same page" in terms of simply reading the database first...

code:

---

break on

; Change these to your specifics ...

$source=   "data source=\\dn1cov\shawn\msaccess\app.mdb; provider=microsoft.jet.oledb.4.0;"
$table=    "app"
$sql=      "select appname, commandline, snapname from app;"
$delim=    ";"
$cnnobj=   "adodb.connection"
$rstobj=   "adodb.recordset"

$cnn = olecreateobject ( "$cnnobj" )

if @error
 ?"Error creating object $cnbobj."
 ?"@serror"
 quit(0)
endif

$= olecallfunc ( $cnn, "open", "s", "$source" )
 
if @error
 ?"Error connecting to datasource."
 ?"@serror"
 quit(0)
endif

$rst = olecreateobject ( "$rstobj" )

if @error
 ?"Error creating object $rstobj."
 ?"@serror"
 quit(0)
endif

$xcnn= dectohex ($cnn)

$= olecallfunc ( $rst, "open", "so", "$sql", "$xcnn" )

if @error
 ?"Error opening recordset."
 ?"@serror"
 quit(0)
endif

$rec = olecallfunc ( $rst, "GetString", "sss", "2", "1","$delim" )
   
while @error = 0
    
 ; parse out the fields ...
    
 $rec=$rec+"$delim"

 $pos= instr("$rec","$delim")
 $appname= substr("$rec",1,$pos-1)
 $rec= substr($rec,$pos+1,len($rec))

 $pos= instr("$rec","$delim")
 $commandline= substr("$rec",1,$pos-1)
 $rec= substr($rec,$pos+1,len($rec))

 $pos= instr("$rec","$delim")
 $snapname= substr("$rec",1,$pos-1)
 $rec= substr($rec,$pos+1,len($rec))

 ?
 ?"AppName:     $appname"
 ?"CommandLine: $commandline"
 ?"Snapname:    $snapname"

 $rec = olecallfunc ( $rst, "GetString", "sss", "2", "1", "$delim" )
   
loop

:finish

; object cleanup ...

if $rst
 $rs = olereleaseobject ( $rst )
endif

if $cnn
 $rs = olereleaseobject ( $cnn )
endif

exit

---

Shawn.

[This message has been edited by Shawn (edited 01 December 2000).]

I'm sorry to tell you it didn't work. I made the neccesery changes but I get an error when he is opening the database:

$= olecallfunc ( $cnn, "open", "s", "$source" )

ERROR NUMBER: -2147352567
(isn't this an end-of-file?)

Could you please explain the variable $source ?
And why don't I have to make an ODBC data source?

Is anyone else trying this?? If so, does it work for them?

Shawn, I really appreciate your work here, and I really like to know how it works, but it's not very essential that this script is going to work soon. I would much rather learn some guidelines to OLE than just have this script running, so if you could explain some of the commands that you use, and why you use them, that would be really cool...

Greetings,

Roger the Young

The connect string is just a shortcut for MSAccess - you shouldn't have to define an ODBC Data Source but if you've already got one - whats it called ?

The assumption in the above script was that you already had a database called app.mdb located somewhere on your workstation or on a share.

If you just want to learn more about OLE - we can work with other objects like IE or MSAgent. These objects don't have as many external dependencies.

Or are you specifically interested in OLEDB ?

I just figured it would pretty tough to explain how KIX OLE works, using objects that don't (work - that is).

Shawn.

I think it probably better to switch to IE then. I think I can figure out OLEDB once I have an idea about the concept OLE. I've surfed the net for information about OLE, and I think I understand the basic idea.

for instance: I understand that OLE uses object from other programs. An example is the spelling checker (= Object) in word is used in Outlook so that they don't have to write it all over again?

What I don't understand is: how do you know what objects are available, and how do you access them?

In my simple mind I kind of see them as calling sub-scripts (to stay in Kix terms) with some parameters. Or is this completely wrong?

I'm humbly awaiting your wise lessons.

Greetings,

Roger the Young

Word to Ruud v. Velsen: is it an idea to include a little OLE explanation in the next user manual, or is OLE too complex for that?

In your OLE travels, did you happen to come accross this thread ...

OLE 101 - Roll your own COM Object

To MY simple mind - it's actually easier to understand this whole OLE business if you can see how things work from both "ends".

If you haven't already - read the thread and try to get the COM object installed - then well dig right in - if you can't get it installed - well use Internet Explorer !

Shawn.

Sorry I didn't reply sooner but the travel home takes me a while sometimes.

The time we have together is too short
(insert sad music and a train slowly leaving the station)

It's 22:14 over here now. I promise you I will do my homework in the weekend and I've got some nice ideas for that MEGA-script.

(Jeezz, this Kix-stuff is addictive)

I'll get back to you on Monday

Greetz,

Roger the Young

Yeah - KIX is addictive isn't it ?

But for me - the real addiction are the fantastic members of this BBS... quite the knowledgable and friendly bunch - and always good for a laugh or two !

Shawn.

p.s.

When you get into work on Monday - why don't start a fresh thread.

Shawn..