I need to open an access DB, delete a table, open a second DB and copy a table from the second DB into the first DB.

Did that make any sense??

Try this:

code:

---

break on

$TargetDatabase = "m:\msaccess\db1.mdb"
$TargetTable    = "table1"
$SourceDatabase = "m:\msaccess\db2.mdb"
$SourceTable    = "table1"

$access = olecreateobject ( "access.application" )

$rs =olecallproc ( $access, "OpenCurrentDatabase", "s",
                                "$TargetDatabase")

$docmd = val("&"+olegetproperty ( $access, "docmd" ))

$rs = olecallproc ( $docmd, "DeleteObject", "ss",
                                "0",
                                "$TargetTable" )

$rs = olecallproc ( $docmd, "TransferDatabase", "ssssss",
                                "0",
                                "Microsoft Access",
                                "$SourceDatabase",
                                "0",
                                "$SourceTable",
                                "$TargetTable" )

$rs = olecallproc ( $access, "CloseCurrentDatabase") 

$rs = olecallproc ( $access, "Quit" )

$rs = olereleaseobject ( $docmd )

$rs = olereleaseobject ( $access )

exit

---

Shawn.

BTW, would you know off hand if this would work if Access wasn't installed on the client, or perhaps just MS data Access Components were.

I'll test against this anyway, I was just looking for a shortcut...

To be honest - I'm not too sure what gets shipped with the MS Access Runtimes. I've got the full-blown version on my machine.

The best thing to check for on the client workstation would be at:

HKEY_CLASSES_ROOT\Access.Application

If this key is there - then the Automation server should work !

If not - the olecreateobject() function will fail for sure !

Shawn.

I'm looking at the Code and I was wondering what these "ss" and "ssssss" parameters were for:

code:

---

$rs = olecallproc ( $docmd, "DeleteObject", "ss","0","$TargetTable" )
$rs = olecallproc ( $docmd, "TransferDatabase", "ssssss","0","Microsoft Access","$SourceDatabase","0","$SourceTable","$TargetTable" )

---

The manual describes it this way:

---8<---

TypeList

- TypeList is a case sensitive series of characters that define the type of each optional argument. Based on the type specified, KiXtart will convert the argument(s) to the correct type before calling the OLE function. This parameter can have the following values:

b Boolean
c Currency
D Date
i Short integer
I Long integer
o Object handle
r 4 byte real
R 8 byte real

---8<---

What the manual doesn't say is the code for a string is 's'.

So, an OLE function that takes three parameters (two strings and a boolean) would be coded like this:

$rs = olecallfunc ( $handle, "function", "ssb","str1","str2","1")

I like to use only strings when possible. Most well-written Automation servers will convert variants for you anyway, so that any number (byte,word,long) can be specified as a string.

The other thing is that KiX does a run-time check of the number of "types" in your list and the actual number of parameters that you provided - this is a good thing. But just remember that KiX has no way of knowing how many parameters the Automation server is actually expecting.

How's the testing going ?

Shawn.

[This message has been edited by Shawn (edited 17 July 2000).]

and it is FAST, have I mentioned that you are the MAN!!

I haven't tested using just the runtimes, since HQ has upgraded Office std to Office Pro, so I'll just deploy access97 to the clients needing this functionality.

I'm gonna add some backup procedures to it, like copying the DB before I update the table (for CYA)

BTW, is there a function that calls record count of the table? like check the number of records in the 2 tables, and if table1 is fewer than table2 then do the copy.

Errrrrmmm .... Ummmmmm .....

Here you go ...

code:

---

break on

; copy source table to target table if required ( recordcount )

$sourcedatabase = "m:\msaccess\db1.mdb"

$sourcetable    = "table1"

$targetdatabase = "m:\msaccess\db2.mdb"

$targettable = "table1"

; get my objects

$access = olecreateobject ( "access.application" )

$docmd = val( "&" + olegetproperty ( $access, "docmd" ) )

$dbengine = val( "&" + olegetproperty ( $access, "dbengine" ) )

; get record count of source table

$database = val( "&" + olecallfunc ( $dbengine, "OpenDatabase", "s", "$sourcedatabase" ) )

$recordset = val ( "&" + olecallfunc ( $database, "openrecordset", "s", 
                                "$sourcetable" ))

$sourcecount = val ( olegetproperty ( $recordset, "recordcount" ) )

?"Table $sourcetable in $sourcedatabase contains $sourcecount records"

$= olereleaseobject ( $recordset )

$= olereleaseobject ( $database )

; get record count of target table

