Page 1 of 3 123>
Topic Options
#84878 - 2002-01-16 06:29 PM Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter
*****

Registered: 2001-11-15
Posts: 513
Loc: PA, USA
Not be repetative, but I require info on how to create, read, and update an MS Access DB using the new features of Kix 2001 ONLY. I think it would be helpful for all to have an updated source for this information.
(I have searched through the board, and have found that there is indeed a lot of information on this subject. However, a great deal of this info is sort of outdated or requires a ver of kix other than 2001. There are also several methods that accomplish this, making finding the right one difficult...)

I also have a question regarding record/file locking. Using this newer method, can several people update the DB simultaneously? Are records locked or is the entire DB locked while updating?

_________________________
-Jim

...the sort of general malaise that only the genius possess and the insane lament.

Top
#84879 - 2002-01-16 08:01 PM Re: Create/Read/Update MS Access Database
Alex.H Offline
Seasoned Scripter

Registered: 2001-04-10
Posts: 406
Loc: France
You should get a look here :
Connect to any database with ADO

To connect to an Access db, use this informations (ODBC is used) :
$AdoCon.Provider = "MSDASQL"
$AdoCon.Properties("DSN")="ODBC dsn"
$AdoCon.Properties("UID")="userName"
$AdoCon.Properties("PWD")="password"
$AdoCon.Open
The odbc dsn must be defined through the ODBC icon in the windows control panel (in administratives tools for w2k and XP)

I'm not sure this one will work.

For the record locking, all user can update the DB simultaneously, only if they're :
adding a new entry
changing a single entry that won't be changed by another user

In fact, only the record(s) that had to be changed will be locked for write. all other will be available, and the locked one will only be here for read-only purposes

_________________________
? getobject(Kixtart.org.Signature)

Top
#84880 - 2002-01-16 08:08 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Hey jtokach (what`s your first name ?)

Guess we can start at the beginning, how to dynamically define and create a new MS Access table:

1) Go into MS Access and create a new database, call it DB1.MDB

