Create a Constants table in VB code

[mpfohl]

I need to create a constants table that exists entirely in VB code.

The table consists of 3 columns and 5 rows
I want the table to look like this:

ID Start Finish
1 3/1/07 3/5/07
2 3/6/07 4/10/07
....etc.


I then want to have code to pull out values in the table e.g.
GetStart(1) would use the Dlookup function to return the value in the
"Start" from the row with id "1". I'm pretty confident I can program
this portion of it.

What I don't know how to do is declare this table to exist in VB. Do
I want a table? Do I want an array instead? What is the best way to
go about this?

Thanks

 

[Bob Hairgrove]

I need to create a constants table that exists entirely in VB code.

The table consists of 3 columns and 5 rows
I want the table to look like this:

ID Start Finish
1 3/1/07 3/5/07
2 3/6/07 4/10/07
...etc.


I then want to have code to pull out values in the table e.g.
GetStart(1) would use the Dlookup function to return the value in the
"Start" from the row with id "1". I'm pretty confident I can program
this portion of it.

What I don't know how to do is declare this table to exist in VB. Do
I want a table? Do I want an array instead? What is the best way to
go about this?

Thanks

Well, DLookup doesn't work except with tables and select queries, so you'd have
to use different programming methods to get to the data if it were totally
contained within VBA code. And VBA doesn't allow you to define an array of
constants because there is no valid syntax to initialize their values at
declaration time (in C or C++, you can). Therefore, you will most likely be
happier using a table.

Whenever you define constants in VBA code, you will have to change them within
the VBA code modules. If the front end is split from the back end, and you have
15 clients sharing the same data, for example, then each client will (should!)
have their own copy of the code modules. That means that whenever one of the
"constant" values needs to change, you will have to update all 15 client MDB
files (or maybe 150 as the case may be, although that is not a realistic figure
for applications which use Access as a back end). Not a very pretty scenario
(especially if you have distributed MDE files to the clients)!

However, you CAN store these "constant" values in a table residing in theback
end (i.e. one single MDB file containing all the data needed by the clients) and
share them by having the clients link to the tables. That way, whenever
something changes (believe me, something WILL change sooner or later!), you will
have only one update to do (and you can use DLookup as well).

However, as to the "constantness" of the data, you have to fake it by using the
built-in permissions mechanisms of Access. This in itself might be more overkill
than you are willing to accept. But if your users are few in number and not
malicious, then you could probably get away with just putting the tables with
the "constant" data into a separate MDB file and linking them into the other
applications. Set the file properties of that MDB in Windows to read-only, and
nobody will be able to change the data in it (unless they know how to reset the
Windows read-only flag -- which is pretty easy to do, of course <g>).

 

[John W. Vinson]

I need to create a constants table that exists entirely in VB code.

Then use an Array.

John W. Vinson [MVP]

 

[mpfohl]

Thanks but I don't think that will work.

I do actually want a table of Constant values. The user will not
change it, I will in the VB code moduel itself right before
distribution. The list of 5 constant start/stop dates will be
different for each user, but once I distribute teh program, those
dates will not change.

I would prefer not to have an actual backend on the table because I
don't want the user to be able to modify the contents or really even
know it exists.

Thus, if there is a way to have an array or table that lives only in
the VB module which i can call from, it is safest to reside there.

There has to be some way to simply declare a table that exists in the
VB code, in the same way I can declare a constant string to exist in
the vb code which I can then call elsewhere in the program.

Any thoughts?

 

[mpfohl]

Then use an Array.

John W. Vinson [MVP]

Can you help me figure out how to declare the array in the module? I
think it is something like this:
If i call the array iarray

iarray(1, 1) = "1/15/07" 'start
iarray(1, 2) = "2/15/07" 'finish

iarray(2, 1) = "2/16/07" 'start
iarray(2, 2) = "3/15/07" 'finish

But that isn't working.

 

[John W. Vinson]

Can you help me figure out how to declare the array in the module? I
think it is something like this:
If i call the array iarray

iarray(1, 1) = "1/15/07" 'start
iarray(1, 2) = "2/15/07" 'finish

iarray(2, 1) = "2/16/07" 'start
iarray(2, 2) = "3/15/07" 'finish

But that isn't working.

Actually arrays are zero based unless you use Option Base 1. You may want to
explicitly dim the array as being of Date type:

Dim iArray(24,1) As Date

and use

iArray(0,0) = #1/15/07# 'start
iArray(0,1) = #2/15/07# 'finish

etc.

The # delimiter specifies a date constant - dates are NOT text strings.

I do have to agree with Bob that a (local, in the frontend) table is going to
be a lot easier to manage. You can hide it from the user; it will be static,
efficient, easy to update when needed but out of view of users; unaffected by
changes in the backend... many advantages.

John W. Vinson [MVP]

 

[mpfohl]

Thanks for the help.

I'm still having trouble with the code:
iArray(0,0) = #1/15/07# 'start
iArray(0,1) = #2/15/07# 'finish

I think i need to initialize the array all at once in order to use it
in a module, don't I? or can i just have the inilization statement
seperate from the decleration statement some how?

 

[John W. Vinson]

Thanks for the help.

I'm still having trouble with the code:
iArray(0,0) = #1/15/07# 'start
iArray(0,1) = #2/15/07# 'finish

I think i need to initialize the array all at once in order to use it
in a module, don't I? or can i just have the inilization statement
seperate from the decleration statement some how?

You can declare the array, using

Global iArray(25,1) As Date

at the top of a general module, so that the array will be available throughout
the database; and initialize it using inline code per your example, or (if
feasible) in a loop. Just put the initialization code in the Load event of
your startup form or some other suitable event so that it gets executed upon
opening the database.

John W. Vinson [MVP]

 

[mpfohl]

thanks