Alter Back-End Table from Front-End

[QB]

How can I Alter a Back-End Table from the Front-End?

I need to add a column (Integer) to an existing table.

I tried DAO, but it gives an error that it cannot be performed on linked
tables.

I tried executing a DoCmd.RunSQL using the ALTER [] IN 'BackEndDB' but it
keep given a Synthax Error. When I Check it in the SQL Editor It always goes
to the 'IN' part of the SQL Statement?

Thank you for the help,

QB

 

[Jeff Boyce]

Not!

If you use an Access front-end file and link to a back-end, you can't. And
that's as it should be ... really now, do you want anyone who can link to
the data to be able to change it?!

You'll need to open the back-end to change the back-end.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[QB]

Not! Not!

I am not worried about other people trying to change it.

I am simply trying to create a vba method for pushing some updates to the
back-end of a database. I can even use a admin db that I create just for
this purpose, so no one else has access to the code. Most of the updates,
have been straight forward (query based update/append), but in 2 specific
cases I need to alter the table structure. How would you propose I go about
this?

Directly editing the back-end is not an option I wish to use for various
reasons. I truly was looking for a VBA alternative?

QB

Not!

If you use an Access front-end file and link to a back-end, you can't. And
that's as it should be ... really now, do you want anyone who can link to
the data to be able to change it?!

You'll need to open the back-end to change the back-end.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I Alter a Back-End Table from the Front-End?

I need to add a column (Integer) to an existing table.

I tried DAO, but it gives an error that it cannot be performed on linked
tables.

I tried executing a DoCmd.RunSQL using the ALTER [] IN 'BackEndDB' but it
keep given a Synthax Error. When I Check it in the SQL Editor It always
goes
to the 'IN' part of the SQL Statement?

Thank you for the help,

QB

 

[Sky]

Open the back end database directly with a database variable:

Dim dbBackEnd As DAO.Database
Set dbBackEnd = OpenDatabase("My Path to the Back End")
dbBackEnd.Execute "Alter Table MyTableName etc."
Set dbBackEnd.Execute = Nothing

or you can use DAO methods on dbBackEnd as well.

- Steve

 

[Jeff Boyce]

Check Allen Browne's website for ideas on this.

Still not a good idea, but if you're determined ... <g>

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Not! Not!

I am not worried about other people trying to change it.

I am simply trying to create a vba method for pushing some updates to the
back-end of a database. I can even use a admin db that I create just for
this purpose, so no one else has access to the code. Most of the updates,
have been straight forward (query based update/append), but in 2 specific
cases I need to alter the table structure. How would you propose I go
about
this?

Directly editing the back-end is not an option I wish to use for various
reasons. I truly was looking for a VBA alternative?

QB

Not!

If you use an Access front-end file and link to a back-end, you can't.
And
that's as it should be ... really now, do you want anyone who can link to
the data to be able to change it?!

You'll need to open the back-end to change the back-end.

Regards

Jeff Boyce
Microsoft Office/Access MVP

How can I Alter a Back-End Table from the Front-End?

I need to add a column (Integer) to an existing table.

I tried DAO, but it gives an error that it cannot be performed on
linked
tables.

I tried executing a DoCmd.RunSQL using the ALTER [] IN 'BackEndDB' but
it
keep given a Synthax Error. When I Check it in the SQL Editor It
always
goes
to the 'IN' part of the SQL Statement?

Thank you for the help,

QB

 

[Tony Toews [MVP]]

I tried DAO, but it gives an error that it cannot be performed on linked
tables.

As Sky points out you need to use a database variable open to the BE
MDB.

Updating an Access Backend MDBs structure using VBA code
http://www.granite.ab.ca/access/backendupdate.htm

The Compare'Em utility has made this process much easier.
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

In both cases I would suggest keeping a version number of the FE and
BE in a table or property thus helping you to figure out when to run
the code or not allow a new FE to be run against an old format BE.

Also make a backup before doing any such updates, double check the
number of records in the new tables after the conversion and include a
mechanism to ensure users aren't in the backend when all this starts
or during the process.

Tony