DX Tutorial #7: Microsoft Access DB in DX

How to access your access data from inside DX

Wednesday, October 18, 2006 by RomanDA | Discussion: DesktopX Tutorials

RomanDA's DekstopX Tutorials:
My goal is to make a set of tutorials for DekstopX.  If you have ideas on what you would like to see, please email me at [email protected]

Database Access - PART 7:
I am working on something that will require access to a local (on the hard drive) database.
I do a LOT of ASP work on web sites, and was hoping that there would be some carry over.
I believe the DX code is about 3-4 lines different than the ASP (web page) code.

This will be a VERY short tutorial, its more like an article but I want this to be around for future people.
I will add more later if I can.

Text object / DB Access:
  Local database is on my D drive under TEMP, i want to access the TABLE in it called "Hooked Up"
Be aware this is a TEST I pulled a db from a web site i have running just to verify this works.

I'm not uploading anything for this, its just some code examples

Creating the Objects:
  We will make a TEXT object and put the DB access code into the On Start.

LOAD DekstopX and go into the CREATE mode:
* if you do not have DekstopX (builder) you will need to download it.
TEST OBJECT:
  Create the BASE object by
  RIGHT-CLICK on the DekstopX Icon in the system tray.

Select "New object"

<-- See this for more info
  The Object Properties Dialog will show up.

Click the "States" Tab

Change to "TEXT"

Type in something for the text

Set the font to whatever you want.

hit APPLY
  Click the "Summary" tab

Set the width to 400-500

it keeps the text from going off the edge of the screen.

Hit Apply
  Click on the "GENERAL" tab.

Click on "NEW" for the script

the window like this will load.
 

The Script:
  The Code for this little bit we are doing is VERY simple.
 
'-- The OnScriptEnter is executed when the object is loaded
Sub Object_OnScriptEnter
  object.Text = ""  ' Clear the text in the object
Set MyConn = CreateObject("ADODB.Connection") ' Create the connection to the DB COM
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\database.mdb" ' Connect to the DB FILE
Set RS = MyConn.Execute("SELECT * FROM HookedUp") ' Read the TABLE "HookedUp"
rs.movefirst ' Move to the first record in the table

While Not RS.EOF ' LOOP until the end of the table is reached
  For Each x In rs.fields    'Loop through the fields in the table
     object.text = object.text & x.name & ": " & x.value & vbnewline  'Make the object's text show the field name & value
Next 'Loop through the fields
object.text = object.text & vbnewline 'add an extra line between each record
  RS.MoveNext 'Move to the next record

Wend 'end the loop
MyConn.Close 'Close the DB connection
End Sub 'Exit Sub

Click the "File" menu item, then "Save and Close editor", then click "OK" in the properties dialog box.

That's it, the db will be read when you save this and the info displayed in the object.

Again, this is just a "Proof of concept" that shows that you can access a Microsoft DB with DX, and actually VERY easily too!!

I assume no comments means no one is finding these useful.  If this is the case, I can stop a lot easier then I can continue.  This one tutorial alone has taken me around 3 hrs to type up and setup.

Enjoy,
RomanDA
AKA: David A. Roman
 http://romanda.wincustomize.com
http://www.romanda.org
[email protected]
19 Reply 27 Referrals
ZubaZ
Reply #1 Wednesday, October 18, 2006 4:00 PM
Wicked cool

Now to set up some queries. . .

Bichur
Reply #2 Wednesday, October 18, 2006 5:13 PM
Scripting makes my little Troll brain hurt!  

But I bookmark your stuff for future reference.   

Thanks.   

SirSmiley
Reply #3 Wednesday, October 18, 2006 10:23 PM
Nicely laid out. Easily can see why it takes so much time.

You comment your code beautifully.

c242
Reply #4 Thursday, October 19, 2006 3:24 AM
As I try to find a more professional approuch to use gadgets, I have something in the works that even writes to an access database, which requires a different method to open the tables. I hope I will find time to finish that thing. Roman, we could work on an enhanced tutorial together, if You wish. Just contact me.

