AccessMUD developer's log

[Aeristan]

Good evening! My name is Sarjenka and I am a Storyteller, Game Master, and independent designer from Berea, Ohio.

I was introduced to Microsoft Access in the Fall term of 2018. I enrolled in a Database Implementation and Design course, hoping to get ideas on making a database for my Game. By the end, I was astounded that the game was already well underway! (This 1st year class focused on relational database theory and SQL; Visual Basic is far beyond the scope.) I have taught myself quite a bit in the last 6 months that I have been working on this project, and a large part of that knowledge has come from this Forum.

Inspired by the text-based telnet Multi-User Dungeons we played in the early 90s, I am calling my virtual environment AccessMUD. The game operates according to Pathfinder mechanics, but it is set in the Forgotten Realms campaign setting. I am a chaotic good female human Sorceress(3)/Priestess(3) of Selûne the MoonMaiden, and all acts of Love and Pleasure [and Gaming] are Her Rituals!

I'd like to use this Thread to "show off" screenshots of the AccessMUD Project, and to solicit compliments, criticisms, and healthy critiques. I will also use it as a "development log" to mark my progress and provide ongoing documentation. I can link this thread to my D&D friends and also to my software design friends, and possibly even greater opportunities in the future!

... that is, if you don't mind...

There are three primary objects in this database: CHARACTER, ITEM, and LOCATION. These three database tables, and their relationships, comprise the entire game world. Characters exist within Locations, and Items may either be carried by Characters, or unattended within Locations. Note that the records in these tables represent actual objects in the game world, not classes of objects. (In the future I may implement "templates" ‒ separate tables with objects not placed in the game world, which can quickly be copied.)

 

[NauticalGent]

Why not? Count me in...!

 

[Aeristan]

Character

CHARACTER

ID, ' AutoNumber Primary Key
ObjType, ' "C" for Character
Name,
PC, ' true if this is a Player Character
Gender, ' foreign key to GENDER table
Race, ' foreign key to RACE table
Size, ' foreign key to SIZE table
Alignment, ' foreign key to ALIGNMENT table
Deity,
currentHP, maxHP, ' Numerical stats for the Pathfinder game mechanics
AC, AC_flat, AC_touch, AC_flat_touch,
Strength, Dexterity, Constitution, Intelligence, Wisdom, Charisma,
BAB, FortSave, ReflexSave, WillSave, Initiative,
CharacterLevel, XP, NeededForNextLevel, Move,
EncounterGroup, CR, ' NPC stats
insideOfType, ' "L" (currently, Characters may only be contained within Locations)
insideOf, ' Foreign Key to LOCATION table
ShortDesc, LongDesc, InfoText, Color )​

​

Please allow me to introduce myself:

 

[mike60smart]

Hi

I would make the following changes to field names as follows:-

ID, ' AutoNumber Primary Key - CharacterID
ObjType, ' "C" for Character - ObjectTypeID
Name, - CharacterName - (Name is a reserved word in Access)
PC, ' True if this is a Player Character
Gender, ' Foreign Key to GENDER table - GenderID
Race, ' Foreign Key to RACE table - RaceID
Size, ' Foreign Key to SIZE table - SizeID
Alignment, ' Foreign Key to ALIGNMENT table - AlignmentID
Deity,
currentHP, maxHP, ' Numerical stats for the Pathfinder game mechanics
AC, AC_flat, AC_touch, AC_flat_touch,
Strength, Dexterity, Constitution, Intelligence, Wisdom, Charisma,
BAB, FortSave, ReflexSave, WillSave, Initiative,
CharacterLevel, XP, NeededForNextLevel, Move,
EncounterGroup, CR, ' NPC stats
insideOfType, ' "L" (currently, Characters may only be contained within Locations)
insideOf, ' Foreign Key to LOCATION table -InsideOFID
ShortDesc, LongDesc, InfoText, Color )

 

[Aeristan]

OK. I can do that.

How do I embed an image directly into the post? Does it have to be http instead of https?

 

[mike60smart]

When you post a reply just use the Attachment Icon to manage attachments

 

[Aeristan]

...yes. My character sheet.

 

[Aeristan]

I apologize for being AFK for so long. I was away getting LARPed on with a foam sword. I find that it is good for my metabolism. I'll be getting back to this thread and to coding in general in a few hours.

 

[gemma-the-husky]

The form display you posted looks pretty well laid out. Does it work well? Would users be able to edit all the values, or is this something else?

 

[Aeristan]

It's working quite well so far! All of the white fields you see can be edited, and the grey ones are derived from other values in some way. Thank you for the compliment! I love OCD-ing the UI almost as much as I love coding.

 

[Aeristan]

Characters may possess one class, many classes, or zero classes (which means that they are a "monster"). There are three association Tables to handle this information.

CLASS_DEFINITIONS enumerates the class options and stores information relating to attributes of that class overall.

CLASS_DEFINITIONS
( ClassName, 'a unique string in all caps
HitDice, 'the die to use to "roll" hit points at every level
SkillPoints, ' this class gains this many skill points plus the Int modifier
BABprogression, FortSave, ReflexSave, WillSave, ClassSkills, SpecialAbilities )' other information pertaining to Pathfinder

CLASS_PROGRESSION relates each class to the abilities that vary based on the level the character has in that class. At the moment, this is just a descriptive string to be displayed.

CLASS_PROGRESSION
( ClassName, 'a unique string in all caps; foreign key to CLASS_DEFINITIONS
ClassLevel, 'the level at which these abilities are gained
SpecialAbilities ) ' other information pertaining to Pathfinder

Finally, CHARACTER_CLASS is the table that associates each Character with the class(es) that they possess and the number of levels they have in each class.

CHARACTER_CLASS
( CharacterID, 'foreign key to CHARACTER
ClassName, 'foreign key to CLASS_DEFINITIONS and CLASS_PROGRESSION
ClassLevel, 'foreign key to CLASS_PROGRESSION
Favored, ' extra hit points or skill points are gained from one's favored class
HitPoints, SkillPoints, BAB, FortSave, ReflexSave, WillSave ) ' other information pertaining to Pathfinder

This is the subform that I use to edit this information. It isn't very "pretty"; it's the first one I made when beginning this Project. There isn't any code behind it at all; it's just a linked subform with bound fields. At some point, I will change it so that it looks just like the Inventory subform.

 

[Aeristan]

AccessMUD developer's log -- Items

The second primary data structure in AccessMUD is the Item. This represents any inanimate object that can be examined, used, carried, equipped, or anything else.

ITEM
(ID, ' AutoNumber Primary Key
ObjType, ' "I" for Item
Name,
Type, ' foreign key to the ITEM_TYPES table
Alignment, ' foreign key to ALIGNMENT table
Equippable, ' foreign key to EQUIP_SLOTS table
Equipped, ' zero or one of the valid options in Equippable
Weight, Cost, currentHP, maxHP, Hardness, Owner, ' other information pertaining to the Pathfinder game mechanics
isMagic, isSoulbound, isTwoHanded, canBeCarried, ' Yes/No flags for certain item properties. I'll be thinking of many more as I go along.
insideOfType, ' "C" or "L" (may be contained within Locations or Characters)
insideOf, ' Foreign Key to CHARACTER or the LOCATION table
ShortDesc, LongDesc, InfoText, Notes ) ' other descriptive text


The valid values for Type are: ARMOR, CLOTHING, COINS, JUNK, POTION, SCROLL, SHIELD, TREASURE, WAND, or WEAPON. The valid values for Equipped are: BODY (meant for clothing), CLOAK, FEET, HANDS, HEAD, LEGS, MAIN_HAND, NECK, OFF_HAND, RING (left), RING (right), SHEATHED, TORSO (meant for chest armor), WAIST, or WRISTS.

The Form I have for this is but a framework. Eventually, it will exactly match the Character Form, except a different color with differing fields.

Equippable is a look-up field bound to a ComboBox which accepts multiple values. I will soon be starting an entire separate thread in which you may try and convict me for this "database normalization heresy" but for now, I'm just going to move forward.

 

[Mark_]

Still need any help with this? Looks to be straight 3.5 rules from the postings.

 

[Aeristan]

It's Pathfinder, sometimes referred to as 3.p The community split apart when fifth edition was released. However, we live in the High Dale from the Forgotten Realms campaign setting. It's a copyright that WotC defends vigorously... but I'm not going to change my home plane just to suit their legal department, you know?

Help? I'm open to that but it's kind-of a foreign concept to me. I have always coded as a passion, not a profession. (I was an only child and a "latch-key kid" in the 80's, so I spent a lot of time alone with my Apple ][+) This is something that I must become comfortable with, now that coding is my major in college. I'm not sure how to "share" a Project like this with someone else.

 

[Aeristan]

AccessMUD developer's log -- Inventory

Now we arrive at the complicated part. This is where the majority of my VBA code has gone into, and where the majority of my tension headache has come from. This, my Friends, is the Inventory subform.

Naturally, characters may carry as many items as their Strength will allow. These items may be Equipped, if they are designed to be. This is how Characters will be prepared to be in the game. I needed a way to manipulate that data from within the Character form, while ensuring that fields that should not be changed, cannot be changed.

Characters and Locations do not contain any association to the Items within them. It is the Items which contain information about which Character or Location contains them. Because changes made to the main Item table cannot be undone, it was clear that I needed to create a temporary table to manage that Character's inventory, so the user could be prompted to confirm any changes.

The temp_INVENTORY table is never deleted, but the data inside is cleared and repopulated in the main form's Current event using an INSERT INTO SELECT statement. This table contains all of the fields present in the ITEM table, whether that data is displayed or not. Because I am still changing the structure of the ITEM table, the list of fields in the SQL statement in constructed in VBA by opening a Recordset with one record from ITEM, iterating through and concatenating the names of all the fields. However, then the "Equippable" field must be removed from this list, as any "multi-value" field will throw a wrench into the SQL. Finally, the temp_INVENTORY table add four additional boolean fields: toAdd, toDrop, toEquip, and toTrade, all of which are set to false initially.

To add an Item to the Character's Inventory, the Item is selected by ID or by Name from one of the ComboBoxes in the form footer. These ComboBoxes are populated with all of the Items in the game except for those which are already in the Character's Inventory or which have canBeCarried set to false. When chosen, the fields in the footer are populated by selecting that record directly from the ITEM table and storing the values in the controls, so that the user can see the Item's values. That Recordset is closed at the end of the event. Equipped is set to NULL, because Items that are added are not equipped automatically. Then, the Add button is enabled.

When the Add button is clicked, the game uses an INSERT INTO SELECT statement to copy that record from the Item table and append it to temp_INVENTORY. (Again, the multi-valued field must be removed from this statement.) The following fields are changed: insideOfType is set to "C" for Character, insideOf is set to this Character's ID, toAdd is set to true, and the ShortDesc field is changed to note the Character or Location that this Item will be transferred from. The Update button is enabled, the Add button is disabled, and the controls in the form footer are cleared. The color-coding of the subform details has been accomplished through conditional formatting.

Items in the subforms may be manipulated through combinations of mouse-clicks and keyboard modifier keys, as the Control Tip text above shows. The Item form can be opened to the selected Item by double-clicking, and for those that desire a mouse-only interface, a "secondary button" click will in the future open a small shortcut menu.

If the Item is to be Dropped, an UPDATE SQL statement is used to change that record [in the temporary table]. InsideOfType and insideOf are changed to this Character's values -- the Item will be unattended at the Character's current Location. Equipped is set to NULL because unattended Items cannot be equipped. toDrop is set to true and the other three flags set to false.

If the Item is to be Traded (moved) to another Character, an UPDATE statement is used to change insideOf to the ID of the destination Character. Trading to another Character directly will require a control to select that Character's ID, but I have not implemented this yet.

Because Dropped and Traded items in temp_INVENTORY have already had their insideOf and insideOfType values changed to their destination ID, the Character ID field on the main form is not linked to those fields in the subform. If it were, those items would not be displayed, even though they would be present in the table itself.

Since the Equipped ComboBox is a bound field, no SQL statement is needed to update that value. The list of values for that ComboBox must match those allowed in the Equippable multi-value field, so when the Inventory subform's Current event fires, the RowSource property of that detail's ComboBox is set to SELECT Equippable.Value FROM ITEM WHERE ID = [the Item's ID].

When the Update button is clicked, the User is prompted to make certain that they want to update the Item table with these changes. If so, an UPDATE SQL statement is used to INNER JOIN on the ID fields of ITEM and temp_INVENTORY. Only the insideOf and insideOfType fields need to be updated, along with Equipped. The Inventory subform is re-queried to reflect the changes made to this Character's Inventory, and both the Add and Update buttons are disabled.

It is important to perfect this interface as much as I can, because the Location form will need to have two identical ones on it: one for Characters present, and another for Items left unattended there.

What do all of you think so far?

 

[Mark_]

If you haven't check it out, HeroForge has a great character creation spreadsheet for 3.5 and pathfinder. This can give you a solid idea of just how extensive what you are trying for is.

One thing I would not do is have a hard coded "Location" in the character table. Rather I'd have the location's be set and use a junction table to link characters to locations with a time component. This can be "Character 1" at "Location 1" arrived "992 6:00am". This way you can track who was where, when to see if you have overlaps that can help with story arcs.

 

[Aeristan]

Well the Locations aren't hard-coded. LOCATION is the third primary relation in this database, and I will be getting to that.

However, your suggestion that a table linking Characters, Locations, and game times is intriguing. Not only would that serve as an excellent log of events, but the time could be set to the future to give the illusion of AI (the Characters proceed towards certain Location at pre-arranged times, possibly involving recursion). I like it!

This can give you a solid idea of just how extensive what you are trying for is.

I know, but I can't help it. It's my OCD.

 

[Aeristan]

AccessMUD developer's log -- Locations, Directions, and the Map

The third primary data structure in AccessMUD is the Location. This represents anyplace that a Character or Item can exist.

LOCATION
(ID, ' AutoNumber Primary Key
ObjType, ' "L" for Location
Name,
Zone, ' foreign key to the ZONES table
Alignment, ' foreign key to ALIGNMENT table
isOutdoors, isDark, isDifficult, isUnstable, ' Yes/No flags for certain Location properties. I'll be thinking of many more as I go along.
insideOfType, insideOf, ' Not Implemented. Locations cannot be contained inside other objects.
ShortDesc, LongDesc, InfoText ) ' other descriptive text

I wanted it to be easy to change the list of defined directions, so I created this look-up table. You can see that I have included twelve directions.

DIRECTIONS
(long, ' The "long form" of this direction [ north ]
ABBR, ' The abbreviation for this direction [ N ]
opposite, ' The direction that is the "opposite" [ south ]
OrderBy ) ' In this case, starting at north and clockwise around the compass

The MAP relation, then, is a pairing of one Location with one Direction, which arrives at a second Location. In the future, these "exits" could include locks, traps, wards, etc.

MAP
(LocationID, ' foreign key to the LOCATION table. "Where you are"
Direction, ' foreign key to the DIRECTIONS table. "Which way you want to go"
GoToRoom, ' another foreign key to the LOCATION table. "Where you end up"
ShortDesc ) ' descriptive text

The Form I have for this is but a framework. Eventually, it will exactly match the Character Form, except a different color with differing fields.

For the Map subform above, I wanted all of the directions to be displayed in the continuous forms, not just those that already have entries in the MAP table. This makes it easier to visualize the map in your mind. I accomplish this by using a LEFT JOIN with the DIRECTIONS table. If the "Reciprocal Directions?" box is checked, then the destination room will create an entry in the MAP table that leads back to this room in the opposite Direction. (Note that in the subform pictured above, the output is incorrect.)

When finished, the Location and Item forms will have TabControls similar to the one seen on the Character form. The Location form will have two subforms identical to the Character's Inventory subform: one for Characters in that Location, and a second for Items left unattended there.

 

[Aeristan]

AccessMUD developer's log -- relational diagram

The only detail that this cannot show is that the insideOf property of ITEM may reference either the CHARACTER or the LOCATION table, based on the value of insideOfType. (Is that bad database design?)

 

[Aeristan]

AccessMUD developer's log -- Encounters by CR

As I'm sure you are well aware, one of the Game Master's tasks is to make sure that the difficulty of the Encounters are appropriate for the PC's experience level, and that they are well balanced for their Challenge Rating. This can be time consuming, tedious, and rather subjective. Fortunately, AccessMUD automates this task in my first Report: Encounters by CR.

First, the database selects only the Player's Characters. It calculates the Average Party Level and adds a modifier based on the number of Characters in the Party.

The remaining Characters are grouped according to the numeric EncounterGroup field, then sorted in descending order by the CR. A simple look-up table was created so that CRs could be displayed as ASCII fractions, mainly because I like the way it looks. Each Character's ID and Name are displayed, along with a few pertinent stats and and the amount of XP that defeating that NPC awards. Finally, the available XP for that Encounter is totaled, the Encounter Challenge Rating is computed, and the difficulty of that Encounter relative to the PCs abilities is expressed as a color-coded text box. Once I have the Item table more fully developed, each record will also list that Character's equipped gear.

After the Item and Location data are more complete, I plan to implement a second and similar report: Treasure by Zone. It will list the Items available as treasure for every Location, grouped by the Zone number, along with their stats and base value in gold. This will be somewhat complicated, as the Report will need to include not only Items that are Unattended in those Locations, but also the Items carried in the Inventory of all the non-PC Characters in that zone. The purpose will be to compare the treasure available to the Average Party Level, to make sure that the Party's resources are appropriate.