migrating SQL table properties to Access Front end

  • Thread starter Thread starter skaclmbr
  • Start date Start date
S

skaclmbr

I have set up a MSDE backend (to eventually be migrated to SQL Server), to
be accessed from an Access database (.mdb, NOT a access project) front end.
I have already set up some code to refresh the links to the SQL Server
tables upon opening the access db, but it does not copy over the field
properties (other than the basics: field type, field name, etc.). I would
like to migrate over the values for field descriptions and the lookup
settings for a number of fields. Is there any way of doing this when
attaching a linked table, or even retrieving the values with VB code and
setting the properties in the newly attached tables?

Any help would be appreciated.

Thanks,
Scott Anderson
 
Once you've established the connection, you can issue the following queries
against SQL Server/MSDE:

To return the table's SQL Server Description property...
SELECT Value FROM ::fn_listextendedproperty (NULL, 'user', 'dbo',
'table', strTableName, NULL, NULL)

To return the column's SQL Server Description property...
SELECT Value FROM ::fn_listextendedproperty(NULL, 'user', 'dbo',
'table', strTableName, 'column', strColumnName)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top