HELP PLEASE - need app to modify multiple MDB table designs simultaneously

  • Thread starter Thread starter Scotter
  • Start date Start date
If you have banks as clients and you are building these applications for banking systems. I suggest
you get away from Microsoft Access.You may want to look into the Sarbanes-Oxley Act and find that
you must be providing tractability and encryption throughout your web app. Only certain database
solutions can be used for certain data such as banking information.You as the developer will be help
accountable.

Enough said from me.

'dlbjr
'Pleading sagacious indoctrination!
 
not tracking money.
Banking *associations* and other bank-related customers.
If I were hosting an actual BANK, I'd be using SQL Server back end.
 
Scotter said:
Yes, I understand why you and the others are recommending I make this
application so it runs multiple web sites instead of just one per MDB.
Tell me something: If you wrote an application to run with an MDB back-end
and you planned on keeping it MS-Access based and this application is a web
CMS, and some of your web clients are big banking institutions and they do
not want their data comingled with other company's data... and some clients
want to have access to their particular MDB... and... and .... and....
would you then put all your eggs in one basket?
Another reason is that these *identical* MDBs sometimes do not stay
identical. I inevitably have clients who want their version of HotKey
customized to run different in some ways and oh boy then it departs from
being like all the others and yeah that presents headaches of it's own but
hey they pay more for support.
Yeah, I realize I could still design each app so it uses one MDB and has
*potential* to host more than one URL. But please please please trust that I
have plenty of other reasons, too, to keep them separate in the case of this
particular application.
If you truly are a pro then you know that there are typically exceptions to
rules.
Click to expand...

Makes sense to me. It can be more work in one way, as you are finding
out but given your situation having separate MDBs makes a lot of
sense.

Also given that Access doesn't handle a large volume of concurrent
updates/inserts, this minimizes the risks of corruptions and reduces
it to just one of the clients at a time.

Do note that "large volume of concurrent updates/inserts" varies
betweens systems, developers and other variables. And differs between
IIS usage vs thick client, ie Access FE MDB/MDE usage.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Hi there,

My telepathic functions are not working properly, but I suppose you want
this:

<%
Dim MyDB, MyUpdates
Redim MyDB (5)
Redim MyUpdates (3)

MyDB (1) = "somewhere/mydb1.mdb"
MyDB (2) = "somewhere/mydb2.mdb"
MyDB (3) = "somewhere/mydb3.mdb"
MyDB (4) = "somewhere/mydb4.mdb"
MyDB (5) = "somewhere/mydb5.mdb"

MyUpdates (1) = "INSERT INTO .........."
MyUpdates (2) = "CREATE TABLE .........."
MyUpdates (3) = "ALTER TABLE .........."

Set goCN = Server.CreateObject("ADODB.Connection")

For I = 1 To 5
goCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & MyDB (I)
goCN.CursorLocation = 3
goCN.CommandTimeout = 60
goCN.Open

For N = 1 To 3
goCN.Execute MyUpdates (N)
Next

goCN.Close
Next

Set goCN = Nothing
%>

With best regards,
 
Scotter said:
Okay I think my title line was worded misleadingly. So here goes again.

I've got quite 20 identical MDB files running on an IIS5 server.
From time to time I need to go into various tables and add a field or two.
It would be great if there were an application out there that could either:
(a) sync all MDB designs (and/or data) designated to match one I've added
some fields/tables to
OR
(b) go into all designated MDBs and create new field(s) or table(s).

I've began writing something like this that right now just does part of (b)
but I'd rather find something that is much more slick and complete.
Click to expand...

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

This assumes you can run Access on the IIS server or link to the
directory containing the Access MDBs as a share from another system
which does have Access installed.

If access via is via a VPN or T1 or link slower than 10 mbps as a
share this might be too slow to effectively do the job. Depending on
the number of records in the table. Access does not work well over
VPNs, T1s and such unlike SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
ok NOW we are getting down to business.
Since no one knows of software to do this, yeah, I've written something.
My latest lil hitch is how to tell a new field to have an index.
Here is the part of my code that does that part.
See the comment that says " 'turn index on "? That is where I need to
somehow tell it to create an index for this new column.