$database = val( "&" + olecallfunc ( $dbengine, "opendatabase", "s", 
                                "$targetdatabase" ) )

$recordset = val ( "&" + olecallfunc ( $database, "openrecordset", "s", 
                                "$targettable" ))

$targetcount = val ( olegetproperty ( $recordset, "recordcount" ) )

?"Table $targettable in $targetdatabase contains $targetcount records"

$= olereleaseobject ( $recordset )

$= olereleaseobject ( $database )

; check record counts and copy as required

if $sourcecount > $targetcount

 ?"Copying tables ... "

 $= olecallproc ( $access, "opencurrentdatabase", "s",
                                "$targetdatabase")

 $= olecallproc ( $docmd, "deleteobject", "ss",
                                "0",
                                "$targettable" )

 $= olecallproc ( $docmd, "transferdatabase", "ssssss",
                                "0",
                                "microsoft access",
                                "$sourcedatabase",
                                "0",
                                "$sourcetable",
                                "$targettable" )

 $= olecallproc ( $access, "closecurrentdatabase") 

else

 ?"No need to copy"

endif

; release my objects ( very important )

$= olereleaseobject ( $dbengine )

$= olereleaseobject ( $docmd )

$= olecallproc ( $access, "Quit" )

$= olereleaseobject ( $access )

exit

---

Here's the deal - I would have prefered not to open up a new recordset to get the record count. I don't know if this will effect your performance or not.

There is another method of getting the record count ( by querying the properties of the tabledef for table1 ), but unfortunately, since KiX OLE doesn't support olegetproperty parameters, one can't enumerate the tabledefs collection (Ruud - are you listening ?)

Shawn.

p.s.

I need to get me a KiX coding standard real bad - anyone want to offer up any suggestions or better yet - write a standard. I guess the trick will be for everyone to agree on it !

Shawn..

[This message has been edited by Shawn (edited 20 July 2000).]

This goes to show that there are reasons to have programmers in MIS staff... instead of helpdesk monkeys. (unless they are oracle programmers, ick)

1 programmer save HOURS of work... of course you typically have to pay a programmer what a room full of helpdesk monkeys make combined

Thanks a lot, you didn't have to do all this, but I'm grateful for it. I'll compare times for blind copying vs 'at need' copying

You and Bryce ought to make "Kix Konsultants" and go into business...

Quid pro quo, my friend - Quid Pro Quo

No problem at all.

I do it mostly for the love of KiX ( and Automation) and because you're such a great contributor to this board, but mostly you caught me at a good time.

I've actually got a usefull piece of KiX OLE script running right now that is helping us debug a pesky IE4 problem !

Our users are getting (relatively) frequent Dr. Watson's (IE4 Traps) when hitting certain Internet High Encryption (SSL) sites. We're running IE4 SP1 in WinNT 4 SP3 ( no, we can't upgrade easily).

I wrote this little KiX/OLE beaut that

1) Starts an instance of IE4

2) Goes to the pesky website

3) Hit's the refresh button over and over

This is enough to repro the problem ! I start the script, then go have a coffee or three, then come back and check for traps.

We think it has something to do with our firewall. We're running Checkpoint Firewall 1 Version 4.0. From what I understand, there are "issues" with IE4 and Checkpoint.

Can anyone shed some light or has had similar "issues" ?

Jochen:

You seem to be a proxy/firewall guru - got any insight here ?

Shawn.

I am using kix and few other programs to load our standard software packages on new system builds. Currently I log all infomation I need to a txt file.. I was wonderin what the benifit (if any) would be to log this info to an access file so that I don't have to go back and import it later. It is possible for me to have 10 systems working at the same time.

Does anyone know if I shoud take the time to make my logs go directly to an access file?

thanks

I've heard that this has been done successfully in the past ! Can't remember where though, and it may or may not have been using KiX.

I (myself) can't enumerate the benefits of implementing real-time logon telemetry using Microsoft Access.

The one problem ( that may not be a problem for you ) is that you would need the MS Access runtimes on all your client boxes.

With this in place, one can (easily?) call the DBEngine directly from the logon script and format, datafill and insert a recordset into a database located on a server.

Hmmmmmm...

Wanna try it out ?

Shawn.

I want to import data from text file with dynamic number of columns and column labels. If Access Table does not have enough columns and labels to import all of the data from text file, then add columns and labels and import data from text file.

Here is the code to generate the text file:

code:

---

break on
$softcount = READPROFILESTRING('c:\windows\desktop\test.ini', 'Unresolved', 'softcount')
IF Open( 4 , 'c:\windows\desktop\test.txt', 5 ) = 0
$count = 1
Do
$x = WriteLine( 4 , 'app' + $count + ",")
$count = $count + 1				
Until $count > $softcount
$x = WriteLine( 4 , Chr(13) + Chr(10))
close ( 4 )
IF Open( 4 , 'c:\windows\desktop\test.txt', 5 ) = 0
$count = 1
Do
$key = 'Exe' + $count
$ReadString = READPROFILESTRING('c:\windows\desktop\test.ini', 'Unresolved', '$key')
$x = WriteLine( 4 , $ReadString + ",")
$count = $count + 1				
Until $count > $softcount
$x = WriteLine( 4 , Chr(13) + Chr(10))
close ( 4 )
exit

---

[This message has been edited by 3MGrant5 (edited 17 August 2000).]

Whether one can do it from KiX ( with it's arcane OLE syntax ) is not so clear.

There is only one way to find out though.

Anybody interested in pursuing this ?

Shawn.

I will repost this question when I can free up some time.

thanks

quote:

---

Jochen:

You seem to be a proxy/firewall guru - got any insight here ?

Shawn.

---

Not at all , my friend , not at all.
I'm just a poor, lonesome cowboy, and a far way from home

However, browsed instant microsoft support
(Technet) for yer :

The only Issue i found for ie 4.01 was when
it is used to ODBC-query a database with ASP
on an IIS, or when running large ASP files
while SSL security is enabled...(Q155116)
but this is causing an invalid page fault in
kernel32.dll on w9x ...there are several possible workarounds, the best i found is :
-make sure that no dll's are located on Desktop ! there are several other articles linked on this one , maybe another one is valuable for You!

-> Never worked with checkpoint firewalls,
-> Sp3 is ok [would rather use it than sp4]
-> but , one moment ... there is another article : Q164073 ! 'ASP Queries when SSL enabled Gives incomplete results or Errors':

and there is only a workaround for ie 3.01,
sorry!
ok however the workaround looks like this:
Go to registry -> find ASP\Parameters ->
change BufferingOn value to 1 !
think this fits only IIS ?!?!? Yep , i couldn't find the key on my machine....
maybe the Sites are using IIS ???? no , i don't hink so ...

So maybe one of the following solves the prob.:

-> upgrade to sp 6a, or
-> upgrade to ie 5, or
-> check for Checkpoint support, or
-> disable browsing for sites with IIS!

Jochen

_________________________

quote:

---

Jochen:

You seem to be a proxy/firewall guru - got any insight here ?

Shawn.

---

Not at all , my friend , not at all.
I'm just a poor, lonesome cowboy, and a far way from home

However, browsed instant microsoft support
(Technet) for yer :

The only Issue i found for ie 4.01 was when
it is used to ODBC-query a database with ASP
on an IIS, or when running large ASP files
while SSL security is enabled...(Q155116)
but this is causing an invalid page fault in
kernel32.dll on w9x ...there are several possible workarounds, the best i found is :
-make sure that no dll's are located on Desktop ! there are several other articles linked on this one , maybe another one is valuable for You!

-> Never worked with checkpoint firewalls,
-> Sp3 is ok [would rather use it than sp4]
-> but , one moment ... there is another article : Q164073 ! 'ASP Queries when SSL enabled Gives incomplete results or Errors':

and there is only a workaround for ie 3.01,
sorry!
ok however the workaround looks like this:
Go to registry -> find ASP\Parameters ->
change BufferingOn value to 1 !
think this fits only IIS ?!?!? Yep , i couldn't find the key on my machine....
maybe the Sites are using IIS ???? no , i don't hink so ...

So maybe one of the following solves the prob.:

-> upgrade to sp 6a, or
-> upgrade to ie 5, or
-> check for Checkpoint support, or
-> disable browsing for sites with IIS!

Jochen

_________________________

ThanX pal - I can always count on you for a thoughtful and well researched reply.

I checked out the links you gave but I (think) they don't apply to my situation.

The site in question is using ODBC and ASP and IIS so I've forwarded your reply to the WebAdmin of the offending site - I'll let you know of any developments.

Please accept these URL's as thanks. I printed-off a hardcopy last night and read it on the train ride home. The document is very good and quite funny in places. Perfect for a Perl newbie like me !

Perl Tutorial:

http://www.netcat.co.uk/rob/perl/win32perltut.html

ActivePerl:

http://www.activestate.com/Products/ActivePerl/index.html

Thanks again !

Shawn.

the tutorial was new to me ,
and my perl is active ! installed it 2 weeks ago on Friday (guess when : yep , on a train ride home !)

Jochen

_________________________