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

[Bob Barrows [MVP]]

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

Scotter wrote:[color=blue]
> 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[/color]

There is. It's called "replicatio n" and it's built into Access.
..

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Steven Burn]

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

40 tables per DB?...... if you'd said that when I first asked, I'd have shut
up about the amount of DB's <g>

--

Regards

Steven Burn
Ur I.T. Mate Group

https://www.it-mate.co.uk

Keeping it FREE!


"Scotter" <spam@spam.co m> wrote in message
news:06I4d.1668 0$Qb.14620@fe2. texas.rr.com...[color=blue]
> hehehee now you are just baiting me, right?
> "one table for each application"?
> You have no idea what my application does and why it requires about 40
> different tables because this is a RELATIONAL database. Maybe you are[/color]
living[color=blue]
> in 1978 where all databases were flat?
> Will someone, anyone here who understands that ONE SOLUTION DOES NOT FIT[/color]
ALL[color=blue]
> please step forward and correct these guys and hopefully they will get off
> my back.
>
>
> "Steven Burn" <pvt@noyb.com > wrote in message
> news:edhzzsboEH A.3728@TK2MSFTN GP09.phx.gbl...[color=green]
> >I read it just fine..... and still don't see why you would need a[/color][/color]
seperate[color=blue][color=green]
> > database for each application when you could just as easily have one
> > database, and one table for each application?
> >
> > --
> >
> > Regards
> >
> > Steven Burn
> > Ur I.T. Mate Group
> > www.it-mate.co.uk
> >
> > Keeping it FREE!
> >
> >
> > "Scotter" <spam@spam.co m> wrote in message
> > news:gUH4d.1662 1$Qb.1956@fe2.t exas.rr.com...[color=darkred]
> >> I see you saying absolutely no need for 20 databases and I'm wondeirng[/color][/color][/color]
if[color=blue][color=green][color=darkred]
> >> maybe you confused my description of my situation to be that I'm[/color][/color][/color]
running[color=blue][color=green]
> > 20[color=darkred]
> >> databases PER application?
> >> If that were the case then yeah maybe but please re-read to find that I
> >> am
> >> running ONE database PER ONE application.
> >>
> >> "Steven Burn" <pvt@noyb.com > wrote in message
> >> news:%239dEplbo EHA.2032@TK2MSF TNGP10.phx.gbl. ..
> >> > "Scotter" <spam@spam.co m> wrote in message
> >> > news:jvH4d.1661 4$Qb.10305@fe2. texas.rr.com...
> >> >> Trust me your question is irrelevant to what I need help finding.
> >> >
> >> > It's not irrelevant at all...... there's absolutely no need to have[/color][/color][/color]
20[color=blue][color=green][color=darkred]
> >> > database's.... (20 _tables_ in ONE database maybe (and even then,[/color][/color][/color]
this[color=blue][color=green][color=darkred]
> >> > shouldn't be necessary), but definately not 20 database's)
> >> >
> >> >> If you are really curious, though, see my answer at
> >> >> microsoft.publi c.access.3rdpar tyusrgrp.
> >> >
> >> > I don't visit that NG ;o)
> >> >
> >> >> I'm not sure of exact protocol for the cross-posting I'm doing and I
> >> >> don't
> >> >> want to piss people off.
> >> >
> >> > See the link Ray posted......
> >> >
> >> > http://aspfaq.com/5004
> >> >
> >> > --
> >> >
> >> > Regards
> >> >
> >> > Steven Burn
> >> > Ur I.T. Mate Group
> >> > www.it-mate.co.uk
> >> >
> >> > Keeping it FREE!
> >> >
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

[dlbjr]

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

If the databases are identical for all applications then all applications are capable of running
from one main database.
The key is which data belongs to which application in the database. This can be easily managed. Then
you would be changing just one database. I'm a DBA for Oracle and Microsoft SQLServer systems in a
large environment. So, you can either take advice from people truly trying to help you. Or struggle.
Why do you think not many have struggled to build such a tool you are requesting?


'dlbjr
'Pleading sagacious indoctrination!

[Scotter]

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

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.


"dlbjr" <oops@iforgot.c om> wrote in message
news:Om$F54boEH A.1460@TK2MSFTN GP12.phx.gbl...[color=blue]
> If the databases are identical for all applications then all applications
> are capable of running
> from one main database.
> The key is which data belongs to which application in the database. This
> can be easily managed. Then
> you would be changing just one database. I'm a DBA for Oracle and
> Microsoft SQLServer systems in a
> large environment. So, you can either take advice from people truly trying
> to help you. Or struggle.
> Why do you think not many have struggled to build such a tool you are
> requesting?
>
>
> 'dlbjr
> 'Pleading sagacious indoctrination!
>
>[/color]

