Updating the structure of a linked database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave S - 21 May 2005 16:37 GMT

I have used the Database Splitter utility to create a "_be" version of my
database and moved that to the server. Other computers have code version
with tables linked to the server _be.
I had previously used VB code in my start up routine that checked the
current code version against the data version # stored in a table. This code
will now not run due to the restriction against updating a linked table. I'm
using a "dbs.Execute 'ALTER TABLE ... '" command to do the table change.
Any suggestions on how to update the table structure without manually doing
it to the _be on the server?

Reply to this Message

Douglas J. Steele - 21 May 2005 16:42 GMT

I don't understand what you were doing with ALTER TABLE.

If all you're trying to do is relink your tables, see
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have used the Database Splitter utility to create a "_be" version of my
> database and moved that to the server. Other computers have code version
[quoted text clipped - 8 lines]
> doing
> it to the _be on the server?

Reply to this Message

Dave S - 21 May 2005 16:49 GMT

Thanks for your reply.

I'm using the ALTER TABLE to add new fields to an existing table. As the
system evolves I'm sure I'll also be adding new tables.

> I don't understand what you were doing with ALTER TABLE.
>
[quoted text clipped - 13 lines]
> > doing
> > it to the _be on the server?

Reply to this Message

Douglas J. Steele - 21 May 2005 22:45 GMT

Ah, I see.

You don't need to use ALTER TABLE on the front end. Make the changes on the
back end, and they should be automatically reflected in the front end. (If
not, use the code I pointed to earlier to relink the tables)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for your reply.
>
[quoted text clipped - 23 lines]
>> > doing
>> > it to the _be on the server?

Reply to this Message

Dave S - 22 May 2005 16:09 GMT

OK Doug - thanks for the assist.

> Ah, I see.
>
[quoted text clipped - 29 lines]
> >> > doing
> >> > it to the _be on the server?

Reply to this Message

dave k - 30 May 2005 07:00 GMT

I understand your question and have the same issue. Did you ever figure out
how to programmically update the backend? If the backend is not accessible
to me or is installed at 100 locations, how can you update the backend
structure when you send updated front-ends with additional features?

Any luck answering?

> OK Doug - thanks for the assist.
>
[quoted text clipped - 31 lines]
> > >> > doing
> > >> > it to the _be on the server?

Reply to this Message

Tim Ferguson - 30 May 2005 11:48 GMT

=?Utf-8?B?ZGF2ZSBr?= <davek@discussions.microsoft.com> wrote in
news:9C5B7B4F-072E-491B-BDCD-A15E44654C52@microsoft.com:

> I understand your question and have the same issue. Did you ever
> figure out how to programmically update the backend? If the backend
> is not accessible to me or is installed at 100 locations, how can you
> update the backend structure when you send updated front-ends with
> additional features?

' get exclusive, read-write access to the back end
set db = OpenDatabase(strPathToBackEnd, True, False)

' create the command
strSQL = "ALTER TABLE MyTable etc "

' and do it
db.Execute strSQL, dbFailOnError

The equivalent in ADO uses connection strings and command objects, but
the same sort of thing happens.

Hope that helps

Tim F

Reply to this Message

dave k - 31 May 2005 06:25 GMT

Helps a lot!

Thanks

> =?Utf-8?B?ZGF2ZSBr?= <davek@discussions.microsoft.com> wrote in
> news:9C5B7B4F-072E-491B-BDCD-A15E44654C52@microsoft.com:
[quoted text clipped - 20 lines]
>
> Tim F

Reply to this Message