RomanDA
Reply #5 Thursday, October 19, 2006 6:49 AM
Carl,

Just email me, im open for working out better tutorials.

All,

I updated the code above to show ALL the Records in the table, in a more simple way.
Enjoy.

The next widget will be a DB Access widget. It will prompt you for a DB and Table, then show the info in a form, and allow you to edit/update/delete/add. *if i can make all this work.

ZubaZ
Reply #6 Thursday, October 19, 2006 8:03 AM
David/Carl if you can figure out how to use ADO (ActiveX Data Objects) you would be able to connect to any data source, not just Access. Might be worth looking at.

If you are making widgets for small business use, Access might be OK. But mySQL and MS SQL are going to be where the power users are.

Good luck, and thanks.

c242
Reply #7 Thursday, October 19, 2006 8:48 AM
Well, we use Access here for business, but it's only a small company. That's where I was coming from.

ZubaZ
Reply #8 Thursday, October 19, 2006 9:28 AM
I work for a huge company and am greedy about gadgets that work for me.

http://www.w3schools.com/ado/ado_examples.asp

RomanDA
Reply #9 Thursday, October 19, 2006 2:04 PM
I work for a huge company and am greedy about gadgets that work for me.


Well, Hire me to make them...


c242
Reply #10 Friday, October 20, 2006 12:55 AM
And one can upload an access database included in the widget as an example. It's a bit different with sql databases.

Randahl
Reply #11 Monday, October 23, 2006 5:59 PM
Don't stop Roman!

I just got back to snivilisation and was thinking about something along these lines myself for a project coming up! Was wondering if this was possible, but I was thinking about using "server.mappath" for the db! Not sure if it would be the best practice though?

Thanks for the heads up!

Rand

Einstein
Reply #12 Wednesday, October 25, 2006 7:58 PM
Since I use an AccessDB on my website - I have found a need to get info from it and to it often - this script might be able to really open up some possibilities - thanks tons.

Hey, do you know how to convert numbers to a bar graph? I have been wanting to do that for a long time. I just can't see to figure it out. Just an idea if you are needing a topic for a new tutorial. Cheers.

_Martin_
Reply #13 Thursday, October 26, 2006 12:51 PM
RomanDA, regarding your comment about "lack of comments", maybe they all just saw this a couple of years ago: Link.

Sorry, couldn't resist, you know I'm a fan, and the detail in these guides really helps.


ZubaZ
Reply #14 Thursday, October 26, 2006 12:57 PM
LMAO!!

RomanDA
Reply #15 Thursday, October 26, 2006 1:24 PM
Damn Martin.. you gave Zu more ammo to slam me.. now he will say i took this from YOU..

i give up.. next time ill just keep my info to myself.

_Martin_
Reply #16 Friday, October 27, 2006 4:12 AM
hehe, sorry

Seriously, it's really great stuff and Time Commander (Link) looks like a great innovation.

c242
Reply #17 Friday, October 27, 2006 6:05 PM
I think the reason for the lack of comments is, that no weather widget ist involved here.

RomanDA
Reply #18 Friday, October 27, 2006 8:37 PM
u r 1 sick person!

David Brelo
Reply #19 Monday, December 31, 2007 10:10 AM
Thanks, just what I was looking for!

Please login to comment and/or vote for this skin.

Welcome Guest! Please take the time to register with us.
There are many great features available to you once you register, including:

  • Richer content, access to many features that are disabled for guests like commenting on the forums and downloading skins.
  • Access to a great community, with a massive database of many, many areas of interest.
  • Access to contests & subscription offers like exclusive emails.
  • It's simple, and FREE!
Login

Home | About | Privacy | Upload Guidelines | Terms of Service | Help
Copyright © Stardock Corporation. All Rights Reserved.


web-wc01