[Ray Costanzo [MVP]]

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

Send me links to your product. I'm on all the software committees at the
bank where I work. ray@securitynat .com

Ray at work

"Scotter" <spam@spam.co m> wrote in message
news:_pI4d.1673 3$Qb.6786@fe2.t exas.rr.com...[color=blue]
> 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.
>
>
> "dlbjr" <oops@iforgot.c om> wrote in message
> news:Om$F54boEH A.1460@TK2MSFTN GP12.phx.gbl...[color=green]
>> If the databases are identical for all applications then all applications
>> are capable of running
>> from one main database.
>> The key is which data belongs to which application in the database. This
>> can be easily managed. Then
>> you would be changing just one database. I'm a DBA for Oracle and
>> Microsoft SQLServer systems in a
>> large environment. So, you can either take advice from people truly
>> trying to help you. Or struggle.
>> Why do you think not many have struggled to build such a tool you are
>> requesting?
>>
>>
>> 'dlbjr
>> 'Pleading sagacious indoctrination!
>>
>>[/color]
>
>[/color]

[dlbjr]

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

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!

[Scotter]

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

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.


"dlbjr" <oops@iforgot.c om> wrote in message
news:u7DxcecoEH A.3460@tk2msftn gp13.phx.gbl...[color=blue]
> 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!
>
>[/color]

[Tony Toews]

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

"Scotter" <spam@spam.co m> wrote:
[color=blue]
>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.[/color]

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

[Andrew Zamkovoy]

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

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.CreateOb ject("ADODB.Con nection")

For I = 1 To 5
goCN.Connection String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=" & MyDB (I)
goCN.CursorLoca tion = 3
goCN.CommandTim eout = 60
goCN.Open

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

goCN.Close
Next

Set goCN = Nothing
%>

With best regards,

--

Should you have any questions, please don't hesitate to contact me.
If you response to an email, please quote the complete message.

http://1click.lv

"Scotter" <spam@spam.co m> wrote in message
news:EDG4d.1658 8$Qb.12964@fe2. texas.rr.com...[color=blue]
> 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[/color]
either:[color=blue]
> (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[/color]
(b)[color=blue]
> but I'd rather find something that is much more slick and complete.
> --
> Scotter
>
>[/color]

[Tony Toews]

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

"Scotter" <spam@spam.co m> wrote:
[color=blue]
>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.[/color]

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

[Scotter]

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

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.CreateOb ject("ADOX.Colu mn")
if (left(s_new_fie ld_name,2)="ID" ) then set objIndex =
server.CreateOb ject("ADOX.Inde x")
objColumn.Name = s_new_field_nam e
objColumn.Type = s_new_field_typ e
if (s_new_field_si ze<>"") then objColumn.Defin edSize =
s_new_field_siz e
'if (s_new_field_re quired<>"") then objColumn.Prope rties("Nullable ")
= true
'if (s_new_field_re quired<>"") then
objColumn.attri butes=s_new_fie ld_required
set objColumn.Paren tCatalog=Cat ' must set before setting properties
if (s_new_field_de fault_value<>"" ) then
objColumn.prope rties("Default" )=s_new_field_d efault_value
if (s_new_field_ty pe <> adBoolean) then
if (s_new_field_nu ll_ok=true) then
objColumn.prope rties("Nullable ")=s_new_field_ null_ok
end if

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

'.properties("A utoincrement")= true
objTable.Column s.Append objColumn
set objColumn=nothi ng

Response.Write( "<td>field added: " & s_new_field_nam e & "</td>")
else
Response.Write( "<td>field already exists</td>")
end if

[Scotter]

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

Ah thanks, Tony, for the validation :)

"Tony Toews" <ttoews@teluspl anet.net> wrote in message
news:1ot6l01jid 9nph97uq7a7p2lp i5n18mc8d@4ax.c om...[color=blue]
>
> 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[/color]

[Bob Barrows [MVP]]

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

Scotter wrote:[color=blue]
> ok NOW we are getting down to business.
> Since no one knows of software to do this,[/color]

What's wrong with builtin replication?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Ray Costanzo [MVP]]

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

As much as I admire SQL Server, DB/2 is still king in the banking world.

Ray at home

"Scotter" <spam@spam.co m> wrote in message
news:F5K4d.1684 0$Qb.9583@fe2.t exas.rr.com...[color=blue]
> 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.
>
>
> "dlbjr" <oops@iforgot.c om> wrote in message
> news:u7DxcecoEH A.3460@tk2msftn gp13.phx.gbl...[color=green]
>> 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!
>>
>>[/color]
>
>[/color]

[Jeff Cochran]

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

On Thu, 23 Sep 2004 22:04:26 GMT, "Scotter" <spam@spam.co m> wrote:
[color=blue]
>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.[/color]

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