Results 1 to 10 of 10
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    String table or variable table

    This is a best practice question or a don't ever do question. I was wondering if it is normal to use a table to store strings or variables needed in the VBA code. I was thinking of using a table to store things like file locations for the shared network files. Is this a normal practice or is there a certain place in the code to put it. I am trying to not have to locate multiple locations of instances in the code if it needs to be changed. Thank you. --Walker

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Store data in the tables,(not variables)
    store the network paths in a table too.

    the code is in forms and modules.

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    What would you store with the network paths and file locations? Like a nickname to look it up by or something? Do you store global variables in one location? I have been learning a lot about passing variables and using more variables to accomplish what i am wanting to do in multiple places. I am new at this part so any proper practice advice will be much appreciated. Thank you. --Walker

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm? Not sure I'm following your question--perhaps you could give an example of a few strings you are considering storing.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I agree with ranman. You could use a global constant for network paths (I have) but when it inevitably changes, you have to update the app. If it's in a table, users can maintain the path via a form. I would use a nickname to lookup, but use a combo or something so users can't change it. If your code is looking for "FilePath" and the user types "File path", your code breaks.

    You having to do it is better for job security though.

    I do put all my global constants/variables in the same place as a rule, for ease of finding them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    And along the same lines as the good recommendations above I'd suggest possibly creating a table perhaps called something like tblConfig that has several fields in it (one field for each "variable") but the table would have ONLY ONE record. And by the way another place to sometimes keep a "variable" that you may want to use any old time anywhere is to have it as an invisible control (Visible property set to No) on a main menu that stays open the whole time.

  7. #7
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I have to do a lot of excel manipulation. Part of what i was thinking of storing is things like locations of cells or words or phrases that would be manipulated. The reason behind my thinking is so if someone changes the spreadsheets that it would be easier to change the references to the locations. Things like that. I am trying to make things more modular and easier to pass on to my successor.

    Where do you keep all the global constants/variables?

    @Bull
    Do you have an example of the config table?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Like @pbaldy, I save all global variables in a standard module so they can easily be found.

    As my commercial apps are in use by multiple clients with their own network settings, I also save configuration data in a table with 3 fields: ItemID, ItemName, ItemValie
    However, unlike BullSchmidt's suggestion, each item forms a separate record so I can add as many items as necessary without needing to change the table design.
    To retrieve the data, I create user defined functions with DLookup expressions.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Colin,

    Do you have an example i could see? I am sorry I am having a hard time picturing what you are saying.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by NightWalker View Post
    Colin,
    Do you have an example i could see? I am sorry I am having a hard time picturing what you are saying.
    I have many such examples such as this one:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	100.9 KB 
ID:	41556

    Some values have been redacted for security

    And here's an example of the type of table suggested by Bullschmidt which I don't recommend as it isn't normalised

    Click image for larger version. 

Name:	Capture2.jpg 
Views:	10 
Size:	25.4 KB 
ID:	41557
    With that structure, every new item requires a change in table design.

    Hope that helps
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Getting whole value from a string variable
    By Always_Learning in forum Programming
    Replies: 4
    Last Post: 09-30-2016, 06:57 AM
  2. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  3. Replies: 3
    Last Post: 09-16-2013, 01:05 PM
  4. Replies: 3
    Last Post: 05-28-2013, 12:53 PM
  5. Replies: 3
    Last Post: 12-02-2011, 04:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums