Page 3 of 3 <123
Topic Options
#84918 - 2002-02-07 06:51 AM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
anyone figure out how to script the build of the tables??

Like to have that automated to make it easier to archive the old ones and then the next logged on user can create the tables (and DB) before populating it.

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

#84919 - 2002-02-07 03:05 PM Re: Create/Read/Update MS Access Database
Breaker Offline

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
You can use the SQL statement "CREATE TABLE", and specify fields and data types within this, eg "CREATE TABLE SOFTWARE (SOFTWARE_ID INTEGER NOT NULL, SOFTWARE CHAR(40) NOT NULL, PUBLISHER CHAR(40), LICENCES INTEGER);".

This would create a table called Software, with four fields - Software_ID, Software, Publisher, Licences. The type of the data allowed is specified as well - integer, text(CHAR) where the entry length is also specified, and the NOT NULL expression shows that certain fields must be populated.

For more info on SQL functions look here, or try the Access docs if you'll be using Access.

I would have thought, given the example above, that the most efficient way to perform any DB admin would be independent of the data-gathering process. I suppose it depends on the size and complexity of your network...


That might not be exactly what you meant. Whoops! Are you thinking more of the archival process and the rebuilding of new tables, identical to the archived data?

If so, building the tables, as shown above is not such a problem. The trickier part would be identifying your criteria for archival, and actually performing this process. I think (no SQL guru, me!) that you can rename a table, so maybe it would be a case of deciding when there was enough data, either by:

 If $Recordset.RecordCount > 1000 ;too many records!
ArchiveTable($CurrentTable) ;possible set of DB admin UDF's coming on!

Or parsing the Date field and deciding to archive when the data covers three months (or whatever!)

HTH as well.

[ 07 February 2002: Message edited by: Breaker ]


#84920 - 2002-02-07 09:24 PM Re: Create/Read/Update MS Access Database
Radimus Moderator Offline

Registered: 2000-01-06
Posts: 5187
Loc: Tampa, FL
You had it right the first time... periodically, net admin would collect the MDB and archive it for historical purposes.

It would be convienient if the script would note that the MDB was absent and make a new mdb and recreate the table structure.

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

#84921 - 2002-06-25 11:50 AM Re: Create/Read/Update MS Access Database
JSchroeder Offline
Fresh Scripter

Registered: 2002-06-24
Posts: 11
Ups ... your script is exiting at the point "if $Connection"

Any Ideas ?


#84922 - 2002-06-25 01:59 PM Re: Create/Read/Update MS Access Database
Shawn Administrator Offline

Registered: 1999-08-13
Posts: 8611
Exiting, as in bombing-out (ie script error) or exiting as in evaluating FALSE and circumventing the IF logic ? What version of Kixtart ? There was a change made with 4.10 that may impact this logic (default properties).
#84923 - 2002-06-25 05:30 PM Re: Create/Read/Update MS Access Database
Ghost Offline
Starting to like KiXtart

Registered: 2000-09-14
Posts: 171
I have a question:

Here's what I want to do...

The Sports teacher would like to poll the students with a number of questions regarding the out of school activities they are in to.

Once the user has entered the details, which will be performed during login, it should not as them ever again.


How do I search the database to see if the user has already filled in the poll?

I've got the rest sorted, however, this part is baffling me.

Who are you? Who slips into my robot body and whispers to my ghost?

#84924 - 2002-06-25 08:46 PM Re: Create/Read/Update MS Access Database
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
It can depend on how your database is set up. I assume you require an answer for each question, so you can check to see if there is a value for the questions you asked, or you can create a new field that is set whenever information is written to the database for a certain user. Generically, the easiest way to check to see if someone has taken a poll is to check if their record exists (assuming you are generating a record when they take the poll.)


#84925 - 2002-06-25 08:50 PM Re: Create/Read/Update MS Access Database
jtokach Offline
Seasoned Scripter

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

If you read through the above scripts by Rad, you can see where he checks if the record exists and either creates a new or updates and existing record. Rather than the update code, your logic can exit.

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

#84926 - 2003-03-31 11:23 PM Re: Create/Read/Update MS Access Database
Jhawk Offline
Fresh Scripter

Registered: 2002-06-11
Posts: 12
Loc: KS
A couple of quick things.. first off, big thanks to Breaker! I ran into this script earlier today because someone asked for a script like this and he linked to this thread.

I put this script in place w/ a few minor variations, and I thought I would add one that a lot of people might have a need for.

Here goes.. I don't post much, so don't hate me if something goes wrong [Smile]

The majority of our PC's are Dell, and dell likes to use "Service Tags"... these tags weren't being displayed in the serial number field, so I added the following statements.


$ServiceTag = WMIQuery("serialNumber","Win32_SystemEnclosure")
$AssetTag = WMIQuery("SmBiosAssetTag","Win32_SystemEnclosure"

Writes to the DB:

$Recordset.Fields("ServiceTag").Value = $ServiceTag
$Recordset.Fields("AssetTag").Value = $AssetTag

At any rate, like I mentioned thanks a ton for the hard work it took to get this together Breaker!

#84927 - 2003-04-02 01:15 PM Re: Create/Read/Update MS Access Database
masken Offline
MM club member

Registered: 2000-11-27
Posts: 1222
Loc: Gothenburg, Sweden
Good thing I found this thread!

I'm just like jtokach, who started this thread. I often don't do things at all before I full understand how they work.

Shawn, the description you did regarding objects kicked it. Short (relatively [Wink] ), nothing missing for the understanding of the concepts, informative, and very educationalistic, thanks!  -
The tart is out there

#84928 - 2003-04-02 04:28 PM Re: Create/Read/Update MS Access Database
Sealeopard Offline
KiX Master

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
It is recommended to use the DB...() UDFs as a database interface. See e.g. DBCommand() - Executes a SQL statement and returns a recordset if applicable
There are two types of vessels, submarines and targets.

Page 3 of 3 <123

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

Who's Online
0 registered and 170 anonymous users online.
Newest Members
msnhungnmaes, noihiweferh, ANHNHA, Junior, QuinnVanOrder
17609 Registered Users

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

Search the board with:
superb Board Search
or try with google: