Copy and Linking Access Front End to SQL Back End

[Typehigh]

I'll try and keep this short and sweet.
MS-Access is my front end (FE) and SQL Server is my back end (BE).
I created an Access application and then migrated the tables to a SQL
Server.

Periodically, the application needs to create new tables based on a
specific set of fields. Instead of tediously going through all the
definitions and indexing processes, I created a tblTemplate table. This
exists in the SQL BE and is linked to the Access FE.

When I execute:

DoCmd.CopyObject , newTableName , acTable, "dbo_tblTemplate"

all I get is a copy of the link. Inserting data into the "newTableName"
actually drops the data into the dbo_tblTemplate table (not good).

Here's what I need to have happen:

1) Copy the SQL BE template table to a new table which will then exist
on the SQL BE.
2) Link the new table back to the Access FE.
3) Do everything with VBA code so it is transparent to the user and
involves no manual processes.

Thanks in advance to all of you gurus who can bail me out of this mess.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sound like a bad design. It should be that the DB design is set by the
DB designer & no tables are added by the users.

Anywho... You have to create the table in the SQL environment. If
you're using an .mdb app instead of an .adp app you can use a pass thru
query to create the table. Create the table using DDL. Of course the
user has to have table creation permission on the SQL Server.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/Dc7YechKqOuFEgEQJ7nwCgwJxxoWA2I+L3oc+aWpN9BCXJ3/sAoPSe
mY8ZmluzHWDCX3YnnOVIStxt
=ZPJG
-----END PGP SIGNATURE-----

Typehigh wrote:

I'll try and keep this short and sweet.
MS-Access is my front end (FE) and SQL Server is my back end (BE).
I created an Access application and then migrated the tables to a SQL
Server.

Periodically, the application needs to create new tables based on a
specific set of fields. Instead of tediously going through all the
definitions and indexing processes, I created a tblTemplate table. This
exists in the SQL BE and is linked to the Access FE.

When I execute:

DoCmd.CopyObject , newTableName , acTable, "dbo_tblTemplate"

all I get is a copy of the link. Inserting data into the "newTableName"
actually drops the data into the dbo_tblTemplate table (not good).

Here's what I need to have happen:

1) Copy the SQL BE template table to a new table which will then exist
on the SQL BE.
2) Link the new table back to the Access FE.
3) Do everything with VBA code so it is transparent to the user and
involves no manual processes.

Thanks in advance to all of you gurus who can bail me out of this mess.