THE CODE:

if (n_found=0) then
set objColumn = server.CreateObject("ADOX.Column")
if (left(s_new_field_name,2)="ID") then set objIndex =
server.CreateObject("ADOX.Index")
objColumn.Name = s_new_field_name
objColumn.Type = s_new_field_type
if (s_new_field_size<>"") then objColumn.DefinedSize =
s_new_field_size
'if (s_new_field_required<>"") then objColumn.Properties("Nullable")
= true
'if (s_new_field_required<>"") then
objColumn.attributes=s_new_field_required
set objColumn.ParentCatalog=Cat ' must set before setting properties
if (s_new_field_default_value<>"") then
objColumn.properties("Default")=s_new_field_default_value
if (s_new_field_type <> adBoolean) then
if (s_new_field_null_ok=true) then
objColumn.properties("Nullable")=s_new_field_null_ok
end if

if (left(s_new_field_name,2)="ID") then
'turn index on
end if

'.properties("Autoincrement")=true
objTable.Columns.Append objColumn
set objColumn=nothing

Response.Write("<td>field added: " & s_new_field_name & "</td>")
else
Response.Write("<td>field already exists</td>")
end if
 
As much as I admire SQL Server, DB/2 is still king in the banking world.

Ray at home
 
I really wish you all would stop second guessing how/why I built the
application as I did and just tell me of software that does what I need if
you know if it existing. If you can't contribute then please don't badger me
with advice about an application you have no idea about.
Click to expand...

There are no commercial applications designed to do what you do using
ASP. Since you posted to six different groups, perhaps you can now
drop the ASP groups and find a solution elsewhere. Or if it must be
ASP, stop posting to any but the relevant one, asp.db.

For your purposes, you will likely need to write your own
install/patch routines, but commercial patch integration software may
suit your needs. It's not ASP though.

Jeff
 
ok NOW we are getting down to business.
Since no one knows of software to do this, yeah, I've written something.
My latest lil hitch is how to tell a new field to have an index.
Here is the part of my code that does that part.
See the comment that says " 'turn index on "? That is where I need to
somehow tell it to create an index for this new column.
Click to expand...

For that simple a change replicating the structure can be done from
within Access. Indexes and all. Plus if your databases were designed
for it, you could replicate saved queries and update a significant
amount of code as well with the same process. Do you have to use
ASP/.NET for your changes (you posted to both ASP and .NET groups...)?

Jeff
 
To add a index:


if (left(s_new_field_name,2)="ID") then


objIndex.NAME = "myindex"
objIndex.COLUMNS.APPEND(s_new_field_name)
objTable.INDEXES.APPEND(objIndex)

end if

Also, I agree Andrew that you need to repeat these command on all tables
touched in your applications.

Luke
 
Bob Barrows said:
What's wrong with builtin replication?
Click to expand...

Replication works on the data contents as well.

If each database has data for different clients, you're going to end up with
all of the data being copied into all of the databases.
 
Douglas said:
Replication works on the data contents as well.

If each database has data for different clients, you're going to end
up with all of the data being copied into all of the databases.
Click to expand...

My mistake, I guess. I thought there was a way to do selective replication
(specifically, Design replication), but it's been so long since I worked
with Access ...

Bob Barrows
 
Bob Barrows said:
My mistake, I guess. I thought there was a way to do selective replication
(specifically, Design replication), but it's been so long since I worked
with Access ...
Click to expand...

Actually, I'm not a replication guru, so it's possible you're right that
it's possible. However, I don't think setting everything up as replicable is
necessarily the way to go.
 
There is Partial Replication, where each "client" .MDB get data only for
that client.

There is a lot of advice against using replication for design changes,
however. Particularly from Michael Kaplan.

I have found you avoid a lot of problems if you follow one rule: Never
CHANGE a replicated object. Create a new one, and delete the old.

So for many application, a batch design change file may be the better
approach on non-replicated databases.

Stephen Rasey
WiserWays, LLC
Houston, TX
http://excelsig.org
 
Back
Top