2) Run the following script that will create a table called JTOKACH in DB1.MDB that contains like a small address book schema. Don`t forget to change $DATABASE to point this script to your new database. Hope this is the kind of information you were looking for.


Break On


$=SetOption("wrapateol","on")


$DATABASE = "C:\ACCESS\DB1.MDB" ; <--- change this


$DSN = "DATA SOURCE=$DATABASE; PROVIDER=MICROSOFT.JET.OLEDB.4.0;"


$SQL = "CREATE TABLE JTOKACH (NAME CHAR(50) PRIMARY KEY, ADDRESS CHAR(50), PHONE CHAR(20))"


$Connection = CreateObject("ADODB.Connection")


If $Connection
$Connection.ConnectionString = $DSN
$Connection.Open()
$=$Connection.Execute("$SQL")
If @ERROR
?"@SERROR"
Else
?"Table successfully created."
Endif
$Connection.Close()
$Connection = 0
Else
?"ADODB is not installed on this machine"
Endif


Exit 1

-Shawn

[ 16 January 2002: Message edited by: Shawn ]

Top
#84881 - 2002-01-19 12:32 AM Re: Create/Read/Update MS Access Database
Anonymous
Unregistered


Shawn

is there any quick way to define the Access workgroup to use.

TIA

Top
#84882 - 2002-01-18 05:55 PM Re: Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter
*****

Registered: 2001-11-15
Posts: 513
Loc: PA, USA
First off, let me thank you for offering your help; I greatly appreciate it. I have tried to send this message to you several times but I don't know if you got it.

My programming abilities are limited to kix and batch files. I am well advanced in both, however I trip when confronted with anything OLEish. With that in mind, I can most certainly lookup on the msdn site all of this information that I require, however, I don't know how to tie the two together. I would very much like to make this a BBS project as you offered! I believe that it would be a great service to myself and others to be able to follow the natural progression of learning this advanced topic from a beginners point of view. I would be happy to do homework and figure some things out on my own.

With all of that in perspective, here it what I would like to accomplish. General Network Engineer/Admins like myself are not well pronounced in programming languages. So we need to make up for this deficiency by using a simpler tool to create the same end effect. Enter Kix. I would like to create a script that runs during the logon that creates, adds or updates records in a database. The primary key will be the hostname of the box. The DB will be flat as relational DBs confuse the hell out of me. We can modify this later I suppose with little impact. The fields will include info such as C: free space, last logon time, current DAT file, etc. We have about 20-30 fields we would like to capture and already have the code to capture them. The requirements are this:

Must be a database. CSV and XLS are far to limited in their abilities.
Must be able to be written to by several users at once. Record locking only, not file locking.
Records should be updated, not overwritten?
The updating process should be as quick as possible.

Given all of that, I have tried your script and it worked flawlessly. However, I have no idea what the code is doing. Can you explain line by line what's happening so that I can learn from this and write my own stuff?

Also, where can I find info on the parameters of the SQL command you used so that I can create more elaborate fields; like how to create a combo box instead of a text field or change the data type from CHAR to YES/NO or NUMBER?


Again, THANK YOU!

_________________________
-Jim

...the sort of general malaise that only the genius possess and the insane lament.

Top
#84883 - 2002-01-18 06:53 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
There are really four things (diciplines) to this whole kix oledb thingy:

1) How to use kixtart to talk to COM objects (access,excel,ie,adsi,etc).

2) Understanding the OLEDB object model. Which isn't necessary tied to just Microsoft Access, you can talk SQL to Oracle, SQL7, Foxpro and a host of others. It's a generic object model for interfacing with any DB that provides a driver.

3) Understanding SQL. We use the OLEDB object to establish connections and retrieve recordsets, but formulating SQL queries is a huge subject in itself (not to mention any Access specific extentions to the standards).

4) Understanding how to Automate Microsoft Access (the application). It's easy to confuse the two (automating queries vs automating the app that services the queries). Automating the app (building forms and databases (not tables), changing settings and customizing the GUI is something I would suggest is out of scope and usually not required. One would normally just manually start the app and customize the gui that way.

So having said all that, where would you like to start ? (Whew)

-Shawn

Top
#84884 - 2002-01-18 07:01 PM Re: Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter
*****

Registered: 2001-11-15
Posts: 513
Loc: PA, USA
Shawn,
Step 1 - Understanding COM objects and their usage.

I assume this should build a foundataion from which to build upon varying object models. I really don't know how build the code or can't really understand the pre-written code to interpret its usage...

-Jim

_________________________
-Jim

...the sort of general malaise that only the genius possess and the insane lament.

Top
#84885 - 2002-01-18 09:18 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Ok, that makes sense. Good place to start.

Sometimes it helps to have an abstract mental picture of how things work. I'll tell you my mental picture in terms of how KiXtart COM works. I think of COM objects as sort of like smart DOS programs. For example, lets say you wanted to add a new user account to your local workstation. There are two ways to do it in kixtart:

1) Shell-out to the NET command
2) Use the ADSI COM object

So looking at the first method, we would code:

shell "%comspec% /c net user john.doe /add"

and what happens here ? net.exe is loaded into memory and run. net.exe then queries the command line for the parameters passed to it then simply creates the account. Then it exits, the process is unloaded from memory and your kixtart script carrys on. Pretty basic stuff eh ? If you wanted to create another user, you would have to shell out again and create another running instance of net.exe and so it goes, again and again. And the only way to communicate with these programs (specify what we want done) is with commandline parameters on the inbound side. And to extract information on the outbound side, use temporary textfiles and read them back into our script. You know the whole story

With COM, things are a little different. Instead of shelling-out and running the program every time you need it, you create an instance of the running program that is always "running" in the background, waiting at all times to recieve (service) our requests. And sending information in and out of the "program" is handled much, much more eloquently. Oh yeah, and instead of calling them programs, we call them objects

So with COM, instead of using the SHELL command, we use a kixtart function called CreateObject("progid"). For example, if I wanted to create a running instance of Internet Explorer, I would code this:

$IE = CreateObject("InternetExplorer.Application")

This one function does a whole lot. Here's the blow-by-blow play (might want to start regedit for this):

1) COM first looks in the registry for a key in HKEY_CLASSES_ROOT called "InternetExplorer.Application" (try it yourself manually to see if you can find it). If you crack open that key, there's a key called a CLSID which is basically an unique identifer for this application. Every COM object has one and they are all differnet from one another. They have to be or else COM couldn't locate your object. The string "InternetExplorer.Application" is just like a shortcut, or a friendly name for the object.

2) If you select this key into your clipboard, then go back up to the top and select HKEY_CLASSES_ROOT again, then do a find on this identifier, it should take you to a very strange place in the registry where all the identifiers are kept. Crack it open and look for LocalSever32 - whats the value there ? Look familiar ?

3) So then COM starts Internet Explorer but it puts it into a mode where it is continually running, in the background. If you look at taskmgr, you will see iexplore.exe running. So like using the SHELL command, CreateObject() just starts the program. But then here's where the fun starts.

So now iexplore.exe is running in the background, along side your kix script and it's just waiting for requests (from your script). How does one talk to the object ? Well if you remember, the CreateObject() function returns a variable, here it is again:

$ie = CreateObject("InternetExplorer.Application")

What is $ie ? $ie is a pointer to your running instance of Internet Explorer that you just created. It's like a channel or a pipe to the process (proper term is an address to an interface). Sometimes for whatever reason, the object your trying to create doesn't get created. We can test for this by querying the value of the pointer like this:

$ie = CreateObject("InternetExplorer.Application")
If $ie = 0
?"Some error occurred"
Endif

CreateObject() will either return the address of the object, or return a 0 (zero) if it fails. Try creating an object that doesn't exist and see what i mean. So this object pointer or interface pointer, in simple terms, consists of the following two things:

1) Properties
2) Methods


Properties are "attributes" of the currently running object. The syntax is like this:

Setting a property:

$objectname.propertyname = 1

Querying a property:

$kixvariable = $objectname.propertyname ; query a property

So we can set these attributes and/or we can query them. For example, IE has a property called "Visible" that tells wether the browser window is actually visible to the user or not (by default, when you create an IE object, it is *not* visible). I tend to think of properties as simply variables maintained by the object, inside the object itself. And in order for us to get at them, we have to specifically ask the object to set or get them for us. So, to make IE visible we must "set the Visible property", in this case to TRUE (1)

$ie = CreateObject("InternetExplorer.Application")
$ie.visible = 1

So not only does IE set it's internal variable "Visible" to 1 (on our behalf), it actually went ahead and made itself visible. So now that its visible, we can query the value of this property (read it back in) to see if it really did set it.

If $ie.Visible = 1
?"Yup, it is..."
Endif

or, since the value 1 is "true", we could just as easily code it this way:

If $ie.Visible
?"Yup, it is..."
Endif

Methods calls are simply function calls that you can invoke within the running instance of IE. The syntax of invoking a method call is like this:

$objectname.methodname(parm1,parm2,...)

So for example, continuing our IE script:

$ie = CreateObject("InternetExplorer.Application")

$ie.navigate("http://www.kixtart.org")

so we have just told IE to navigate to the best little KiXtart board on the Internet. There just like any other function call, but they are implemented inside the object.

And since the IE object is always running in the background, we need a way to stop it. We do that in kixtart like this:

$ie.quit() ; another method call - gracefully closes the browser
$ie = 0

Just set the variable to 0 (zero). What that does is to stop the running instance of iexplore.exe and unload it from memory.

So here's our little example script in it's full form:

break on
$ie = CreateObject("InternetExplorer.Application")
$ie.visible=1
$ie.navigate("http://www.kixtart.org")
gets$ ; just pause for now
$ie.quit
$ie = 0


My fingers hurt

-Shawn

[ 18 January 2002: Message edited by: Shawn ]

Top
#84886 - 2002-01-18 10:30 PM Re: Create/Read/Update MS Access Database
MadDawgMike Offline
Fresh Scripter

Registered: 2002-01-18
Posts: 9
Loc: Durham, NC
Shawn,
You are AWESOME !! Please, please, please keep it up!!! I have learned more about this subject here (or at least Understood more...) than ever.

Thanks again!
Mike

Top
#84887 - 2002-01-18 11:29 PM Re: Create/Read/Update MS Access Database
Alex.H Offline
Seasoned Scripter

Registered: 2001-04-10
Posts: 406
Loc: France
And so God ... hem... Shawn speaks
Shawn, put yours hands in cold water, after in hot one, and restart typing
_________________________
? getobject(Kixtart.org.Signature)

Top
#84888 - 2002-01-19 12:09 AM Re: Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter
*****

Registered: 2001-11-15
Posts: 513
Loc: PA, USA
You should charge! That was the most informative 10 minutes of my tech career! And it was so simple...

I can conclude that I can find the various properties and methods for any given application from a website, say MSDN. I have access to Visual Studio where I have seen these but I don't remember seeing an explanation of what they do. I think I can scrounge up a copy of the MSDN Library if that will help. Do you have any useful links?

Again that was phenomenal. Lesson 2 here we come! Have a nice weekend all!

_________________________
-Jim

...the sort of general malaise that only the genius possess and the insane lament.

Top
#84889 - 2002-01-19 01:01 AM Re: Create/Read/Update MS Access Database
Bryce Offline
KiX Supporter
*****

Registered: 2000-02-29
Posts: 3167
Loc: Houston TX
For ADSI I use the ADSI SDK that you can get here 3rd option down.

Bryce

Top
#84890 - 2002-01-19 03:38 AM Re: Create/Read/Update MS Access Database
New Mexico Mark Offline
Hey THIS is FUN
****

Registered: 2002-01-03
Posts: 223
Loc: Columbia, SC
Good job, Shawn.

If I were to recommend any one book on scripting, it would be "Windows Scripting Secrets" by Weltner.

His approach is both practical and fun. While he primarily uses VBScript, with KiXtart's new features, most scripts translate pretty easily. Almost all his scripts are oriented toward either exploring resources or accomplishing real administrative tasks.

One of the cooler things he explores is how to dump properties from objects. The nice thing about this is that any documentation on a particular object always seems to be a day late and a dollar short. By digging into the objects themselves with a few simple scripts, you can find out what features (say) IE 6.x has added. The core properties and methods will stay pretty much the same, but you will often find some very cool undocumented features -- and in this case I don't mean bugs.

New Mexico Mark

Top
#84891 - 2002-01-22 07:08 PM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Evening, all.

I've been lurking on the edges of this board for a few months now - so some rather belated thanks are due to Shawn, Bryce, CJ and others. Cheers!

My first post has been occasioned by trying to get my head around COM/OLE with Kix2001 - I can see its caused others some stress as well , so I figured you were the guys to turn to.

I'm basically trying to write logon info to an Access 2000 database. There are two separate tables, one for user-related info, and one for machine-related. Based on Shawn's code earlier in this thread, I'm simply executing two SQL statements like this:

code:
 $SQL1 = "INSERT INTO TBL_USERS (USERNAME,FULLNAME,WORKSTATION,PRIVILEGELEVEL,HOMEDRIVE,LASTUPDATE) VALUES ('@USERID','@FULLNAME','@WKSTA','@PRIV','@HOMESHR','@DATE @TIME');"
$SQL2 = "INSERT INTO TBL_COMPUTERS (WORKSTATION,DOMAIN,IPADDRESS,MACADDRESS,OS,LOGGEDONUSER,LASTUPDATE) VALUES ('@WKSTA','@DOMAIN','@IPADDRESS0','@ADDRESS','@PRODUCTTYPE','@USERID','@DATE @TIME');"

Now the first statement works fine. However the second produces the following error:

Error: COM exception error "Execute" (Microsoft JET Database Engine - Syntax error in INSERT INTO statement.) [1/1]

I've been banging my head against this for a couple of hours now, so any advice would be appreciated.

Thanks in advance,

_________________________
================================================
Breaker


Top
#84892 - 2002-01-22 07:48 PM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Hmmm....

Well, I've resolved the issue, though I have no idea why it should have been a problem.

After testing each field individually, it turned out that DOMAIN was causing the problem. Renaming that particular field to NTDOMAIN seems to have done the job.

Maybe some kind of Access OLE naming constraint...? Glad I'm not a proper programmer...!!

G'night, y'all.

_________________________
================================================
Breaker


Top
#84893 - 2002-01-22 08:03 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Breaker,

This is waht I do when I get into a KiXtart
SQL bind:

Within Microsoft Access (2000):

1) Bring up the Dialog box that has the
menus for Tables, Queries, Forms, etc.. Know what I mean ?

2) Select the Queries "button"

3) Select "New" from the menu

4) Select "Design View"

5) Select your troublesome table
(TBL_COMPUTERS?) and "Add" and "Close"

6) In the main MS Access menubar,
select "View" and "SQL View"

7) This is an emmediate mode SQL query
builder. Paste your select statement from
the script into this window, then make
some adjustments like pluggin some dummy data for values list, etc ... then close the
window, give your query a name then execute
it.

This is a real handy way to formulate and
tryout (debug) queries before using them in
your script ... give it a go ... apoligies
if you've already tired this !

-Shawn

Top
#84894 - 2002-01-23 03:46 AM Re: Create/Read/Update MS Access Database
cj Offline
MM club member
*****

Registered: 2000-04-06
Posts: 1102
Loc: Brisbane, Australia
Breaker, I found the same problem with a column called DATE. I spent probably as long as you did until I put a "j" in front of all the column names in desperation. jDATE worked.

I suspect that you are right in that there are reserved words that you can't use, like Domain and Date etc.

cj

p.s. Welcome to the board!

Top
#84895 - 2002-01-23 02:57 PM Re: Create/Read/Update MS Access Database
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
CJ,

Yep, that's right. You can go here for a full(ish!) list of the reserved words:

SQL reserved word list

Thank you for the response.

Now I just have to figure out how to tie in the rest of this stuff my boss wants.... I suspect I'll be back again soon!

_________________________
================================================
Breaker


Top
#84896 - 2002-01-23 03:12 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline
Moderator
*****

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
Thanks Shawn...
I have been writing these little scripts for a while now, and still had no idea what it was doing or how it worked (I apparently still believe in magic, or cut and paste )

Keep 'em coming.

Perhaps this type of thread could be incorporated into the manual... RTFM only works if there is something in the FM to R

_________________________
How to ask questions the smart way <-----------> Before you ask

Top
#84897 - 2002-01-23 06:46 PM Re: Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter
*****

Registered: 2001-11-15
Posts: 513
Loc: PA, USA
Shawn,

Seems to be a popular subject! Anyway, moving along... I think I'm ready to start scripting. How do I go about adding new records and updating old records? Is the SQL the same for both? How does the SQL know to update an existing record rather than write a new one? Your DB1.MDB should make for a fine example.

Thanks!

-Jim

_________________________
-Jim

...the sort of general malaise that only the genius possess and the insane lament.

Top
Page 1 of 3 123>


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
0 registered and 507 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.054 seconds in which 0.021 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org