Setting Attributes in SQL Back-End DB through VBA

[Marcel Stoop]

Hi all

My Access Application uses a MS SQL 2000 Server as back-End DB.
Question:

Is it possible through VBA Code to set following attributes within the MS
SQL Server DB:
- Pimary Key
- Identity
- Identity Seed
- Identity Increment

If so, could you please give me an example of Code how to do this.

Purpose: with the many tables in the DB it is not that comfortable to set
those settings always manually, as soon as I have to create a new DB.

Thanks for the help.

Cheers
Marcel

 

[Graham R Seach]

Marcel,

It's pretty easy to do, but it is usually far easier to do it using the SQL
Server Enterprise Manager.

If you're trying to create a copy of tables stored in some other database,
then use SQL Server Data Transformation Services (DTS) to import the table
structure (and optionally their data too).

If you're bound and determined to use DDL, see SQL Server Books Online (or
even the Access Help) for "CREATE TABLE". Then use the ADO Command object to
do the work. But I strongly recommend doing it from Enterprise Manager.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia