Alter Back-End Table from Front-End

  • Thread starter Thread starter QB
  • Start date Start date
Q

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
 
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
 
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


Jeff Boyce said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
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

QB said:
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


Jeff Boyce said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
QB said:
I tried DAO, but it gives an error that it cannot be performed on linked
tables.
Click to expand...

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
 
Back
Top